Oracle Stored Procedures with Custom Java Code

Togheter with the Oracle´s official Structured Query Language PL/SQL, and the open-source statistical analysis language bundled in the Oracle Data Mining package, R, we also have the possibilty to use and integrate another great technology in our database: Java, the world's number one programming language in enterprise environments.

The usage of Java inside stored procedures in pretty much simple: you just need to create a standard stored procedure "linking" to your custom java code. Here is a short example:

CREATE OR REPLACE FUNCTION    your_function_name( p_arg1 IN VARCHAR2) RETURN NUMBERAS   LANGUAGE JAVA   NAME 'your_java_function_name(java.lang.String...) return integer';

public static int your_function_name(String separator, String args) {     ...your java code...} 

Getting the List of Referring Tables to a Table

If you wanna know, in Oracle, all referring tables to a certain table here is a simple query:

SELECT  bs.OWNER , bs.TABLE_NAME , bs.CONSTRAINT_NAMEFROM  USER_CONSTRAINTS bs , USER_CONSTRAINTS rf WHERE  bs.CONSTRAINT_TYPE='R'  AND rf.TABLE_NAME='YOUR_TABLE_NAME' AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME;

If, in addition, you wanna also know the referring column names:

SELECT  bs.OWNER , bs.TABLE_NAME , cl.COLUMN_NAME , bs.CONSTRAINT_NAMEFROM  USER_CONSTRAINTS bs , USER_CONSTRAINTS rf  , USER_CONS_COLUMNS clWHERE  bs.CONSTRAINT_TYPE='R'  AND rf.TABLE_NAME='T_CL_KUNDE_D' AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME AND bs.CONSTRAINT_NAME=cl.CONSTRAINT_NAME;

Using the same script of the previous post, we can implement a fast script for disabling all the foreign key contraints referring to the desired table:

SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN
  FOR i IN
(
SELECT
bs.CONSTRAINT_NAME "constraint_name"
, bs.TABLE_NAME "table_name"
FROM
USER_CONSTRAINTS bs
, USER_CONSTRAINTS rf
, USER_CONS_COLUMNS cl
WHERE
bs.CONSTRAINT_TYPE='R'
AND rf.TABLE_NAME='your_table_name'
AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME
AND bs.CONSTRAINT_NAME=cl.CONSTRAINT_NAME
)
  LOOP
   v_stmt := 'ALTER TABLE your_table_name DISABLE CONSTRAINT ' || i.CONSTRAINT_NAME;
   EXECUTE IMMEDIATE v_stmt;

   dbms_output.put_line('disabled: ' || i.constraint_name || ' from table: ' ||  i.table_name);
  END LOOP;
END;
/

Temporary Disabling the Foreign Keys in Oracle

Foreign Keys are the most widely used constraint types in OLTP systems, since they ensures that a value of the referencing column cannot contain a value that does not exist in the corresponding column of the referenced table. Foreign Keys do not guarantee absolute data quality: rather, they guarantee... referential integrity, which is a good starting point for obtaining a good level of data quality and reliability.

OLTP systems are always accessed in a "transactional" way by multiple users: each data entry is inserted row by row, and should therefore be controlled  one by one, togheter with the help of the other database constraints (or the application level filters, if the system is ill-designed).

In DWH environments, however, things are different. First, the data integrity is - or should be- guaranteed by the automatic ETL process; exceptions, error or any data inconsistency has to be automatically managed - and reported - direct at etl level. Sometimes one of the goal of the ETL process is also to find data anomalies and problems: this can be part of the DWH structure itself, for example with the use of an Audit Dimension or Fact.

Second, the presence of explicit foreign keys (logically we ALWAYS have referencial integrity between facts and dimensions) load performance is brutally degraded: a cross-table check is triggered for every insert/update/delete statement, resulting into an unacceptable overhead.

Third, during the developement of a DWH System operations like table truncating and changing are frequent, thus causing headaches when making DML or DDL changes.

If you are not the Project Manager, and you cannot decide to drop -once for all- the explicit and annoying foreign keys from the database, you can temporary disable them by using a simple procedure similar to the one contained in the following anonymous block:

SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN FOR i IN (SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='YOUR_TABLE_NAME' AND CONSTRAINT_TYPE='R') LOOP v_stmt := 'ALTER TABLE ' || i.TABLE_NAME || ' DISABLE CONSTRAINT ' || i.CONSTRAINT_NAME; EXECUTE IMMEDIATE v_stmt; dbms_output.put_line('disabled: ' || i.CONSTRAINT_NAME || ' from table: ' ||  i.TABLE_NAME); END LOOP;END;/

Similarly, to re-enable the foreign keys:

SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN FOR i IN (SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='YOUR_TABLE_NAME' AND CONSTRAINT_TYPE='R') LOOP v_stmt := 'ALTER TABLE ' || i.TABLE_NAME || ' ENABLE CONSTRAINT ' || i.CONSTRAINT_NAME; EXECUTE IMMEDIATE v_stmt; dbms_output.put_line('enabled: ' || i.CONSTRAINT_NAME || ' from table: ' ||  i.TABLE_NAME); END LOOP;END;/

As always, do not forget the backslash "/" at the end of the anonymous block.

Date Comparison in Oracle

