Resolve java source named

How to call Java stored procedures/Classes from PL/SQL

There is lot of requirements where we need to use Java like zipping the files stored in database, managing files on server.
Oracle provides the way to access Java stored Procedure and classes from PL/SQL, we can use according to our need
Following steps are required:

1. Create the Java code elements. You can do this in Netbeans, or in any other Java Integrated Development Environment. Load the Java class(es) into Oracle using the loadjava command-line utility or the CREATE JAVA statement.
2. Publish the Java class methods inside PL/SQL by writing wrapper programs in PL/SQL around the Java code.
3. Grant privileges as required on the PL/SQL wrapper programs and the Java class referenced by the PL/SQL wrapper.
4. Call the PL/SQL programs

Simple Demonstration:

You may write the below code as a simple JAVA program and load it into database. But here we are using the 11g feature, which enables us to write java code directly on sql prompt.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Hello" AS public class Hello < public static String world() < return "Hello world"; >>;

2. Load java class into database. We can also load a compiled .class file or .jar file using loadjava utility. In this case first step will be ignored.

C:\oracle\bin>loadjava -user scott/tiger -resolve Hello.class

LoadJava utility: The loadjava utility loads Java source and class files into the database. When class files are created in a conventional manner, outside the database; loadjava is used to get them into the database.

3. Write PL/SQL Wrapper Program:

CREATE OR REPLACE FUNCTION helloworld RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'Hello.world () return java.lang.String'; /

4. Call PL/SQL Wrapper Program:

DECLARE my_string VARCHAR2(400 CHAR); BEGIN my_string := helloworld(); dbms_output.put_line('The value of the string is ' || my_string); END; /

Here is the syntax for loadjava utility:

loadjava username/password[@database] [-option_name [-option_name] . ] filename [filename ]. where option_name stands for the following syntax: < | debug | | encoding_scheme_name | | [,]. | | oracleresolver | | "resolver_spec" | schema_name | | | >

loadjava requires two database privileges to load java objects into your own schema: CREATE PROCEDURE and CREATE TABLE. To load Java objects into a schema other than the currently connected user, CREATE ANY PROCEDURE and CREATE ANY TABLE privileges are required.

If loading many Java class files at one time, it is advisable to put them in a JAR file and load them into the database at one time, since the loadjava program will also load JAR files.
Oracle has created two new roles to support Java security. For many Java-based operations within the database, you will not have to work with these roles. If, on the other hand, you want to interact with the operating system (to access or modify operating system files, for example), you need to be granted one of the following roles:

You grant these roles as you would any other database role. For example, if I want to allow SCOTT to perform any kind of Java-related operation, I would issue this command from a SYSDBA account:

GRANT JAVASYSPRIV TO SCOTT;
GRANT EXECUTE ON SYS.DBMS_JAVA TO SCOTT

