Home » SQL & PL/SQL » Client Tools » Creating spool file dynamically (Solaris: Version 8.1.7.4.0 - PL/SQL Release 8.1.7.4.0 )
Creating spool file dynamically [message #472417] Fri, 20 August 2010 08:17 Go to next message
Aju
Messages: 94
Registered: October 2004
Member
Can you please guide how to creat a file in a folder based on todays date. More precisely, i need to know how to define a variable in sqlplus and assign a value to it.

Here is the code below. The code gets executed without creating a spool file.

DEFINE _DATE = replace('C:\_sysdate_\EU001.csv', '_sysdate_', TO_CHAR(SYSDATE, 'DD-MON-YYYY'))
spool _DATE
set serveroutput on size 100000
select * from dual;
spool off

Thanks
Re: Creating spool file dynamically [message #472418 is a reply to message #472417] Fri, 20 August 2010 08:18 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
set termout off
col dt new_value dt
select to_char(sysdate,'YYYYMMDDHH24MISS') dt from dual;
set termout on
spool file_&dt
...
spool off
Re: Creating spool file dynamically [message #472420 is a reply to message #472417] Fri, 20 August 2010 08:20 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
You can stop asking the same question after it has already been answered
Re: Creating spool file dynamically [message #472425 is a reply to message #472417] Fri, 20 August 2010 08:29 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
I have tweaked tour code a litle but still unable to get the result.

let me be clear if i am not. My goal is to create a spool file in C:\20-AUG-2010


SQL> set termout off
SQL> col dt new_value dt
SQL> select replace('C:\_sysdate_\EU001.csv', '_sysdate_', TO_CHAR(SYSDATE, 'DD-MON-YYYY')) dt from
dual;

DT
--------------------------------------------------------------------------------
C:\20-AUG-2010\EU001.csv

SQL> set termout on
SQL> spool _dt
SQL> select * from dual;

D
-
X

SQL> spool off
SQL>
Re: Creating spool file dynamically [message #472428 is a reply to message #472425] Fri, 20 August 2010 08:32 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Creating spool file dynamically [message #472440 is a reply to message #472425] Fri, 20 August 2010 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SQL> spool _dt

Where is the & sign from BlackSwan post?

Regards
Michel
Re: Creating spool file dynamically [message #472443 is a reply to message #472428] Fri, 20 August 2010 10:00 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
I am sorry that accidentally i submitted the same twice. I am not sure if you are refering to the foramting of the code, i have done the formating. Please let me know if i have missed any.

SET termout off
COL dt new_value dt
SELECT TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS') dt
  FROM DUAL;
SET termout on
SPOOL file_&dt
SELECT *
  FROM DUAL;
SPOOL off
Re: Creating spool file dynamically [message #472444 is a reply to message #472443] Fri, 20 August 2010 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please let me know if i have missed any.

Telling us what you got and why it is not what you want.

Regards
Michel
Re: Creating spool file dynamically [message #472445 is a reply to message #472444] Fri, 20 August 2010 10:09 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
I am sorry, please ignore the earlier post of my code . Actually I need the spool file to be created at the location C:\20-Aug-2010.


Here is the code
SET termout off
COL dt new_value dt
SELECT REPLACE ('C:\_sysdate_\EU001.csv',
                '_sysdate_',
                TO_CHAR (SYSDATE, 'DD-MON-YYYY')
               ) dt
  FROM DUAL;
SET termout on
SPOOL _&dt
SELECT *
  FROM DUAL;
SPOOL off


And here is the output


SQL> SPOOL off
SQL> SET termout off
SQL> COL dt new_value dt
SQL> SELECT REPLACE ('C:\_sysdate_\EU001.csv',
2 '_sysdate_',
3 TO_CHAR (SYSDATE, 'DD-MON-YYYY')
4 ) dt
5 FROM DUAL;

DT
------------------------------------------------------------------
C:\20-AUG-2010\EU001.csv

SQL> SET termout on
SQL> SPOOL _&dt
SP2-0332: Cannot create spool file.

SQL> SELECT *
2 FROM DUAL;

D
-
X
Re: Creating spool file dynamically [message #472446 is a reply to message #472445] Fri, 20 August 2010 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
>SP2-0332: Cannot create spool file.
FINALLY an error code!

Does the folder actually exist PRIOR to starting sqlplus?
Oracle/sqlplus does NOT create the folder at OS level for you!
Re: Creating spool file dynamically [message #472448 is a reply to message #472445] Fri, 20 August 2010 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DT
------------------------------------------------------------------
C:\20-AUG-2010\EU001.csv

SQL> SET termout on
SQL> SPOOL _&dt
SP2-0332: Cannot create spool file.


Of course, Oracle can't create a file named "_C:\20-AUG-2010\EU001.csv".

Please try to understand what you write.

Regards
Michel
Re: Creating spool file dynamically [message #472449 is a reply to message #472446] Fri, 20 August 2010 10:21 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Oh it already exists. I have cross checked it. I assume the folder already exists in my OS. Thank you for lightning response
Re: Creating spool file dynamically [message #472450 is a reply to message #472449] Fri, 20 August 2010 10:24 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Just to ensure

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\samala>cd c:\20-Aug-2010

C:\20-Aug-2010>
Re: Creating spool file dynamically [message #472451 is a reply to message #472450] Fri, 20 August 2010 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Of course, Oracle can't create a file named "_C:\20-AUG-2010\EU001.csv".
Re: Creating spool file dynamically [message #472454 is a reply to message #472450] Fri, 20 August 2010 10:35 Go to previous message
Aju
Messages: 94
Registered: October 2004
Member
Thank you very much for your time. This is done

SQL> SET termout off
SQL> COL dt new_value dt
SQL> SELECT REPLACE ('C:\_sysdate_\EU001.csv',
  2                  '_sysdate_',
  3                  TO_CHAR (SYSDATE, 'DD-MON-YYYY')
  4                 ) dt
  5    FROM DUAL;

DT
--------------------------------------------------------------------------------
C:\20-AUG-2010\EU001.csv

SQL> SET termout on
SQL> SPOOL &dt
SQL> SELECT *
  2    FROM DUAL;

D
-
X

SQL> SPOOL off
Previous Topic: SQL SYNTAX ON ORACLE SQL DEVELOPER
Next Topic: Remove/Prevent "PL/SQL procedure successfully completed" message
Goto Forum:
  


Current Time: Thu Aug 21 15:01:11 CDT 2014

Total time taken to generate the page: 0.05845 seconds