I agree with Tom that this is probably a bad idea, but then I've had to do this before since others said it must be done. I accomplished what you are looking for by loading the entire sql stmt into a variable and then looping through chopping as big of chunks as I could, but still paying attention to the comma delimiter. There are all kinds of pitfalls with this (which you will probably find and have to code around), but this should get you started:
select SUBSTR('1,2,3,4,5,6,7,8,9,10,11,12',1,INSTR('1,2,3,4,5,6,7,8,9,10,11,12',',',-10,1)) from dual;
You will have to use variables to keep track of the last place you started to print from, but I'm sure the above statement will give you an idea.

Thanks a lot Tom & Brett, Your answers have helped me a lot. I used Tom's approach & the INSTR as said by Brett.

I decided to generate the insert statements using dbms_output.put_line for faster transfer of data from Production.

Basically what I need is, a faster way to populate specific data from Production to other Databases. Transferring & using Dump takes a lot of time because of size, and we do not need all the data from Production. We need only some specific data from few tables, and in 9i there seems no way to export one Dump using different queries for different tables.

Can you please tell me if my approach is correct? Or there is any other Better/Easier way to transfer data faster from one database to another?? Appreciate your help.

I modified my code as below, which generates insert statements:

--------------------------------------------------------
DECLARE

CURSOR my_cursor IS SELECT * FROM TEMPLATE WHERE EXISTS (SELECT UOID FROM INSTRUMENT WHERE UOID=TEMPLATE.C_INSTRUMENT);

rec_count number := 0;
I NUMBER := 1;
colon_position NUMBER;
to_position NUMBER := 1;
instr_position NUMBER := 1;
loop_thru NUMBER;
RESULTS VARCHAR2(4000);

BEGIN

dbms_output.enable(buffer_size => 1000000);

FOR my_rec IN my_cursor
LOOP

I := 1;
to_position := 1;
instr_position := 1;

-- Result which will be printed 250 characters per line (which is max limit of DBMS_OUTPUT.PUT_LINE)
RESULTS := 'INSERT INTO TEMPLATE VALUES ('''||my_rec.uoid||''','''|| my_rec.description||''','''|| my_rec.name||''','''||my_rec.a_product||''','''||my_rec.a_product_type||''','''|| my_rec.c_instrument||''','''||my_rec.a_client_bank||''','''|| my_rec.a_oper_bk_org||''');';

-- The below 2 replace statements put NULL value between two semicolons (,,)
-- whenever a Numeric column's value is NULL, it does not display anything for that, so the statement fails
-- It is required to put NULL or ''(double quotes) to identify NULL values, below 2 statements are solution for that
RESULTS := REPLACE (RESULTS,',,',',NULL,');
RESULTS := REPLACE (RESULTS,',,',',NULL,');

loop_thru := INSTR(RESULTS,',',-1,1);
colon_position := INSTR(RESULTS,',',instr_position,1);
to_position := colon_position;

--Printing Result
WHILE instr_position <= loop_thru LOOP
--DBMS_OUTPUT.PUT_LINE('I- '||I);
--DBMS_OUTPUT.PUT_LINE('instr_position- '||instr_position);
--DBMS_OUTPUT.PUT_LINE('colon_position- '||colon_position);
--DBMS_OUTPUT.PUT_LINE('to_position- '||to_position);

DBMS_OUTPUT.PUT_LINE(SUBSTR(RESULTS, I, to_position));

I := (1 + colon_position);
instr_position := (1 + colon_position);
colon_position := INSTR(RESULTS,',',instr_position,1);
to_position := ((colon_position-I)+1);
END LOOP;

-- This additional line is to Print the last remaining statement
DBMS_OUTPUT.PUT_LINE(SUBSTR(RESULTS, I));

rec_count := rec_count + 1;

END LOOP;

END;
/
-------------------------------------------------------

One way to shorten the INSERT SQL command, in this case, where there are NULL values for a lot of columns, is to name the columns in the INSERT statement that have non-NULL values to be inserted and let the column default to NULL (assuming column is not defined with a non-NULL default value). If the INSERT statement is pretty much the same non-null columns all the time, that is easy to do, but if the number of non-null columns varies, then you'll have to write some code to format the SQL INSERT command.