Solved

Executing shell code from SQLPlus

Posted on 2003-11-26
7
4,306 Views
Last Modified: 2012-05-04
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!
0
Comment
Question by:DrBrain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 7

Expert Comment

by:grim_toaster
ID: 9823508
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
 

Author Comment

by:DrBrain
ID: 9823623
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
 
LVL 7

Accepted Solution

by:
grim_toaster earned 350 total points
ID: 9823874
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 150 total points
ID: 9824058
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 9826205
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
 

Author Comment

by:DrBrain
ID: 9837770
Thanks to everybody suggesting JSP solutions, but I think I was clear that using them is not an option.
0
 
LVL 6

Expert Comment

by:peter991
ID: 10225811
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question