CODEWITHSUNDEEP
javaoracle-databasestored-procedures
Carbon
Carbon

Reputation: 333

Running a Java procedure from an Oracle procedure by defining it as an Oracle procedure

we using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.

I've loaded the OS_COMMAND package in Oracle and I see it's all java.

The procedure I'm most interested in is the one that allows users to run a Linux command from with in an Oracle procedure. It's defined as the following: ex 1

  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';

How can I define this in an Oracle PL?SQL procedure OR a function in order for it to run as an Oracle function?

The only example I have is an Oracle procedure that defines a java method to use as an Oracle function. It's the following: ex 2

CREATE OR REPLACE PROCEDURE SLDPROC.shell (p_command IN VARCHAR2)
AS LANGUAGE JAVA 
NAME 'Host.executeCommand (java.lang.String)';
/

Which comes from the following java method: ex 3

 public class Host {
public static void executeCommand(String command) ...
...

So not having used java for a long time(20 yrs), how can I create a similar Oracle procedure to wrap a java procedure like the set_exec_in_shell procedure defined in the first example, ex 1?

Thanks!

Upvotes: 0

Views: 415

Answers (1)

MT0
MT0

Reputation: 168440

How can I define this in an Oracle procedure in order for it to run as an Oracle function?

You do not need to "define" it as it is already a procedure within the OS_COMMAND package; just make sure the package is compiled and then call it directly from the function.

CREATE FUNCTION your_function RETURN NUMBER IS
BEGIN
  OS_COMMAND.SET_EXEC_IN_SHELL();
  RETURN 1;
END;
/

Upvotes: 1

Related Questions