asked on

Executing shell code from SQLPlus

Hello,

I need to execute shell code from within an sql script running in sqlplus. The whole process is non-interactive, so it starts like:
sqlplus uid/pass @myscript

What I actually need to do, is at a specific point of my script to run a java prog. (Java stored procedures or any other kind of stored procedures is not an option) and then continue with my script.

PLease don't tell me about 'host' and '!' as these do not work when you invoke sqlplus with '@'... Any ideas?

thanks!

grim_toaster

Erm, host should work... Or at least I can get it working on my XP machine, running Oracle 9i.

DOS command: sqlplus user/password @d:\run.sql
d:\run.sql: host d:\runMe.cmd
d:\runMe.cmd dir/w > d:\hlp.txt

d:\hlp.txt is generated with correct details.

DrBrain

ASKER

Thanks for the quick answer.

Let me give you a piece of my code:
myscript.sql:

set verify off
set serveroutput on size 100000
declare
      lastid number;
begin
      -- some code here...
      commit;

      -- shell script execution

end;
/

Unfortunatelly, 'host' command doesn't seem to be accepted within begin/end....

Thanks!

ps. I'm on a 9i on Solaris.

ASKER CERTIFIED SOLUTION

grim_toaster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

Schwertner and grim_toaster are correct, PL\SQL does not support "host" commands directly. Using Java to do the "host" command is the recommended way with Oracle9. Two other options: (external procedures, or DBMS_PIPE/daemon) worked with Oracle7 and Oracle8, and they can still be used for applications that have been upgraded. But if you are building something new, I would say use Java, since that is what Oracle strongly encourages.

DrBrain

ASKER

Thanks to everybody suggesting JSP solutions, but I think I was clear that using them is not an option.

peter991

Hi!
Isnt it easier to run SQL in a SHELL-script instead?

#! /bin/ksh
# Run the database job
sqlplus user/userpasswd >> /tmp/work.log 2>&1 <<!
@/oracle/scripts/sql_script.sql
exit
!