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!
DrBrainAsked:
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.

grim_toasterCommented:
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.
0
DrBrainAuthor Commented:
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.
0
grim_toasterCommented:
Ah, host is a SQLPLus command, so cannot be called inside a PL/SQL block, if possible, change your code to:

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

-- shell script execution
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

schwertnerCommented:
Somebody here has proposed the following way:

You can issue Unix commands from within a pl/sql procedure if you have oracle 8i or up.
STEPS to achieve this:

insert this java procedure into the database:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "HOST" AS
import java.lang.*;
import java.io.*;

public class Host
{
 public static void executeCommand (String command, String etype) throws IOException
 {
   String[] wFullCommand = {"C:\\winnt\\system32\\cmd.exe", "/y", "/c", command};
   String[] uFullCommand = {"/bin/sh", "-c", command};
   if (etype.toUpperCase().equals("W"))
     Runtime.getRuntime().exec(wFullCommand);
   else if(etype.toUpperCase().equals("U+"))
     Runtime.getRuntime().exec(uFullCommand);
   else if(etype.toUpperCase().equals("U"))
     Runtime.getRuntime().exec(command);
 }
};
/

You have to compile this pl/sql wrapper procedure in the database:

CREATE OR REPLACE PROCEDURE Host_Command_Proc (p_command  IN  VARCHAR2, p_etype  IN  VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String, java.lang.String)';
/

To issue a unix command to remove all files from a directory for example, you would put this line in your pl/sql procedure:

host_command_proc ('cd home/yourdir/another_dir', 'U+');
host_command_proc ('rm -f *', 'U+');
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
DrBrainAuthor Commented:
Thanks to everybody suggesting JSP solutions, but I think I was clear that using them is not an option.
0
peter991Commented:
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
!

0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.