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 <
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;
/
#!/bin/ksh
#myscript.sh
sqlplus mfahd/password <
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;
/