Re: Host command within PL/SQL Proc!

Richard,

This will never work. The dbms_sql package is for executing dynamic sql, and unfortunately its functionality does not extend to executing OS commands. There are only two ways (that I know of) where you can execute OS commands from PL/SQL. The first is to use the dbms_pipe package to send a "request" to a 3GL program (eg Pro*C) that is listening on the pipe. When it receives a request it issues either a system() call, or fork()/exec() pair to execute the request. The other way (if you are using Oracle 8 or higher) is to write an external function in either C or java (8.1+ only) to issue a system() call. Either way, it's a reasonable amount of work.

I'm sure that's not what you wanted to hear, but that's Oracle for you ...

Steve

"Richard Fairbairn" <rfairbairnNOrfSPAM_at_uk.intasys.com.invalid> wrote in message news:08f8557c.412f9092_at_usw-ex0105-038.remarq.com...
> Hi,
> I am trying to use DBMS_SQL to initiate Unix scripts from my
> PL/SQL. I have tried the following to start a script called
> banana.sh but it hasn't worked.
>
> CREATE OR REPLACE procedure rwf_test as
> CURSOR$$ INTEGER;
> TMP_STR VARCHAR2(300);
> EXECUTE_SHELL INTEGER;
> BEGIN
> TMP_STR := '! banana.sh';
> CURSOR$$ := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(CURSOR$$,TMP_STR,DBMS_SQL.V7);
> EXECUTE_SHELL := DBMS_SQL.EXECUTE(CURSOR$$);
> DBMS_SQL.CLOSE_CURSOR(CURSOR$$);
> DBMS_OUTPUT.PUT_LINE('Successfully Executed, We Hope!');
> END;
> /
> Error message produced =
> ERROR at line 1:
> ORA-00900: invalid SQL statement
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "RICHARDF.RWF_TEST", line 8
> ORA-06512: at line 1
>
>
> -----------------------------------------------------------
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
>
Received on Wed Jul 26 2000 - 00:00:00 CDT