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
LJP_UK1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
You can use the host or ! command in SQL*Plus.

Here is a step by step example of how to create a JSP that makes an  
operating system call.  This sample was created and tested on Solaris 2.6,  
using Oracle 8.1.5, with JDK 1.1.8.
 
A. Create the java source file.
 
-------------------code begins here---------------------------------------------
   
/*
* ExecuteCmd.java  
* This is a sample application that uses the Runtime Object  
* to execute a program.  
*  
*/  
 
/* Import the classes needed for Runtime, Process, and Exceptions */  
import java.lang.Runtime;  
import java.lang.Process;  
import java.io.IOException;  
import java.lang.InterruptedException;  
 
 class ExecuteCmd {  
 
     public static void main(String args[]) {  
 
         System.out.println("In main");  
 
         try {  
             /* Execute the command using the Runtime object and get the  
                Process which controls this command */  
 
              Process p = Runtime.getRuntime().exec(args[0]);  
 
             /* Use the following code to wait for the process to finish  
                and check the return code from the process */  
             try {  
 
                p.waitFor();  
 
             /* Handle exceptions for waitFor() */  
 
             } catch (InterruptedException intexc) {  
 
                System.out.println("Interrupted Exception on waitFor: " +  intexc.getMessage());  
             }  
 
             System.out.println("Return code from process"+ p.exitValue());  
 
             System.out.println("Done executing");  
 
         /* Handle the exceptions for exec() */  
 
         } catch (IOException e) {  
             System.out.println("IO Exception from exec : " +  
                                e.getMessage());  
             e.printStackTrace();  
         }
   }
}  
-----------------code ends here-------------------------------------------------
 
B. Compile the Java source file.
 
$> javac ExecuteCmd.java  
 
(This creates a file called ExecuteCmd.class)  
   
 
C. Load the .class file into the database.
 
$> loadjava -u scott/tiger ExecuteCmd.class  
 
NOTE:  
If you have already loaded a class of this name before,  
drop the class before re-loading it, or use the -f (force) option:  
 
$> dropjava -u scott/tiger ExecuteCmd.class  
$> loadjava -u scott/tiger ExecuteCmd.class -f  
 
D. Publish the Java code to PL/SQL by creating the PL/SQL wrapper.
 
----------------code begins here------------------------------------------------  
-- create.sql
CREATE OR REPLACE PROCEDURE executecmd (S1 VARCHAR2)  
AS LANGUAGE JAVA  
name 'ExecuteCmd.main(java.lang.String[])';  
/
----------------code ends here--------------------------------------------------
 
E. Call the procedure.  
 
Example 1
---------
 
SQL> set serveroutput on  
SQL> call dbms_java.set_output(2000);  
 
SQL> EXEC executecmd('/usr/bin/ls -la');
 
In main  
Return code from process   0    
Done executing  
 
PL/SQL procedure successfully completed.  
 
Note:
A return code of zero was returned. System call completed succesfully.  
 
Example 2
---------  
 
SQL> EXEC executecmd('ls -la');  
In main  
Return code from process   255    
Done executing  
 
PL/SQL procedure successfully completed.  
 
Note:
A return code of 255 was returned.  System call did not complete.  Full paths
must be specified for the OS command and any file reference.
 
Example 3
---------  
 
1. Create a file called test.txt in your local directory.
 
SQL> !vi test.txt  
SQL>!ls  
test.txt  
 
2. Call the Java stored procedure.
 
SQL> exec EXECUTECMD('/usr/bin/rm -R /u05/home/tsupport/malshous/test.txt');  
In main  
Return code from process   0  
Done executing  
 
PL/SQL procedure successfully completed.  
 
SQL> !ls  
 
You should find that no file called test.txt exists in your local directory.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
schwertnerCommented:
Sorry! The part after the first sentence shows how to use Java for that.
Bigfam5Commented:
If you are using 9i , then it would be easier if you used External Tables.

Here's a link

http://asktom.oracle.com/pls/ask/f?p=4950:8:10187173283953089551::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1615330671789,
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

rogaut1Commented:
Solutions #1 : Use Oracle package UTL_FILE to read the flat file from PL/SQL and insert into your table.

Examples at http://www.modernz.net/books/oracle/bipack/ch06_02.htm


Solution #2: Use SQL*LOADER, you can map your flat file to a table .

Unix or Windows command Line :

---> sqlldr userid/passwd@sid control=controlfile.ctl


controlfile.ctl:
load data
          infile '/tmp/dept.dat'
          replace
          into table departments
          (  dept     position (02:05) char(4),
             deptname position (08:27) char(20)
          )




 
schwertnerCommented:
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\cv','56789.txt', 'r');
 

LOOP

UTL_File.get_line(sasha,sanjeev);

-- 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_filehandle   THEN ... UTL_File.Fclose(sasha);  
      --   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   THEN ... UTL_File.Fclose(sasha);                    

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>
MathiasMagnussonCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.