Exporting LQE data in Oracle using configuration cache

You can export Lifecycle Query Engine (LQE) data by using the configuration cache in Oracle databases.

Before you begin

Java Development Kit (JDK)

Ensure that JDK 8 is installed and configured as the JAVA_HOME variable in the system environment variables.

Note: This procedure was tested with JDK 8, but it is supported on Java 8 and later versions.

To verify the Java version installed on your machine, run the following command:

java -version

Oracle Java virtual machine (JVM)

Verify that Java is installed in Oracle by connecting to SQL*Plus and running the following commands:

SELECT dbms_java.get_jdk_version() FROM dual;
desc DBMS_JAVA;
SELECT COMP_ID, COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'JAVAVM';

TrsFunctions.class file

Download and copy the TrsFunctions.class file to a folder or server where you want to execute the procedure.

User permissions

Use the same user for loading the class and executing commands in SQL*Plus.

Procedure

Load the Java class into Oracle and create the necessary database objects.

Load the Java class into Oracle by running one of the following commands:

Generic command syntax:

loadjava -thin -user user-id/password@//host:port/db-service-name -resolve TrsFunctions.class

Alternative command with additional options:

loadjava -u / -r -v -f -s -grant public -genmissing TrsFunctions.class

Examples:

loadjava -u system/password@//server:1532/ALMS -r -v -f -s -grant public -genmissing TrsFunctions.class
loadjava -u System/password@//127.0.0.1:49687/XEPDB1 -r -v -f -s -grant public -genmissing TrsFunctions.class
loadjava -thin -user system/password@//127.0.0.1:49687/XEPDB1 -resolve TrsFunctions.class

Where:

user-id: Database user ID
password: Database password
host: Host name where the database is hosted
port: Port number on which the listener is listening for connections
db-service-name: Database service name present in the listener file. Run lsnrctl services to get details about database instances and respective services, host, and port details.

Tip: To drop the Java class from Oracle if needed, run:

dropjava -thin -user user-id/password@//host:port/db-service-name TrsFunctions.class

sqlplus sys/password@database-name AS SYSDBA

Example:

sqlplus sys/password@orclpdb.fyre.ibm.com AS SYSDBA

Enable logging in the Oracle database by running the following commands:

SET SERVEROUTPUT ON SIZE 1000000;
BEGIN DBMS_JAVA.SET_OUTPUT(1000000); END;
/

Optional: If needed, switch to the appropriate database container by running the following commands:
```
SHOW CON_NAME;
```
This displays the existing database connection name. Verify that this is the appropriate database where you have LQE rs schemas.

To alter the session to the appropriate LQE rs database:
```
ALTER SESSION SET CONTAINER = ORCLPDB;
```

Verify that the Java class exists by running the following commands:

SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'JAVA CLASS' AND OBJECT_NAME LIKE 'trs%';

Expected output:

OBJECT_NAME                STATUS
------------------------   -------
trs/TrsFunctions          VALID

Alternative verification command:

SELECT OBJECT_NAME, STATUS, OWNER FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'trs%';

Create the stored procedure and function by running the following commands:

Create the procedure:

CREATE OR REPLACE PROCEDURE create_cache_id (schemaPrefix VARCHAR2, configUri VARCHAR2) 
AS LANGUAGE JAVA NAME 'trs.TrsFunctions.cacheConfigurationHierarchy(java.lang.String,java.lang.String)';
/

Create the function:

CREATE OR REPLACE FUNCTION get_cache_id (schemaPrefix VARCHAR2, configUri VARCHAR2) 
RETURN NUMBER AS LANGUAGE JAVA NAME 'trs.TrsFunctions.getLastCachedConfigurationId(java.lang.String,java.lang.String) return java.lang.Long';
/

Verify the status of the database objects:

SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS 
WHERE OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY') 
AND OBJECT_NAME IN ('CREATE_CACHE_ID', 'GET_CACHE_ID');

Check for compilation errors:

SELECT * FROM USER_ERRORS WHERE NAME = 'CREATE_CACHE_ID';

Note: If there are compilation errors, manually recompile the database objects:

ALTER PACKAGE CREATE_CACHE_ID COMPILE;
ALTER PACKAGE CREATE_CACHE_ID COMPILE BODY;

Execute the procedure to cache the configuration data:
```
CALL create_cache_id (schema-prefix, config-url);
```
Example:
```
CALL create_cache_id ('HELLA_', 'https://localhost:9443/gc/configuration/17');
```
Where:

schema-prefix

The schema prefix for your LQE database. If there is no schema prefix, pass null.

config-url

The full URL of the configuration. For example: https://host:port/gc/configuration/id

Optional: To retrieve the cached configuration ID, execute the function:

First, create a temporary variable to hold the function result:

VARIABLE cache_id NUMBER;

Then, call the function:

CALL get_cache_id (schema-prefix, config-url) INTO :cache_id;

Example:

CALL get_cache_id ('HELLA_', 'https://localhost:9443/gc/configuration/17') INTO :cache_id;

Print the results:

PRINT cache_id;