Archive for the ‘Oracle Developer’ tag
Java Class with Struct
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.
Build SQLData Java Class
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.
JDBC-Enabled Java Library
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.
Comparative Techniques
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.
PL/SQL Table Function
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.
PL/SQL Coupled Loops
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.
PL/SQL List to Struct
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.
Hidden thought SQL question
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.
Troubleshoot Oracle Errors
It’s always a bit difficult to trap errors in SQL*Developer when you’re running scripts that do multiple things. As old as it is, using the SQL*Plus utility and spooling to log files is generally the fastest way to localize errors across multiple elements of scripts. Unfortunately, you must break up you components into local components, like a when you create a type, procedure, function, or package.
This is part of my solution to leverage in-depth testing of the Oracle Database 23ai Free container from an Ubuntu native platform. You can find this prior post shows you how to setup Oracle*Client for Ubuntu and connect to the Oracle Database 23ai Free container.
After you’ve done that, put the following oracle_errors Bash shell function into your testing context, or into your .bashrc file:
# Troubleshooting errors utility function. oracle_errors () { # Oracle Error prefixes qualify groups of error types, like # this subset of error prefixes used in the Bash function. # ============================================================ # JMS - Java Messaging Errors # JZN - JSON Errors # KUP - External Table Access Errors # LGI - File I/O Errors # OCI - Oracle Call Interface Errors # ORA - Oracle Database Errors # PCC - Oracle Precompiler Errors # PLS - Oracle PL/SQL Errors # PLW - Oracle PL/SQL Warnings # SP2 - Oracle SQL*Plus Errors # SQL - SQL Library Errors # TNS - SQL*Net (networking) Errors # ============================================================ # Define a array of Oracle error prefixes. prefixes=("jms" "jzn" "kup" "lgi" "oci" "ora" "pcc" "pls" "plw" "sp2" "sql" "tns") # Prepend the -e for the grep utility to use regular expression pattern matching; and # use the ^before the Oracle error prefixes to avoid returning lines that may # contain the prefix in a comment, like the word lookup contains the prefix kup. for str in ${prefixes[@]}; do patterns+=" -e ^${str}" done # Display output from a SQL*Plus show errors command written to a log file when # a procedure, function, object type, or package body fails to compile. This # prints the warning message followed by the line number displayed. patterns+=" -e ^warning" patterns+=" -e ^[0-9]/[0-9]" # Assign any file filter to the ext variable. ext=${1} # Assign the extension or simply use a wildcard for all files. if [ ! -z ${ext} ]; then ext="*.${ext}" else ext="*" fi # Assign the number of qualifying files to a variable. fileNum=$(ls -l ${ext} 2>/dev/null | grep -v ^l | wc -l) # Evaluate the number of qualifying files and process. if [ ${fileNum} -eq "0" ]; then echo "[0] files exist." elif [ ${fileNum} -eq "1" ]; then fileName=$(ls ${ext}) find `pwd` -type f | grep -in ${ext} ${patterns} | while IFS='\n' read list; do echo "${fileName}:${list}" done else find `pwd` -type f | grep -in ${ext} ${patterns} | while IFS='\n' read list; do echo "${list}" done fi # Clear ${patterns} variable. patterns="" } |
Now, let’s create a debug.txt test file to demonstrate how to use the oracle_errors, like:
ORA-12704: character SET mismatch PLS-00124: name OF EXCEPTION expected FOR FIRST arg IN exception_init PRAGMA SP2-00200: Environment error JMS-00402: Class NOT found JZN-00001: END OF input |
You can navigate to your logging directory and call the oracle_errors function, like:
oracle_errors txt |
It’ll return the following, which is file number, line number, and error code:
debug.txt:1:ORA-12704: character set mismatch debug.txt:2:PLS-00124: name of exception expected for first arg in exception_init pragma debug.txt:3:SP2-00200: Environment error debug.txt:4:JMS-00402: Class not found debug.txt:5:JZN-00001: End of input |
There are other Oracle error prefixes but the ones I’ve selected are the more common errors for Java, JavaScript, PL/SQL, Python, and SQL testing. You can add others if your use cases require them to the prefixes array. Just a note for those new to Bash shell scripting the “${variable_name}” is required for arrays.
For a more complete example, I created the following files for a trivial example of procedure overloading in PL/SQL:
- tables.sql – that creates two tables.
- spec.sql – that creates a package specification.
- body.sql – that implements a package specification.
- test.sql – that implements a test case using the package.
- integration.sql – that calls the the scripts in proper order.
The tables.sql, spec.sql, body.sql, and test.sql use the SQL*Plus spool command to write log files, like:
SPOOL spec.txt
-- Insert code here ...
SPOOL OFF |
The body.sql file includes SQL*Plus list and show errors commands, like:
SPOOL spec.txt
-- Insert code here ...
LIST
SHOW ERRORS
SPOOL OFF |
The integration.sql script calls the tables.sql, spec.sql, body.sql, and test.sql in order. Corrupting the spec.sql file by adding a stray “x” to one of the parameter names causes a cascade of errors. After running the integration.sql file with the introduced error, the Bash oracle_errors function returns:
body.txt:2:Warning: Package Body created with compilation errors. body.txt:148:4/13 PLS-00323: subprogram or cursor 'WARNER_BROTHER' is declared in a test.txt:4:ORA-06550: line 2, column 3: test.txt:5:PLS-00306: wrong number or types of arguments in call to 'WARNER_BROTHER' test.txt:6:ORA-06550: line 2, column 3: |
I hope that helps those learning how to program and perform integration testing in an Oracle Database.
sqlplus on Ubuntu
With the release of Oracle Database 23c Free came the ability to update components of the container’s base operating system. Naturally, I took full advantage of that to build my development machine on an Ubuntu 22.0.4 VMware instance with a Docker implementation of the Oracle Database 23c Free container.
Unfortunately, there were changes from that release to the release of Oracle Database 23ai Free. Specifically, Oracle disallows direct patching of their published container’s native Unbreakable Linux 8. It appears the restriction lies in licensing but I haven’t been able to get a clear answer. Oracle’s instructions also shifted from using Docker to using Podman, which reduces the development platform to a limited type of Database as a Service (DaaS) environment. Moreover, that means it requires more skill to leverage the Oracle Database 23ai Free container as a real developer environment by installing and configuring Oracle’s Client software on the host Ubuntu operating system. Then, you must create a host of shared directories to the container to use external files or test external libraries.
While Oracle’s invocation of proprietary control of their native OS is annoying, it’s not nearly as onerous as Apple’s decision to not offer an Intel chip for their MacBook Pro machines. I’ve a hunch Oracle will grant access to their Oracle 23ai Free container in the future but for now this article shows you how to get native SQL*Plus access working.
As to Apple, while I’ve fixed my older machines by upgrading my Intel-based MacBook Pro (i7) to native Ubuntu, it still annoying. Yes, Tim Cooke, I’d rather run Ubuntu than sell back a wonderful piece of hardware on the cheap to Apple. I also did the same upgrade to my iMac 5K with 32 GB of RAM but swapped the cheap hybrid drive for a 2TB SSD.
Now to the technical content that lets you natively develop using Oracle’s SQL*Plus on Ubuntu against the Oracle Database 23ai Free container. While I love SQL*Developer, it has significant limits when testing large blocks of code. Whereas, good techniques, sqlplus, and Bash shell can simplify code development and integration testing.
Here are the steps to get sqlplus working on Ubuntu for your Oracle Database 23ai Free container:
- You need to download the following two zip files from the Oracle Instant Client Downloads for Linux x86-64 (64-bit) website, which assumes an Intel x86 Chip Architecture:
- Open a terminal as your default Ubuntu user and do the following to assume the root superuser responsibility:
sudo sh
As the root user, create the following directory for the Oracle Client software:
mkdir /opt/oracle/instantclient_23_4
As the root user, copy the previously downloaded files to the /opt/oracle directory (this assumes your default user is name as the student user:
cp ~student/Downloads/instantclient*.zip /opt/oracle/.
As the root user, change directory with the cd command to the /opt/oracle directory and verify with the ls -al command that you have the following two files:
total 120968 drwxr-xr-x 4 root root 4096 Jul 3 14:29 . drwxr-xr-x 6 root root 4096 Jul 3 09:09 .. drwxr-xr-x 4 root root 4096 Jul 3 10:11 instantclient_23_4 -rw-r--r-- 1 root root 118377607 Jul 3 14:29 instantclient-basic-linux.x64-23.4.0.24.05.zip -rw-r--r-- 1 root root 5471693 Jul 3 14:29 instantclient-sqlplus-linux.x64-23.4.0.24.05.zip
As the root user, unzip the two zip files in the following order with the unzip command:
unzip instantclient-basic-linux.x64-23.4.0.24.05.zipDisplay detailed console log →
Archive: instantclient-basic-linux.x64-23.4.0.24.05.zip inflating: META-INF/MANIFEST.MF inflating: META-INF/ORACLE_C.SF inflating: META-INF/ORACLE_C.RSA inflating: instantclient_23_4/adrci inflating: instantclient_23_4/BASIC_LICENSE inflating: instantclient_23_4/BASIC_README inflating: instantclient_23_4/fips.so inflating: instantclient_23_4/genezi inflating: instantclient_23_4/legacy.so linking: instantclient_23_4/libclntshcore.so -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.12.1 -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.18.1 -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.19.1 -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.20.1 -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.21.1 -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.22.1 -> libclntshcore.so.23.1 inflating: instantclient_23_4/libclntshcore.so.23.1 linking: instantclient_23_4/libclntsh.so -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.10.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.11.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.12.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.18.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.19.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.20.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.21.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.22.1 -> libclntsh.so.23.1 inflating: instantclient_23_4/libclntsh.so.23.1 inflating: instantclient_23_4/libnnz.so linking: instantclient_23_4/libocci.so -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.10.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.11.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.12.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.18.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.19.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.20.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.21.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.22.1 -> libocci.so.23.1 inflating: instantclient_23_4/libocci.so.23.1 inflating: instantclient_23_4/libociei.so inflating: instantclient_23_4/libocijdbc23.so inflating: instantclient_23_4/libtfojdbc1.so creating: instantclient_23_4/network/ inflating: instantclient_23_4/ojdbc11.jar inflating: instantclient_23_4/ojdbc8.jar inflating: instantclient_23_4/pkcs11.so inflating: instantclient_23_4/ucp11.jar inflating: instantclient_23_4/ucp.jar inflating: instantclient_23_4/uidrvci inflating: instantclient_23_4/xstreams.jar creating: instantclient_23_4/network/admin/ inflating: instantclient_23_4/network/admin/README finishing deferred symbolic links: instantclient_23_4/libclntshcore.so -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.12.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.18.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.19.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.20.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.21.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.22.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntsh.so -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.10.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.11.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.12.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.18.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.19.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.20.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.21.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.22.1 -> libclntsh.so.23.1 instantclient_23_4/libocci.so -> libocci.so.23.1 instantclient_23_4/libocci.so.10.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.11.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.12.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.18.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.19.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.20.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.21.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.22.1 -> libocci.so.23.1and, then
unzip instantclient-sqlplus-linux.x64-23.4.0.24.05.zipDisplay detailed console log →
Archive: instantclient-sqlplus-linux.x64-23.4.0.24.05.zip replace META-INF/MANIFEST.MF? [y]es, [n]o, [A]ll, [N]one, [r]ename: y inflating: META-INF/MANIFEST.MF replace META-INF/ORACLE_C.SF? [y]es, [n]o, [A]ll, [N]one, [r]ename: y inflating: META-INF/ORACLE_C.SF replace META-INF/ORACLE_C.RSA? [y]es, [n]o, [A]ll, [N]one, [r]ename: y inflating: META-INF/ORACLE_C.RSA inflating: instantclient_23_4/glogin.sql inflating: instantclient_23_4/libsqlplusic.so inflating: instantclient_23_4/libsqlplus.so inflating: instantclient_23_4/sqlplus inflating: instantclient_23_4/SQLPLUS_LICENSE inflating: instantclient_23_4/SQLPLUS_README
- As the root user, run these two commands:
sudo sh -c "echo /opt/oracle/instantclient_23_4 > \ /etc/ld.so.conf.d/oracle-instantclient.conf" sudo ldconfig
Next, you’ll test the installation. As the root user, run these three commands, which you’ll later add to your standard Ubuntu user’s .bashrc file:
export ORACLE_HOME=/opt/oracle/instantclient_23_4 export LD_LIBRARY_PATH=$ORACLE_HOME export PATH=$PATH:$ORACLE_HOME
As the root user, you can now test whether you can start the Oracle SQL*Plus client with the following command:
sqlplus /nologIt should connect and return this:
SQL*Plus: RELEASE 23.0.0.0.0 - Production ON Wed Jul 3 10:12:33 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. ALL rights reserved. SQL>
If you get this type of error, either you didn’t install the Oracle instant client basic libraries or you installed an incompatible version:
sqlplus: error while loading shared libraries: libclntsh.so.23.1: cannot open shared object file: No such file or directory
If you got the error, you’ll need to readdress the installation of the Oracle instant client typically.
Another type of error can occur if you get ahead of these instructions and try to connect to the Oracle Database 23ai Free container with syntax like this:
sql> connect c##student/student@free
because you’ll most likely get an error like this:
ERROR: ORA-12162: TNS:net service name is incorrectly specified Help: https://docs.oracle.com/error-help/db/ora-12162/
The error occurs because you haven’t setup the Oracle Net Services, which is level 5 in the OSI (Open System Interconnection) Model. In Oracle-speak, that means you haven’t setup a tnsnames.ora file, failed to put the tnsnames.ora file in the right place, or failed to set the $TNS_ADMIN environment variable correctly.
- While there are many ways to setup a tnsnames.ora file, the best way is to follow Oracle’s recommended approaches. In the Oracle client approach you should put the tnsnames.ora file in the $ORACLE_HOME/network/admin directory and use the $TNS_ADMIN environment variable to point to it. Unfortunately, that approach doesn’t work when you’re installing the Oracle client software unless you want to play with mount points. It’s easiest to create a hidden directory in your sandbox user, which is student in this example.
As the root user, use the mkdir command to create the .oracle directory in your student user directory:
mkdir /home/student/.oracle
As the student user, navigate to the /home/student/.oracle directory and create the tnsnames.ora file with the following text:
# tnsnames.ora Network Configuration FILE: FREE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREE) ) ) LISTENER_FREE = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.0)(PORT = 1521)) FREEPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB1) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_FREE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )
Exit the root user to your student user. As the student user set the $TNS_ADMIN environment variable like:
export TNS_ADMIN=$HOME/.oracle
Assuming you’ve already created a container user, like c##student, connect to sqlplus with the following syntax:
sqlplus c##student/student@freeYou should see the following when connection to an Oracle 23c Container:
SQL*Plus: Release 23.0.0.0.0 - Production on Wed Jul 3 15:05:10 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Wed Jul 03 2024 10:52:13 -06:00 Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL>
You should see the following when connection to an Oracle 23ai Container:
SQL*Plus: Release 23.0.0.0.0 - Production on Sat Jul 20 11:05:08 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Sat Jul 20 2024 10:41:38 -06:00 Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL>
- The last step adds all of the configuration settings into the .bashrc file. Before we do that, you may want to add the rlwrap utility library so you can use the up-arrow to navigate the sqlplus history. You install it as the root or sudo user on Ubuntu, like
apt install -y rlwrap
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following packages were automatically installed and are no longer required: libwpe-1.0-1 libwpebackend-fdo-1.0-1 Use 'sudo apt autoremove' to remove them. The following NEW packages will be installed: rlwrap 0 upgraded, 1 newly installed, 0 to remove and 5 not upgraded. Need to get 98.2 kB of archives. After this operation, 309 kB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 rlwrap amd64 0.43-1build3 [98.2 kB] Fetched 98.2 kB in 1s (160 kB/s) Selecting previously unselected package rlwrap. (Reading database ... 211287 files and directories currently installed.) Preparing to unpack .../rlwrap_0.43-1build3_amd64.deb ... Unpacking rlwrap (0.43-1build3) ... Setting up rlwrap (0.43-1build3) ... update-alternatives: using /usr/bin/rlwrap to provide /usr/bin/readline-editor ( readline-editor) in auto mode Processing triggers for man-db (2.10.2-1) ...
If you want to manually check what you’re removing, use the following command as the root user:
apt autoremove
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following packages will be REMOVED: libwpe-1.0-1 libwpebackend-fdo-1.0-1 0 upgraded, 0 newly installed, 2 to remove and 5 not upgraded. After this operation, 182 kB disk space will be freed. Do you want to continue? [Y/n] Y (Reading database ... 211329 files and directories currently installed.) Removing libwpebackend-fdo-1.0-1:amd64 (1.14.2-0ubuntu0.22.04.1) ... Removing libwpe-1.0-1:amd64 (1.14.0-0ubuntu0.22.04.1) ... Processing triggers for libc-bin (2.35-0ubuntu3.8) ...
- The last step requires that you put the environment variables into the student user’s .bashrc shell script, and add a sqlplus function to take advantage of the new libraries added to read your prior history inside the SQL*Plus command line.
You should edit the .bashrc file and add the following environment variables and sqlplus() function:
# Configure Oracle Client software. export ORACLE_HOME=/opt/oracle/instantclient_23_4 export LD_LIBRARY_PATH=$ORACLE_HOME export PATH=$PATH:$ORACLE_HOME export TNS_ADMIN=$HOME/.oracle # A user-defined function to wrap the sqlplus history. sqlplus () { # Discover the fully qualified program name. path=`which rlwrap 2>/dev/null` file='' # Parse the program name from the path. if [ -n ${path} ]; then file=${path##/*/} fi; # Wrap when there is a file and it is rewrap. if [ -n ${file} ] && [[ ${file} = "rlwrap" ]]; then rlwrap $ORACLE_HOME/sqlplus "${@}" else echo "Command-line history unavailable: Install the rlwrap package." $ORACLE_HOME/sqlplus "${@}" fi }
You should remember that when you access sqlplus from the Ubuntu environment the TNS net service name is required. If you should forget to include it like this:
sqlplus c##student/studentYou’ll get the following error:
ERROR: ORA-12162: TNS:net service name is incorrectly specified Help: https://docs.oracle.com/error-help/db/ora-12162/
The correct way is:
sqlplus c##student/student@free
As always, I hope this helps those looking for a solution.
Subscribe via RSS