MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle’ Category

Java Subclass with Struct

without comments

This blog post relies on the “How to write a Java Class with the SQLData Interface” blog post. Oracle object types support subclasses with the UNDER keyword from Oracle Database 10g forward. Naturally, you can create Java classes that subclass another Java class.

A subclass is a specialized behavior of a parent class. Parent classes are considered more generalized than their subclasses. There are certain rules and behaviors for using Java subclasses as Oracle object subtypes. That’s why we wrote this section.

The two subsections show you how to implement a Java subclass and how to implement an Oracle object subtype.

Creating a Java Subclass

Like Java classes and Oracle object types, you must implement the Java class file first, and the PL/SQL wrapper second. Java subclasses rely on the existence of their parent, and object subtypes rely on the existence of their parent.

The following ItemSt class is a subclass or specialization of the Java Item class. You should take note that it doesn’t redefine the attributes of the parent class. The ItemSt class does add one new attribute, so we can demonstrate how Java subclasses or Oracle subtypes work.

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ItemSt" AS
  2    // JAVA LIBRARY imports.
  3    import JAVA.SQL.*;
  4    import JAVA.io.*;
  5    import oracle.SQL.*;
  6    import oracle.jdbc.*;
  7    import oracle.oracore.*;
  8    import oracle.jdbc2.*;
  9    import JAVA.math.*;
 10
 11    PUBLIC class ItemSt EXTENDS Item implements SQLData
 12    {
 13      // Implement the attributes AND operations FOR this TYPE.
 14      PRIVATE String bluray;
 15
 16      // A getter FOR the rating attribute.
 17      PUBLIC String getBluray() {
 18        RETURN this.bluray; }
 19
 20      // A setter FOR the rating attribute.
 21      PUBLIC void setBluray(String bluray) {
 22        this.bluray = NEW String(bluray); }
 23
 24      // A setter FOR this object.
 25      PUBLIC void setItem(Struct item) throws JAVA.SQL.SQLException {
 26
 27        // Assign Item instance variables.
 28        super.setItem(item);
 29
 30        // Get the attributes OF the Item object.
 31        Object[] attributes = (Object[]) item.getAttributes();
 32
 33        // Assign the SUBTYPE element.
 34        this.bluray = (String) attributes[6]; }
 35
 36      // DECLARE an instance toString method.
 37      PUBLIC String toString() {
 38        RETURN super.toString() + "Blu-ray [" + this.bluray +"]\n"; }
 39
 40      /* Implement SQLData interface.
 41      || --------------------------------------------------------
 42      || Required interface components:
 43      || ==============================
 44      || 1. String sql_type instance variable.
 45      || 2. getSQLTypeName() method returns the sql_type value.
 46      || 3. readSQL() method to read from the Oracle session.
 47      || 4. writeSQL() method to write to the Oracle session.
 48      */
 49
 50      // Required INTERFACE variable.
 51      PRIVATE String sql_type;
 52
 53      // Reads the stream FROM the Oracle SESSION.
 54      PUBLIC void readSQL(SQLInput stream, String typeName) throws SQLException {
 55
 56          // CALL TO parent class.
 57          super.readSQL(stream, typeName);
 58
 59          // Map instance variables.
 60          sql_type = typeName;
 61          bluray = stream.readString(); }
 62
 63      // Writes the stream TO the Oracle SESSION.
 64      PUBLIC void writeSQL(SQLOutput stream) throws SQLException {
 65        // CALL TO parent class.
 66        super.writeSQL(stream);
 67
 68        // Map instance variables.
 69        stream.writeString(bluray); }
 70
 71      /*
 72      || --------------------------------------------------------
 73      || End Implementation of SQLData interface.
 74      */
 75  }
 76  /

Lines 25 through 34 show you how to handle a Struct of the ItemSt object type. This is where we implement a specialized setItem() method. You should note that in line 28 the subclass calls the parent class (or super) setItem() method. The balance of the setItem() method converts the Struct to an array of objects and then assigns the correct element to the
bluray variable.

Lines 38 and 39 show you how to handle the toString() method of a subclass. Like the setItem() method, the toString() method must call super to get the behavior of the parent
before adding on its own behavioral characteristics. Lines 57 and 66 also make super calls to the
readSQL() and writeSQL() methods of the parent class.

You should discover from this example that a subclass depends on the program logic in the parent class. Also, you should see that subclass methods generally override behaviors of equivalent methods in the subclass.

Testing the Java Subclass

Testing subclass behavior is also known as testing polymorphic behavior. Polymorphic behavior occurs when a subclass performs differently from its parent and sibling classes.

We need to slip one more example in here, and that’s a stand-alone function that constructs either a super class or subclass. That’s done with the following get_item function. You should note two things about the get_item function. First, it takes a list of parameters necessary to create an item_obj_st instance. Second, it makes any subtype-only parameters optional parameters in the function’s signature.

SQL> CREATE OR REPLACE FUNCTION get_item
  2  ( id NUMBER
  3  , title VARCHAR2
  4  , subtitle VARCHAR2
  5  , rating VARCHAR2
  6  , rating_agency VARCHAR2
  7  , release_date DATE
  8  , bluray VARCHAR2 DEFAULT NULL )
  9  RETURN item_obj IS
 10    /* Declare a local variable. */
 11    lv_item ITEM_OBJ;
 12  BEGIN
 13    /* Check for the subtype attribute. */
 14    IF bluray IS NULL THEN
 15      lv_item := item_obj( id
 16                         , title
 17                         , subtitle
 18                         , rating
 19                         , rating_agency
 20                         , release_date);
 21    ELSE
 22      lv_item := item_obj_st( id
 23                            , title
 24                            , subtitle
 25                            , rating
 26                            , rating_agency
 27                            , release_date
 28                            , bluray);
 29    END IF;
 30
 31    /* Return a type. */
 32    RETURN lv_item;
 33  END;
 34  /

Line 8 provides the bluray attribute, which only belongs to the item_obj_st object type. The bluray parameter is optional because an item_obj instance can ignore that parameter. The IF statement on line 14 checks whether the subtype’s bluray variable is null. The get_item function creates an item_obj instance when the bluray variable is null and an item_obj_st when the bluray variable isn’t null.

The simplest way to test polymorphic behavior is an anonymous PL/SQL block. The following constructs item_obj and item_obj_st instances by leveraging our get_item function, and it assigns the result from the get_item function to a collection of the generalized item_obj class. Then, the anonymous block reads through the collection in a range FOR loop.

SQL> DECLARE
  2    /* Declare a generalized object type. */
  3    lv_item_tab ITEM_TAB := item_tab();
  4  BEGIN
  5    /* Assign an object to a generalization. */
  6    lv_item_tab.EXTEND;
  7    lv_item_tab(lv_item_tab.COUNT) :=
  8      get_item( id => 2
  9              , title => 'The Hobbit'
 10              , subtitle => 'The Desolation of Smaug'
 11              , rating => 'PG-13'
 12              , rating_agency => 'MPAA'
 13              , release_date => '13-DEC-2013');
 14
 15    /* Assign an object to a specialization. */
 16    lv_item_tab.EXTEND;
 17    lv_item_tab(lv_item_tab.COUNT) :=
 18      get_item( id => 3
 19              , title => 'The Hobbit'
 20              , subtitle => 'The Battle of the Five Armies'
 21              , rating => 'PG-13'
 22              , rating_agency => 'MPAA'
 23              , release_date => '17-DEC-2014'
 24              , bluray => 'Sony Blu-ray');
 25
 26    /* Print header line. */
 27    dbms_output.put_line(
 28      '---------------------------------------------');
 29
 30    /* Print items in collection. */
 31    FOR i IN 1..lv_item_tab.COUNT LOOP
 32    /* Print the contents of the item_obj. */
 33    parse_rows(lv_item_tab(i).to_string());
 34    dbms_output.put_line(
 35       '---------------------------------------------');
 36    END LOOP;
 37  END;
 38  /

For reference, the parse_rows procedure is in the “How to write a Java Class with the SQLData Interface” blog post.

Lines 8 through 14 assign an item_obj instance to the lv_item_tab collection, and lines 18 through 26 assign an item_obj_st instance to the lv_item_tab collection. Line 35 uses the parse_rows procedure.

It should print:

---------------------------------------------
ID # [2]
Title [The Hobbit: The Desolation of Smaug]
Rating [MPAA:PG-13]
Release [2013-12-13]
---------------------------------------------
ID # [3]
Title [The Hobbit: The Battle of the Five Armies]
Rating [MPAA:PG-13]
Release [2014-12-17]
Blu-ray [Sony Blu-ray]
---------------------------------------------

The first element prints the item_obj output, and the second element prints the item_obj_st output. Please take note that Oracle determined which to run based on their base object type, and you didn’t need to write any specialized instanceOf logic.

This collection of articles should have enabled you to learn the necessary skills to implement Java classes inside the Oracle Database. The SQLData Interface affords the ability to read and write to the database and those methods may be replaced by other more direct methods using the JDBC standard approaches.

Written by maclochlainn

October 5th, 2025 at 8:23 pm

Posted in Java,Oracle

Java Class with Struct

without comments

If you’re new to writing Java classes that use the SQLData interface, inside the Oracle Database, please read the blog post on: “How to build a Java Class with the SQLData Interface”. This is a much larger example that shows you how to implement a Struct as a parameter option and how to work with other input and output data types.

Building the Java Object Class

The Item Java source file provides us with a lot of topics to cover. We debated about breaking the code up for display, but decided against it. In this section, we simply show you a complete Java
example class. We did limit the example to one getter, the getRating() method, and one setter, the setRating() method, for single attribute values.

We also gave you a getName() method that lets you capture the Java class name, and a setItem() method that lets you set the list of class attributes with a Struct object type. The setItem() method shows you how to set the instance values through a direct method call rather than the readSQL() method implementation of the SQLData interface.

