Forum Stats

  • 3,770,159 Users
  • 2,253,079 Discussions
  • 7,875,349 Comments

Discussions

Questions about installing using the OS_COMMAND Package

rjsosi
rjsosi Member Posts: 221 Bronze Badge
edited Sep 7, 2021 8:01PM in SQL & PL/SQL

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,

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    Accepted Answer

    @@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
«1

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond

    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
    rjsosi Member Posts: 221 Bronze Badge

    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
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Sep 7, 2021 11:13PM

    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
    rjsosi Member Posts: 221 Bronze Badge

    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
    Solomon Yakobson Member Posts: 18,925 Red Diamond

    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
    rjsosi Member Posts: 221 Bronze Badge

    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
    User_H3J7U Member Posts: 690 Silver Trophy

    @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
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    Accepted Answer

    @@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
  • rjsosi
    rjsosi Member Posts: 221 Bronze Badge
    edited Sep 9, 2021 4:33PM

    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
    Solomon Yakobson Member Posts: 18,925 Red Diamond

    ??? 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.