?
Solved

Executing shell code from SQLPlus

Posted on 2003-11-26
7
Medium Priority
?
4,315 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 1050 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 450 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

777 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