Wednesday, April 21, 2010

Shell Script to Call PL/SQL Procedure, which writes to File from table

The myscript.sh script is calling the procedure foo, which is created in database schema mfahd. foo is inserting records in pipe delimited form in file test.txt file from a table. The output file test.txt is also located in directory /u02/loaddata/test.txt. I have also created a directory loaddata in the database which is pointing to an actual location at OS level.


#!/bin/ksh
#myscript.sh
sqlplus mfahd/password < begin
foo();
end;
/
exit;
ENDOFSQL

===================================================


create or replace procedure foo as
f utl_file.file_type;
vname varchar2(30);
vempno number;
vrecord varchar2(300);
begin
select empno, ename into vempno, vname from emp where empno=1;
vrecord:=vname || '|' || vempno;
f := utl_file.fopen('LOADDATA','test.txt','W');
utl_file.put_line(f,vrecord);
utl_file.fclose(f);
end;
/

Tuesday, April 20, 2010