Solved

Execute Unix command from a pl/sql procedure

Posted on 2004-10-12
21
20,082 Views
Last Modified: 2012-08-14
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
Comment
Question by:fouaddba
  • 6
  • 6
  • 4
  • +4
21 Comments
 
LVL 4

Expert Comment

by:Vinay_dba
ID: 12288703
You can use  HOST command in sql plus

You can use utl_file to write to file.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12288734
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 12291600
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
 
LVL 9

Expert Comment

by:pratikroy
ID: 12292526
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
 
LVL 2

Author Comment

by:fouaddba
ID: 12293724
I am working on 9.2.0.4 and can you pls tell how to check Java is enabled or not.
0
 
LVL 47

Accepted Solution

by:
schwertner earned 250 total points
ID: 12295254
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12296808
if you are curious:

to see if java is enabled or not:

SQ>select * from v$option;
0
 
LVL 2

Author Comment

by:fouaddba
ID: 12299507
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 12299539
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
 
LVL 2

Author Comment

by:fouaddba
ID: 12299833


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
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 23

Expert Comment

by:seazodiac
ID: 12299881
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
 
LVL 2

Author Comment

by:fouaddba
ID: 12300423

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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12300568
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
 
LVL 2

Author Comment

by:fouaddba
ID: 12300662
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12300731
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
 
LVL 2

Author Comment

by:fouaddba
ID: 12301163
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12301177
hmmm...I also gave you the working code....

i don't get any points....
0
 
LVL 34

Expert Comment

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

Expert Comment

by:jifeak
ID: 13277596
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 13277993
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
 
LVL 47

Expert Comment

by:schwertner
ID: 13283660
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

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

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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.
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…

705 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

16 Experts available now in Live!

Get 1:1 Help Now