The following SQL statement creates the Item class in an Oracle Database as follows:

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Item" AS
  2
  3 // JAVA LIBRARY imports.
  4 import JAVA.SQL.*;
  5 import JAVA.io.*;
  6 import oracle.SQL.*;
  7 import oracle.jdbc.*;
  8 import oracle.oracore.*;
  9 import oracle.jdbc2.*;
 10 import JAVA.math.*;
 11
 12 PUBLIC class Item implements SQLData
 13 {
 14   // Implement the attributes AND operations FOR this TYPE.
 15   PRIVATE BigDecimal id;
 16   PRIVATE String title;
 17   PRIVATE String subtitle;
 18   PRIVATE String rating;
 19   PRIVATE String ratingAgency;
 20   PRIVATE DATE releaseDate;
 21
 22   // A getter FOR the rating attribute.
 23   PUBLIC String getRating() {
 24     RETURN this.rating; }
 25
 26   // A getter FOR the class instance name.
 27   PUBLIC String getName() {
 28     RETURN this.getClass().getName(); }
 29
 30   // A getter FOR the rating attribute.
 31   PUBLIC String getUserName() {
 32     
 33     // DECLARE local variables.
 34     String userName = NEW String();
 35     String query = "SELECT user";
 36
 37     // CREATE a connection discover a USER name.
 38     try {
 39       // DECLARE an Oracle connection.
 40       OracleConnectionWrapper conn =
 41         (oracle.jdbc.OracleConnectionWrapper)
 42         DriverManager.getConnection("jdbc:default:connection:");
 43
 44       // Prepare AND EXECUTE a statement.
 45       JAVA.SQL.PreparedStatement ps = conn.prepareStatement(query);
 46       ResultSet rs = ps.executeQuery();
 47
 48       // Read the result SET.
 49       WHILE (rs.next())
 50         userName = rs.getString(1); }
 51     catch (SQLException e) {}
 52       // RETURN a USER name.
 53       RETURN userName; }
 54
 55   // A setter FOR this object.
 56   PUBLIC void setItem(Struct item) throws JAVA.SQL.SQLException {
 57
 58   // Get the attributes OF the Item object.
 59   Object[] attributes = (Object[]) item.getAttributes();
 60
 61   // Assign Item instance variables.
 62   this.id = (BigDecimal) attributes[0];
 63   this.title = (String) attributes[1];
 64   this.subtitle = (String) attributes[2];
 65   this.rating = (String) attributes[3];
 66   this.ratingAgency = (String) attributes[4];
 67   this.releaseDate = NEW DATE(((TIMESTAMP) attributes[5]).getTime()); }
 68
 69   // A setter FOR the rating attribute.
 70   PUBLIC void setRating(String rating) {
 71     this.rating = rating; }
 72 
 73   // DECLARE an instance toString method.
 74   PUBLIC String toString() {
 75     RETURN "ID # [" + this.id + "]\n" + "Title [" + this.title + ": " + this.subtitle +"]\n" +
 76            "Rating [" + this.ratingAgency + ":" + this.rating + "]\n" +
 77            "Release [" + this.releaseDate + "]\n"; }
 78
 79  /* Implement SQLData interface.
 80  || --------------------------------------------------------
 81  || Required interface components:
 82  || ==============================
 83  || 1. String sql_type instance variable.
 84  || 2. getSQLTypeName() method returns the sql_type value.
 85  || 3. readSQL() method to read from the Oracle session.
 86  || 4. writeSQL() method to write to the Oracle session.
 87  */
 88
 89  // Required INTERFACE variable.
 90  PRIVATE String sql_type;
 91
 92  // Returns the INTERFACE required variable VALUE.
 93  PUBLIC String getSQLTypeName() throws SQLException {
 94    RETURN this.sql_type; }
 95
 96  // Reads the stream FROM the Oracle SESSION.
 97  PUBLIC void readSQL(SQLInput stream, String typeName) throws SQLException {
 98
 99    // Map instance variables.
100    this.sql_type = typeName;
101    this.id = stream.readBigDecimal();
102    this.title = stream.readString();
103    this.subtitle = stream.readString();
104    this.rating = stream.readString();
105    this.ratingAgency = stream.readString();
106    this.releaseDate = stream.readDate(); }
107
108    // Writes the stream TO the Oracle SESSION.
109    PUBLIC void writeSQL(SQLOutput stream) throws SQLException {
110
111  // Map instance variables.
112  stream.writeBigDecimal(this.id);
113  stream.writeString(this.title);
114  stream.writeString(this.subtitle);
115  stream.writeString(this.rating);
116  stream.writeString(this.ratingAgency);
117  stream.writeDate(this.releaseDate); }
118
119  /*
120  || --------------------------------------------------------
121  || End Implementation of SQLData interface.
122  */
123  }
124 /

The generic getter on lines 23 and 24 lets you get the instance value of the rating attribute, and the generic setter on lines 70 and 71 lets you override the rating instance variable. Lines 56 through 67 contain the logic for the setItem method. The setItem method takes a Struct parameter, which illustrates how you can use a composite data type. Line 57 converts the fields of the Struct parameter to an array of objects. Most of the conversions are straightforward and map through simple casting operations based on the Oracle Database rules. A date data type in the Oracle database is a DATE data type. While you may think that an Oracle DATE data type should convert to a Java Date class, it doesn’t. An Oracle DATE inside a Struct converts to a Timestamp class. You must cast the Java Timestamp class to a Java Date class through a two-step method. First, use the getTime() method to capture the long value of the Timestamp. Second, construct a new Date class by using the long value result from the getTime() method.

Line 67 from the preceding Item class example demonstrate the required Oracle DATE to Java Timestamp technique:

67

  this.releaseDate = new Date(((Timestamp) attributes[5]).getTime()); }

Line 90 declares the sql_type variable. Lines 93 and 94 implement the getSQLTypeName() method for the SQLData interface. Lines 97 through 106 implement the readSQL() method, and lines 109 through 117 implement the writeSQL() method for the SQLData interface.

After creating and compiling the Item class, you need to create an object type for it. The object type wraps access to the Java class file.

Creating the PL/SQL Object Type

The Item object type provides us with the syntax for mapping object methods against native Java classes. As a refresher, you create object types with a CREATE TYPE statement. Then, you can deploy them as persistent or transient objects. Persistent objects can be either stand-alone or embedded objects. Stand-alone objects are stored in a database table and have a unique identifier. Embedded objects are not stored in a database table but are embedded in another Oracle structure, like a stored program parameter, return type, or as an attribute of another object type. Transient objects are instances of objects that aren’t stored in the database. They have a lifetime limited to the duration of their use in a PL/SQL block.

You create an instance of an object type and use an INSERT or UPDATE statement to assign it as a persistent object in a table. Alternatively, you can retrieve an object type from an IN OUT or OUT-only mode parameter, or get one as the output parameter of a function.

The following CREATE TYPE statement creates the object declaration in the session scope. You should note that each object type’s methods point to valid Java methods from the same Java class.

SQL> CREATE OR REPLACE TYPE item_obj IS OBJECT
  2  ( id NUMBER
  3  , title VARCHAR2(60)
  4  , subtitle VARCHAR2(60)
  5  , rating VARCHAR2(8)
  6  , rating_agency VARCHAR2(4)
  7  , release_date DATE
  8  , MEMBER FUNCTION get_rating RETURN VARCHAR2
  9  AS LANGUAGE JAVA
 10  NAME 'Item.getRating() return java.lang.String'
 11  , MEMBER FUNCTION get_name RETURN VARCHAR2
 12    AS LANGUAGE JAVA
 13    NAME 'Item.getName() return java.lang.String'
 14  , MEMBER FUNCTION get_sql_type RETURN VARCHAR2
 15    AS LANGUAGE JAVA
 16    NAME 'Item.getSQLTypeName() return java.lang.String'
 17  , MEMBER FUNCTION get_user_name RETURN VARCHAR2
 18    AS LANGUAGE JAVA
 19    NAME 'Item.getUserName() return java.lang.String'
 20  , MEMBER PROCEDURE set_item (item ITEM_OBJ)
 21    AS LANGUAGE JAVA
 22    NAME 'Item.setItem(java.sql.Struct)'
 23  , MEMBER PROCEDURE set_rating (rating VARCHAR2)
 24    AS LANGUAGE JAVA
 25    NAME 'Item.setRating(java.lang.String)'
 26  , MEMBER FUNCTION to_string RETURN VARCHAR2
 27    AS LANGUAGE JAVA
 28    NAME 'Item.toString() return java.lang.String')
 29  INSTANTIABLE NOT FINAL;
 30  /

Lines 2 through 7 declare a list of attributes. The physical size assigned to the VARCHAR2 data types limits the size of the Java String attributes. Any attempt to pass a variable-length string longer than the imposed limit in the PL/SQL object type definition raises a “character string buffer too small” error.

Line 20 shows a set_item procedure that passes a SQL item_obj object type, which maps to a java.sql.Struct on line 22. All the other parameters and return types use Java String classes.

The next section shows you how to test the Java class instance by calling the PL/SQL object type in various contexts.

Testing the Java Class File

This section shows you how to insert instances of the item object type into an item_struct table, and how to query the persistent item_obj object instance from the item_struct table. Then, this section shows you how to work with transient object instances in an anonymous PL/SQL block.

The following CREATE TABLE statement creates the item_struct sample table:

SQL> CREATE TABLE item_struct
  2  ( item_struct_id NUMBER DEFAULT item_struct_id_seq.NEXTVAL
  3  , item_struct ITEM_OBJ);

You can construct and insert an item_obj object instance to the item_struct table with the following syntax:

SQL> INSERT INTO item_struct VALUES
  2  ( item_struct_s.NEXTVAL
  3  , item_obj( 1
  4  ,'The Hobbit'
  5  ,'An Unexpected Journey'
  6  ,'PG-13'
  7  ,'MPAA'
  8  ,'14-DEC-2012'));

Lines 3 through 8 create an instance of (instantiate) the item_obj type. The instance is assigned as a flattened object to the VALUES clause of the INSERT statement, and is inserted into the item_struct table.

Any attempt to access a single object type from a table in a query returns

ORA-22905: cannot ACCESS ROWS FROM a non-nested TABLE item

To avoid this error, you create an item_tab or table collection of item_obj with the following syntax:

SQL> CREATE OR REPLACE
  2    TYPE item_tab IS TABLE OF item_obj;
  3  /

Now, you can query persistent copies of the item_obj data type like this:

SQL> COLUMN title FORMAT A36 HEADING "Title"
SQL> COLUMN rating FORMAT A6 HEADING "Rating"
SQL> COLUMN rating_agency FORMAT A6 HEADING "Rating|Agency"
SQL> COLUMN release_date FORMAT A9 HEADING "Release|Date"
SQL> SELECT title||': '||subtitle AS title
  2  , rating
  3  , rating_agency
  4  , release_date
  5  FROM item_struct
  6  CROSS JOIN
  7  TABLE(
  8    SELECT CAST(COLLECT(item_struct) AS item_tab));

