Java Stored Procedures

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Mon, 13 Dec 2004 9:44 am

Hey guys, I don't know if this is the right area for this subject, but I would like to know how does the process of creating and execute Java procedures at the Bank, step by step.
If anyone knows and can help me, thanks.
lamanita
Location: Porto Alegre - RS


Poston Mon, 13 Dec 2004 10:34 am

Well, there's two ways to do this.
One is when you have the program source (the file .Java). Another is when you only have the bytecode (i.e. the .class) here we go: [size = 150] without the SOURCE-LOADJAVA [/size] if u only have the class, you must load it into Oracle. Here's an example:
Code: Select all
--=======================
--== CRIACAO DA CLASSE ==
--=======================
-- Em um arquivo txt crie seu código

public class StoreProcedureJava {
public static String Procedimento (){
return "Estah String veio de cohdigo java";
};
}

-- Salve o Arquivo com o mesmo nome da classe mais a extensao .java
-- compile o arquivo: javac StoreProcedureJava.java
-- note que será gerado o bitecode (.class)

--==========================
--== ARMAZENANDO NO BANCO ==
--==========================
-- loadjava é um utilitario que vem com o banco, deverá estar em
$ORACLE_HOME/bin

loadjava.bat -user esd/esd StoreProcedureJava.class

--==========================
--== Verificando a Criacao
--==========================
select OBJECT_NAME, OBJECT_TYPE, CREATED from USER_OBJECTS
where upper(OBJECT_NAME) = upper('StoreProcedureJava')
/

--====================================================
--== Criando uma Funcao PL/SQL para Chamar a Classe
--====================================================
-- Atencao na sintaxe, Classe.funcao/procedimento.

create or replace function JavaOracle return varchar2 AS
LANGUAGE JAVA NAME 'StoreProcedureJava.Procedimento() return
java.lang.String';
/

--=======================
--== Executando a Classe
--=======================
select JavaOracle() from dual;


Now, will an example with parameter passing.
Code: Select all
-- ======================================================================
-- ================ EXEMPLO DE PASSAGEM DE PARAMETROS ===================
-- ======================================================================

--=======================
--== CRIACAO DA CLASSE ==
--=======================

public class StoreProcedureJava {

public static String Procedimento (java.lang.String Texto){
return "Estah String veio de cohdigo java: " + Texto;
};


public static void main (String args[]){
file://System.out.println(Procedimento("TESTE"));
};

}

--==========================
--== ARMAZENANDO NO BANCO ==
--==========================
loadjava.bat -user esd/esd StoreProcedureJava.class

--==========================
--== Verificando a Criacao
--==========================
select OBJECT_NAME, OBJECT_TYPE, CREATED from USER_OBJECTS
where upper(OBJECT_NAME) = upper('StoreProcedureJava')
/

--====================================================
--== Criando uma Funcao PL/SQL para Chamar a Classe
--====================================================
CREATE OR REPLACE function JavaOracle(p1 IN VARCHAR2) return VARCHAR2 AS
LANGUAGE JAVA NAME 'StoreProcedureJava.Procedimento(java.lang.String) return
java.lang.String';
/

--=======================
--== Executando a Classe
--=======================
select JavaOracle('funciona !!!') from dual;
/

-- Resultado: Estah String veio de cohdigo java: funciona !!!
[size = 150] with the source [/size] the following example shows how to create a function in Java, which later will be called in the PL/SQL function called RUN. It should be noted that the function RUN must be supplied with the name/path of the full executable to be run. If desired the OS command, create a batch and pass the batch as an argument.

Code: Select all
create or replace and compile java source named "RunCommand" as
import java.util.*;
import java.io.*;

public class RunCommand extends Object
{
  static String result;
  public static String getOutput(){
     return result;
  }

  public static int RunThis(String args)
  {
  Runtime rt = Runtime.getRuntime();
  int        rc = -1;

  try
  {
     Process p = rt.exec(args);

     int bufSize = 4096;
     BufferedInputStream bis =
      new BufferedInputStream(p.getInputStream(), bufSize);
     int len;
     byte buffer[] = new byte[bufSize];

     // Echo back what the program spit out
     while ((len = bis.read(buffer, 0, bufSize)) != -1)
         result = result + new String(buffer);

     rc = p.waitFor();
  }
  catch (Exception e)
  {
     e.printStackTrace();
     rc = -1;
  }
  finally
  {
     return rc;
  }
  }
}
/

