• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 20352
  • Last Modified:

Execute Unix command from a pl/sql procedure

Hi guys,

The problem is related to error loging, when ever an exeception is raised in my procedure, I have to execute this unix command:
" logged -p local7.debug -t "DBA APPLICATION:" " FATAL TABLESPACE FULL"

This command only writes these words (DBA APPLICATION:" " FATAL TABLESPACE FULL) into a file, that file is watched by some guys, when ever there is a line inserted into that file (unix), they called the respected group (i.e., DBA) for this FATAL error.

I just want to do it via my stored procedure.
Help will be highly appreciate.
Thanks.

0
fouaddba
Asked:
fouaddba
  • 6
  • 6
  • 4
  • +4
1 Solution
 
Vinay_dbaCommented:
You can use  HOST command in sql plus

You can use utl_file to write to file.
0
 
seazodiacCommented:
depending up where you are (in oracle datasbase versions);

if you are in oracle 8i and 9i,
then you can do it in java stored procedure or C stored procedure, that's right, you have to write your own little Java external stored procedure:


if you are in oracle10g , you can call the Unix command or shell script directly from DBMS_SCHEDULER package.




we have a bunch of examples in this board already for the first approach :

Your Java method being called must be public and static.   Example:

--------------------java class
public class RunSQLPlus{
   public execSQL(String param){
       Process p = Runtime.getRuntime().exec("c:\\orant\\bin\\sqlplusw user/pwd@db " + param);  //note escaped backslashes...

   }
}
-----------------------------------Loadjava command
loadjava -force -resolve -user usr/pwd@db -verbose RunSQLPlus.java

----------------------------------------PLSQL wrapper
create or replace procedure Runsqlplus as
     LANGUAGE JAVA
     Name 'RunSQLPlus.execSQL(java.lang.String param)';

That's it.  If the JVM isn't installed, you will need to have your DBA do this.  A few pointers:
   1. try a simple function that returns a string first (HelloWorld example in oracle docs).
   2. Must use absolute pathnames - environmental variables are not set.
   3. Windows paths use a backslash character, which is an 'escape' in java, so to have a backslash in a string, you must escape it, e.g., \\ will yield \


0
 
Mark GeerlingsDatabase AdministratorCommented:
PL\SQL procedures by default do not support "host" commands, so you have to use one of these three options if you want PL\SQL to be able to do "host" commands:
1. external procedures
2. DBMS_PIPE/daemon
3. Java

Options 1 and 2 have worked since Oracle7.  With Oracle8 and later, it may be best to use Java, but the others options also work.
Which version of Oracle do you have, and is Java enabled in your database?
0
Industry Leaders: 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!

 
pratikroyCommented:
Well, you have two choices :
1. If you wish to use the existing command/utility - logged, then you might want choose one of the options given by seazodiac and markgeer, or have a look at the thread - http://www.experts-exchange.com/Databases/Oracle/Q_21015687.html

2. In case, you wish to write to the file directly, you could choose to use the utl_file package to write to the log file yourself. After all you just have to append the following lines to the log file - DBA APPLICATION:" " FATAL TABLESPACE FULL :)
0
 
fouaddbaAuthor Commented:
I am working on 9.2.0.4 and can you pls tell how to check Java is enabled or not.
0
 
schwertnerCommented:
Java is enabled by default.

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);
 }
};
/

Now 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
 
seazodiacCommented:
if you are curious:

to see if java is enabled or not:

SQ>select * from v$option;
0
 
fouaddbaAuthor Commented:
Thanks Mr. schwertner

But when I tried to insert your jave procedure, it gave me this error:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "HOST" AS
                                                *
