Showing posts with label Miscellaneous. Show all posts
Showing posts with label Miscellaneous. Show all posts

Monday, 19 March 2012

Java Stored Procedures and Load Java Utility

Step 1: DEVELOP JAVA CLASS, WITH MEMBER METHOD(S)

public class Hello
{
   public static String world ()
   {
      return "Hello world";
    }
}     
  
Then compile the source using:
javac Hello.java
NOTE:
1.      It is not necessary to compile the java source file in this step since the utility loadjava (refer STEP 2) does this for us.
2.      Methods with VOID return type get mapped to Procedures, where as methods with NON – VOID return type get mapped to functions when these are published using the call specifications (STEP 3)

Step 2: LOAD THE JAVA SOURCE/CLASS INTO THE ORACLE DATABASE

loadjava –u user_name/password@oracleSID -r –v hello_word.java

C:\oracle9i\bin> loadjava -u scott/tiger@dbsmgi Hello.java

Step 3: WRITE THE CALL SPECS TO PUBLISH THE JAVA CLASSES

SQL> CREATE OR REPLACE FUNCTION proc_get_msg RETURN VARCHAR2
 AS LANGUAGE JAVA
 NAME 'Hello.world() return java.lang.String';
  /
Function created.

Step 4:  CALLING THE JAVA STORED PROCEDURES

SQL> set serveroutput on;
SQL> call dbms_java.set_output(50);

Define the variable to get the message returned by function
SQL> VARIABLE var_MSG VARCHAR2(20);

Call the Function (get the return value into the variable)
SQL> CALL proc_get_msg() INTO :var_MSG;

Print the result
SQL> print var_MSG;

VAR_MSG
---------------------
Hello world

Where are the class files stored ?

SQL> desc all_objects;

