I am not sure whether I can use SPOOL in PL/SQL or not? Basically I need to put my output in files. There is a UTL_FILE package for this, but we are not allowed to set this in init.ora file. Anyone has idea? Thanks so much.

UTL_FILE is the way for large data.

But if you have small set of data, you can use dbms_output.put_line to write data out which can be spooled to a file.

Thanks for replying.

Here is my code:

SPOOL test_file
dbms_output.put_line(rec_spe.mkt_seqnum || ',' || rec_spe.market_id);
SPOOL OFF;

I got error for the above code, it complains that I am using SPOOL there.

Your help will be much appreciated.

SPOOL is a SQL Plus command, it means nothing inside a PL/SQL block (it is a syntax error there).

Do this:

SQL> SPOOL test_file
SQL> BEGIN -- PL/SQL begins
...
49 dbms_output.put_line(rec_spe.mkt_seqnum || ',' || rec_spe.market_id);
...
98 END; -- PL/SQL ends
99 /

PL/SQL procedure successfully completed.

SQL> SPOOL OFF

I see. Thanks for the explanation. It is what I want.

hi
Actually I want to use spool command inside loop of PL/SQL.
But its giving error.

Any advice plz?

create or replace procedure mane as
BEGIN
DECLARE
TYPE v_arr IS VARRAY(3) of VARCHAR2(10);
v1 v_arr;
count NUMBER(2);
BEGIN
v1 := v_arr('mae','bbb','ddd');
cnt := 1;
LOOP
spool long_cnt.lst
dbms_output.put_line('The Value of v1('||cnt||'i) is ' || v1(cnt));
count := cnt + 1;
spool off;
EXIT when cnt > 3;
end loop;
END mane;

SPOOL is SQL*Plus command and can not be used in PL/SQL. So, either use SPOOL outside of PL/SQL or use UTL_FILE. Here's a quick spooling example: