Handling REF CURSOR in SOA Layer

Refer to my previous post on handling strong & weak REF CURSOR output in PLSQL.

In this post, let's discuss about handling REF CURSOR output in SOA layer.

Consider following sample procedure having weak REF CURSOR as output. Compile this procedure in database.

CREATE OR REPLACE PROCEDURE PROC_WEAK_REF_CUR(
                                                in_dept_id  IN  DEPARTMENTS.DEPARTMENT_ID%TYPE,
                                               out_ref_cur OUT SYS_REFCURSOR)
IS
BEGIN
       OPEN    out_ref_cur FOR
       SELECT EMP.*
       FROM   EMPLOYEES EMP,
                     DEPARTMENTS DEPT,
                     LOCATIONS LOC
       WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
       AND       DEPT.LOCATION_ID = LOC.LOCATION_ID
       AND       DEPT.DEPARTMENT_ID = in_dept_id;
END;

Refer following steps to convert Weak XSD to Strong XSD:

• Create a sample SOA composite application in JDeveloper.
• Open composite.xml and Drag Database adapter to External References lane.
• Choose Operation Type 'Call a Stored Procedure or Function'
• In the next step 'Specify Stored Procedure', click on Browse and select the procedure given above. Here verify the output parameter type.

•  In the next step 'RowSet', by default, database adapter generates Weak XSD as shown below. 

• The XML output from Weak XSD is hard to use. It is very difficult to write an Xpath expression or XSL based on a weakly typed XSD and column names as attribute values instead of element names.

• To convert Weak XSD to Strong XSD, enter valid value for input parameters. Click on Introspect.

• If the stored procedure or function returns a row set with at least 1 row, the RowSets page is updated to display a strongly typed XSD in the XSD text field a shown below. 

• In case of Strongly typed XSD, it's easy to identify elements and write an transformation map.

Strong and Weak REF CURSOR Structure - PLSQL

Strong REF CURSOR: Structure of REF cursor is well known during compile time.
Weak REF CURSOR: Not known during compile time.

Following 3 samples will give different ways to use REF CURSOR in PLSQL. These examples created using default HR schema available with XE database.

Weak REF CURSOR Example:
CREATE OR REPLACE PROCEDURE PROC_WEAK_REF_CUR(
                                                in_dept_id  IN  DEPARTMENTS.DEPARTMENT_ID%TYPE,
                                               out_ref_cur OUT SYS_REFCURSOR)
IS
BEGIN
       OPEN    out_ref_cur FOR
       SELECT EMP.*
       FROM   EMPLOYEES EMP,
                     DEPARTMENTS DEPT,
                     LOCATIONS LOC
       WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
       AND       DEPT.LOCATION_ID = LOC.LOCATION_ID
       AND       DEPT.DEPARTMENT_ID = in_dept_id;
END;


DECLARE
        out_ref_cur SYS_REFCURSOR;
        employee EMPLOYEES%ROWTYPE;
BEGIN
        PROC_WEAK_REF_CUR(20,out_ref_cur);
        LOOP
                FETCH out_ref_cur INTO employee;
                EXIT WHEN out_ref_cur%NOTFOUND;
                dbms_output.put_line(employee.EMPLOYEE_ID);
                dbms_output.put_line(employee.FIRST_NAME);
                dbms_output.put_line(employee.LAST_NAME);
                dbms_output.put_line(employee.EMAIL);
                dbms_output.put_line(employee.HIRE_DATE);
                dbms_output.put_line(employee.JOB_ID);
                dbms_output.put_line(employee.SALARY);
                dbms_output.put_line(employee.COMMISSION_PCT);
                dbms_output.put_line(employee.MANAGER_ID);
                dbms_output.put_line(employee.DEPARTMENT_ID);
         END LOOP;
        CLOSE out_ref_cur;
END;

Strong REF CURSOR Example:

CREATE OR REPLACE PACKAGE PKG_STRONG_REF_CUR AS

   TYPE ref_cur_type IS RECORD (

                                EMPLOYEE_ID             EMPLOYEES.EMPLOYEE_ID%TYPE,

                        FIRST_NAME                EMPLOYEES.FIRST_NAME%TYPE,

                                DEPARTMENT_ID        DEPARTMENTS.DEPARTMENT_ID%TYPE,

                                DEPARTMENT_NAME DEPARTMENTS.DEPARTMENT_NAME%TYPE,

                                LOCATION_ID              LOCATIONS.LOCATION_ID%TYPE,

                                CITY                                LOCATIONS.CITY%TYPE );

   TYPE ref_cur IS REF CURSOR RETURN ref_cur_type;

   PROCEDURE PROC_STRONG_REF_CUR(

                                  in_dept_id   IN     DEPARTMENTS.DEPARTMENT_ID%TYPE,

                                  out_ref_cur  OUT ref_cur);

END PKG_STRONG_REF_CUR;

CREATE OR REPLACE PACKAGE BODY PKG_STRONG_REF_CUR AS

   PROCEDURE PROC_STRONG_REF_CUR(

                                                        in_dept_id   IN     DEPARTMENTS.DEPARTMENT_ID%TYPE,

out_ref_cur  OUT ref_cur)

   IS

   BEGIN

      OPEN   out_ref_cur FOR

      SELECT EMP.EMPLOYEE_ID,

                     EMP.FIRST_NAME, 

                     DEPT.DEPARTMENT_ID,

                     DEPT.DEPARTMENT_NAME, 

                     LOC.LOCATION_ID, 

                     LOC.CITY

      FROM   EMPLOYEES EMP,

                    DEPARTMENTS DEPT,

                    LOCATIONS LOC

      WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID

      AND       DEPT.LOCATION_ID = LOC.LOCATION_ID

      AND       DEPT.DEPARTMENT_ID = in_dept_id;  

   END;