Here is an another example that is acting as a TCP/IP client socket and sends data to a TCP/IP server.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "CounterDisplay" AS import java.io.*; import java.io.*; import java.net.*; public class CounterDisplay < public static String display(String ip,int port,String counternumber,String counter) < String errmsg="Working Fine"; try< Socket clientSocket = new Socket(ip, port); DataOutputStream outToServer = new DataOutputStream(clientSocket.getOutputStream()); String dataToDisplay = counter+counternumber; outToServer.write(dataToDisplay.getBytes()); clientSocket.close(); outToServer.close(); >catch(Exception e) < errmsg=e.getMessage(); return errmsg; >return errmsg; > > --****************************************************************************** CREATE OR REPLACE FUNCTION COUNTERDISPLAY_FUNC(IP IN VARCHAR2,PORT IN NUMBER,COUNTERNUM IN VARCHAR2,COUNTER IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'CounterDisplay.display(java.lang.String,int,java.lang.String,java.lang.String) return java.lang.String'; --****************************************************************************** DECLARE on_completion VARCHAR2(400); BEGIN on_completion :=COUNTERDISPLAY_FUNC('192.192.21.42',5100,'5','503'); dbms_output.put_line('' || on_completion); END; --******************************************************************************

Источник

MacLochlainns Weblog

While helping in the forum, I noticed that folks don’t know that Java isn’t deployed with the Oracle 10g XE. However, it strikes me that you might be able to fudge it but I’m not sure that’s allowed in the EULA. If you want Java inside the database, why wouldn’t you install the licensed product?

There was an OTN article that listed three limitations but this wasn’t one in the article. Maybe I’ll run across the marketing note sometime in the furture or somebody will post the URL as a comment, which is always appreciated.

Anyway, the presence or lack of Java inside the database is pretty easy to test. You only need to do this:

SQL*Plus: Release 10.2.0.1.0 - Production ON Thu Nov 26 21:19:42 2009 Copyright (c) 1982, 2005, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 10g Express Edition Release 10.2.0.1.0 - Production SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS 2 public class HelloWorldSQL { 3 public static String hello() { 4 RETURN "Hello World."; } 5 } 6 / CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS * ERROR at line 1: ORA-29538: Java NOT installed

SQL*Plus: Release 10.2.0.1.0 — Production on Thu Nov 26 21:19:42 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 — Production SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS 2 public class HelloWorldSQL < 3 public static String hello() < 4 return "Hello World."; >5 > 6 / CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS * ERROR at line 1: ORA-29538: Java not installed

This is also true for Oracle Database 11g XE, as shown:

SQL*Plus: Release 11.2.0.2.0 Production ON Fri DEC 9 02:34:20 2011 Copyright (c) 1982, 2011, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS 2 public class HelloWorldSQL { 3 public static String hello() { 4 RETURN "Hello World!"; } 5 } 6 / CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS * ERROR at line 1: ORA-29538: Java NOT installed

SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 9 02:34:20 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 — 64bit Production SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS 2 public class HelloWorldSQL < 3 public static String hello() < 4 return "Hello World!"; >5 > 6 / CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS * ERROR at line 1: ORA-29538: Java not installed

It also explains the lack of loadjava or dropjava from the $ORACLE_HOME/bin directory.

November 26th, 2009 at 10:34 pm

Источник

Java – Create or replace and resolve Java source, I am not able to create a function using java source

The following Java source has been correctly compiled:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED hr.roman AS package Package.NumeriRomani; class Numero < String r=""; int a=0; Numero(int n)< a=n; if(n==0)< r="NULL"; return;>while(n>=1000) < r+="M"; n-=1000;>if(n>=900) < r+="CM"; n-=900;>while(n>=500) < r+="D"; n-=500;>if(n>=400) < r+="CD"; n-=400;>while(n>=100) < r+="C"; n-=100;>if(n>=90) < r+="XC"; n-=90;>while(n>=50) < r+="L"; n-=50;>if(n>=40) < r+="XL"; n-=40;>while(n>=10) < r+="X"; n-=10;>if(n>=9) < r+="IX"; n-=9;>if(n>=5) < r+="V"; n-=5;>if(n==4) < r+="IV"; n-=4;>while(n>=1) < r+="I"; n--;>>//Numero(int) Numero(String k) < int vCor,vNext; r=k; for(int i=0;;i++)< vCor=valore(k.charAt(i)); if(i==k.length()-1)< a+=vCor; break;>vNext=valore(k.charAt(i+1)); if(vCor>=vNext) a+=vCor; else a-=vCor; > > static public int valore(char c) < switch(c)< case 'I': return 1; case 'V': return 5; case 'X': return 10; case 'L': return 50; case 'C': return 100; case 'D': return 500; case 'M': return 1000; default: return 0; >> public int getArabo() < return a; >public String getRomano() < return r; >> / 

I would like to create a function for getArabo and a function for getRoman, but I always receive errors.

CREATE OR REPLACE FUNCTION hr.converti (alfa IN varchar2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'ROMAN.getArabo(java.lang.String) return java.lang.String'; / 

select hr.converti('XII') from dual; 

ORA-29540: ROMAN class does not exist

Best Solution

Here are the things you need to correct to be able to use this java class:

1. Use static methods so that they can be called from Oracle without object instantiation
2. Your source is named ROMAN , but your class is named Numero . You will reference the class when calling it from Oracle. Giving both the class and the source the same name will prevent confusion.
3. java is strongly typed, you can’t convert the method int getArabo() to:

getArabo(java.lang.String) return java.lang.String 

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Roman" AS 2 class Roman < 3 public static int getArabo(java.lang.String x)< 4 return 0; 5 >6 > 7 / Java created SQL> CREATE OR REPLACE FUNCTION converti (alfa IN varchar2) 2 RETURN NUMBER 3 AS LANGUAGE JAVA NAME 'Roman.getArabo(java.lang.String) return int'; 4 / Function created SQL> select converti ('aaa') from dual; CONVERTI('AAA') --------------- 0 

Additionally, you can use Oracle functions to convert a number to its character value in roman with TO_CHAR (but the TO_NUMBER function can’t be used for the reverse operation):

SQL> SELECT to_char(2013, 'RN') FROM dual; TO_CHAR(2013,'RN') ------------------ MMXIII 

Источник