Questions about installing using the OS_COMMAND Package

rjsosi

Hi,

We’re using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.

I want to use the OS_COMMAND package so I can run Linux commands on the server from with in an Oracle procedure. I‘ve used this at a previous client. They don’t have it installed here. I know I need to have the OS_COMMAND pkg run and installed in the SYS schema.

When I used it before it worked perfectly. From inside and Oracle procedure I’d just add:

Shell( ‘ls -ltr’);

and that was it.

I’ve installed the OS_COMMAND package in a non-SYS schema (SLDBATCH)  just to test it.

Of course I’m getting an error when I try to compile the procedure I’m testing it in.

“14:21:34 Error: ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.io.FilePermission /bin/sh execute) has not been granted to SLDBATCH. The PL/SQL to grant this is dbms_java.grant_permission( 'SLDBATCH', 'SYS:java.io.FilePermission', '/bin/sh', 'execute' ) ORA-06512: at "SLDBATCH.OS_COMMAND", line 56”

My question is this a function of having it ‘test’ installed under and non SYS id?

Also, was the ‘shell’ command I used, a proprietary command the DBAs at the previous place used? It there a more standard or Generic command to use?

Thanks,

Solomon Yakobson

@@rjsosi This was suggested to me by another user

1. Take a look at package body you posted. All overloads of EXEC are functions. So why are you calling it as procedure?
2. All overloads of EXEC have mandatory parameters, so why are you not passing any?

Anyway,

CREATE FUNCTION SLDPROC.shell_os(p_command in varchar2) RETURN NUMBER IS
BEGIN
 RETURN OS_COMMAND.EXEC(p_command);
END;
/

Now question is why would you need to wrap function into another function that simply does nothing additional? Why not simply call OS_COMMAN.EXEC(p_command);

SY.

Solomon Yakobson

Not sure what your question is but Oracle will let you execute OS file through Java only if you have execute java permission on it. SYS obviously has it and SLDBATCH does not. So you need to login as privileged user and issue:

exec dbms_java.grant_permission('SLDBATCH','SYS:java.io.FilePermission','/bin/sh','execute' ) 

SY.

rjsosi

So this command I was able to use in my Oracle procedure.

Was that something proprietary then? I assumed the OS_COMMAND package had something to do with it.

Again from inside the Oracle procedure I make the following call:

shell('ls -ltr');

In the log it returns a list of files.

Could this have been some proprietary script using the OS_COMMAND package code? Or is this some other functionality of Oracle?

Solomon Yakobson

Oracle is client server artchitecture. Clinent from his/her box connects to database which in general resides not on his/her machine. Imagine what harm can client do if allowed to run scripts/OS commands on someone else's machine. That is why this requires privileges. You can grant java execute privilege on individual files or on all files in some directory or on all files on database server or just use wildcards. So if Oracle user is granted execute permission on SYS:java.io.FilePermission on OS command ls it doesn't mean user can call OS command sh. Anyway, DBA_JAVA_POLICY has all info on what user has what java privileges.

SY.

rjsosi

OK, upon looking at this further. I guess my final question is how do I take a procedure out of the OS_COMMAND package like the following:

 procedure set_exec_in_shell i

is language java name 'ExternalCall.useShell()'; 

and define it so it can be used in an Oracle procedure? I'll probably have to re-ask this in the Java section....

Solomon Yakobson

This is what I use:

CREATE OR REPLACE AND RESOLVE
  JAVA SOURCE NAMED "JCommand"
  AS import java.io.*;
      import java.lang.*;


      public class JCommand
      {
          public static String execute(String cmd) {
            try
            {
              Process process = Runtime.getRuntime().exec(cmd);
              int exitVal = process.waitFor();
              return (Integer.toString(exitVal));
            }
            catch (Exception e)
            {
             System.out.println("Exception: " + e.getMessage());
             return e.getMessage();
            }
          }
      }
/
CREATE OR REPLACE 
  FUNCTION OS_COMMAND(
                      p_Cmd IN VARCHAR2
                     )
    RETURN VARCHAR2
    AS LANGUAGE JAVA
       NAME 'JCommand.execute(java.lang.String) return String';
/

SY.

rjsosi

This was suggested to me by another user:

CREATE FUNCTION SLDPROC.shell_os(p_command in varchar2) RETURN NUMBER IS
BEGIN
 OS_COMMAND.EXEC();
 RETURN 1;
END;
/

when I went to compile it I got the following error:

[Warning] ORA-24344: success with compilation error

3/3   PLS-00306: wrong number or types of arguments in call to 'EXEC'

3/3   PL/SQL: Statement ignored

 (1: 0): Warning: compiled but with compilation errors

I'm getting an error telling me :"[Warning] ORA-24344: success with compilation error 3/3 PLS-00306: wrong number or types of arguments in call to 'EXEC' 3/3 PL/SQL: Statement ignored (1: 0): Warning: compiled but with compilation errors"

