Home arrow Oracle arrow Page 3 - Extending PL/SQL with Java Libraries, concluded

More on Building Internal Server Java Objects - Oracle

If you are looking for a way to extend stored programs with Java, look no further. This article, the second of two parts, is excerpted from chapter five of Expert Oracle PL/SQL, written by Ron Hardman and Michael McLaughlin (McGraw-Hill/Osborne, 2005; ISBN: 0072261943).

TABLE OF CONTENTS:
  1. Extending PL/SQL with Java Libraries, concluded
  2. Building Internal Server Java Objects
  3. More on Building Internal Server Java Objects
  4. Troubleshooting Java Class Library Build, Load, Drop, and Use
  5. Mapping Oracle Types
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 12
January 19, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

If you have not built the PLSQL schema, please run the create_user.sql script now. When you have the PLSQL schema built, you can compile it with the javac utility as covered earlier in the chapter. However, there is an alternative syntax that enables you to load and compile against the Oracle JVM libraries. You can directly load a Java source, or text, file with the loadjava utility as follows:

loadjava -r -f -o -user plsql/plsql HelloWorld4.java

The loadjava utility command behaves slightly differently when you choose this option. It parses, stores the Java source as a text entry, and compiles the stored Java source into a Java byte stream in the Oracle JVM under the PLSQL schema.

TIP

After loading the Java class file into the database this way, you won’t be able to use the dropjava utility to remove the HelloWorld4.class file. Instead, use the dropjava utility to remove the HelloWorld4.java file, which also drops the HelloWorld4.class file.

You’ll need to build a SQL object type to wrap the Java stored object class. The following HelloWorld4.sql script builds the object type as a wrapper to the Java class object:

-- Available online as part of HelloWorld4.sql file.
-- Create a PL/SQL wrapper package to a Java class file.
CREATE OR REPLACE TYPE hello_world4 AS OBJECT
EXTERNAL NAME 'HelloWorld4' LANGUAGE JAVA USING SQLData
( instanceName VARCHAR2(100) EXTERNAL NAME 'java.lang.String'
, CONSTRUCTOR FUNCTION hello_world4
 
RETURN SELF AS RESULT
, MEMBER FUNCTION getQualifiedName
 
RETURN VARCHAR2 AS LANGUAGE JAVA
 
NAME 'HelloWorld4.getQualifiedName() return java.lang.String' 
, MEMBER FUNCTION getSQLTypeName
 
RETURN VARCHAR2 AS LANGUAGE JAVA
 
NAME 'HelloWorld4.getSQLTypeName() return java.lang.String' )
INSTANTIABLE FINAL;
/

The SQL object type wrapper does the following:

  1. It defines an object type using an external name that is the case-sensitive Java class name and the USING SQLData clause. The USING SQLData clause requires at least one variable with an external name that identifies the Java data type.
  2. It defines a single constructor function that takes no arguments. If you provide an overriding constructor, it will not be used.
  3. It defines two functions. One reads the instance qualifiedName value and another reads the SQLTypeName value.

NOTE

Any attempt to use SQLData without a mapped type will raise an exception. If you want to instantiate a class and not pass any variables to it, you can designate a blank VARCHAR2(1) EXTERNAL NAME ‘java.lang.String’ in the wrapper. Then, you simply avoid defining the streams in the SQLData interface methods readSQL and writeSQL and pass a NULL argument when instantiating the PL/SQL wrapper in your PL/SQL programs.

After you’ve defined the PL/SQL object type wrapper, you can see that both the object type and body have been registered in the Oracle instance metadata. You can see this by running the following query:

COL object_name FORMAT A30
COL object_type FORMAT A12
COL status      FORMAT A7
SELECT   object_name
,        object_type 
'        status
FROM     user_objects
WHERE    object_name = 'HELLO_WORLD4';

The output, if you have run everything successfully, will be the following:

OBJECT_NAME        OBJECT_TYPE     STATUS
-----------------  -----------     ------
HELLO_WORLD4       TYPE            VALID
HELLO_WORLD4       TYPE BODY       VALID

If you use the dropjava utility at this point, you’ll invalidate the TYPE BODY. Reloading the Java source file with the loadjava utility leaves the TYPE BODY in an invalid status. The first call to the object results in the following error:

--Available online as part of HelloWorld4.sql script as qualified above.
DECLARE
*
ERROR at line 1:
ORA-29549: class PLSQL.HelloWorld4 has changed, Java session state cleared
ORA-06512: at "PLSQL.HELLO_WORLD4", line 0 ORA-06512: at line 10

A second call to the object results in success, but the Oracle instance metadata will still report that the TYPE BODY is invalid. The metadata report is incorrect, but you’ll need to run an ALTER command to fix it. For example, you can use the following:

ALTER TYPE hello_world4 COMPILE BODY;

Now, you’ll test this PL/SQL object type wrapper by instantiating two object instances with the following script:

-- Available online as part of HelloWorld4.sql file.
DECLARE
 
-- Define and instantiate an object instance.
  my_obj1 hello_world4 := hello_world4('Adam');
  my_obj2 hello_world4 := hello_world4('Eve');
BEGIN
 
-- Test class instance. dbms_output.put_line('Item #1: ['||my_obj1.getQualifiedName||']'); dbms_output.put_line('Item #2: ['||my_obj2.getQualifiedName||']'); dbms_output.put_line('Item #3: ['||my_obj1.getSQLTypeName||']'); dbms_output.put_line('Item #4: ['||my_obj1.getSQLTypeName||']');
 
-- Test metadata repository with DBMS_JAVA.
  dbms_output.put_line(
    'Item #5: ['||user||'.'||dbms_java.longname('HELLO_WORLD4')||']');
END;
/

You should see the following output displayed:

Item #1: [PLSQL.HelloWorld4.class.Adam]
Item #2: [PLSQL.HelloWorld4.class.Eve]
Item #3: [PLSQL.HELLO_WORLD4]
Item #4: [PLSQL.HELLO_WORLD4]
Item #5: [PLSQL.HELLO_WORLD4]

The SQLData interface allows you to pass a User Defined Type (UDT), which means you can use any defined user structure. If you debug the execution of the Java instance, you’ll find that each invocation of the instance method actually reinstantiates the class instance.

The next section discusses troubleshooting the Java class library processes that build, load/drop, and use Java server stored object classes.



 
 
>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: