Call oracle from java

Call oracle from java

You can run Java stored procedures in the same way as PL/SQL stored procedures. Usually, a call to a Java stored procedure is a result of database manipulation, because it is usually the result of a trigger or SQL DML call. To call a Java stored procedure, you must publish it through a call specification.

Before you can call Java stored procedures, you must load them into Oracle Database instance and publish them to SQL. Loading and publishing are separate tasks. Many Java classes, which are referenced only by other Java classes, are never published.

To load Java stored procedures automatically, you can use the loadjava tool. It loads Java source, class, and resource files into a system-generated database table, and then uses the SQL CREATE JAVA statement to load the Java files into Oracle Database instance. You can upload Java files from file systems, popular Java IDEs, intranets, or the Internet.

You must perform the following steps for creating, loading, and calling Java stored procedures:

To load Java stored procedures manually, you can use the CREATE JAVA statements. For example, in SQL*Plus, you can use the CREATE JAVA CLASS statement to load Java class files from local BFILE and LOB columns into Oracle Database.

5.3.1 Creating or Reusing the Java Classes

Use a preferred Java IDE to create classes, or reuse existing classes that meet your requirements. Oracle Database supports many Java development tools and client-side programmatic interfaces. For example, Oracle JVM accepts programs developed in popular Java IDEs, such as Oracle JDeveloper, Symantec Visual Cafe, and Borland JBuilder.

In the following example, you create the public class Oscar . It has a single method named quote() , which returns a quotation from Oscar Wilde.

Save the class as Oscar.java . Using a Java compiler, compile the .java file on your client system, as follows:

The compiler outputs a Java binary file, in this case, Oscar.class .

5.3.2 Loading and Resolving the Java Classes

Using the loadjava tool, you can load Java source, class, and resource files into Oracle Database instance, where they are stored as Java schema objects. You can run the loadjava tool from the command line or from an application, and you can specify several options including a resolver.

In the following example, the loadjava tool connects to the database using the default JDBC OCI driver. You must specify the user name and password. By default, the Oscar class is loaded into the schema of the user you log in as, in this case, HR .

$ loadjava -user HR Oscar.class Password: password 

When you call the quote() method, the server uses a resolver to search for supporting classes, such as String . In this case, the default resolver is used. The default resolver first searches the current schema and then the SYS schema, where all the core Java class libraries reside. If necessary, you can specify different resolvers.

5.3.3 Publishing the Java Classes

For each Java method that can be called from SQL or JDBC, you must write a call specification, which exposes the top-level entry point of the method to Oracle Database. Typically, only a few call specifications are needed. If preferred, you can generate these call specifications using Oracle JDeveloper.

In the following example, from SQL*Plus, you connect to the database and then define a top-level call specification for the quote() method:

SQL> connect HR Enter password: password SQL> CREATE FUNCTION oscar_quote RETURN VARCHAR2 2 AS LANGUAGE JAVA 3 NAME 'Oscar.quote() return java.lang.String';

Related Topics

5.3.4 Calling the Stored Procedures

You can call Java stored procedures from JDBC, SQLJ, and all third party languages that can access the call specification. Using the SQL CALL statement, you can also call the stored procedures from the top level, for example, from SQL*Plus. Stored procedures can also be called from database triggers.

In the following example, you declare a SQL*Plus host variable:

SQL> VARIABLE theQuote VARCHAR2(50);

Then, you call the function oscar_quote() , as follows:

SQL> CALL oscar_quote() INTO :theQuote; SQL> PRINT theQuote; THEQUOTE -------------------------------------------------- I can resist everything except temptation.

You can also call the Java class using the ojvmjava tool.

Related Topics

Источник

Call oracle from java

The type of the Java application determines how the client calls a Java method. The following sections discuss each of the Java application programming interfaces (APIs) available for calling a Java method:

3.1.1 Using PL/SQL Wrappers

You can run Java stored procedures in the same way as PL/SQL stored procedures. In Oracle Database, Java is usually invoked through PL/SQL interface.

To call a Java stored procedure, you must publish it through a call specification. The following example shows how to create, resolve, load, and publish a simple Java stored procedure that returns a string:

• As individual files in one or more directories, where the directories are specified in the CLASSPATH
• Within .jar or .zip files, where the directories containing these files are specified in the CLASSPATH

In this case, load Hello.class on the server, where it is stored in the database as a Java schema object. When you call the world() method, Oracle JVM locates the necessary supporting classes, such as String , using a resolver. In this case, Oracle JVM uses the default resolver. The default resolver looks for these classes, first in the current schema, and then in PUBLIC . All core class libraries, including the java.lang package, are found in PUBLIC . You may need to specify different resolvers. You can trace problems earlier, rather than at run time, by forcing resolution to occur when you use the loadjava tool.

loadjava -user HR Hello.class Password: password 

To call a Java static method with a SQL call, you must publish the method with a call specification. A call specification defines the arguments that the method takes and the SQL types that it returns. In SQL*Plus, connect to the database and define a top-level call specification for Hello.world() as follows:

sqlplus HR Enter password: password connected SQL> CREATE OR REPLACE FUNCTION helloworld RETURN VARCHAR2 AS 2 LANGUAGE JAVA NAME 'Hello.world () return java.lang.String'; 3 / Function created.

