Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Unix PL/SQL Spooling

915131Jun 15 2012 — edited Jun 15 2012
I have Unix script which calls one PL/SQL Block file. When I run sql block in SQL Developer it displays output in output area. There is no problem in PL/SQL block.
Shell script is also not throwing any error but not writing anything in spool file. Spool file gets created with 0 bytes.
I am using Oracle 9. I don't want to use UTL File because Shell script is on one server and Database on other. It will add more code to ftp file from DB server to Data server.

Shell script code

export dttime=`date +%d%m%Y`'_'`date +%H%M%S`'.csv'
export filePath='App/log'

sqlplus -S <<END
`getLogin.ksh`
spool app/log/splfile_$dttime
@/Test/plsql_data.sql
spool off
exit
END

PL/SQL File -> plsql_data.sql

SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET SERVEROUTPUT On
SET RECSEP WRAP
set verify off
Declare

version CHAR(4);
app_user_id CHAR(8);
...
...
...
CURSOR csr IS
SELECT * from table;

BEGIN
dbms_output.enable(1000000);

version := 1.0;
app_user_id : = 'testusr';

open csr
fetch into ...
loop
if found then
dbms_output.put_line(details);
else
exit
end if;
end loop;
close csr;
end;
/

--------------- end of sql file

Edited by: 912128 on Jun 15, 2012 3:28 PM

Edited by: 912128 on Jun 15, 2012 3:31 PM
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 13 2012
Added on Jun 15 2012
2 comments
1,386 views