OraFAQ Forum: Client Tools » Creating spool file dynamically

Home » Other » Client Tools » Creating spool file dynamically (Solaris: Version 8.1.7.4.0 - PL/SQL Release 8.1.7.4.0 )

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

Creating spool file dynamically [message #472417]

Fri, 20 August 2010 08:17

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

Report message to a moderator

Re: Creating spool file dynamically [message #472418 is a reply to message #472417]

Fri, 20 August 2010 08:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator

Re: Creating spool file dynamically [message #472420 is a reply to message #472417]

Fri, 20 August 2010 08:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You can stop asking the same question after it has already been answered

Report message to a moderator

Re: Creating spool file dynamically [message #472425 is a reply to message #472417]

Fri, 20 August 2010 08:29

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>

Report message to a moderator

Re: Creating spool file dynamically [message #472428 is a reply to message #472425]

Fri, 20 August 2010 08:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Creating spool file dynamically [message #472440 is a reply to message #472425]

Fri, 20 August 2010 09:53

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

SQL> spool _dt

Where is the & sign from BlackSwan post?

Regards
Michel

Report message to a moderator

Re: Creating spool file dynamically [message #472443 is a reply to message #472428]

Fri, 20 August 2010 10:00

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

Report message to a moderator

Re: Creating spool file dynamically [message #472444 is a reply to message #472443]

Fri, 20 August 2010 10:04

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: Creating spool file dynamically [message #472445 is a reply to message #472444]

Fri, 20 August 2010 10:09

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

Report message to a moderator

Re: Creating spool file dynamically [message #472446 is a reply to message #472445]

Fri, 20 August 2010 10:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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!

Report message to a moderator

Re: Creating spool file dynamically [message #472448 is a reply to message #472445]

Fri, 20 August 2010 10:19

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: Creating spool file dynamically [message #472449 is a reply to message #472446]

Fri, 20 August 2010 10:21

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

Report message to a moderator

Re: Creating spool file dynamically [message #472450 is a reply to message #472449]

Fri, 20 August 2010 10:24

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>

Report message to a moderator

Re: Creating spool file dynamically [message #472451 is a reply to message #472450]

Fri, 20 August 2010 10:25

Michel Cadot
Messages: 68641
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".

Report message to a moderator

Re: Creating spool file dynamically [message #472454 is a reply to message #472450]

Fri, 20 August 2010 10:35

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

Report message to a moderator

Previous Topic:	SQL SYNTAX ON ORACLE SQL DEVELOPER
Next Topic:	Remove/Prevent "PL/SQL procedure successfully completed" message

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 22:57:50 CDT 2024