SQL> VARIABLE myString VARCHAR2(20); SQL> CALL helloworld() INTO :myString; Call completed. SQL> PRINT myString; MYSTRING --------------------------------------- Hello world SQL>

The call helloworld() into :myString statement performs a top-level call in Oracle Database. SQL and PL/SQL see no difference between a stored procedure that is written in Java, PL/SQL, or any other language. The call specification provides a means to tie inter-language calls together in a consistent manner. Call specifications are necessary only for entry points that are called with triggers or SQL and PL/SQL calls. Furthermore, JDeveloper can automate the task of writing call specifications.

Related Topics

3.1.2 About JNI Support

The Java Native Interface (JNI) is a standard programming interface for writing Java native methods and embedding the JVM into native applications. The primary goal of JNI is to provide binary compatibility of Java applications that use platform-specific native libraries.

Native methods can cause server failure, violate security, and corrupt data. Oracle Database does not support the use of JNI in Java applications. If you use JNI, then your application is not 100 percent pure Java and the native methods require porting between platforms.

3.1.3 About Utilizing SQLJ and JDBC with Java in the Database

You can use SQLJ and Java Database Connectivity (JDBC) APIs from a Java client. Both APIs establish a session with a given user name and password on the database and run SQL queries against the database. The following table lists the APIs and their description:

Use this API for more complex or dynamic SQL queries. JDBC requires you to establish the session, construct the query, and so on.

Use this API for easy SQL queries, both static and dynamic. SQLJ typically runs against a known table with known column names.

This section covers the following topics:

3.1.3.1 Using JDBC

JDBC is an industry-standard API that lets you embed SQL statements as Java method arguments. JDBC is based on the X/Open SQL Call Level Interface (CLI) and complies with the Entry Level of SQL-92 standard. Each vendor, such as Oracle, creates its JDBC implementation by implementing the interfaces of the standard java.sql package. Oracle provides the following JDBC drivers that implement these standard interfaces:

• The JDBC Thin driver, a 100 percent pure Java solution that you can use for either client-side applications or applets and requires no Oracle client installation.
• The JDBC OCI driver, which you use for client-side applications and requires an Oracle client installation.
• The server-side JDBC driver embedded in Oracle Database.

Using JDBC is a step-by-step process of performing the following tasks:

1. Obtaining a connection handle
2. Creating a statement object of some type for your desired SQL operation
3. Assigning any local variables that you want to bind to the SQL operation
4. Carrying out the operation
5. Optionally retrieving the result sets

This process is sufficient for many applications, but becomes cumbersome for any complicated statements. Dynamic SQL operations, where the operations are not known until run time, require JDBC. However, in typical applications, this represents a minority of the SQL operations.

3.1.3.2 Using SQLJ

SQLJ offers an industry-standard way to embed any static SQL operation directly into the Java source code in one simple step, without requiring the multiple steps of JDBC. Oracle SQLJ complies with the X3H2-98-320 American National Standards Institute (ANSI) standard.

SQLJ consists of a translator, which is a precompiler that supports standard SQLJ programming syntax, and a run-time component. After creating your SQLJ source code in a .sqlj file, you process it with the translator. The translator translates the SQLJ source code to standard Java source code, with SQL operations converted to calls to the SQLJ run time. In Oracle Database SQLJ implementation, the translator calls a Java compiler to compile the Java source code. When your SQLJ application runs, the SQLJ run time calls JDBC to communicate with the database.

SQLJ also enables you to catch errors in your SQL statements before run time. JDBC code, being pure Java, is compiled directly. The compiler cannot detect SQL errors. On the other hand, when you translate SQLJ code, the translator analyzes the embedded SQL statements semantically and syntactically, catching SQL errors during development, instead of allowing an end user to catch them when running the application.

Following is a complete example of a simple SQLJ program:

import java.sql.*; import sqlj.runtime.ref.DefaultContext; import oracle.sqlj.runtime.Oracle; #sql iterator MyIter (String first_name, int employee_id, float salary); public class MyExample < public static void main (String args[]) throws SQLException < Oracle.connect("jdbc:oracle:thin:@localhost:5521:orcl", "HR", ""); #sql < INSERT INTO employees (first_name, employee_id, salary) VALUES ('SMITH', 32, 20000) >; MyIter iter; #sql iter=< SELECT first_name, employee_id, salary FROM employees >; while (iter.next()) < System.out.println(iter.first_name()+" "+iter.employee_id()+" "+iter.salary()); >> >

In the preceding example, you perform the following:

Declare your iterators. SQLJ uses a strongly-typed version of JDBC result sets, known as iterators. An iterator has a specific number of columns of specific data types. You must define your iterator types before using them, as in this example.

#sql ITERATOR MyIter (String first_name, int employee_id, float salary);

Oracle.connect("jdbc:oracle:thin:@localhost:5521:orcl","HR", " ");

1. Registers Oracle JDBC drivers that SQLJ uses to access the database, in this case, the JDBC Thin driver.
2. Opens a database connection for the specified schema, in this case, user HR with the specified password, at the specified URL. In this case, the URL points to host localhost , port 5521 , and SID orcl .
3. Establishes this connection as the default connection for the SQLJ statements. Although each JDBC statement must explicitly specify a connection object, a SQLJ statement can either implicitly use a default connection or optionally specify a different connection.

Источник