The combination of the SQL COLLECT, CAST, and TABLE built-in functions on lines 7 and 8 let us query persistent objects. Combining these SQL functions with a CROSS JOIN lets us write a SELECT statement to query persistent object types. The preceding query prints the following when you have one or more rows in the item_struct table:

                                            Rating Release
Title                                Rating Agency Date
------------------------------------ ------ ------ ---------
The Hobbit: An Unexpected Journey PG-13 MPAA 14-DEC-12

An anonymous PL/SQL block provides a more robust test case solution. The following shows you how to create and assign an object type to a local variable, and shows you how to test the getter functions and setter procedures:

SQL> DECLARE
  2    /* Create an object type instance. */
  3    lv_item ITEM_OBJ :=
  4              item_obj( 1
  5                      ,'The Hobbit'
  6                      ,'An Unexpected Journey'
  7                      ,'PG-13'
  8                      ,'MPAA'
  9                      ,'14-DEC-2012');
 10  BEGIN
 11    /* Print the getter rating result. */
 12    dbms_output.put_line(
 13      '---------------------------------------------');
 14    dbms_output.put_line(
 15      'Rating Value: ['||lv_item.get_rating()||']');
 16
 17    /* Set the value of the rating. */
 18    lv_item.set_rating('PG');
 19
 20    /* Print the getter rating result. */
 21    dbms_output.put_line(
 22      'Rating Value: ['||lv_item.get_rating()||']');
 23    dbms_output.put_line(
 24      '---------------------------------------------');
 25
 26    /* Print user name and sql_type value. */
 27    dbms_output.put_line(
 28      'User Name: ['||lv_item.get_user_name()||']');
 29    dbms_output.put_line(
 30      'Class Name: ['||lv_item.get_name()||']');
 31    dbms_output.put_line(
 32      'Object Name: ['||lv_item.get_sql_type()||']');
 33
 34     /* Print the toString value. */
 35    dbms_output.put_line(
 36       '---------------------------------------------');
 37     parse_rows(lv_item.to_string());
 38     dbms_output.put_line(
 39       '---------------------------------------------');
 40  END;
 41  /

Lines 3 through 9 create an instance of the item_obj object type and assign the instance to the lv_item local variable. Lines 14 and 15 print the current instance’s rating value, line 18 resets the instance’s rating value, and lines 21 and 22 print the modified rating value. These three activities demonstrate a call to the getter of an object type’s attribute before and after the call to the setter to reset the value of an object type’s attribute.

Lines 27 through 32 show how you can call several of the other getter functions, like the get_user function. The get_userto_string procedure of the lv_item local variable as a parameter to the parse_rows procedure.

The toString() methods of Java classes often return long strings. The parse_rows procedure lets you avoid the forced line breaks imposed by the dbms_output package. Wrapping text in a call to the following parse_rows procedure lets you avoid the 80-character limit of the put_line procedures:

SQL> CREATE OR REPLACE PROCEDURE parse_rows
  2  ( pv_text VARCHAR2 ) IS
  3    /* Declare parsing indexes. */
  4    lv_start NUMBER := 1;
  5    lv_end NUMBER := 1;
  6    lv_length NUMBER;
  7  BEGIN
  8    /* Assign an end value by parsing line return. */
  9    IF INSTR(pv_text,CHR(10),lv_start) = 0 THEN
 10      lv_end := LENGTH(pv_text) + 1;
 11    ELSE
 12      lv_end := INSTR(pv_text,CHR(10),lv_start);
 13    END IF;
 14
 15    /* Assign a length value to parsed strings. */
 16    lv_length := lv_end - lv_start;
 17
 18    /* Print first line. */
 19    dbms_output.put_line(SUBSTR(pv_text,lv_start,lv_length));
 20
 21    /* Print the rows of a multiple line string. */
 22    WHILE (lv_end < LENGTH(pv_text)) LOOP
 23
 24      /* Assign a new start and end value. */
 25      lv_start := lv_end + 1;
 26      IF INSTR(pv_text,CHR(10),lv_start + 1) = 0 THEN
 27        lv_end := LENGTH(pv_text) + 1;
 28      ELSE
 29        lv_end := INSTR(pv_text,CHR(10),lv_start + 1);
 30      END IF;
 31      lv_length := lv_end - lv_start;
 32
 33      /* Print the individual rows. */
 34      dbms_output.put_line(SUBSTR(pv_text,lv_start,lv_length));
 35
 36    END LOOP;
 37  END;
 38  /

It prints the following:

---------------------------------------------
Rating Value: [PG-13]
Rating Value: [PG]
---------------------------------------------
User Name: [VIDEO]
Class Name: [Item]
Object Name: [VIDEO.ITEM_OBJ]
---------------------------------------------
ID # [1]
Title [The Hobbit: An Unexpected Journey]
Rating [MPAA:PG]
Release [2012-12-14]
---------------------------------------------

The SELECT statement and anonymous PL/SQL blocks have shown you how to work with and test Oracle object types, that insert and query data. The next step would require you to learn how to use object subclasses, which are in the “How to write a Java Subclass with the SQLData Interface” blog post.

Written by maclochlainn

October 5th, 2025 at 11:34 am

Posted in Java,Oracle

Tagged with

Build SQLData Java Class

without comments

If you’re new to writing Java inside the Oracle Database, please read the following two blog posts in sequence first: How to build a Deterministic Java Library and how to build a JDBC-Enabled Java Library.

Implementing the SQLData interface is done by providing a variable definition and three concrete methods in your Java class file. The following are the required components:

  • A String data type named sql_type.
  • A getSQLTypeName() method that returns a String data type.
  • A readSQL() method that takes two formal parameters and returns a void. One formal parameter is a SQLInput that contains a stream. The other is a string that contains a data type name.
  • A writeSQL() method that takes one formal parameter, which is a SQLOutput that contains a stream.

Oracle Database 12c forward also provides the OracleData and OracleDataFactory interfaces. Applying the Factory pattern is inherently more complex. Since applying the pattern requires advanced Java coding skills, they’re not covered in this blog post.

The SQLData interface only maps SQL objects. The OracleData interface lets you map SQL objects as well as any other SQL data type. The OracleData interface is necessary when you want to serialize RAW data in Java libraries. Please check the Oracle Database JDBC Developer’s Guide for more information on the OracleData and OracleDataFactory interfaces.

Subsequent examples in this chapter show you how to implement runtime Java classes. The HelloWorld4.java class file shows you a minimalist Java library to support a runtime instance of the HelloWorld4 object.

The source code for the class is as follows (with line numbers added for your convenience
while reading the discussion of the code):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

// Oracle class imports.
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
import oracle.oracore.*;
import java.math.*; // Needed for BigDecimal maps of NUMBER data type.
 
// Class definition.
public class HelloWorld4 implements SQLData {
 
// Declare class instance variable.
private String whom;
 
  // Declare getter for SQL data type value.
  public String getSQLTypeName() throws SQLException {
    return sql_type; }
 
  // Required interface variable.
  private String sql_type;
 
  // Implements readSQL() method from the SQLData interface.
  public void readSQL(SQLInput stream, String typeName)
    throws SQLException {
    sql_type = typeName;
    this.whom = stream.readString(); }
 
  // Implements writeSQL() method from the SQLData interface.
  public void writeSQL(SQLOutput stream) throws SQLException {
    stream.writeString(whom); }
 
  // Declare a toString method.
  public String toString() {
    String datatype = null;
 
    try {
      datatype = getSQLTypeName(); }
    catch (SQLException e) {}
 
    // Return message.
    return datatype + " says hello [" + this.whom + "]!\n"; } 
}

The Java class implements the SQLData interface. You must create four elements to implement the SQLData interface. The first element requires declaring a private sql_type variable (declared on line 20). The sql_type variable holds an object type name defined in the Oracle Database. The second element, on lines 23 through 26, lets you read data from the database into the Java object instance through a SQLInput stream. The third element, on lines 29 and 30, lets you write any changes to the local copies of the data (held in the Java class instance) into the database. The getSQLTypeName() method lets you read the user-defined data type from the class. The sample code uses the getSQLTypeName() method to print a message from the toString() method.

If you have not built a test schema, you should do so now. The code in this example runs in either a container database (CDB) or a pluggable database (PDB). We’re using a video PDB database with a trivial video password.

The HelloWorld4.java program contains import statements that require you to place the ojdbcX.jar file (where X is the version required for your Oracle Database’s compatibility) in your CLASSPATH environment variable. If you have not done so, you should do so now. The file is found in your $ORACLE_HOME/jdbc/lib directory on Linux or Unix, and %ORACLE_HOME%\jdbc\lib directory on Windows.

You can directly load a Java source file with the loadjava utility as follows into a CDB
schema with the default listener:

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

A slight change is required when you load this into a PDB. The following shows you must add the TNS alias to the credentials, which in this case is also videodb:

loadjava -r -f -o -user video/video@videodb 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 videodb schema.

Creating the Java class file is only the first step. Next, you need to create an Oracle object type that wraps the Java class. Object types are declared as SQL data types and are implemented in PL/SQL, or callable languages such as Java.

Just a quick note: 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.

Building the Object Type Wrapper

You’ll need to build a SQL object type to wrap the Java stored object class. That means you have to connect to the Oracle database. You can connect to a CDB or a PDB.

Assuming you have a c##video CDB schema, connect as follows:

sqlplus c##video/password

Assuming you have a videodb PDB schema, you connect as follows:

sqlplus video/password@videodb

The following HelloWorld4.sql script builds the object type as a wrapper to the Java class object:

SQL> CREATE OR REPLACE TYPE hello_world4 AS OBJECT
  2  ( whom VARCHAR2(100)
  3  , MEMBER FUNCTION get_sql_type_name
  4    RETURN VARCHAR2 AS LANGUAGE JAVA
  5    NAME 'HelloWorld4.getSQLTypeName() return java.lang.String';
  6  , MEMBER FUNCTION to_string
  7    RETURN VARCHAR2 AS LANGUAGE JAVA
  8    NAME 'HelloWorld4.toString() return java.lang.String'; )
 10  INSTANTIABLE FINAL;
 11  /

The SQL object type declares a local whom attribute. The whom attribute has a maximum length of 100 characters. Both line 5 and line 8 wrap the name of the Java class name, method, and return type. As signatures to your methods become longer, we recommend that you take advantage of the ability to break these strings across multiple lines for readability.

You can create a table collection of the HELLO_WORLD4 type with this syntax:

