OraFAQ Forum: SQL & PL/SQL » spool a file by passing a parameter

Home » SQL & PL/SQL » SQL & PL/SQL » spool a file by passing a parameter

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

spool a file by passing a parameter [message #24085]

Fri, 24 January 2003 14:04

ravi_kiran99
Messages: 8
Registered: December 2002

Junior Member

Hi
Does any one know how to spool a file in a sql script ,by passing the filename as a parameter to that script.

Thanks for your help
Ravi

Report message to a moderator

Re: spool a file by passing a parameter [message #24087 is a reply to message #24085]

Fri, 24 January 2003 14:13

Jon
Messages: 483
Registered: May 2001

Senior Member

simply craete the script as

spool &1;
select * from table_name;
spool off;

then at the SQL prompt
SQL>@c:tempspool c:tempspool_out.txt

Report message to a moderator

Re: spool a file by passing a parameter [message #24090 is a reply to message #24087]

Fri, 24 January 2003 14:29

ravi_kiran99
Messages: 8
Registered: December 2002

Junior Member

Hi..
I did try that , and I get the following error.
and I get the following error. Can we pass a parameter to the spool command.
------------------

SP2-0332: Cannot create spool file.

no rows selected

not spooling currently

Report message to a moderator

Re: spool a file by passing a parameter [message #24099 is a reply to message #24087]

Sat, 25 January 2003 00:50

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

The error message indicates that you have insufficient privileges to create the file in the directory. It has accepted the parameter; It just can't create the file. Try it without any directory paths, so that you know you have sufficient privileges. In the example below, spool_out.txt is the value that is passed to the spool.sql file for the paramter &1, which is the file name to spool to.

-- edit spool.sql and type:
spool &1
select * from dual;
spool off
-- and save the file

-- from the SQL prompt, type:
SQL> @spool spool_out.txt

-- to view the results, type:
SQL> EDIT spool_out.txt

Report message to a moderator

Previous Topic:	sql text summary
Next Topic:	v$sqlarea for a particular session

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 06 04:26:51 CDT 2025