END PKG_STRONG_REF_CUR;

DECLARE

      v_ref_cur      PKG_STRONG_REF_CUR.ref_cur;

      v_ref_cur_type PKG_STRONG_REF_CUR.ref_cur_type;

BEGIN

     PKG_STRONG_REF_CUR.PROC_STRONG_REF_CUR(20,v_ref_cur);

     LOOP

         FETCH v_ref_cur INTO v_ref_cur_type;

    EXIT WHEN v_ref_cur%notfound;

         dbms_output.put_line(v_ref_cur_type.EMPLOYEE_ID);

         dbms_output.put_line(v_ref_cur_type.FIRST_NAME);

         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_ID);

         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_NAME);

         dbms_output.put_line(v_ref_cur_type.LOCATION_ID);

         dbms_output.put_line(v_ref_cur_type.CITY);

  END LOOP;

END;

Another Example:

CREATE OR REPLACE PROCEDURE PROC_STRONG_REF_CUR(

                                                 in_dept_id  IN     DEPARTMENTS.DEPARTMENT_ID%TYPE,

                                                 out_ref_cur OUT SYS_REFCURSOR)

IS

BEGIN

  OPEN   out_ref_cur FOR

  SELECT EMP.EMPLOYEE_ID,

                 EMP.FIRST_NAME, 

                 DEPT.DEPARTMENT_ID,

                 DEPT.DEPARTMENT_NAME, 

                 LOC.LOCATION_ID, 

                 LOC.CITY

  FROM   EMPLOYEES EMP,

                DEPARTMENTS DEPT,

                LOCATIONS LOC

  WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID

  AND       DEPT.LOCATION_ID = LOC.LOCATION_ID

  AND       DEPT.DEPARTMENT_ID = in_dept_id;

END;

DECLARE

TYPE ref_cur_type IS  RECORD(

                                EMPLOYEE_ID             EMPLOYEES.EMPLOYEE_ID%TYPE,

                        FIRST_NAME                EMPLOYEES.FIRST_NAME%TYPE,

                                DEPARTMENT_ID        DEPARTMENTS.DEPARTMENT_ID%TYPE,

                                DEPARTMENT_NAME DEPARTMENTS.DEPARTMENT_NAME%TYPE,

                                LOCATION_ID              LOCATIONS.LOCATION_ID%TYPE,

                                CITY                                LOCATIONS.CITY%TYPE );

TYPE ref_cur IS  REF CURSOR RETURN ref_cur_type;

      v_ref_cur ref_cur;

      v_ref_cur_type ref_cur_type;

  BEGIN

       PROC_WEAK_REF_CUR(20,v_ref_cur);

       LOOP

            FETCH v_ref_cur INTO v_ref_cur_type;

       EXIT WHEN v_ref_cur%NOTFOUND;

         dbms_output.put_line(v_ref_cur_type.EMPLOYEE_ID);

         dbms_output.put_line(v_ref_cur_type.FIRST_NAME);

         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_ID);

         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_NAME);

         dbms_output.put_line(v_ref_cur_type.LOCATION_ID);

         dbms_output.put_line(v_ref_cur_type.CITY);

  END LOOP;

  CLOSE v_ref_cur;

END;

JDeveloper Compilation Error - Android SDK

When deploying ADF mobile application using JDeveloepr 11.1.2.4.0 to Android Emulator, following error appears on deployment log and won't allow to deploy application.

It happens due to non-availability of following files/directories in android-sdk/platform-tools directory. In the latest release of Android SDK(Android 4.2.2 API 17), listed files/directories moved from platform-tools to build-tools directory. But still JDeveloper refers to platform-tools directory. Instead of waiting for fix/update from Oracle, following quick fix will help to finish deployment.

Files:
aapt.exe
dx.bat

Directory:
\lib

Quick Fix Steps:
1. Open command prompt with admin privileges in Windows
2. Create symbolic links in platform-tools directory for the listed files exists in build-tools directory.

mklink E:\Android\sdk\platform-tools\aapt.exe E:\Android\sdk\build-tools\android-4.2.2\aapt.exe

mklink /D E:\Android\sdk\platform-tools\lib E:\Android\sdk\build-tools\android-4.2.2\lib

mklink E:\Android\sdk\platform-tools\dx.bat E:\Android\sdk\build-tools\android-4.2.2\dx.bat

Encrypt Data Using MD5 and SHA - Oracle Database

Following Oracle database packages enables you to encrypt critical data like passwords using SHA or MD5 encryption algorithms.

MD5: dbms_obfuscation_toolkit.md5
SHA: dbms_crypto.hmac_sh1

Sample usage:

MD5: 
utl_i18n.string_to_raw(dbms_obfuscation_toolkit.md5(input_string => <value to be encrypted>))

SHA:
encryption_key := dbms_random.string('X',16);
dbms_crypto.mac(utl_i18n.string_to_raw(<value to be encrypted>),
                           dbms_crypto.hmac_sh1,
                           utl_i18n.string_to_raw(encryption_key));

For more info, refer link.