SQL> CREATE OR REPLACE
  2  TYPE hello_tab IS TABLE OF HELLO_WORLD4;
  3  /

Now, you can test this in SQL with the following complex query:

SQL> SELECT *
  2  FROM   TABLE(
  3           SELECT CAST(COLLECT(hello_world4('Bilbo')) AS hello_tab));

You must collect the object type into a table collection and cast it to a table collection of the hello_tab base type. Then, you use the TABLE function to return a SQL result set.

The query prints from the toString() method of the HelloeWorld4 class:

VIDEO.HELLO_WORLD4 says hello [Bilbo]!

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.

Written by maclochlainn

October 5th, 2025 at 10:02 am

Posted in Java,Oracle,pl/sql

Tagged with

JDBC-Enabled Java Library

without comments

This section shows you how to build Java libraries that interact with data in an Oracle Database and relies on this earlier blog post. Here you build Java methods to support both functions and procedures. Building a procedure follows a very similar rule to how you build functions.

While PL/SQL functions and procedures have IN, IN OUT, and OUT parameter modes, you can’t use IN OUT mode in PL/SQL when wrapping a scalar parameter in a Java method. All formal parameters are defined as IN mode only. When you want output back, you should write a PL/SQL function, not a procedure.

The Oracle Database raises the following exception when you attempt to define a packagebody with a procedure using IN OUT modes:

PLS-00235: the external TYPE IS NOT appropriate FOR the parameter

In this section you create functions and procedures with IN-only mode parameters. The difference between how you implement a Java method to support a function or procedure is simple. A Java method returns any data type except a void to support a PL/SQL function wrapper, and a Java method returns only a void data type when supporting a PL/SQL procedure.

There are two subsections. The first shows you how to work with scalar data types, such as strings. The second shows you how to work with Attribute Data Types (ADTs), which are collections of scalar data types.

JDBC Enabled Using Scalar Data Types

This section revisits how you will create a Java library externally and use the loadjava utility to deploy a Java class file into the database. You should note that we’ve tried to conserve space and show how to bind variables, which means the code passes a SQL statement and parameter to each of these Java methods.

The following Java source file supports both a function and a procedure:

// Oracle class imports.
import java.sql.*;
import oracle.jdbc.driver.*;
 
  // Class definition.
  public class HelloWorld3 {
  public static void doDML(String statement
                          ,String name) throws SQLException {
 
    // Declare an Oracle connection.
    Connection conn =
      DriverManager.getConnection("jdbc:default:connection:");
 
    // Declare prepared statement, run query and read results.
    PreparedStatement ps = conn.prepareStatement(statement);
    ps.setString(1,name);
    ps.execute(); }
 
  public static String doDQL(String statement) throws SQLException {
 
    // Define and initialize a local return variable.
    String result = new String();
 
    // Declare an Oracle connection.
    Connection conn =
      DriverManager.getConnection("jdbc:default:connection:");
 
    // Declare prepared statement, run query and read results.
    PreparedStatement ps = conn.prepareStatement(statement);
    ResultSet rs = ps.executeQuery();
 
    while (rs.next())
      result = rs.getString(1);
    return result; }
}

This program creates a Java Connection instance using the Oracle Database’s internal process connection. The
process calls a static getConnection() method of the DriverManager class. The program implements two methods—one to insert records and another to query records. While these methods don’t use explicit cursors, statements and result sets persist across calls, and their finalizers do not release database cursors. You should remember to always close explicitly opened
cursors.

The INSERT statement returns a void, and the query returns a string.

There is no main() method in the HelloWorld3.java class file. Including a main() method to test the program externally to the database would require changing the connection to a client-side or OCI driver. As with the other Java libraries, HelloWorld3.java is a library file. Assuming you’re using a demo schema, you could load the Java class file into the Oracle JVM with the loadjava utility using the following:

loadjava -r -f -o -user video/video@video HelloWorld3.class

The loadjava utility command loads the Java HelloWorld3 class file into the Oracle JVM under the PLSQL schema. After loading the Java class file into the database, you need to build an example table and PL/SQL wrapper to use it.

The following HelloWorld3.sql script builds the package and package body as a wrapper to the Java class library. The definition of the package specification is

SQL> CREATE OR REPLACE PACKAGE hello_world3 AS
  2    PROCEDURE doDML
  3    ( dml VARCHAR2
  4    , input VARCHAR2 );
  5
  6    FUNCTION doDQL
  7    ( dql VARCHAR2 )
  8    RETURN VARCHAR2;
  9  END hello_world3;
 10  /

The definition of the package body is

SQL> CREATE OR REPLACE PACKAGE BODY hello_world3 AS
  2    PROCEDURE doDML
  3    ( dml VARCHAR2
  4    , input VARCHAR2 ) IS
  5    LANGUAGE JAVA
  6    NAME 'HelloWorld3.doDML(java.lang.String,java.lang.String)';
  7
  8    FUNCTION doDQL
  9    ( dql VARCHAR2 )
 10    RETURN VARCHAR2 IS
 11    LANGUAGE JAVA
 12    NAME 'HelloWorld3.doDQL(java.lang.String) return String';
 13  END hello_world3;
 14  /

This program defines two methods:

  • The doDML procedure takes two formal parameters that are VARCHAR2 data types and returns nothing as a stored procedure.
  • The doDQL function takes one formal parameter that is a VARCHAR2 and returns a VARCHAR2 data type as a stored function.

You can verify that all components are present to test by querying the user_objects view with the following:

SQL> COLUMN object_name FORMAT A30
SQL> SELECT object_name
  2  ,      object_type
  3  ,      status
  4  FROM user_objects
  5  WHERE object_name IN ('HelloWorld3','HELLO_WORLD3')
  6  ORDER BY CASE
  7   WHEN object_type = 'JAVA CLASS' THEN 1
  8   WHEN object_type = 'PACKAGE' THEN 2
  9   ELSE 3
 10  END;

The script should output the following results:

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ----------------------- -------
HelloWorld3                    JAVA CLASS              VALID
HELLO_WORLD3                   PACKAGE                 VALID
HELLO_WORLD3                   PACKAGE BODY            VALID

If you did not get the same output, you’ll need to see what step you may have skipped. Please do this before attempting to proceed. If you did get the same output, you can now test the Java class library in SQL and PL/SQL.

You can test the doDML procedure inside an anonymous block program to write a string value to the example table, which you can create with the following command:

SQL> CREATE TABLE example
  2  (character VARCHAR2(100));

Now, you can insert the “Bobby McGee” string with the following anonymous PL/SQL block:

SQL> BEGIN
  2    hello_world3.dodml('INSERT INTO example VALUES (?)','Bobby McGee');
  3  END;
  4  /

After inserting a row into the example table, you can test the doDQL function. Running a query is the easiest way. The following illustrates a SQL query of the wrapper, which uses the internal Java class file:

SQL> SELECT hello_world3.doDQL('SELECT character FROM example') AS "Name";

The query returns the following results:

Name
-----------------------------------
Bobby McGee

You’ve now covered how to build Oracle database instance-stored Java class files that map a Java method to a PL/SQL procedure. The next section discusses how to build real Java objects wrapped by PL/SQL object types.

JDBC Enabled Using ADT Collections

This section shows you how to create a Java library that receives and sorts an Attribute Data Type (ADT) collection of a scalar VARCHAR2 data type. It introduces how you can map an ADT collection into a Java collection, and how you can map the Java collection back into a PL/SQL collection type.

The following are four key classes available in Oracle’s JDBC implementation:

  • oracle.sql.ARRAY
  • oracle.sql.ArrayDescriptor
  • oracle.sql.Datum
  • oracle.sql.STRUCT

The ARRAY class lets you implicitly cast an ADT or user-defined type (UDT) collection into a Java context without explicit conversion. You can transfer the contents of an ADT by calling the getArray() method of the ARRAY class. To transfer an Oracle collection of a scalar data type to a generic Object collection requires you to use the java.util.Map class. You map the Oracle data type to its equivalent Java data type, assuming that the Java library designates the collection’s formal parameter as a list, like

Object[] object = (Object[])list.getArray(map);

Alternatively, you can avoid the expense of converting the object type by using the Oracle-
specific Datum class, like

Datum[] arrayOracle = list.getOracleArray();

A subsequent blog post shows you the more complex approach required to map UDT collections to Java structures. The ArrayDescriptor classes let you map a Java data type into an Oracle data type. You can find more about Oracle collections that other blog post, where you will work with UDT collections, or you can check Chapter 16 of the Oracle Database JDBC Developer’s Guide.

The first step requires that you create an ADT collection. An ADT collection uses a scalar data type as its base type. Scalar data types in Oracle are like primitive data types in Java, and they’re more or less numbers, dates, and strings.

The following syntax creates a table of strings:

SQL> CREATE OR REPLACE
  2    TYPE stringlist IS TABLE OF VARCHAR2(4000);
  3  /

While it would have been straightforward to create this without the use of Java generics, we wanted to make sure you knew they’re available to you. Actually, you can use Java generics from Oracle Database 11g forward.

The following SQL statement creates the SortList Java class, which has a single sortTitleCaseList() method. The Java long name is the same as the short name because of its length.

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortList" AS
  2
  3     // Import required classes.
  4     import java.io.*;
  5     import java.security.AccessControlException;
  6     import java.sql.*;
  7     import java.util.Arrays;
  8     import java.util.Comparator;
  9     import oracle.sql.driver.*;
 10     import oracle.sql.ArrayDescriptor;
 11     import oracle.sql.ARRAY;
 12
 13     // Define class.
 14     public class Sorting {
 15       public static ARRAY sortTitleCaseList(oracle.sql.ARRAY list)
 16         throws SQLException, AccessControlException {
 17
 18     // Convert Oracle data type to Java data type.
 19     String[] unsorted = (String[])list.getArray();
 20
 21     // Sort elements.
 22     Arrays.sort(unsorted, new Comparator<String>() {
 23       public int compare(String s1, String s2) {
 24
 25       // Declare a sorting key integer for the return value.
 26       int sortKey;
 27
 28       // Check if lowercase words match and sort them.
 29       if (s1.toLowerCase().compareTo(s2.toLowerCase()) == 0)
 30         sortKey = s1.substring(0,1).compareTo(s2.substring(0,1));
 31       else
 32         sortKey = s1.toLowerCase().compareTo(s2.toLowerCase());
 33
 34       // Return the sorting index.
 35       return sortKey; }});
 36
 37       // Define a connection (this is for Oracle 11g).
 38       Connection conn =
 39         DriverManager.getConnection("jdbc:default:connection:");
 40
 41       // Declare a mapping to the schema-level SQL collection type.
 42       ArrayDescriptor arrayDescriptor =
 43         new ArrayDescriptor("STRINGLIST",conn);
 44
 45       // Translate the Java String{} to the Oracle SQL collection type.
 46       ARRAY sorted =
 47         new ARRAY(arrayDescriptor,conn,((Object[])unsorted));
 48
 49       // Return the sorted list.
 50       return sorted; }
 51  }
 52  /

