Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to use variable in Spool

Merlin128Feb 17 2010 — edited Feb 17 2010
how do I pull a sequence number from the database and use it in a sqlplus spool filename...
this is running as: sqlplus user/pass@sid@c:\file.sql

example of the file:

BEGIN
declare

V_E_NUMBER varchar2(7);

begin

SeLECT 'E'||lpad(RWP.E_NUMBER_seq.NEXTVAL,6,'0') INTO V_E_NUMBER FROM DUAL ;

end;
end;

SeLECT 'E'||lpad(RWP.E_NUMBER_seq.CURRVAL,6,'0') INTO V_E_NUMBER FROM DUAL ;
set echo off
SET HEADING OFF
SET LINESIZE 430
set verify off
SET PAGESIZE 0
SET TRIMSPOOL ON
SET timing OFF
SET termout ON
SET feedback OFF
spool d:\securedASP\CORE.BU34500.AR.'||:V_E_NUMBER||'.RECEIVD
select * from table;
spool off
This post has been answered by Frank Kulash on Feb 17 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 17 2010
Added on Feb 17 2010
2 comments
9,330 views