Home » SQL & PL/SQL » SQL & PL/SQL » how to get result into spool file
how to get result into spool file [message #185310] Tue, 01 August 2006 02:48 Go to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
Hi

I want to get the result into the spool file but how to do that?
I call procedure from this unix script:

#!/usr/bin/ksh

sqlplus -s xx/xx@yyy @proc.tst <<END_SCRIPT >> log.txt
END_SCRIPT

And procedure proc.tst looks:


declare 

begin

  dbms_output.put_line('here is result');
 
end;
/

Re: how to get result into spool file [message #185313 is a reply to message #185310] Tue, 01 August 2006 03:13 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know about UNIX, but if your proc.tst script looks like this, the output will be spooled out into the log.txt file:
set serveroutput on

spool log.txt

begin
  dbms_output.put_line('testing');
end;
/

spool off;
Re: how to get result into spool file [message #185316 is a reply to message #185310] Tue, 01 August 2006 03:26 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
You right, but the procedure proc.tst is written as unix script.

So unix script call oracle procedure who is not put down into the oracle database, but as unix shell.
Re: how to get result into spool file [message #185320 is a reply to message #185316] Tue, 01 August 2006 04:03 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How do you mean, "Oracle procedure is not in the database"? What do you mean, why is it called a STORED PROCEDURE? Because it is stored in the database!
Re: how to get result into spool file [message #185330 is a reply to message #185310] Tue, 01 August 2006 04:34 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
simply code of procedure is written as unix text file


declare 

begin

  dbms_output.put_line('here is result');
 
end;
/



Everything works in the unix platform.
Re: how to get result into spool file [message #185339 is a reply to message #185330] Tue, 01 August 2006 05:03 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But this is not a stored procedure; this is anonymous PL/SQL block.

Never mind that; did you get the output into the file? I'm really sorry, but I don't know much about UNIX. If you can find it useful, on MS Windows command prompt it is done like this:

>sqlplus -s scott/tiger@ora10 @proc.tst > log.txt
Re: how to get result into spool file [message #185346 is a reply to message #185310] Tue, 01 August 2006 05:20 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
this is anonymous PL/SQL block of course.

I got the output into the file like this

sqlplus -s xx/xx@yy @proc.tst <<END_SCRIPT > log.txt
END_SCRIPT

But it didnt help.
The result on the output file is:
PL/SQL procedure successfully completed.

thats all
Re: how to get result into spool file [message #185348 is a reply to message #185346] Tue, 01 August 2006 05:22 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That is because Oracle doesn't know you want to have output on the screen; at the beginning of your PL/SQL script put "SET SERVEROUTPUT ON" statement, so that it would be like this:
SET SERVEROUTPUT ON
BEGIN
  dbms_output.put_line('here is result');
END;
/
Re: how to get result into spool file [message #185352 is a reply to message #185310] Tue, 01 August 2006 05:32 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
thanks a lot
its working

I have to put "set serveroutput on" on the beginning of PL/SQL.

I tried to get this statement on the beginning of unix script like this, but it was not right.

sqlplus -s xx/xx@yy @proc.tst <<END_SCRIPT > log.txt
SET SERVEROUTPUT ON
END_SCRIPT

Thanks a again.
Re: how to get result into spool file [message #185564 is a reply to message #185330] Wed, 02 August 2006 06:21 Go to previous message
shrichandray
Messages: 2
Registered: August 2006
Location: BANGALORE
Junior Member
#!/bin/sh
sqlplus -s xx/xxx@123 <<EOFF > log.txt
set heading off
set feedback off
set verify off
set termout on
set linesize 10000
set pages 0
set serveroutput on
spool xyz
@proc.tst
spool off
EOFF

end_of script

Now check you xyz.lst or log.txt file.
Previous Topic: suffix or prefix negative value of the column
Next Topic: SQL Queries
Goto Forum:
  


Current Time: Mon Dec 05 18:51:08 CST 2016

Total time taken to generate the page: 0.09020 seconds