Lines 10 and 11 import two key Oracle Java classes—the ARRAY and ArrayDescriptor classes. As discussed earlier in this section, the ARRAY class lets you transfer a collection of numbers, dates, strings, or any other scalar data type to a Java data type. Line 19 avoids using the java.util.Map class, shown earlier in this section, by explicitly designating the Java String data type as the natural equivalent of a VARCHAR2 data type. Line 22 implements a Java generic Comparator method that performs a natural language sort of String data type.

The ArrayDescriptor class implements a mediator pattern, and the declaration on lines 42 and 43 creates a map to the Oracle stringlist data type. Lines 46 and 47 construct an instance of the Oracle ARRAY class with a collection of the Java Object type, which is the most generalized class in the single-inheritance Java tree.

The PL/SQL wrapper for this Java library is

SQL> CREATE OR REPLACE
  2    FUNCTION sortTitleCaseList(list STRINGLIST) RETURN STRINGLIST IS
  3    LANGUAGE JAVA NAME
  4      'Sorting.sortTitleCaseList(oracle.sql.ARRAY) return oracle.sql.ARRAY';
  5  /

You can test this configuration with the following anonymous PL/SQL block:

SQL> DECLARE
  2    /* Declare a counter. */
  3    lv_counter NUMBER := 1;
  4    /* Declare a unordered collection of fruit. */
  5    lv_list STRINGLIST := stringlist('Oranges'
  6                                    ,'apples'
  7                                    ,'Apples'
  8                                    ,'Bananas'
  9                                    ,'Apricots'
 10                                    ,'apricots');
 11  BEGIN
 12    /* Read through an element list. */
 13    FOR i IN (SELECT column_value
 14              FROM TABLE(sortTitleCaseList(lv_list))) LOOP
 15      dbms_output.put_line('['||lv_counter||']['||i.column_value||']');
 16      lv_counter := lv_counter + 1;
 17    END LOOP;
 18  END;
 19  /

Line 14 has a call to the TABLE function that translates the returned collection type into a SQL result set. The anonymous PL/SQL block returns

[1][Apples]
[2][apples]
[3][Apricots]
[4][apricots]
[5][Bananas]
[6][Oranges]

The program also demonstrates how to accept Oracle collections into a Java program and how to return collections from a Java program to an Oracle collection.

Written by maclochlainn

October 3rd, 2025 at 7:31 pm

Posted in Java,Oracle

Tagged with

Deterministic Java Library

without comments

In this post, you build an internal server Java function by building a Java class file that will use the server-side internal connection or JDBC thick connection. As qualified earlier in the chapter, the JDBC thick connection depends on OCI libraries. All OCI libraries are directly accessible from your Java class file when you’ve loaded it into the Oracle JVM.

Java internal or server-side class files are built and accessed by a three-step process. You use Java to build and compile the class file. Then, you use the Oracle loadjava utility to load the compiled class file into the server. Once the class file is built and loaded into the server, you build a PL/SQL wrapper to the Java class library. Deployment requires that you load the compiled Java class file into the database, which you do with the loadjava utility.

Building a Java Library

To build a Java library for the Oracle Database server, the CLASSPATH must resolve to an ojdbcX.jar file (where X maps to the version for the Oracle Database Server). You can modify the earlier javaenv.bat file to include a CLASSPATH reference to the directory that contains the ojdbcX.jar file:

C:>copy con javaenv.bat
SET PATH=%ORACLE_HOME%\jdk\bin;%PATH%
SET CLASSPATH=%ORACLE_HOME%\jdbc\lib\ojdbc6.jar;.
^Z
1 file(s) copied.

You’ll get the following prompt when the javaenv.bat file already exists:

Overwrite javaenv.bat? (Yes/No/All): Yes

Naturally, you have the alternative of using any text editor of your choice. Several free or inexpensive editors are available, but we recommend Sublime as an inexpensive editor or Notepad++ as a free editor.

The Linux or Unix operating system would require the following changes for a javaenv.sh Bash shell environment:

export set PATH=%ORACLE_HOME%/jdk/bin:%PATH%
export set CLASSPATH=%ORACLE_HOME%\jdbc\lib\ojdbc6.jar:.

You need to source the javaenv.bat or javaenv.sh file before compiling the Java source file. You source the Windows batch file by simply running it from the command line, and you must use the dot (.) in the Bash or Korn shell or the keyword source in Tcsh.

Java Database Connectivity (JDBC) lets you build connections by using the DriverManager class. This is a change over the defaultConnection() method for internal Java class files and external connections. They now both use the getConnection() static method from the DriverManager class. The only difference between a thin client and a thick client is the actual parameter provided to the method. Examples in this chapter use the internal syntax, and examples in the appendix use the external thin client syntax.

The following assumes you have the correct CLASSPATH and PATH to use Java. If you are unable to compile or test the Java programs, it’s possible your environment is configured incorrectly.

// Oracle class imports.
import oracle.jdbc.driver.*;
 
// Class definition.
public class HelloWorld2 {
 
  public static String hello() {
    return "Hello World."; }
 
  public static String hello(String name) {
    return "Hello " + name + "."; }
 
  public static void main(String args[]) {
    System.out.println(HelloWorld2.hello());
    System.out.println(HelloWorld2.hello("Larry")); }
}

The program defines two overloaded hello methods. One takes no formal parameters and the other takes one. You can compile HelloWorld2.java with the following syntax:

javac HelloWorld2.java

After you compile and run this program with this syntax:

java HelloWorld2

the method without any formal parameters always prints

Hello World.

while the one that takes one formal parameter always prints

Hello Larry.

This happens because the static main() method always sends either no parameter or the same actual parameter to the dynamic method. As a rule, you want to remove testing components like the main() method before loading them into the database and pass actual parameters to dynamic methods. You can leave the static main() method in the program because it harms nothing and enables your testing of the program with the ojvmjava interactive utility.

Deploying a Java Library

You deploy a Java library with the loadjava utility, which is run from the operating system’s command-line interface. This step requires the HelloWorld2.class file from the prior section. You should note that sometimes problems can occur with the loadjava utility. If you run into problems with the loadjava utility, check the “Building, Loading, and Dropping Java Class Library Objects” below in this blog post.

Deploying a Java library also requires a demo schema, which may be a container database (CDB) or a pluggable database (PDB). If you’re unfamiliar with the difference between a CDB and PDB, the former refers to the base container and the latter are pluggable from one container to another.

After creating the demo schema in a PDB and compiling the Java class file, you load it into the Oracle JVM with the loadjava utility as follows:

loadjava -r -f -o -user video/video@video HelloWorld2.class

You can verify that the loadjava command worked with the following query:

SQL> SELECT object_name
  2  FROM user_objects
  3  WHERE object_name = 'HelloWorld2'
  4  AND object_type = object_type LIKE 'JAVA%';

It displays:

OBJECT_NAME
--------------
HelloWorld2

On the Microsoft platform, you may get a message that states “The procedure entry point kpuhhalo could not be located in the dynamic link library OCI.dll.” If you receive this error, it means you don’t have %ORACLE_HOME\bin% in your PATH environment variable.

The loadjava utility command loads the Java HelloWorld2 class file into the Oracle JVM under the demo schema. After loading the Java class file into the database, you’ll need to build a
PL/SQL wrapper to use it.

Wrapping a Java Library

PL/SQL lets you wrap Java libraries. This section shows you how to wrap the previously created HelloWorld2 Java library, which differs from wrapping a PL/SQL stored program unit. The following HelloWorld2.sql script (included in the online resources file) builds the hello_world2 package specification and a package body. The package body serves as a wrapper to the two methods implemented in the HelloWorld2 Java class.

SQL> CREATE OR REPLACE PACKAGE hello_world2 AS
  2    FUNCTION hello
  3    RETURN VARCHAR2;
  4
  5    FUNCTION hello
  6    ( who VARCHAR2 )
  7    RETURN VARCHAR2;
  8  END hello_world2;
  9  /

The following package body builds wrapper methods in the HelloWorld2 Java class library:

SQL> CREATE OR REPLACE PACKAGE BODY hello_world2 AS
  2    FUNCTION hello
  3    RETURN VARCHAR2 IS
  4    LANGUAGE JAVA
  5    NAME 'HelloWorld2.hello() return java.lang.String';
  6
  7    FUNCTION hello
  8    ( who VARCHAR2 )
  9    RETURN VARCHAR2 IS
 10    LANGUAGE JAVA
 11    NAME 'HelloWorld2.hello(java.lang.String) return java.lang.String';
 12  END hello_world2;
 13  /

Line 5 and line 11 designate a lowercase hello method. It is critical that the case-sensitive string description in the PL/SQL wrapper exactly match the case-sensitive method names that are used in the Java code. You raise the following exception if they don’t:

SELECT hello_world2.hello() FROM dual
*
ERROR AT line 1:
ORA-29531: no method hello IN class HelloWorld2

When you ensure a case-sensitive match and run this in your schema, it creates a wrapper to the HelloWorld2.class file that you previously loaded. The return type of your PL/SQL wrapper is a VARCHAR2 data type. You map it to a java.lang.String class, and it must be that fully qualified path.

You can verify that all components are present to test by querying the user_objects view with the following:

COLUMN object_name FORMAT A30
SQL> SELECT object_name
  2  ,      object_type
  3  ,      status
  4  FROM user_objects
  5  WHERE object_name IN ('HelloWorld2','HELLO_WORLD2')
  6  ORDER BY CASE
  7    WHEN object_type = 'JAVA CLASS' THEN 1
  8    WHEN object_type = 'PACKAGE' THEN 2
  9    ELSE 3
 10  END;

The query will return the following results:

Method Call
-----------------------------------
Hello Paul McCartney.

This section has shown you how to wrap and identify Java class libraries and related PL/SQL packages. Next you’ll see how to compile, deploy, and wrap a Java library.