Name                       Type                   Nullable                         Default Comments                                                                     
-------------- ------------ -------- ------- ----------------------------------------------------------------------------
OWNER                    VARCHAR2(30)            Username of the owner of the object                                          
OBJECT_NAME          VARCHAR2(30)                Name of the object                                                          
SUBOBJECT_NAME    VARCHAR2(30)       Y       Name of the sub-object (for example, partition
OBJECT_ID              NUMBER                        Object number of the object                                                 
DATA_OBJECT_ID      NUMBER                         Y       Object number of the segment which contains the object                      
OBJECT_TYPE            VARCHAR2(19)      Y       Type of the object                                                          
CREATED                 DATE                             Timestamp for the creation of the object                                    
LAST_DDL_TIME       DATE                             Timestamp for the last DDL change (including GRANT and 
                                                                                                                         REVOKE) to the object
TIMESTAMP           VARCHAR2(19)       Y       Timestamp for the specification of the object                               
STATUS                  VARCHAR2(7)         Y       Status of the object                                                        
TEMPORARY      VARCHAR2(1)                Y       Can the current session only see data that it placed in this                   
                                                                     object itself? 
GENERATED      VARCHAR2(1)                Y       Was the name of this object system generated?                               
SECONDARY      VARCHAR2(1)                Y       Is this a secondary object created as part of icreate for domain
                                                                     indexes?   

SQL>select * from all_objects where object_name like 'Hello%';

OWNER  OBJECT_NAME    SUBOBJECT_NAME   OBJECT_ID  DATA_OBJECT_ID OBJECT_TYPE  CREATED   LAST_DDL_TIME TIMESTAMP  STATUS  TEMPORARY GENERATED SECONDARY
------------------------------ ------------------------------ ------------------------------ --------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
APPS    Hello                                                  736206                JAVA CLASS          6/6/2007 5: 6/14/2007 12: 2007-06-06:05:40:45 VALID   N         N         N


SQL> desc user_java_classes

Name          Type           Nullable Default Comments                                                                   
------------- -------------- -------- ------- --------------------------------------------------------------------------
NAME          VARCHAR2(4000) Y                name of the java class                                                     
MAJOR         NUMBER         Y                the major version number of the java class as defined in JVM specification
MINOR         NUMBER         Y                the minor version number of the java class as defined in JVM specification
KIND          VARCHAR2(9)    Y                is the stored object a java class or java interface?                      
ACCESSIBILITY VARCHAR2(6)    Y                the accessiblity of the java class                                        
IS_INNER      VARCHAR2(3)    Y                is this java class an inner class                                         
IS_ABSTRACT   VARCHAR2(3)    Y                is this an abstract class?                                                
IS_FINAL      VARCHAR2(3)    Y                is this an final class?                                                   
IS_DEBUG      VARCHAR2(3)    Y                does this class contain debug information?                                
SOURCE        VARCHAR2(4000) Y                source designation of the java class                                      
SUPER         VARCHAR2(4000) Y                super class of this java class                                            
OUTER         VARCHAR2(4000) Y                outer class of this java class if this java class is an inner class       


SQL> Select * From user_java_classes where NAME like 'Hello%';

Hello     45  3 INTERFACE PUBLIC NO  NO  NO  NO Hello.java java/lang/Object
-

1 row selected.

Using the main Method

The main method can take a variable number of arguments which are typically passed on the command line to the Java VM.

Example:

public class  DemoMain {
  public static void main(String args[])  {
   for(int i=0; i< args.length; i++)
        System.out.println("args[" + i + "]:" + args[i]);
     }
}

Now we have to load it in the database using :

loadjava -user scott/tiger@dbsmgi -verbose -resolve DemoMain.java

Create a wrapper for the method. Assming that it takes either one or two arguments

CREATE OR REPLACE PACKAGE DemoMain AS
          Procedure  Main(p1 IN VARCHAR2) IS
          Language Java
          Name 'DemoMain.main(java.lang.String[])';
         
          Procedure  Main(p1 IN VARCHAR2, p2 IN VARCHAR2) IS
          Language Java
          Name 'DemoMain.main(java.lang.String[])';
END DemoMain;
SQL>  CALL DemoMain.Main(‘One’, ‘Two’);

Using the IN OUT or OUT parameters

If a parameter is IN OUT or OUT, then it needs to be passed by reference rather than by value. To achieve this, the Java parameter should be passed as an array.

Example:
import oracle.sql.*;
import java.sql.*;
class JavaModes
{
          public static void numP(int p1, int[] p2, int[] p3) {
          p3[0] = p2[0] * 2; -- Assign p3= p2 * 2
          p2[0] = p2[0] + p1; -- Assign p2=p2 + p1
          }
         
          public static void charP(String p1, String[] p2, String[] p3) {
          p2[0] += p1; -- Concatenate p1 on end of p2
          p3[0] = "Hello, I am a Java String";
          }
         
          public static void dateP(DATE p1, DATE[] p2, DATE[] p3) throws SQLException{
          p2[0] = p2[0].addMonths(6); -- Add 6 months to p2
          p3[0] = p1.addMonths(-6); -- Set p3 to p1 -6 months
          }
}

The corresponding PL/SQL wrapper is

CREATE OR REPLACE Package JavaModes AS
      Procedure NumP(p1 IN Number, p2 IN OUT Number, p3 OUT Number) IS
      Language Java
      Name 'JavaModes.numP(int, int[], int[])';
         
      Procedure CharP(p1 IN VARCHAR2, p2 IN OUT VARCHAR2, p3 OUT VARCHAR2) IS
      Language Java
      Name 'JavaModes.charP(java.lang.String, java.lang.String[], java.lang.String[])';
         
      Procedure DateP(p1 IN DATE, p2 IN OUT DATE, p3 OUT DATE) IS
      Language Java
      Name 'JavaModes.dateP(oracle.sql.DATE, oracle.sql.DATE[], oracle.sql.DATE[])';
END JavaModes;

Generate XML Data for generic SQL Query

Using below query I've develope a package, which gives XML data if you input you query and tags.

SELECT xmlelement("BusinessGroup1",xmlagg(XMLELEMENT("BusinessGroup2", XMLELEMENT("name", e.full_name),XMLELEMENT ( "Number", e.employee_number)))).getclobval()
FROM per_all_people_f e where trunc(sysdate) between effective_start_date and effective_end_date and rownum <10

Package:

Specification:
create or replace PACKAGE CPAY_GENERATE_XML_FILE
AS

FUNCTION GENERATE_XML_QUERY(
    p_tag       IN VARCHAR2,
    p_query     IN VARCHAR2,
    p_condition IN VARCHAR2)
  RETURN CLOB;

FUNCTION tokens(
    chr_string IN VARCHAR2,
    token      IN VARCHAR2)
  RETURN NUMBER;

FUNCTION tokenize(
    chr_string IN VARCHAR2,
    token      IN VARCHAR2,
    pos        IN NUMBER)
  RETURN VARCHAR2;

END CPAY_GENERATE_XML_FILE;

Package Body:

create or replace
PACKAGE BODY CPAY_GENERATE_XML_FILE
AS
FUNCTION GENERATE_XML_QUERY(
    p_tag       IN VARCHAR2,
    p_query     IN VARCHAR2,
    p_condition IN VARCHAR2)
  RETURN CLOB
AS
  no_tokens        INT;
  no_inside_tokens NUMBER;
  i                INT;
  v_string         VARCHAR2(150);
  v_column         VARCHAR2(150);
  v_tag            VARCHAR2(150);
  v_xml_query      VARCHAR2(4000) := 'SELECT xmlagg(XMLELEMENT("'||p_tag||'"';
  no_tags          NUMBER         :=0;
  v_braces         VARCHAR2(55)   := NULL;
  j                INT;
type r_cursor
IS
  REF
  CURSOR;
    v_xml_cursor r_cursor;
    v_count NUMBER :=0;
    v_xml CLOB;
  BEGIN
    no_tags       := TOKENS( CHR_STRING => p_tag, TOKEN => ' ' );
    IF (no_tags   <> 0) THEN
      v_xml_query := 'select';
      FOR j       IN 1..no_tags
      LOOP
        v_xml_query := v_xml_query||' xmlelement ("'||trim(tokenize(p_tag,' ',j))||'",';
        v_braces    := v_braces || ')';
      END LOOP;
      v_xml_query := v_xml_query||' xmlagg(XMLELEMENT("'||trim(SUBSTR(p_tag,instr(p_tag,' ',-1,1)+1,LENGTH(p_tag)))||'"';
      dbms_output.put_line ('v_xml_query: '||v_xml_query);
    END IF;
    no_tokens := TOKENS( CHR_STRING => p_query, TOKEN => ',' );
    dbms_output.put_line('no_of_tokens:'||no_tokens);
    FOR i IN 1..no_tokens
    LOOP
      v_string :=trim(tokenize(p_query,',',i));
      DBMS_OUTPUT.PUT_LINE('v_string = ' ||v_string);
      v_column := SUBSTR(v_string,0,(instr(v_string,' ',1,1)));
      v_tag    := SUBSTR(v_string,(instr(v_string,' ',1,1)));
      DBMS_OUTPUT.PUT_LINE('v_column = ' ||v_column);
      DBMS_OUTPUT.PUT_LINE('v_tag = ' ||v_tag);
      IF (v_string  IS NOT NULL) THEN
        v_xml_query := v_xml_query|| ', XMLELEMENT('||v_tag||', '||v_column||')';
      END IF;
      v_string := NULL;
      v_column :=NULL;
      v_tag    := NULL;
    END LOOP;
    v_string := trim(SUBSTR(p_query,instr(p_query,',',-1,1)+1,LENGTH(p_query)));
    DBMS_OUTPUT.PUT_LINE('v_string = ' ||v_string);
    v_column := SUBSTR(v_string,0,(instr(v_string,' ',1,1)));
    v_tag    := SUBSTR(v_string,(instr(v_string,' ',1,1)));
    DBMS_OUTPUT.PUT_LINE('v_column = ' ||v_column);
    DBMS_OUTPUT.PUT_LINE('v_tag = ' ||v_tag);
    IF (no_tags   <> 0) THEN
      v_xml_query := v_xml_query|| ', XMLELEMENT('||v_tag||', '||v_column||')))'||v_braces||'.getclobval() '||p_condition;
    ELSE
      v_xml_query := v_xml_query|| ', XMLELEMENT('||v_tag||', '||v_column||'))).getclobval() '||p_condition;
    END IF;
    IF (p_query   IS NULL) THEN
      v_xml_query := 'SELECT xmlagg(XMLELEMENT("'||p_tag||'")) from dual';
    END IF;
    IF (v_xml_query IS NOT NULL) THEN
      EXECUTE immediate (v_xml_query) INTO v_xml;
      RETURN v_xml;
    END IF;
  EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
    dbms_output.put_line(sqlerrm);
  END GENERATE_XML_QUERY;
FUNCTION tokenize(
    chr_string IN VARCHAR2,
    token      IN VARCHAR2,
    pos        IN NUMBER)
  RETURN VARCHAR2
AS
  v_out   VARCHAR2(100);
  chk_pos NUMBER:=1;
BEGIN
  WHILE instr(chr_string,token,1,chk_pos) > 0
  LOOP
    chk_pos := chk_pos + 1;
  END LOOP;
  IF pos <= chk_pos-1 AND pos > 0 THEN
    --chk_pos := chk_pos - 1;
    IF pos   = 1 THEN
      v_out := SUBSTR(chr_string,instr(chr_string,token,0,1)+1,(instr(chr_string,token,1,1) - instr(chr_string,token,0,1))-1);
    ELSE
      v_out := SUBSTR(chr_string,instr(chr_string,token,1,pos-1)+1,(instr(chr_string,token,1,pos) - instr(chr_string,token,1,pos-1))-1);
    END IF;
  ELSE
    v_out := SUBSTR(chr_string,instr(chr_string,token,-1,1)+1,LENGTH(chr_string));
    --v_out := to_char(length(chr_string));
  END IF;
  IF pos   > chk_pos+1 OR pos <= 0 THEN
    v_out := NULL;
  END IF;
  RETURN v_out;
END tokenize;
FUNCTION tokens(
    chr_string IN VARCHAR2,
    token      IN VARCHAR2)
  RETURN NUMBER
AS
  v_out   VARCHAR2(100);
  chk_pos NUMBER :=1;
  tkn     NUMBER := 0;
BEGIN
  WHILE instr(chr_string,token,1,chk_pos) > 0
  LOOP
    chk_pos := chk_pos + 1;
    tkn     := tkn     + 1 ;
  END LOOP;
  RETURN tkn;
END TOKENS;
END CPAY_GENERATE_XML_FILE;

Example of Procedure call to generate XML:

DECLARE
  P_TAG VARCHAR2(2000);
  P_QUERY VARCHAR2(4000);
  P_CONDITION VARCHAR2(200);
  v_Return clob;
BEGIN
v_Return := '<ORGUSER>';
   P_TAG := 'ORGID';
  P_QUERY := 'ORGANIZATION_ID "organizationid", name "orgname"';
  P_CONDITION := 'from hr_all_organization_units where rownum=1';

  v_Return := v_Return||CPAY_GENERATE_XML_FILE.GENERATE_XML_QUERY(
    P_TAG => P_TAG,
    P_QUERY => P_QUERY,
    P_CONDITION => P_CONDITION
  );
  P_TAG := 'USER1 USER2';
  P_QUERY := 'user_name "uname", user_id "uid"';
  P_CONDITION := 'from fnd_user where rownum<=10';

  v_Return := v_Return||CPAY_GENERATE_XML_FILE.GENERATE_XML_QUERY(
    P_TAG => P_TAG,
    P_QUERY => P_QUERY,
    P_CONDITION => P_CONDITION
  );
 

  v_Return := v_Return||'</ORGUSER>';
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
 

Get Column Names from SQL Query

DECLARE
   TYPE ref_cursor IS REF CURSOR;
   rc_         ref_cursor; 
   c_          NUMBER;
   i_          NUMBER;
   col_count_  NUMBER;
   desc_tab_   DBMS_SQL.DESC_TAB;
BEGIN
   OPEN rc_ FOR 'SELECT user_id uid1,user_name FROM fnd_user';
   c_ := DBMS_SQL.to_cursor_number(rc_);

   DBMS_SQL.DESCRIBE_COLUMNS(c_, col_count_, desc_tab_);

   FOR i_ IN 1..col_count_ LOOP
      DBMS_OUTPUT.PUT_LINE(desc_tab_(i_).col_name);
   END LOOP;

   DBMS_SQL.CLOSE_CURSOR(c_);
END;

How to open Excel Spread Sheets from Concurrent Request

Add highlighted line using System Administrator > Install > Viewer Options


Add the following for each listed user.

User Hooks in Oracle HRMS


API User Hooks refers to points in the API execution to which customers can
attach or extend business logic. User-defined code can be “hooked” into these
predefined locations, and executed in conjunction with the API call.

User code can: extend validation on Oracle HRMS tables, maintain data in
external customer-specific tables (not part of Oracle Applications)
simultaneously with the Oracle HRMS updates through the API, or send alerts at
particular Oracle HRMS events.

The explicit limitation for customer extensions remains—any and all HRMS
data manipulation must be performed by HRMS APIs.

User Hooks offer similar functionality to the Custom Library, but at the database
rather than in the Forms front end.

User Hooks allow logic extensions without changing delivered API code. Hooks
are regenerated at upgrade. However, since schema changes may occur at any
time, all custom procedures should be tested after upgrade. Additionally, API
Hooks are available to any interface that calls the API, such as: Forms, Self-
Service, and Data Pump.

Not all HRMS Forms use APIs; a trace can identify whether a Form uses APIs.
Only Forms that call APIs will execute a User Hooked code. If the Form does
not use APIs, the same code extension can be implemented in Custom Library.
The HR_API_HOOKS table holds all available API hooks for APIs in
HR_API_MODULES table.

Implementing User Hooks

Four steps are required to implement API User Hooks:

1. Determine the API to which you need to hook extra logic

2. Write the PL/SQL procedure that executes your logic.

3. Register your procedure with one or more specific user hooks.

4. Run the pre-processor program that hooks your PL/SQL procedure to the
hook(s).

Oracle HRMS has several types of hooks. Only Business Process Hooks
correspond to Business Process APIs. They are used when logic must extend
beyond the standard business process. Only Business Process Hooks are
Supported for Customer use. A list can be obtained by running the following
query:

select module_name
from hr_api_modules
where api_module_type='BP';

The $PER_TOP/admin/sql/hrahkpar.sql script provides listing of each API, its
hooks, and corresponding procedure parameters.Sample Hook Implementation
Get Hook Information
SELECT      ahk.api_hook_id,
ahk.api_module_id,
ahk.hook_package,
                    ahk.hook_procedure
  FROM       hr_api_hooks ahk, hr_api_modules ahm
 WHERE      ahm.module_name = 'CREATE_PERSON_ABSENCE'   -- Module Name
                    AND ahm.api_module_type = 'BP'
AND ahk.api_hook_type = 'AP'
                    AND ahk.api_module_id = ahm.api_module_id


Hook Registration
DECLARE
l_api_hook_call_id      NUMBER;
l_object_version_number NUMBER;
l_sequence              NUMBER;
BEGIN
SELECT HR_API_HOOKS_S.NEXTVAL
  INTO   l_sequence
 FROM   dual;
 Hr_Api_Hook_Call_Api.create_api_hook_call
 (p_validate                 => FALSE,
  p_effective_date           => TO_DATE('01-JAN-1952','DD-MON-YYYY'),
  p_api_hook_id              => <HOOK ID>
  p_api_hook_call_type       => 'PP',
  p_sequence                 => l_sequence,
  p_enabled_flag             => 'Y',
  p_call_package             => 'LMHR_HOOK_VALIDATIONS_PKG',    -- Custom Package
 p_call_procedure           => UPPER('Validate_Objectives_Weight'),  -- Custom procedure
 p_api_hook_call_id         => l_api_hook_call_id,
 p_object_version_number    => l_object_version_number);
 
  DBMS_OUTPUT.PUT_LINE('l_api_hook_call_id '|| l_api_hook_call_id);
END ;

Run the pre-processor script
        Run pre-processor script hrahkone.sql with module name as parameter (PER_TOP/admin/sql/hrahkone.sql).It compile API hook. After running this your APIhook Package should have custom procedure call.

Delete custom code from APIuser Hook

      Get Hook Call ID and Object Version_number

Select  api_hook_call_id,object_version_number from HR_API_HOOK_CALLS where call_package like ‘LMHR%’ and call_procedure = UPPER('Validate_Objectives_Weight')

Hook Deletion
BEGIN
 Hr_Api_Hook_Call_Api.delete_api_hook_call  (     p_validate         => FALSE,
                                                                                p_api_hook_call_id  => <Hook Call ID>,
                                                                                p_object_version_number =>2
                                                                           );
  DBMS_OUTPUT.PUT_LINE('deleted Successfully');
END;