ERROR at line 1:
ORA-04031: unable to allocate 4032 bytes of shared memory ("java pool","unknown object","joxs heap
init","ioc_allocate_pal")

and thanks Mr. seazodiac, I checked it is enabled.

Best regards
0
 
Mark GeerlingsDatabase AdministratorCommented:
You will have to check your initialization parameters (spfile or init*.ora file) for: java_pool_size.  That will have to be at least 20M, and you may need 32 - 50M to compile this procedure.  After that, you may be able to reduce the size of the Java pool to 20M or even less.
0
 
fouaddbaAuthor Commented:


select name, (value/1000)/1000 from v$parameter
where name like 'java_pool%'

java_pool_size  33.554432, I think it is 33M, is it sufficient to execute this procedure
0
 
seazodiacCommented:
You may also need to increase your SHARED_POOL_SIZE,

increase your shared_pool_size value by a few megabytes
by:

SQL>alter database set shared_pool_size =<to a larger value>;

then try to recompile your procedure
0
 
fouaddbaAuthor Commented:

select name, (value/1000)/1000 from v$parameter
where name like 'shared_pool%'

shared_pool_size                       1006.63296
shared_pool_reserved_size           20.97152

I think it is sufficient....
0
 
seazodiacCommented:
try to increase java_pool_size and shared_pool_size to a higher value.

just try by a few megabytes (which is sufficient for this java proceure test)

why don't you go ahead and try it?

Nobody knows how long your database is running and how much memory it have to use...

0
 
fouaddbaAuthor Commented:
sorry the problem is I can't do this, because I am not DBA and for this issue I cannot even request for this...

Is there any way around that I can do this without having THERE intervention.
0
 
seazodiacCommented:
well, you can test this compile from the TEST database instance.

you can install Oracle9i personal edition and test this in your own computer.
0
 
fouaddbaAuthor Commented:
Thanks for you guys help, I think I'll try it first at my home & then will try to test over here.

And thanks again for all your help.
0
 
seazodiacCommented:
hmmm...I also gave you the working code....

i don't get any points....
0
 
Mark GeerlingsDatabase AdministratorCommented:
What exactly do you need to have PL\SQL do:
1. just write a line to a text file on the server that includes: "DBA APPLICATION:" " FATAL TABLESPACE FULL"
or
2. actually run a command in Unix when this happens?

If you just need to write a line to a text file, utl_file can do that, but only if your database is configure to support PL\SQL doing that.

If you actually need PL\SQL to execute a Unix command, you will need DBA and/or Unix system administrator co-operation.
0
 
jifeakCommented:
Hi there,

I'm new and want to invoke SQLLDR on Unix (Solaris) from PLSQL in and oracle forms 6i, Oracle 8.1.7. database

I got the DBA to install Java and compiled the java code by Scwhertner above. however I cannot use code as I get the following error

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  dbms_java.grant_permission
  3  ('MQM',
  4   'java.io.FilePermission',
  5  '/bin/sh',
  6   'execute');
  7* end;
SQL> /
begin
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.SecurityException: policy
table update SYS:java.io.FilePermission, /bin/sh
ORA-06512: at "SYS.DBMS_JAVA", line 0
ORA-06512: at line 2

Can you plesae help?

Many thanks
0
 
Mark GeerlingsDatabase AdministratorCommented:
To jifeak:

You will likely get more help if you open a new question, than by adding a question here, since this question is considered "answered" already.

Another option is to search the previously-answered questions on this site for "host" and "plsql" or "pl\sql", since there have been many questions on this issue.

Be aware that running "host" commands (like sqlldr) from PL\SQL is not what PL\SQL is best at.  It may be simpler to have PL\SQL create a text file (that is actually a shell script) in a particular directory and have a continuously-running or frequently-run job in the O/S to look for new files there, and execute them, then move or delete them.

Another possibility that works pretty well with Oracle8.1.7 is to use dbms_pipe and a "daemon" program to execute host commands from PL\SQL instead of using Java for this.  This is how "host" commands were executed from PL\SQL in Oracle7 and 8.0.  Check your Oracle documentation for Supplied PL\SQL Packages: DBMS_PIPE for information on this approach.
0
 
schwertnerCommented:
This is what Oracle recommends for your issue (I hope you are on 8.1.7, please every time you post question mention the Oracle version and OS you  use):

Make a complete JVM installation:
- adjust JAVA_POOL_SIZE in the init.ora
  Oracle recommends that the minimum value of this parameter should be:
         JAVA_POOL_SIZE   = 20971520    # 20Mb
- drop and reinstall JVM

Everything that Markgeer wrote is true but you will run in other technical issues by every proposal.
The reason is very simple. RDBMS are intendended for manipulating and accessing data, not for running of another programatic utilities.
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!

  • 6
  • 6
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now