Run system command (like ls or exp) from Oracle (without java)

[lecorr]

Hi,

Sem gave me a good answer when I asked for this on this forum. He gave me the link to Ask Tom.

I wrote a richer version which I give to you.

How it works:
You call a function HOST with the text of the command to run in shell. This command is sent in a PIPE by Oracle. It is received by a small program whiwch always run in Unix. Your command is logged in a table (COMMANDE_LOG) by the UNIX part of the Code and this Unix part of the code puts a OK in this table when the command ends. So, you can check what the system has done, and wait for the end of the execution of your command.

In the database:
Note: My schema/user is DBASYS.

You need under SYS:
GRANT EXECUTE ON dbms_pipe TO DBASYS;

In your SCHEMA/USER

CREATE OR REPLACE procedure DBASYS.host( numTrace NUMBER, numOrdre NUMBER, cmd in varchar2 )
as
    status number;
begin
    dbms_pipe.pack_message( numTrace );
    dbms_pipe.pack_message( numOrdre );
    dbms_pipe.pack_message( cmd );
    status := dbms_pipe.send_message( 'HOST_PIPE' );
    if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
    end if;
end;
/
CREATE TABLE DBASYS.COMMANDE_LOG
(
  ID      NUMBER                                NOT NULL,
  ORDRE   NUMBER                                NOT NULL,
  TEXT    VARCHAR2(4000 BYTE),
  STATUS  VARCHAR2(30 BYTE)
);

CREATE UNIQUE INDEX DBASYS.PK_COMMANDE_LOG ON DBASYS.COMMANDE_LOG (ORDRE, ID) LOGGING;

ALTER TABLE DBASYS.COMMANDE_LOG ADD (
  CONSTRAINT PK_COMMANDE_LOG PRIMARY KEY (ORDRE, ID)
    USING INDEX );

  -- Waits the OK in the COMMANDE_LOG table
  -- Easy to modify to return an answer from unix
  FUNCTION WAIT_ANSWER(pn_Code_Batch NUMBER, pn_Ordre NUMBER) RETURN VARCHAR2 AS
    vc_temp VARCHAR2(4000);
  BEGIN
    vc_temp := '';
    WHILE NVL(vc_temp,'xx') <> 'OK' LOOP
      BEGIN
        SELECT STATUS 
          INTO vc_temp
          FROM DBASYS.COMMANDE_LOG
         WHERE ID = pn_Code_Batch
           AND ORDRE = pn_Ordre;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
    END LOOP;
    RETURN vc_temp;
  END WAIT_ANSWER;

In UNIX:
You have to run a program, I call it host.csh :
It should run in the same unix user than Oracle server.
Chack that the OPS$... user which connect can access DBASYS.COMMANDE_LOG.

#!/bin/csh -f

sqlplus / <<"EOF" | grep '^#' | sed 's/^.//' > tmp.csh

set serveroutput on size 200000
set linesize 4000

declare
        status  number;
        numTrace NUMBER;
        numOrdre NUMBER;
        command varchar2(4000);
begin
        status := dbms_pipe.receive_message( 'HOST_PIPE' );
        if ( status <> 0 ) then
                dbms_output.put_line( '#exit' );
        else
                dbms_pipe.unpack_message( numTrace );
                dbms_pipe.unpack_message( numOrdre );
                dbms_pipe.unpack_message( command );
                INSERT INTO DBASYS.COMMANDE_LOG(ID, ORDRE, TEXT, STATUS) VALUES (numTrace, numOrdre, command, '');
                commit;
                dbms_output.put_line( '##!/bin/csh -f' );
                dbms_output.put_line( '#' || command );
                dbms_output.put_line( '#TEST_BASE=`sqlplus -s / << EOF' );
                dbms_output.put_line( '#UPDATE DBASYS.COMMANDE_LOG SET STATUS = ''OK'' WHERE ID='||numTrace||' AND ORDRE='||numOrdre||';');
                dbms_output.put_line( '#COMMIT;');
                dbms_output.put_line( '#exit;');
                dbms_output.put_line( '#EOF`');
                dbms_output.put_line( '#exec host.csh' );
        end if;
end;
/
spool off
"EOF"

chmod +x tmp.csh
exec tmp.csh

This is sqlplus code which waits the message sent by the pipe, create a text command, runs it, then restart itself.

If needed, you could write anather program to catch the return code of your command and put it in COMMANDE_LOG instead of OK.


Drawbacks:
It does not support cd (change dir), since if you change dir, it will not be able to restart itself (this is easy to fix).

If the command crashes, then, the unix part of the code stops. So you need to check your commands carefully, or use your crontab to restart it if it does not run.

I use this code to EXPort parts of my own database using a job to schedule it (I do transportable tablespaces instead of text files to deliver data to other databases, they plug instantly). And lots of other things. An also because in the company I work for, anyone has access at the user of Oracle and can modify CRONTAB.
But they DON'T HAVE sys, system... passwords and so they can not stop my jobs.

I hope this can help.

Christian