Compiling, Deploying and Wrapping a Java Library

As an alternative to using the loadjava utility, you can also build the entire Java source, publishing specification, and implementation in a single SQL file. Then, you can run it directly from the SQL*Plus command line. This is done by using a single Data Definition Language (DDL)
command.

The prototype for the DDL command is

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED <java_class_name> AS <java_source>
/

You must use a forward slash shown in the prototype to execute a DDL command that builds an internal Java class. If you substitute a semicolon, you’ll raise an ORA-29536 exception.

The Java class file name uniquely identifies the Java class in the Oracle JVM’s CLASSPATH. You can treat a class with multiple methods like a package equivalent and define a package as a wrapper to complete the Java class. Alternatively, you can ignore the similarity to a package and implement individual Java methods as functions or procedures. Java methods that return a static value act like PL/SQL procedures, and Java methods that return a value act like PL/SQL functions.

Overloading doesn’t work when you implement a schema-level function or procedure for each Java method in a Java class. You must implement PL/SQL wrappers inside a package when you want to leverage object-oriented overloading in Oracle’s PL/SQL programming language.

You create the Java HelloWorldSQL class with the following DDL command:

SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS
  2  // Class Definition.
  3  PUBLIC class HelloWorldSQL {
  4
  5    PUBLIC static String hello() {
  6    RETURN "Hello World."; }
  7
  8    PUBLIC static String hello(String name) {
  9    RETURN "Hello " + name + "."; }
 10  }
 11  /

RESOLVE JAVA SOURCE NAMED targets the class file name and parses and compiles the Java source into the instance. You can then publish individual methods as functions or procedures, or the class as a package.

The following publishes the class as a package specification:

SQL> CREATE OR REPLACE PACKAGE hello_world_sql AS
  2    FUNCTION hello
  3    RETURN VARCHAR2;
  4
  5    FUNCTION hello
  6    ( who VARCHAR2 )
  7    RETURN VARCHAR2;
  8  END hello_world_sql;
  9  /

And this wraps the class as a package body:

SQL> CREATE OR REPLACE PACKAGE BODY hello_world_sql AS
  2    FUNCTION hello
  3    RETURN VARCHAR2 IS
  4    LANGUAGE JAVA
  5    NAME 'HelloWorldSQL.hello() return String';
  6
  7    FUNCTION hello
  8    ( who VARCHAR2 )
  9    RETURN VARCHAR2 IS
 10    LANGUAGE JAVA
 11    NAME 'HelloWorldSQL.hello(java.lang.String) return String';
 12  END hello_world_sql;
 13  /

The package provides an overloaded hello function. You can call it with or without an actual parameter. You can then publish only a single method of the class as a function, like this:

SQL> CREATE OR REPLACE FUNCTION hello
  2  ( who VARCHAR2) RETURN VARCHAR2 IS
  3  LANGUAGE JAVA
  4  NAME 'HelloWorldSQL.hello(java.lang.String) return String';
  5  /

You can query the function by using

SQL> SELECT hello('Nathan') AS SALUTATION;

and it returns

SALUTATION
-----------------------------------
Hello Nathan.

You have now covered how to build Oracle database instance-stored Java class files that map
methods to functions. The next blog post will examine how you build components to deliver
procedure DML behaviors.

Written by maclochlainn

October 3rd, 2025 at 12:33 pm

Posted in Java,Oracle

Comparative Techniques

without comments

This post is designed to illustrate PL/SQL code with techniques employed before Oracle Database 12c with manual sequence references rather than auto generated IDs. The trick is small but should be made with older code. It impacts creation statement, conditional drop statements, and modification to stored procedures that manage transactions across two or more tables.

The key to the difference is in the table creation. Prior to Oracle Database 12c, there were no automatic sequences that you could assign to table definitions. Tables were created without sequences, and sequences were created independently. Likewise, there was no “IF EXISTS” clause for a DROP statement, which meant conditional drops were required as anonymous blocks.

Old tables and sequences from the Oracle Database 11g would use the following definitions:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

/* Conditionally drop grandma table and grandma_s sequence. */
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ','GRANDMA','GRANDMA_SEQ')
            ORDER BY 1 DESC, 2) LOOP
    IF i.object_type = 'TABLE' THEN
      /* Use the cascade constraints to drop the dependent constraint. */
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
/* Create the table. */
CREATE TABLE GRANDMA
( grandma_id     NUMBER       CONSTRAINT grandma_nn1 NOT NULL
, grandma_house  VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL
, CONSTRAINT grandma_pk       PRIMARY KEY (grandma_id)
);
 
/* Create the sequence. */
CREATE SEQUENCE grandma_seq;
 
/* Create the table with primary and foreign key out-of-line constraints. */
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id     NUMBER        CONSTRAINT tweetie_bird_nn1 NOT NULL
, tweetie_bird_house  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn2 NOT NULL
, grandma_id          NUMBER        CONSTRAINT tweetie_bird_nn3 NOT NULL
, CONSTRAINT tweetie_bird_pk        PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
);
 
/* Create sequence. */
CREATE SEQUENCE tweetie_bird_seq;

The key to the differences in Oracle Database 12c are in the table creation, and then in the INSERT statement because you can exclude the surrogate key column. A surrogate key column is also known as an ID column or sequence identified column.

New tables and sequences from the Oracle Database 12c forward use the following definitions to manage sequences:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

/* Conditionally drop grandma table and grandma_s sequence. */
DROP TABLE IF EXISTS tweetie_bird;
DROP TABLE IF EXISTS grandma;
 
/* Create the table. */
CREATE TABLE GRANDMA
( grandma_id     NUMBER        DEFAULT grandma_seq.NEXTVAL
, grandma_house  VARCHAR2(30)  CONSTRAINT grandma_nn2 NOT NULL
, CONSTRAINT grandma_pk        PRIMARY KEY (grandma_id)
);
 
/* Create the table with primary and foreign key out-of-line constraints. */
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id     NUMBER        DEFAULT tweetie_bird_seq.NEXTVAL
, tweetie_bird_house  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn1 NOT NULL
, grandma_id          NUMBER        DEFAULT grandma_seq.CURRVAL
, CONSTRAINT tweetie_bird_pk        PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
);

This is the pattern that I setup to explain the new default surrogate primary and foreign keys when I wrote the Oracle Database 12c PL/SQL Programming book (pp. 20-22). Unfortunately, it was just a simple example and fits this use case:

  • You write one create_something procedure or function, also known interchangeably as a method, to insert initial records; and
  • You write one add_something method add a subsequent dependent record; and

This is a common practice by many but it creates unnecessary code that you need to maintain, which translates to accumulated technical debt throughout a design. The technical debt grows by a factor of two when you want to overload behaviors for security features or batch processing.

A better solution is to write one method that accommodates both an creates a new record and adds dependent records. The warner_brother procedure will do that later in this post. To prepare for the warner_brother procedure the dependent tweetie_bird table needs to change.

The default of the .currval of a “parent” table’s sequence value is no longer guaranteed in an independent action. The create and add methods now share the same method, and inspection of an existing independent record is necessary for both the create and add methods.

The new tweetie_bird table looks like:

/* Create the table with primary and foreign key out-of-line constraints. */
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id     NUMBER        DEFAULT tweetie_bird_seq.NEXTVAL
, tweetie_bird_house  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn1 NOT NULL
, grandma_id          NUMBER        CONSTRAINT tweetie_bird_nn2 NOT NULL
, CONSTRAINT tweetie_bird_pk        PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
);

The next part of the post discusses how to write the create and add procedure in pre-12c and 12c forward implementations. Clearly, the automatic generated sequence values are the best approach when you use user-defined names for them as shown earlier.

The warner_brother procedure models the idea that any grandma may have one or more Tweetie birds, which means you must check if there’s a row in the grandma table. If there’s a row in the grandma table, you shouldn’t insert a new row in the grandma table. The internal get_grandma_id function returns:

  • A zero if there is no existing row in the grandma table. After which, the nested if-block will insert a new row into the grandma table; and then assign the grandma_seq.CURRVAL value to a local variable.
  • An existing grandma_id value, which can be used as a foreign key value in the subsequent insert statement to the tweety_bird table.

The warner_brother procedure for Oracle Database 11g is shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

/* Create or replace procedure warner_brother. */
CREATE OR REPLACE PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2 ) IS
 
  /* Declare a local variable for an existing grandma_id. */
  lv_grandma_id   NUMBER;
 
  FUNCTION get_grandma_id
  ( pv_grandma_house  VARCHAR2 ) RETURN NUMBER IS
 
    /* Initialized local return variable. */
    lv_retval  NUMBER := 0;  -- Default value is 0.
 
    /* A cursor that lookups up a grandma's ID by their name. */
    CURSOR find_grandma_id
    ( cv_grandma_house  VARCHAR2 ) IS
      SELECT grandma_id
      FROM   grandma
      WHERE  grandma_house = cv_grandma_house;
 
  BEGIN   
    /* Assign a grandma_id as the return value when a row exists. */
    FOR i IN find_grandma_id(pv_grandma_house) LOOP
      lv_retval := i.grandma_id;
    END LOOP;
 
    /* Return 0 when no row found and the grandma_id when a row is found. */
    RETURN lv_retval;
  END get_grandma_id;
 
BEGIN
 
  /* Set the savepoint. */
  SAVEPOINT starting;
 
  /*
   *  Identify whether a member account exists and assign it's value
   *  to a local variable.
   */
  lv_grandma_id := get_grandma_id(pv_grandma_house);
 
  /*
   *  Conditionally insert a new member account into the member table
   *  only when a member account does not exist.
   */
  IF lv_grandma_id = 0 THEN
 
    /* Insert grandma. */
    INSERT INTO grandma
    ( grandma_id
    , grandma_house )
    VALUES
    ( grandma_seq.NEXTVAL
    , pv_grandma_house );
    /* Assign grandma_seq.currval to local variable. */
    lv_grandma_id := grandma_seq.CURRVAL;
 
  END IF;
 
  /* Insert tweetie bird. */
  INSERT INTO tweetie_bird
  ( tweetie_bird_id
  , tweetie_bird_house 
  , grandma_id )
  VALUES
  ( tweetie_bird_seq.NEXTVAL
  , pv_tweetie_bird_house
  , lv_grandma_id );
 
  /* If the program gets here, both insert statements work. Commit it. */
  COMMIT;
 
EXCEPTION
  /* When anything is broken do this. */
  WHEN OTHERS THEN
    /* Until any partial results. */
    ROLLBACK TO starting;
