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

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, deleting files from 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 Java class according to your requirement
  2. Compile and load it into database
  3. Write one PL/SQL Wrapper Program to call this.
Simple Demonstration:
Requirement: To delete file from server, file name will be provided as input parameter
Steps to achieve this:
1Write Java class to delete the file and compile it. 
 public class TestDelete {
   public static int delete ... 
   public static void main (String args[]) {
      System.out.println (
         delete (args[0])
         );
    }
}
2. Load java class into database
 
C:\oracle9i\bin>loadjava -user scott/tiger -oci8 -resolve 
TestDelete.class
 
3. Write PL/SQL Wrapper Program:
CREATE OR REPLACE FUNCTION fDelete (
   file IN VARCHAR2) 
   RETURN NUMBER
AS LANGUAGE JAVA
   NAME 'TestDelete.delete (
            java.lang.String) 
            return int';
 
LoadJava utility: The loadjava utility (Oracle 8.1.5 and up) 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.

Figure 1: Loading Java elements into Oracle

 
load utility diagram
Here is the syntax:
loadjava {-user | -u} username/password[@database]
  [-option_name [-option_name] ...] filename [filename ]...
where option_name stands for the following syntax:
{  {andresolve | a}
 | debug
 | {definer | d}
 | {encoding | e} encoding_scheme_name
 | {force | f}
 | {grant | g} {username | role_name}[,{username | role_name}]...
 | {oci8 | o}
 | oracleresolver
 | {resolve | r}
 | {resolver | R} "resolver_spec"
 | {schema | S} schema_name
 | {synonym | s}
 | {thin | t}
 | {verbose | v} }
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.
This example will use a simple Java program that will be compiled outside of Oracle and then loaded into the database.
 public class SimpleJava {
   public void main(String[] args) {
      System.out.println(“Here we are”);
   }
From DOS or  UNIX :
C:\oracle9i\bin>javac SimpleJava.java
C:\oracle9i\bin>loadjava -user scott/tiger SimpleJava.class
The class file is now loaded into the database and visible from the dba_objects view with an object type of JAVA CLASS.
From SQL*Plus, create the PL/SQL wrapper to invoke the newly loaded Java class:
SQL> create or replace procedure call_simplejava
  2  as language java
  3  name ‘SimpleJava.showMessage()’;
  4  /
Execute the code from SQL*Plus:
SQL> set serveroutput on;
SQL> call dbms_java.set_output(50);
Call completed.
SQL> execute call_simplejava;
Here we are
PL/SQL procedure successfully completed.
In this example, the Java class file was loaded into the database.  The Java source file can also be loaded.  But, both the source and class files cannot be loaded at the same time.
 C:\oracle9i\bin>loadjava -user scott/tiger SimpleJava.java
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.   A JAR file is a group of Java class files lumped into one file, a format similar to TAR (on  UNIX ) and WinZip (on Windows).   The contents of a JAR file can be viewed using these popular utilities.  Java developers prefer to distribute a few JAR files rather than many individual Java class files.
Oracle8i 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:
JAVASYSPRIV
JAVAUSERPRIV
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;
If I want to place some restrictions on what the user can do with Java, I might execute this grant instead:
GRANT JAVAUSERPRIV TO SCOTT;
To access Java class methods from within Oracle, you must take the following steps:
  1. Create the Java code elements. You can do this in Oracle’s JDeveloper, 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 from any one of a number of environmentsOracle8i offers a variety of components and commands to work with Java following table summarizes these different elements:
Table 1: Oracle Components and Commands for Java
ComponentDescription
Aurora JVMThe Java Virtual Machine (JVM) that Oracle implemented in its database server
loadjavaAn operating system command-line utility that loads your Java code elements (classes, .jar files, etc.) into the Oracle database
dropjavaAn operating system command-line utility that drops your Java code elements (classes, .jar files, etc.) from the Oracle database
CREATE JAVA
DROP JAVA
ALTER JAVA
New DDL statements that perform some of the same tasks as loadjava and dropjava
DBMS_JAVAA built-in package that offers a number of utilities to set options and other aspects of the JVM
DBMS_JAVA_TESTA built-in package you can use to more easily test your JSPs
JPublisherA utility used to build Java classes around object types and REFs defined in the Oracle database

Figure 9.1: Accessing JSPs from within the Oracle database

Accessing JSPs from within the Oracle database

Data Type Mapping Between Java and SQL:
SQL TypeJava Class
CHAR, NCHAR, LONG, VARCHAR2, NVARCHAR2
oracle.sql.CHAR 
java.lang.String 
java.sql.Date 
java.sql.Time 
java.sql.Timestamp 
java.lang.Byte 
java.lang.Short 
java.lang.Integer 
java.lang.Long 
java.lang.Float 
java.lang.Double 
java.math.BigDecimal 
byte, short, int, long, float, double
DATE
oracle.sql.DATE 
java.sql.Date 
java.sql.Time 
java.sql.Timestamp 
java.lang.String
NUMBER
oracle.sql.NUMBER 
java.lang.Byte 
java.lang.Short 
java.lang.Integer 
java.lang.Long 
java.lang.Float 
java.lang.Double 
java.math.BigDecimal 
byte, short, int, long, float, double
RAW, LONG RAW
oracle.sql.RAW 
byte[]
ROWID
oracle.sql.CHAR 
oracle.sql.ROWID 
java.lang.String
BFILE
oracle.sql.BFILE
BLOB
oracle.sql.BLOB 
oracle.jdbc2.Blob
CLOB, NCLOB
oracle.sql.CLOB 
oracle.jdbc2.Clob 
OBJECT
oracle.sql.STRUCT 
oracle.SqljData 
oracle.jdbc2.Struct 
REF
oracle.sql.REF 
oracle.jdbc2.Ref 
TABLE, VARRAY
oracle.sql.ARRAY 
oracle.jdbc2.Array 
Any of the above SQL types
oracle.sql.CustomDatum 
oracle.sql.Datum 

COMMENTS

Name

Advance Table In OAF,1,AOL Concepts,18,AP Holds,1,Apps Initialization,1,Blanket Purchase Orders,1,Blanket Releases,1,Careers,9,Common Errors and Solutions,2,Concurrent Programs,10,Contract Purchase Orders,1,Corporate Culture,3,Corporate Ladder,3,Customer Interface,3,Customization,1,De-Normalization,1,EAM,1,EBS Tutorials,10,Education,16,Emterprise Asset Management,1,English,1,Enterprise Asset Management,1,Entrepreneur,1,Error Fixes,1,Executables,2,Extension,1,fnd_user,1,FNDLOAD,1,Global Agreements,1,Guest Posts,7,Handy Scripts,63,Health Tips,1,Important Apps Queries,1,Infographic,3,Interview Preparation,57,Interview Q/A,57,Inventory Org ID,1,Iproc,4,Iprocurement,4,IT,3,jDeveloper,1,Job Search,1,Key Tables In Oracle Apps,5,Learning English,1,MDS,1,Metadata Tables,2,Multi Org Architecture,2,Normalization,1,O2C,1,OAF Interview,49,OAF Personalization,1,OAF Tutorials,52,Operating Unit,1,Oracle Alerts,1,Oracle AP,11,Oracle APIs,4,Oracle Application Object Library,19,Oracle Applications,10,Oracle Apps,2,Oracle Apps Interviews,29,Oracle Apps Tips,8,Oracle Apps Tutorials,97,Oracle AR,18,Oracle Assets,2,Oracle Dabase Structure,2,Oracle Database Architecture,2,Oracle Database Key Tables,3,Oracle EAM,2,Oracle EBS,5,Oracle Financials,2,Oracle Forms And Reports,17,Oracle Forms Interview Questions,6,Oracle Forms Tutorials,6,Oracle GL,4,Oracle Holds,1,Oracle HRMS,10,Oracle Human Resources,1,Oracle Inventory,4,Oracle Payables,1,Oracle Payroll,1,Oracle POTutorials,1,Oracle Purchasing,7,Oracle Reports Interview,9,Oracle Reports Tutorials,3,Oracle System Administrator,4,Oracle Workflow,3,Order Management,2,Organization ID,3,Organizations,3,Performance Tuning,2,Personalization,1,PL/SQL Block,1,PL/SQL Interview Questions,11,PL/SQL Scripts,44,PL/SQL Tutorials,53,Planned Purchase Orders,1,PO,1,PO Action History,1,PO_ACTION_HISTORY,1,POracle Purchasing,1,PR,1,Procedures And Functions,2,Profile Options,2,Purchase Orders,1,Purchase Requisition,2,Receipts,1,Request Groups,1,Responsibility,1,Shell Script,1,SOA Tutorials,4,Social Media,1,Software Industry,2,Software Tools,1,SQL And PL/SQL Interview,38,SQL Interview,51,SQL Scripts,38,SQL Tutorials,43,Standard Purchase Orders,1,Troubleshooting,1,Unexpected error,1,Unix/Linux Commands,3,User,1,Value Sets,1,Weblogic,1,XML Publisher,3,
ltr
item
Oracle Apps Guy: How to call Java stored procedures/Classes from PL/SQL
How to call Java stored procedures/Classes from PL/SQL
How to call Java stored procedures/Classes from PL/SQL,How to call java classes from PLSQL
https://4.bp.blogspot.com/-jJEpLfZ74C8/V9LASUjobpI/AAAAAAAAQto/KQow23B8Rw0yCAbMliyDpmWIALtfU1ghQCEw/s1600/Load-utility.jpg
https://4.bp.blogspot.com/-jJEpLfZ74C8/V9LASUjobpI/AAAAAAAAQto/KQow23B8Rw0yCAbMliyDpmWIALtfU1ghQCEw/s72-c/Load-utility.jpg
Oracle Apps Guy
https://www.oracleappsguy.com/2016/09/how-to-call-java-classes-from-plsql.html
https://www.oracleappsguy.com/
https://www.oracleappsguy.com/
https://www.oracleappsguy.com/2016/09/how-to-call-java-classes-from-plsql.html
true
4186973960539901882
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS CONTENT IS PREMIUM Please share to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy