OraFAQ Forum: SQL & PL/SQL » how to get result into spool file

Home » SQL & PL/SQL » SQL & PL/SQL » how to get result into spool file

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

how to get result into spool file [message #185310]

Tue, 01 August 2006 02:48

mape
Messages: 298
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;
/

Report message to a moderator

Re: how to get result into spool file [message #185313 is a reply to message #185310]

Tue, 01 August 2006 03:13

Littlefoot
Messages: 21823
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;

Report message to a moderator

Re: how to get result into spool file [message #185316 is a reply to message #185310]

Tue, 01 August 2006 03:26

mape
Messages: 298
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.

Report message to a moderator

Re: how to get result into spool file [message #185320 is a reply to message #185316]

Tue, 01 August 2006 04:03

Littlefoot
Messages: 21823
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!

Report message to a moderator

Re: how to get result into spool file [message #185330 is a reply to message #185310]

Tue, 01 August 2006 04:34

mape
Messages: 298
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.

Report message to a moderator

Re: how to get result into spool file [message #185339 is a reply to message #185330]

Tue, 01 August 2006 05:03

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: how to get result into spool file [message #185346 is a reply to message #185310]

Tue, 01 August 2006 05:20

mape
Messages: 298
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

Report message to a moderator

Re: how to get result into spool file [message #185348 is a reply to message #185346]

Tue, 01 August 2006 05:22

Littlefoot
Messages: 21823
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;
/

Report message to a moderator

Re: how to get result into spool file [message #185352 is a reply to message #185310]

Tue, 01 August 2006 05:32

mape
Messages: 298
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.

Report message to a moderator

Re: how to get result into spool file [message #185564 is a reply to message #185330]

Wed, 02 August 2006 06:21

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.

Report message to a moderator

Previous Topic:	suffix or prefix negative value of the column
Next Topic:	SQL Queries

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 02:17:04 CDT 2025