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
Post Reply
lamanita
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 42
Joined: Mon, 17 May 2004 2:41 pm
Location: Porto Alegre - RS
Samuel, o lamanit@

And guys, I do not know if this is the right area for this subject, but I would like to know how the process works and perform procedures in Java in the bank, step by step.
If someone knows and can help me, thank you.
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Well, there are two ways to do this.
One of them, is when you have the source of the program (the .java file). Another, it's when you only have the bytecode (ie the .class)

Come on:

without the source - Loadjava \r\n

If you only have the class, you should load it into Oracle. There is an example:

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, goes an example with parameter passage.

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



with the source \r\n

]] The example below shows how to create a Java function, which will then be called in the PL / SQL function called RUN. Note that the Run function must be fed with the complete name / path of the executable to be rotated. If only command is desired, create a batch and pass the batch as an argument.

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: ** You have to be given the permissions for user,
directory and files, CFRME below:

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); 
 
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Another good link:

Clique Chi

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: 
 
sys@DEV816> 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: 
 
 
rt_test@DEV816> 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. 
 
rt_test@DEV816> 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: 
 
rt_test@DEV816>  
rt_test@DEV816> 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: 
 
rt_test@DEV816> set serveroutput on size 1000000 
rt_test@DEV816> exec dbms_java.set_output(1000000) 
PL/SQL procedure successfully completed. 
rt_test@DEV816> 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. 
 
rt_test@DEV816> 
 
 
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 
 
 
nilodp
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Tue, 24 Jun 2008 3:21 pm
Location: Parana - Parana

What would your path be? The bank path?

exec dbms_java.grant_permission ('ERP', 'SYS: Java.io.filepermission', 'Your Path', 'Read, Write, run',: n);
Shakall
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

A doubt and if I need to use an external library how should the procedure be used to use it? Thanks!
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Is it not with the loadjava quoted above?
Shakall
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

is that in the examples that I saw the staff só used it with the .class, but now I tested using the jar and it was beleza, I have another problem now, my class está turning beleza, so she uses the comm .jar that is a Library so I can have access to the door with, the problem that this library makes use of a DLL, and if I ride my class in Oracle it from this error because it is missing this DLL, how can I do the Oracle fills a DLL? If yes how?
Shakall
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

Someone could help me I'm trying to give java permissions to a user but I can not get it, it's giving syntax error.

I'm trying to use a class and the following error is displaying.

"Error: The Permission (Java.lang.runtimePermission GetClassLoader) Has Not Been Granted to User. The pl / sql to grant this is dbms_java.grant_permission ('User', 'SYS: Java .lang.runtimePermission ',' GetClassLoader ',' ') "

I tried to run the Grant Permission command for the user using the SYS account, but it did not work.

I used the following command.

exec dbms_java.grant_permission ('User', 'sys: java.lang.runtimermission', 'WriteFiledescriptor', '',: N);
exec dbms_java.grant_permission ('User', 'sys: java.lang.runtimermission', 'readFileDescriptor', '',: n);
luiz_daniel
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Tue, 09 Jun 2009 10:10 pm
Location: sp

Personal,
I am doing a PKG that makes an integration with another bank
and a procedure makes a call to a Java class to insert
into another database the past data. ....

the normal compile class ... but when connecting with the other bank
via java, he uses the jar of connection, but he does not find

Driver Is there another way ???

Thanks from now on
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

dr_gori wrote: Well, there are two ways to do this.
One of them, is when you have the source of the program (the .java file). Another, it's when you only have the bytecode (ie, the .class)

Come on:

without the source - Loadjava [/ Size]

If you only have the class, you should load it into Oracle. There is an example:


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, goes an example with parameter passage.

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



with the source \r\n

]] The example below shows how to create a Java function, which will then be called in the PL / SQL function called RUN. Note that the Run function must be fed with the complete name / path of the executable to be rotated. If only command is desired, create a batch and pass the batch as an argument.

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: ** You have to be given the permissions for user,
directory and files, CFRME below:

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?

Let me see if I understand well, these "Javas" that you posted serve to run commands on UNIX through Oracle, right?

Would you call an executable file on a local Windows machine, through a forms running inside the ERP Applications (which runs on UNIX) ???
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot] and 1 guest