If you look at the java package for this there are multiple overloaded definitions for this function: here is a short example:

CREATE OR REPLACE package body SLDBATCH.os_command is
...
...  
function exec(p_command in varchar2, p_stdin in blob) return number
  is language java name 'ExternalCall.exec(java.lang.String, oracle.sql.BLOB) return int';

  function exec(p_command in varchar2, p_stdin in clob) return number
  is language java name 'ExternalCall.exec(java.lang.String, oracle.sql.CLOB) return int';

  function exec(p_command in varchar2) return number
  is language java name 'ExternalCall.exec(java.lang.String) return int';

  function exec(p_command in varchar2, p_stdin in clob, p_stdout in clob) return number
  is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.CLOB, oracle.sql.CLOB) return int';

  function exec(p_command in varchar2, p_stdin in clob, p_stdout in blob) return number
  is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.CLOB, oracle.sql.BLOB) return int';

  function exec(p_command in varchar2, p_stdin in blob, p_stdout in blob) return number
  is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.BLOB, oracle.sql.BLOB) return int';

  function exec(p_command in varchar2, p_stdin in blob, p_stdout in clob) return number
  is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.BLOB, oracle.sql.CLOB) return int';

  function exec(p_command in varchar2, p_stdout in clob) return number
  is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.CLOB) return int';

  function exec(p_command in varchar2, p_stdout in blob) return number
  is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.BLOB) return int';
    
  function exec(p_command in varchar2, p_stdin in clob, p_stdout in clob, p_stderr in clob) return number
  is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.CLOB, oracle.sql.CLOB, oracle.sql.CLOB) return int';
  
  function exec(p_command in varchar2, p_stdin in clob, p_stdout in blob, p_stderr in blob) return number
  is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.CLOB, oracle.sql.BLOB, oracle.sql.BLOB) return int';

  function exec(p_command in varchar2, p_stdin in blob, p_stdout in blob, p_stderr in blob) return number
  is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.BLOB, oracle.sql.BLOB, oracle.sql.BLOB) return int';

  function exec(p_command in varchar2, p_stdin in blob, p_stdout in clob, p_stderr in clob) return number
  is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.BLOB, oracle.sql.CLOB, oracle.sql.CLOB) return int';

  function exec(p_command in varchar2, p_stdout in clob, p_stderr in clob) return number
  is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.CLOB, oracle.sql.CLOB) return int';

  function exec(p_command in varchar2, p_stdout in blob, p_stderr in blob) return number
  is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.BLOB, oracle.sql.BLOB) return int';

end os_command;
/

AM I getting this error because the java function is actually overloaded with multiple versions of 'exec' and multiple versions of parameters? Am I going to have to define a package that houses every combination of overloaded function in order to get this to work?

User_H3J7U

@rjsosi This was suggested to me by another user

You and another user first need to learn programming basics to write pl/sql code.

Error Messages:

PLS-00306: wrong number or types of arguments in call to 'string'

Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype.

Action: Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.

Solomon Yakobson

@@rjsosi This was suggested to me by another user

1. Take a look at package body you posted. All overloads of EXEC are functions. So why are you calling it as procedure?
2. All overloads of EXEC have mandatory parameters, so why are you not passing any?

Anyway,

CREATE FUNCTION SLDPROC.shell_os(p_command in varchar2) RETURN NUMBER IS
BEGIN
 RETURN OS_COMMAND.EXEC(p_command);
END;
/

Now question is why would you need to wrap function into another function that simply does nothing additional? Why not simply call OS_COMMAN.EXEC(p_command);

SY.

rjsosi

To USER_H3J7U,

I'm not a Java person. That's the point of me asking this...

All I got from you answer was, you know there's an error but you don't know what it is.

To Solomon,

First thanks for, at least, getting back to me with an actual answer rather than a pontification insinuating how much you know about stuff.

Second Again this is my first time calling java from oracle so the solutions I found on the web were like the one you just provided me.

Why not call Java directly? Because I want a simple solution to be able to call from a PL/SQL procedure/ Package.

I want to be able to call

shell('ls -ltr');

Not

OS_COMMAN.EXEC(p_command);

And I don't want to have to define "OS_COMMAN.EXEC(p_command);" at the beginning of every code written by myself or anyone here with some local substitute.

I just want them to be able to do a simple call like:

shell();

That's it! End of Discussion

Solomon Yakobson

??? What's the difference if you code uses:

shell('ls -ltr');

or

OS_COMMAND.EXEC('ls -ltr');

Both are pretty obvious to me and both will fail to compile the way you wrote it since both are functions. It should be

ret_val := shell('ls -ltr');

or

ret_val := OS_COMMAND.EXEC('ls -ltr');

SY.