Solved

Executing shell code from SQLPlus

Posted on 2003-11-26
7
4,286 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now