OraFAQ Forum: SQL & PL/SQL » spooling to file SP2-0734

Home » SQL & PL/SQL » SQL & PL/SQL » spooling to file SP2-0734

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

spooling to file SP2-0734 [message #261409]

Wed, 22 August 2007 09:22

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?

Report message to a moderator

Re: spooling to file SP2-0734 [message #261416 is a reply to message #261409]

Wed, 22 August 2007 09:32

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;

Report message to a moderator

Re: spooling to file SP2-0734 [message #261418 is a reply to message #261409]

Wed, 22 August 2007 09:35

joy_division
Messages: 4963
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

Bill B
Messages: 1971
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;

Report message to a moderator

Re: spooling to file SP2-0734 [message #261697 is a reply to message #261409]

Thu, 23 August 2007 06:38

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?

Report message to a moderator

Re: spooling to file SP2-0734 [message #261701 is a reply to message #261697]

Thu, 23 August 2007 06:46

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

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

Report message to a moderator

Re: spooling to file SP2-0734 [message #261767 is a reply to message #261409]

Thu, 23 August 2007 09:42

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?

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 27 10:46:12 CDT 2025