END;
/

The warner_brother procedure for Oracle Database 12c forward simplifies somethings and complicates others. The DEFAULT for the grandma_id foreign key column in the tweetie_bird table must be overwritten with a copy of the correct primary key value. More or less, it requires the correct grandma_id column value by finding the existing row in the grandma table. The internal get_grandma_id function grabs that value while returning a value that precludes writing to the grandma table.

The warner_brother procedure for Oracle Database 12c forward shown below shows that the grandma_id column default value is overridden by a local lv_grandma_id value returned by the internal get_grandma_id function on lines 60 thru 65 below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

/* Create or replace procedure warner_brother. */
CREATE OR REPLACE PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2 ) IS
 
  /* Declare a local variable for an existing grandma_id. */
  lv_grandma_id   NUMBER;
 
  FUNCTION get_grandma_id
  ( pv_grandma_house  VARCHAR2 ) RETURN NUMBER IS
 
    /* Initialized local return variable. */
    lv_retval  NUMBER := 0;  -- Default value is 0.
 
    /* A cursor that lookups up a grandma's ID by their name. */
    CURSOR find_grandma_id
    ( cv_grandma_house  VARCHAR2 ) IS
      SELECT grandma_id
      FROM   grandma
      WHERE  grandma_house = cv_grandma_house;
 
  BEGIN   
    /* Assign a grandma_id as the return value when a row exists. */
    FOR i IN find_grandma_id(pv_grandma_house) LOOP
      lv_retval := i.grandma_id;
    END LOOP;
 
    /* Return 0 when no row found and the grandma_id when a row is found. */
    RETURN lv_retval;
  END get_grandma_id;
 
BEGIN
 
  /* Set the savepoint. */
  SAVEPOINT starting;
 
  /*
   *  Identify whether a member account exists and assign it's value
   *  to a local variable.
   */
  lv_grandma_id := get_grandma_id(pv_grandma_house);
 
  /*
   *  Conditionally insert a new member account into the member table
   *  only when a member account does not exist.
   */
  IF lv_grandma_id = 0 THEN
 
    /* Insert grandma. */
    INSERT INTO grandma
    ( grandma_house )
    VALUES
    ( pv_grandma_house );
    /* Assign grandma_seq.currval to local variable. */
    lv_grandma_id := grandma_seq.CURRVAL;
 
  END IF;
 
  /* Insert tweetie bird. */
  INSERT INTO tweetie_bird
  ( tweetie_bird_house 
  , grandma_id )
  VALUES
  ( pv_tweetie_bird_house
  , lv_grandma_id );
 
  /* If the program gets here, both insert statements work. Commit it. */
  COMMIT;
 
EXCEPTION
  /* When anything is broken do this. */
  WHEN OTHERS THEN
    /* Until any partial results. */
    ROLLBACK TO starting;
END;
/

You can test either version with the following anonymous PL/SQL block:

/* Test the warner_brother procedure. */
BEGIN
  warner_brother( pv_grandma_house      => 'Yellow House'
                , pv_tweetie_bird_house => 'Cage');
  warner_brother( pv_grandma_house      => 'Yellow House'
                , pv_tweetie_bird_house => 'Tree House');
END;
/

Ultimately, we’ll use a natural join to test the integrity of primary and foreign key relationship:

/* Query results from warner_brother procedure. */
COL grandma_id          FORMAT 9999999  HEADING "Grandma|ID #"
COL grandma_house       FORMAT A20      HEADING "Grandma House"
COL tweetie_bird_id     FORMAT 9999999  HEADING "Tweetie|Bird ID"
COL tweetie_bird_house  FORMAT A20      HEADING "Tweetie Bird House"
SELECT *
FROM   grandma NATURAL JOIN tweetie_bird;

As always, I hope this helps those trying to leverage existing PL/SQL code into more modern code.

Written by maclochlainn

March 30th, 2025 at 6:58 pm

Posted in Oracle,Oracle 11g,Oracle 12c,Oracle 18c,Oracle 19c,Oracle 21c,Oracle 23ai,pl/sql

Tagged with ,

PL/SQL Table Function

without comments

Eleven years ago I had an interesting conversation about table functions in Oracle’s PL/SQL; and the fact that they’re not available in MySQL. When I explained they were available in Microsoft T-SQL User-Defined Functions (UDFs) with Microsoft’s Common Language Infrastructure (CLI). Naturally, I wrote an example for them in T-SQL. Now a reader wants an equivalent PL/SQL example.

I figured that borrowing that sample data was appropriate. This creates the conquistador table:

CREATE TABLE conquistador
( conquistador_id    NUMBER GENERATED ALWAYS AS IDENTITY
, conquistador       VARCHAR2(30)
, actual_name        VARCHAR2(30)
, nationality        VARCHAR2(30)
, lang               VARCHAR2(2));

Then, you can insert the following data:

INSERT INTO conquistador
( conquistador, actual_name, nationality, lang )
VALUES
 ('Juan de Fuca','Ioánnis Fokás','Greek','el')
,('Nicolás de Federmán','Nikolaus Federmann','German','de')
,('Sebastián Caboto','Sebastiano Caboto','Venetian','it')
,('Jorge de la Espira','Georg von Speyer','German','de')
,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it')
,('Wenceslao Linck','Wenceslaus Linck','Bohemian','cs')
,('Fernando Consag','Ferdinand Konšcak','Croatian','sr')
,('Américo Vespucio','Amerigo Vespucci','Italian','it')
,('Alejo García','Aleixo Garcia','Portuguese','pt');

Next, create a UDT struct(ure) and table of that structure:

/* Create type of object structure. */
CREATE OR REPLACE
  TYPE conquistador_struct IS OBJECT
  ( conquistador      VARCHAR(30)
  , actual_name       VARCHAR(30)
  , nationality       VARCHAR(30));
/
 
/* Create table of object structure. */
CREATE OR REPLACE
  TYPE conquistador_table IS TABLE OF conquistador_struct;
/

Now, we can create a table function that filters on nationality:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE OR REPLACE
  FUNCTION getConquistador (pv_lang IN VARCHAR) RETURN conquistador_table IS
 
  /* Declare a return variable. */
  lv_retval  CONQUISTADOR_TABLE := conquistador_table();
 
  /* Declare a dynamic cursor. */
  CURSOR get_conquistador
  ( cv_lang  VARCHAR2 ) IS
    SELECT c.conquistador
    ,      c.actual_name
    ,      c.nationality
    FROM   conquistador c
    WHERE  c.lang = cv_lang;
 
  /* Local procedure to add to the song. */
  PROCEDURE ADD
  ( pv_input  CONQUISTADOR_STRUCT ) IS
  BEGIN
    lv_retval.EXTEND;
    lv_retval(lv_retval.COUNT) := pv_input;
  END ADD;
 
BEGIN
  /* Read through the cursor and assign to the UDT table. */
  FOR i IN get_conquistador(pv_lang) LOOP
    ADD(conquistador_struct( conquistador => i.conquistador
                           , actual_name  => i.actual_name
			   , nationality  => i.nationality ));
  END LOOP;
 
  /* Return collection. */
  RETURN lv_retval;
END;
/

You can select only the rows with German nationality, qualified by the ISO standard DE with the following query:

COL conquistador  FORMAT A21
COL actual_name   FORMAT A21
COL nationality   FORMAT A12
SELECT * FROM TABLE(getConquistador('de'));

It should return:

CONQUISTADOR	      ACTUAL_NAME	    NATIONALITY
--------------------- --------------------- ------------
Nicolás de Federmán   Nikolaus Federmann    German
Jorge de la Espira    Georg von Speyer	    German

I retested this on Oracle Database 23ai Free, and found the NLS character set fails to support the characters for Nicolás de Federmán. It returns:

CONQUISTADOR	      ACTUAL_NAME	    NATIONALITY
--------------------- --------------------- ------------
Nicol??s de Federm??n Nikolaus Federmann    German
Jorge de la Espira    Georg von Speyer	    German

This is interesting because it brought to my attention that Oracle does try to account for unreadable ASCII values based on character sets. In Google’s GO language these would be considered “Runes”.

As always, I hope these coding samples help those solving new problems.

Written by maclochlainn

March 29th, 2025 at 6:04 pm

Posted in Oracle,Oracle 19c,Oracle 21c,Oracle 23ai,Oracle 23c,pl/sql

Tagged with

PL/SQL Coupled Loops

without comments

A standard approach using fall through in languages that support it, like C, C++, and Java leverages a case structure. Unfortunately, in language that follow the Pascal pattern case statements act like if-else-if-else statements. In the Pascal model, you need to use coupled loops.

PL/SQL follows that Pascal, via Ada, structure. So, you must couple the performance of the inner descending loop to the maximum value of the outer ascending loop. Here’s an example of how you would code that in PL/SQL with the 12 Days of Christmas song, which has a chorus that grows with each verse.

It requires the definition of two ADTs (Attribute Data Types), a UDT (User-Defined Data Type) like a struct(ure), and a UDT table of the UDT struct. They’re all done in this code snippet.

/* Create a list of strings. */
CREATE OR REPLACE
  TYPE days IS TABLE OF VARCHAR2(8);
/
 
/* Create a list of strings. */
CREATE OR REPLACE
  TYPE song IS TABLE OF VARCHAR2(36);
/
 
/* Create a record structure. */
CREATE OR REPLACE
  TYPE lyric IS OBJECT
  ( DAY  VARCHAR2(8)
  , gift VARCHAR2(24));
/
 
/* Create a list of the record structure. */
CREATE OR REPLACE
  TYPE lyrics IS TABLE OF LYRIC;
/

The twelve_days function that will display the lyrics of the 12-Days of Christmas. It reads forward through the 12 days of Christmas and backwards through the chorus in the inner loop beginning with the current day of the outer loop.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

