LJP_UK1
asked on
Calling OS commands from PL/SQL
I am writing a PL/SQL procedure against an Oracle 9i database to import data from a file into a table. I have done all the coding to write from the file but need some advice for running OS commands from within a PL/SQL procedure.
Any advice would be most welcome.
Cheers
Any advice would be most welcome.
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
schwertner
Sorry! The part after the first sentence shows how to use Java for that.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is a scetch to the procedure using UTL_FILE:
procedure YOUR_PROC is
sasha SYS.UTL_FILE.FILE_TYPE;
sanjeev VARCHAR2(1800);
begin
sasha := UTL_File.Fopen('D:\staff\c
LOOP
UTL_File.get_line(sasha,sa
-- FORMAT THE DATA in/from buffer sanjeev and insert in Oracle DB using INSERT statement
END LOOP
EXCEPTION
WHEN no_data_found THEN
UTL_File.Fclose(sasha);
WHEN UTL_File.invalid_filehandl
-- invalid_filehandle - not a valid file handle
WHEN UTL_File.read_error THEN ... UTL_File.Fclose(sasha);
-- OS error occurred during read
WHEN UTL_File.invalid_operation
end YOUR_PROC ;
REMARK
Server security for PL/SQL file I/O consists of a restriction on
the directories that can be accessed.
Accessible directories must be specified in the
instance parameter initialization file (INIT.ORA).
Specify the accessible directories for the UTL_FILE
functions in the initialization file
using the UTL_FILE_DIR parameter.
For example:
UTL_FILE_DIR = <directory name>
procedure YOUR_PROC is
sasha SYS.UTL_FILE.FILE_TYPE;
sanjeev VARCHAR2(1800);
begin
sasha := UTL_File.Fopen('D:\staff\c
LOOP
UTL_File.get_line(sasha,sa
-- FORMAT THE DATA in/from buffer sanjeev and insert in Oracle DB using INSERT statement
END LOOP
EXCEPTION
WHEN no_data_found THEN
UTL_File.Fclose(sasha);
WHEN UTL_File.invalid_filehandl
-- invalid_filehandle - not a valid file handle
WHEN UTL_File.read_error THEN ... UTL_File.Fclose(sasha);
-- OS error occurred during read
WHEN UTL_File.invalid_operation
end YOUR_PROC ;
REMARK
Server security for PL/SQL file I/O consists of a restriction on
the directories that can be accessed.
Accessible directories must be specified in the
instance parameter initialization file (INIT.ORA).
Specify the accessible directories for the UTL_FILE
functions in the initialization file
using the UTL_FILE_DIR parameter.
For example:
UTL_FILE_DIR = <directory name>
Are you just reading a file? Then UTL_FILE or external tables (as sugested above) would be the easiest. If not, have a look at EXT_PROC.