create or replace function run(Args varchar2 ) return number
as language java
name 'RunCommand.RunThis(java.lang.String) return int';
/
** important: ** have to be given permissions for user, directory and files, cfrme below:
Code: Select all
-- Specific permissions should be given to run this code
var n number;
exec dbms_java.grant_permission('YOURUSER', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '', :n);
exec dbms_java.grant_permission('YOURUSER', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '', :n);
exec dbms_java.grant_permission('YOURUSER', 'SYS:java.io.FilePermission', 'YOUR PATH', 'read,write,execute', :n);

dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 16 Jun 2005 11:27 am

Another good link: [url = ://asktom http . . oracle com/pls/ask/f? p = 8 10784527238964248086 : : 4950 : : : F4950_P8_DISPLAYID :, F4950_P8_CRITERIA in : 952229840241] Click here [/url]
Code: Select all
Steve,

I'm looking for a Java-procedure executing host calls on a Unix environment from
the Oracle-server. I know the standard way of doing it by means of ProC (for
Oracle versions before 8.1) but I do not have the ProC compiler available.

I do not know Java or ProC (nor do I currently have the time to learn them).
I'vê found several examples of these scripts but the "Runtime Power" script on
this site does not seem to do anything at all and the other script I'vê found in
an Oracle newsgroup seems to miss some "#import" statements in order to compile
correctly.

I would greatly appreciate your help on this one.
Henk-Jan


and we said...


Here is how to do this in java in 8.1.6.  8.1.6 added lots of new security
features so this would be a little different in 8.1.5 but mostly the same. 

We need to start by granting some privs.  I'm going to grant as little as I have
to get allow us to execute the program /usr/bin/ps.  As SYS or some
appropriately priveleged user, we will execute:

[email protected]> begin
  2      dbms_java.grant_permission
  3      ('RT_TEST',
  4       'java.io.FilePermission',
  5       '/usr/bin/ps',
  6       'execute');
  7 
  8      dbms_java.grant_permission
  9      ('RT_TEST',
10       'java.lang.RuntimePermission',
11       '*',
12       'writeFileDescriptor' );
13  end;
14  /

PL/SQL procedure successfully completed.


That allows our user RT_TEST to successfully execute that program.  We could
have allowed it to execute /usr/bin/* or * or whatever -- I'm just letting it
execute that one program.

Now, RT_TEST would create in its schema:


[email protected]> create or replace and compile
  2  java source named "Util"
  3  as
  4  import java.io.*;
  5  import java.lang.*;
  6 
  7  public class Util extends Object
  8  {
  9 
10    public static int RunThis(String[] args)
11    {
12    Runtime rt = Runtime.getRuntime();
13    int        rc = -1;
14 
15    try
16    {
17       Process p = rt.exec(args[0]);
18 
19       int bufSize = 4096;
20       BufferedInputStream bis =
21        new BufferedInputStream(p.getInputStream(), bufSize);
22       int len;
23       byte buffer[] = new byte[bufSize];
24 
25       // Echo back what the program spit out
26       while ((len = bis.read(buffer, 0, bufSize)) != -1)
27          System.out.write(buffer, 0, len);
28 
29       rc = p.waitFor();
30    }
31    catch (Exception e)
32    {
33       e.printStackTrace();
34       rc = -1;
35    }
36    finally
37    {
38       return rc;
39    }
40    }
41  }
42  /

Java created.

[email protected]> create or replace
  2  function RUN_CMD( p_cmd  in varchar2) return number
  3  as
  4  language java
  5  name 'Util.RunThis(java.lang.String[]) return integer';
  6  /

Function created.


To make this callable as a procedure (ignoring the return code), we'll create a
procedure:

[email protected]>
[email protected]> create or replace procedure RC(p_cmd in varchar2)
  2  as
  3    x number;
  4  begin
  5    x := run_cmd(p_cmd);
  6  end;
  7  /

Procedure created.

And now to run it:

[email protected]> set serveroutput on size 1000000
[email protected]> exec dbms_java.set_output(1000000)
PL/SQL procedure successfully completed.
[email protected]> exec rc('/usr/bin/ps -ef');
UID   PID  PPID  C    STIME TTY      TIME CMD
root     0     0  0   Aug 17 ?        0:06 sched
root     1     0  0   Aug 17 ?        1:19 /etc/init -
root     2     0  0   Aug 17 ?        0:23 pageout
.....

PL/SQL procedure successfully completed.

[email protected]>


See


http://java.sun.com/j2se/1.3/docs/api/java/lang/RuntimePermission.html

http://java.sun.com/j2se/1.3/docs/api/java/security/SecurityPermission.html

http://java.sun.com/j2se/1.3/docs/api/java/io/FilePermission.html

and



http://download-east.oracle.com/docs/cd/A81042_01/DOC/java.816/a81353/perf.htm#1001971
From the “Java Developer’s Guide”, Part No. A81353-01, Chapter 5:

Table 5–1 Permission Types
n java.util.PropertyPermission
n java.io.SerializablePermission
n java.io.FilePermission
n java.net.NetPermission
n java.net.SocketPermission
n java.lang.RuntimePermission
n java.lang.reflect.ReflectPermission
n java.security.SecurityPermission
n oracle.aurora.rdbms.security.PolicyTablePermission
n oracle.aurora.security.JServerPermission


dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 17 Sep 2008 11:03 am

What would be YOUR PATH? the way to the Bank?

exec dbms_java. grant_permission (' ERP ', ' SYS: java.io.FilePermission ', ' YOUR PATH ', ' read, write, execute ',: n);
nilodp
Location: Parana - Parana

Poston Thu, 26 Feb 2009 12:50 pm

A doubt and if I need to use an external library as it should be the procedure to use it? Thank you!
Shakall
Location: Blumenau - SC

Poston Fri, 27 Feb 2009 11:54 pm

It's not the LOADJAVA quoted above?
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 27 Feb 2009 11:56 pm

is that in the examples that I'vê seen the guys wore him down with só .class, but now I tested using the jar and was another problem, beleza now, my class está running beleza, só that she uses the comm.jar which is a library so that I can have access to com port, the problem that this library makes use of a dll, and if I play my class at oracle he of this error because this dll is missing, how I do oracle looks a dll? If yes how?
Shakall
Location: Blumenau - SC

Poston Tue, 03 Mar 2009 1:49 pm

Could someone help me I am trying to give java permissions for a user but I'm not getting, is giving syntax error.

I'm trying to usaer a class and the following error is showing.

\"error: the Permission (java.lang.RuntimePermission class.getClassLoader) has not been granted to user. The PL/SQL to grant this is dbms_java. grant_permission (' user ', ' SYS: java.lang.RuntimePermission ', ' class.getClassLoader ', '') \"I tried to run the command to grant permission to the user using the sys account, but not worked.

I used the following command.

exec dbms_java. grant_permission (' USER ', ' SYS: java.lang.RuntimePermission ', ' writeFileDescriptor ', ' ',: n);
exec dbms_java. grant_permission (' USER ', ' SYS: java.lang.RuntimePermission ', ' readFileDescriptor ', ' ',: n);
Shakall
Location: Blumenau - SC

Poston Tue, 09 Jun 2009 10:25 pm

guys, I'm doing a pkg that makes an integration with another bank and a procedure make a call to a java class to insert into another database the data passed ... the class compiles normal ... but in time to connect with the other seat via java, it uses the connection jar, but he did not find I tried to call the jar by the loadjava but gave error, noDefFoundError in the jar, you need to have that permission to run it, or to carry a jar of driver is there any other way??

thanking you in advance
luiz_daniel
Location: sp

Poston Wed, 04 Sep 2013 3:09 pm

[quote = " dr_gori "]Well, there's two ways to do this.
One is when you have the program source (the file .Java). Another is when you only have the bytecode (i.e. the .class) here we go: [size = 150] without the SOURCE-LOADJAVA [/size] If you only have the class, you must load it into Oracle. Here's an example:
Code: Select all
--=======================
--== CRIACAO DA CLASSE ==
--=======================
-- Em um arquivo txt crie seu código

public class StoreProcedureJava {
public static String Procedimento (){
return "Estah String veio de cohdigo java";
};
}

-- Salve o Arquivo com o mesmo nome da classe mais a extensao .java
-- compile o arquivo: javac StoreProcedureJava.java
-- note que será gerado o bitecode (.class)

--==========================
--== ARMAZENANDO NO BANCO ==
--==========================
-- loadjava é um utilitario que vem com o banco, deverá estar em
$ORACLE_HOME/bin

loadjava.bat -user esd/esd StoreProcedureJava.class

--==========================
--== Verificando a Criacao
--==========================
select OBJECT_NAME, OBJECT_TYPE, CREATED from USER_OBJECTS
where upper(OBJECT_NAME) = upper('StoreProcedureJava')
/

--====================================================
--== Criando uma Funcao PL/SQL para Chamar a Classe
--====================================================
-- Atencao na sintaxe, Classe.funcao/procedimento.

create or replace function JavaOracle return varchar2 AS
LANGUAGE JAVA NAME 'StoreProcedureJava.Procedimento() return
java.lang.String';
/

--=======================
--== Executando a Classe
--=======================
select JavaOracle() from dual;


Now, will an example with parameter passing.
Code: Select all
-- ======================================================================
-- ================ EXEMPLO DE PASSAGEM DE PARAMETROS ===================
-- ======================================================================

--=======================
--== CRIACAO DA CLASSE ==
--=======================

public class StoreProcedureJava {

public static String Procedimento (java.lang.String Texto){
return "Estah String veio de cohdigo java: " + Texto;
};


public static void main (String args[]){
file://System.out.println(Procedimento("TESTE"));
};

}

--==========================
--== ARMAZENANDO NO BANCO ==
--==========================
loadjava.bat -user esd/esd StoreProcedureJava.class

--==========================
--== Verificando a Criacao
--==========================
select OBJECT_NAME, OBJECT_TYPE, CREATED from USER_OBJECTS
where upper(OBJECT_NAME) = upper('StoreProcedureJava')
/

--====================================================
--== Criando uma Funcao PL/SQL para Chamar a Classe
--====================================================
CREATE OR REPLACE function JavaOracle(p1 IN VARCHAR2) return VARCHAR2 AS
LANGUAGE JAVA NAME 'StoreProcedureJava.Procedimento(java.lang.String) return
java.lang.String';
/

--=======================
--== Executando a Classe
--=======================
select JavaOracle('funciona !!!') from dual;
/

-- Resultado: Estah String veio de cohdigo java: funciona !!!
[size = 150] with the source [/size] the following example shows how to create a function in Java, which later will be called in the PL/SQL function called RUN. It should be noted that the function RUN must be supplied with the name/path of the full executable to be run. If desired the OS command, create a batch and pass the batch as an argument.

Code: Select all
create or replace and compile java source named "RunCommand" as
import java.util.*;
import java.io.*;

public class RunCommand extends Object
{
  static String result;
  public static String getOutput(){
     return result;
  }

  public static int RunThis(String args)
  {
  Runtime rt = Runtime.getRuntime();
  int        rc = -1;

  try
  {
     Process p = rt.exec(args);

     int bufSize = 4096;
     BufferedInputStream bis =
      new BufferedInputStream(p.getInputStream(), bufSize);
     int len;
     byte buffer[] = new byte[bufSize];

     // Echo back what the program spit out
     while ((len = bis.read(buffer, 0, bufSize)) != -1)
         result = result + new String(buffer);

     rc = p.waitFor();
  }
  catch (Exception e)
  {
     e.printStackTrace();
     rc = -1;
  }
  finally
  {
     return rc;
  }
  }
}
/

create or replace function run(Args varchar2 ) return number
as language java
name 'RunCommand.RunThis(java.lang.String) return int';
/
** important: ** have to be given permissions for user, directory and files, cfrme below:
Code: Select all
-- Specific permissions should be given to run this code
var n number;
exec dbms_java.grant_permission('YOURUSER', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '', :n);
exec dbms_java.grant_permission('YOURUSER', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '', :n);
exec dbms_java.grant_permission('YOURUSER', 'SYS:java.io.FilePermission', 'YOUR PATH', 'read,write,execute', :n);

[/quote] gori, beleza?

So let me get this straight, these " Javas " that you posted are to execute commands on Unix through Oracle, right?

you could call an executable file on a local machine with Windows through a forms running within the ERP Applications (which runs on Unix)???
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP


  • See also
    Replies
    Views
    Last Post