/* Create a function. */
CREATE OR REPLACE
  FUNCTION twelve_days
  ( pv_days DAYS
  , pv_gifts LYRICS ) RETURN song IS
 
    /* Initialize the collection of lyrics. */
    lv_retval SONG := song();
 
    /* Local procedure to add to the song. */
    PROCEDURE ADD
    ( pv_input VARCHAR2 ) IS
    BEGIN
      lv_retval.EXTEND;
      lv_retval(lv_retval.COUNT) := pv_input;
    END ADD;
 
  BEGIN
    /* Read forward through the days. */
    FOR i IN 1..pv_days.COUNT LOOP
      ADD('On the ' || pv_days(i) || ' day of Christmas');
      ADD('my true love sent to me:');
 
      /* Read backward through the lyrics based on ascending value of the day. */
      FOR j IN REVERSE 1..i LOOP
        IF i = 1 THEN
          ADD('- '||'A'||' '||pv_gifts(j).gift);
        ELSE
          ADD('- '||pv_gifts(j).DAY||' '||pv_gifts(j).gift);
        END IF;
      END LOOP;
 
      /* A line break by verse. */
      ADD(CHR(13));
    END LOOP;
 
    /* Return the song's lyrics. */
    RETURN lv_retval;
  END;
/

The typical test case for the function in PL/SQL is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

/* Test the function in PL/SQL. */
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  /*
  * Declare an lv_days array of an 8 character variable length string
  * and initialize it with values.
  */
  lv_days days := days('first','second','third','fourth'
                      ,'fifth','sixth','seventh','eighth'
                      ,'nineth','tenth','eleventh','twelfth');
 
  /*
  * Declare an lv_gifts array of the user-defined LYRIC data type and
  * initialize it with values.
  */
  lv_gifts lyrics := lyrics(lyric(DAY => 'and a', gift => 'Partridge in a pear tree')
                           ,lyric(DAY => 'Two', gift => 'Turtle doves')
                           ,lyric(DAY => 'Three', gift => 'French hens')
                           ,lyric(DAY => 'Four', gift => 'Calling birds')
                           ,lyric(DAY => 'Five', gift => 'Golden rings' )
                           ,lyric(DAY => 'Six', gift => 'Geese a laying')
                           ,lyric(DAY => 'Seven', gift => 'Swans a swimming')
                           ,lyric(DAY => 'Eight', gift => 'Maids a milking')
                           ,lyric(DAY => 'Nine', gift => 'Ladies dancing')
                           ,lyric(DAY => 'Ten', gift => 'Lords a leaping')
                           ,lyric(DAY => 'Eleven',gift => 'Pipers piping')
                           ,lyric(DAY => 'Twelve',gift => 'Drummers drumming'));
 
  /*
  * Declare an lv_days array of an 36 character variable length string
  * and initialize it with values.
  */
  lv_song song := song();
 
BEGIN
  /* Call the twelve_days function and assign the results to the local
  * lv_song variable.
  */
  lv_song := twelve_days(lv_days,lv_gifts);
 
  /*
  * Read the lines from the local lv_song variable and print them.
  */
  FOR i IN 1..lv_song.LAST LOOP
    dbms_output.put_line(lv_song(i));
  END LOOP;
END;
/

It displays:

On the first day of Christmas
my true love sent to me:
- A Partridge in a pear tree
 
On the second day of Christmas
my true love sent to me:
- Two Turtle doves
- and a Partridge in a pear tree
 
On the third day of Christmas
my true love sent to me:
- Three French hens
- Two Turtle doves
- and a Partridge in a pear tree
 
...
 
On the twelfth day of Christmas
my true love sent to me:
- Twelve Drummers drumming
- Eleven Pipers piping
- Ten Lords a leaping
- Nine Ladies dancing
- Eight Maids a milking
- Seven Swans a swimming
- Six Geese a laying
- Five Golden rings
- Four Calling birds
- Three French hens
- Two Turtle doves
- and a Partridge in a pear tree

As always, I hope this helps those learning how to write PL/SQL and/or code.

Written by maclochlainn

March 28th, 2025 at 7:42 pm

Posted in Oracle,Oracle 12c,Oracle 18c,Oracle 19c,Oracle 21c,Oracle 23ai,pl/sql

Tagged with

PL/SQL List to Struct

without comments

This post shows you how to take a list of strings and convert them into a struct(ure) of a date, number, and string. Oracle implements IDL (Interface Description Language), which means the solution requires creating:

  • An attribute data type (ADT), or collection of a standard date type, which in this case is a varchar2 (variable length string).
  • A user defined type (UDT), which is an object type with or without methods. In this case, it implements a UDT without methods.
  • A collection of the UDT object type (known in Oracle’s parlance as a table).

Here’s the data definition language (DDL) for the three required structures:

  • The ADT of strings: 

    CREATE OR REPLACE
  TYPE tre IS TABLE OF VARCHAR2(100);
/

  • The UDT struct(ure) object type: 

    CREATE OR REPLACE
  TYPE struct IS OBJECT
  ( xdate    DATE
  , xnumber  NUMBER
  , xstring  VARCHAR2(20));
/

  • The UDT structs collection or Oracle table: 

    CREATE OR REPLACE
  TYPE structs IS TABLE OF struct;
/

Next, we create a function that accepts a collection of strings and returns a record UDT, which is the struct(ure) object type. As PL/SQL code gets larger, using conditional compilation can help discover problems.

Enable conditional compilation with the following statement:

ALTER SESSION SET PLSQL_CCFLAGS = 'debug:1';

The cast_strings function is defined as:

CREATE OR REPLACE
  FUNCTION cast_strings
  ( pv_list  TRE ) RETURN struct IS
 
    /* Declare a UDT and initialize an empty struct variable. */
    lv_retval  STRUCT := struct( xdate => NULL
                               , xnumber => NULL
                               , xstring => NULL); 
 
    /* A debugger function. */	
    FUNCTION debugger
    ( pv_string  VARCHAR2 ) RETURN VARCHAR2 IS
      /* Declare return value. */
      lv_retval  VARCHAR2(60);
    BEGIN
      /* Conditional compilation evaluation. */
      $IF $$DEBUG = 1 $THEN
        lv_retval := 'Evaluating ['||pv_string||']';
      $END
 
      /* Return debug value. */
      RETURN lv_retval;
    END debugger;
 
  BEGIN  
    /* Loop through list of values to find only the numbers. */
    FOR i IN 1..pv_list.LAST LOOP
 
      /* Print debugger remark. */
      dbms_output.put_line(debugger(pv_list(i)));
 
      /* Ensure that a sparsely populated list can't fail. */
      IF pv_list.EXISTS(i) THEN
        /* Order if number evaluation before string evaluation. */
        CASE
            /* Implement WHEN clause that checks that the xnumber member is null and that
               the pv_list element contains only digits; and assign the pv_list element to
               the lv_retval's xnumber member. */
          WHEN REGEXP_LIKE(pv_list(i),'^[[:digit:]]*$') THEN
			lv_retval.xnumber := pv_list(i);
 
            /* Implement WHEN clause that checks that the xdate member is null and that
               the pv_list element is a valid date; and assign the pv_list element to
               the lv_retval's xdate member. */
          WHEN verify_date(pv_list(i)) THEN
            lv_retval.xdate := pv_list(i);
 
            /* Implement WHEN clause that checks that the xstring member is null and that
               the pv_list element contains only alphanumeric values; and assign the pv_list
               element to the lv_retval's xstring member. */
          WHEN REGEXP_LIKE(pv_list(i),'^([[:alnum:]]|[[:punct:]]|[[:space:]])*$') THEN
            lv_retval.xstring := pv_list(i);
          ELSE
            NULL;
        END CASE;
      END IF;
    END LOOP;
 
    /* Print the results. */
    RETURN lv_retval;
  END;
/

The following tests the cast_strings function:

DECLARE
  /* Define a list. */
  lv_list  TRE := tre('16-APR-2018','Day after ...','1040');
 
  /* Declare a structure. */
  lv_struct  STRUCT := struct( xdate => NULL
                             , xnumber => NULL
                             , xstring => NULL); 
BEGIN
  /* Assign a parsed value set to get a value structure. */
  lv_struct := cast_strings(lv_list);
 
  /* Print the values of the compound struct variable. */
  dbms_output.put_line('xstring ['||lv_struct.xstring||']');
  dbms_output.put_line('xdate   ['||TO_CHAR(lv_struct.xdate,'DD-MON-YYYY')||']');
  dbms_output.put_line('xnumber ['||lv_struct.xnumber||']');
END;
/

If the PLSQL_CCFLAGS is enabled for the session, the function will display these three rows:

Evaluating [16-APR-2018]
Evaluating [DAY after ...]
Evaluating [1040]

Whether the PLSQL_CCFLAGS is set or not, the test case returns the following:

xstring [DAY after ...]
xdate	[16-APR-2018]
xnumber [1040]

As always, I hope this helps those looking for new approaches.

Written by maclochlainn

March 27th, 2025 at 10:10 pm

Posted in Oracle,Oracle 19c,Oracle 21c,Oracle 23ai,Oracle 23c,pl/sql

Tagged with

Hidden thought SQL question

without comments

I’m always amazed at how people ask questions about skill sets. A few weeks ago, I ran into a neat Oracle SQL question that hinges on whether the individual truly understands One Phase Commit (1PC) and Two Phase Commit (2PC) operations.

It’s a simple question that tests two levels of understanding. The question is:

  • If you create table A and insert a row of data and subsequently you create table B and insert a row of data before issuing a ROLLBACK; statement, how many rows of data will you find in table A and table B?

Level 1 Understanding

Here’s the test script:

CREATE TABLE a1
( text  VARCHAR2(12) );
INSERT INTO a1 ( text ) VALUES ('Hello World!');
CREATE TABLE b1
( text  VARCHAR2(12) );
INSERT INTO b1 ( text ) VALUES ('Hello World!');
ROLLBACK;

The answer is 1 row in table A1 and no row in table B1 because the second CREATE statement issues an implicit COMMIT. However, the INSERT statement to table B1 is a 2PC and the ROLLBACK statement undoes the first phase of the INSERT statement and removes the data. If you were using a sequence value in the INSERT statement, the sequence value would be consumed because it’s not replaced by a ROLLBACK statement.

Level 2 Understanding

Here’s the test script:

CREATE TABLE a2 AS (SELECT 'Hello World!' AS text);
CREATE TABLE b2 AS (SELECT 'Hello World!' AS text);
ROLLBACK;

The answer is 1 row in table A2 and 1 row in table B2 because a CREATE statement using the AS clause subquery issues an implicit COMMIT on the data inserted from the subquery because its a 1PC transaction.

Most likely and interviewer would be looking for level one understanding but you can demonstrate mastery by sharing level two understanding. As always, I hope this helps those reading it.

Written by maclochlainn

March 21st, 2025 at 10:38 pm

Posted in Oracle,Oracle 18c,Oracle 19c,Oracle 21c,Oracle 23ai,sql

Tagged with ,

« Older Entries