Unix PL/SQL Spooling
915131Jun 15 2012 — edited Jun 15 2012I 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