If you wanna compare (equal) two dates in Oracle, you can' t simply use the "=" operator in the query clause. Instead, you can perform a simple string comparison through the TO_CHAR function:

SELECT   dt1   , dt2FROM   src_tableWHERE TO_CHAR(dt1, 'YYYY-MM-DD')=TO_CHAR(dt2, 'YYYY-MM-DD');

Working with Oracle Sequence Objects in Talend Open Studio

In case you need a column that contains unique, sequentially generated numbers, you can realize autonumber fields (also known as "auto-increment" fields) in RDMBS engines like Oracle and PostgreSQL by using sequence objects. This is often the case of the primary surrogate keys in DWH environments.

In Oracle, a sequence object is a separate structure specifically created to generate sequential values; it can be efficiently and concurrently accessed by more than one process at time, and the same sequence can be used for one or more tables.

If you are using an ELT tool like Oracle Warehouse Builder, the access and increment of the sequence object into a mapping is pretty much easy: we simply drag and drop the sequence object itself, and connect the "NEXTVAL" field to the desired column of the target table.

Mapping of a sequence object in Oracle Warehouse Builder.

With our favourite Data Integration tool, however, things are a bit more complicated. Suppose we don' t wanna delegate the "autoincrement" logic to the ETL process, and that we simply wanna insert new rows into one table, letting the RDBMS manage the sequential logic by itself. In this case, we would obviously make use of a sequence object:

DROP  SEQUENCE seq_name;

CREATE  SEQUENCE seq_nameSTART WITH 1 INCREMENT BY 1 MINVALUE 1 NOCACHE NOCYCLE NOORDER;

The problem is that, with Talend Open Studio, we don't have a way to directly access the sequence object inside the ETL flow - and we can't therefore implement any incremental logic at ETL level; neverthless, the sequence object doesn't simply increment by itself - you have to access it in some way, at application level.

We can' t directly access an Oracle sequence object in Talend Open Studio.

The solution is to implement a trigger object: each row in the target table will trigger an increment of the sequence object, whose value will be stored in the "autoincrement" column itself: 

CREATE OR REPLACE TRIGGER  trg_nameBEFORE INSERT ON  table_name FOR EACH ROW WHEN     (new.autoincr_col IS NULL)  BEGIN    SELECT       seq_name.NEXTVAL    INTO         :new.autoincr_col    FROM        DUAL;  END;/

The trigger object makes slower the loading process - as a matter of fact each insert row triggers a different operation. However, it perfectly works in case of small amount of insert data.

Do not forget to add the backslash "/" at the end of the trigger creation script.

Sending emails... from the Oracle database

ELT tools like Oracle Warehouse Builder are poor of predefined components for integrating the Data Warehouse with the rest of the world.

For istance, suppose that you are using Oracle OWB and your ETL/Data Integration process is required to notify you  -or the system administrator- in case of ETL process failures: well, you don´t have a predefined component to send emails.

Fortunately Oracle offers us the possibilty to directly send emails, with or withoout attachments, from "inside" the database. We just have to build a piece of PL/SQL code.

First of all, we need to connect as database administrator, in order to set the outgoing smtp server:

CONN / AS SYSDBA;ALTER SYSTEM SET smtp_out_server = 'YOURSMTPSERVER' SCOPE=BOTH;

Using a normal user, we can then create an anonymous block in order to test the functioning of the UTL_MAIL package:

BEGIN  dbms_output.put_line('UML MAIL SEND TEST');    UTL_MAIL.SEND   ( sender      => 'ADMINISTRATORNAME' , recipients  =>  'ADMINISTRATOREMAIL', --, cc          IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT       NULL, , bcc         => 'YOUREMAIL', , subject     => 'Test email from Oracle', , message     =>  'This comes from the Oracle Server.' --, mime_type   IN    VARCHAR2 DEFAULT 'text/plain; charset=us-ascii', --, priority    IN    PLS_INTEGER DEFAULT NULL);  )  ;END;/

If we wanna automate the process in order to send more than one email, we can manually encode a solution like this:

CREATE OR REPLACE PROCEDURE   mul_email_send IS  CURSOR  mcur  IS SELECT  EMAILCOLUMN FROM  EMAILSTABLE WHERE EMAILCOLUMN IS NOT NULL; v_sender VARCHAR2(30) := 'ADMINISTRATORNAME'; v_sender_email VARCHAR2(30) := 'ADMINISTRATOREMAIL'; v_subject   VARCHAR2(50) := 'YOURSUBJECT'; v_message   VARCHAR2(4000) := YOURMESSAGE; v_mime_type  VARCHAR2(30) := 'text/plain; charset=us-ascii';BEGIN   SELECT  EMAILCOLUMN   INTO  v_subject   FROM  EMAILTABLE;   FOR  mrec    IN  mcur   LOOP      utl_mail.send(v_sender, v_sender_email, NULL, mrec, v_subject, v_message, v_mime_type, NULL);   END LOOP;END mul_email_send;/

This solution works but it's not elegant neither clean, and should be used only in case your ELT tool doens't offer any alternative. Hand-coding a Data Warehouse (as well as many other systems and solutions) makes it too hard to scale and, makes the data integration process too inaccurate or even incomplete.

In addition, the entire enterprise risks to become too dependent on the specific style of its developers.