Home » SQL & PL/SQL » SQL & PL/SQL » spooling to file SP2-0734
spooling to file SP2-0734 [message #261409] Wed, 22 August 2007 09:22 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
In windows, my folder structure is
D:\spool_test\data

my requirement is, i have a script below, running which,
should spool in data folder,
a file which should capture the eno, and dno from emp table
in a comma seperated format

the filename should be 'myCONVERSION..timestamp'


spool data/outputfile.def
SELECT 'define filelog=myCONVERSION'||TO_CHAR(SYSDATE,'MMDDYYYYHH24MISS')||'.log' FROM dual;

spool off;
 
@data/outputfile.def

SPOOL data/&filelog;

DECLARE
	
    cursor c1 is select * from emp;

BEGIN
	
    
    for r1 in c1 loop

    dbms_output.put_line(r1.eno||','||r1.dno);


    end loop;


EXCEPTION
    WHEN NO_DATA_FOUND THEN
    null;

END;

SPOOL OFF;



the script is named test.sql...and its in D:\spool_test path
now when i run it

D:\>cd D:\spool_test

D:\spool_test>sqlplus gautam/gautam@gwcm

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 22 19:51:22 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @test;

'DEFINEFILELOG=MYCONVERSION'||TO_CHAR(SYSDATE
---------------------------------------------
define filelog=myCONVERSION08222007195125.log

SP2-0734: unknown command beginning "'DEFINEFIL..." - rest of line ignored.
 23



and the file myCONVERSION08222007195125 is created, but
inside it, message is
23 ;
24 /
SPOOL OFF;
*
ERROR at line 22:
ORA-06550: line 22, column 1:
PLS-00103: Encountered the symbol "SPOOL"


SQL> exit

how to spool the contents into the file correctly?

Re: spooling to file SP2-0734 [message #261416 is a reply to message #261409] Wed, 22 August 2007 09:32 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You are missing a "/" after the PL/SQL block.

DECLARE
    cursor c1 is select * from emp;
BEGIN
    for r1 in c1 loop
    dbms_output.put_line(r1.eno||','||r1.dno);
    end loop;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    null;
END;
/

SPOOL OFF;

Re: spooling to file SP2-0734 [message #261418 is a reply to message #261409] Wed, 22 August 2007 09:35 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Set head off

and

you need to put the slash before the spool off. SPOOL is a SQL*Plus command, not a PL/SQL command.

[edit] Cthulhu beat me

[Updated on: Wed, 22 August 2007 09:35]

Report message to a moderator

Re: spooling to file SP2-0734 [message #261500 is a reply to message #261409] Wed, 22 August 2007 16:01 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
A cleaner way to do this follows. Why even have a block?

set echo off
set pagesize 0
set trimspool on
set feedback off

column filelog new_value filelog noprint

SELECT 'D:\spool_test\data\myCONVERSION'||TO_CHAR(SYSDATE,'MMDDYYYYHH24MISS')||'.log' filelog
FROM dual;

SPOOL &filelog

select eno,dno
from emp;

SPOOL OFF;
Re: spooling to file SP2-0734 [message #261697 is a reply to message #261409] Thu, 23 August 2007 06:38 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
thanks, but i am not able to see the data in the table
in my myCONVERSION08232007170311 log file

what i see inside this file is

PL/SQL procedure successfully completed.

i am expecting to see the emp table data as comma seperated
script used is


set head off
spool data/outputfile.def
SELECT 'define filelog=myCONVERSION'||TO_CHAR(SYSDATE,'MMDDYYYYHH24MISS')||'.log' FROM dual;

spool off;
 
@data/outputfile.def

SPOOL data/&filelog;

DECLARE
    cursor c1 is select * from emp;
BEGIN
    for r1 in c1 loop
    dbms_output.put_line(r1.eno||','||r1.dno);
    end loop;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    null;
END;
/

SPOOL OFF;


so in outputfile.def, content is

define filelog=myCONVERSION08232007170732.log

so i am running this file be saying @data/outputfile.def

and in SPOOL data/&filelog;, i mean inside the log file,
i want to store the table data in comma seperated,

but can anyone please tell me why i am not able to get
the table data inside the log file?

Re: spooling to file SP2-0734 [message #261701 is a reply to message #261697] Thu, 23 August 2007 06:46 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
SQL> spool c:\temp\test.lst
SQL> declare
  2  cursor c1 
  3  is
  4  select empno, ename from emp;
  5  begin
  6  for i in c1
  7  loop
  8  dbms_output.put_line(i.empno || i.ename);
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER

PL/SQL procedure successfully completed.



P.S : I presume you are having some data in emp table.

[Updated on: Thu, 23 August 2007 06:50] by Moderator

Report message to a moderator

Re: spooling to file SP2-0734 [message #261754 is a reply to message #261409] Thu, 23 August 2007 09:02 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i am sorry, but the file name

myCONVERSION08232007170732 should contain data...

the example you gave just gives me the test file with output


Re: spooling to file SP2-0734 [message #261767 is a reply to message #261409] Thu, 23 August 2007 09:42 Go to previous message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
S.Rajaram was just showing you that the code produces output as an example.

An obvious question perhaps but I can't see a
set serveroutput on

in your postings, and you need that for dbms_output?
Previous Topic: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast
Next Topic: JOIN USING
Goto Forum:
  


Current Time: Sun Dec 04 23:06:58 CST 2016

Total time taken to generate the page: 0.08547 seconds