Get data from checkpoints OPA Interviews

In Oracle Policy Automation (see also the other blogs) you can save temporary information using the SetCheckpoint operations. This sends you a base64 encoded blob with the temporary information from your interview, but no structured data.
However, this base64 encoded blob is simply a zipfile, so we can extract it, find the file we need with our data and use that data during our checkpoints if necessary.

In our use case we would like to see the structured data before the submit, because interviews could run longer over time (people need to gather information, request help, etc). So an interview could be open for a number of days before the submit takes place.

The first thing we need to do is decode the checkpoint

l_blob := XXX_OPA_UTIL_PKG.base64decode (checkPointData.Encoded_Checkpoint);

This gives us the zipfile as a blob. The file we are looking for is UserData.xml, so let's extract that from the zip using the AS_ZIP utility created by Anton Scheffer and which you can find at the site of Amis
https://technology.amis.nl/wp-content/uploads/2010/06/as_zip7.txt

l_xml_blob := AS_ZIP.Get_File 
  (
    p_zipped_blob   => l_blob 
  , p_file_name     => ' UserData.xml' 
  );

Now we have our file, but we need to convert it to XML first.

l_xml_data := Convert_Blob_To_Xml (l_xml_blob);

And then we run through the XML to find our attributes. You can refer to a specific attr_id (like title, username, etc) to find the value and use that value in your process. In our case it was the title of a request that we wanted to store so we can see from all our running requests which one is which! Otherwise you would only have some kind of internal ID maybe or any data you use to start the interview. In our case we started the interview and all data came from the interview, so we started with nothing more than an ID.

 <<Attributes>>
  FOR x IN C_XML_Data (l_xml_data)
  LOOP 
    dbms_output.put_line (x.attr_id || ': ' || x.attr_val);
  END LOOP Attributes;

The code to convert the blob to XML was written like this. The reason for replacing the boolean and number values is because we want to treat everything as a string (varchar) later on and that makes our XML query easier, but of course you can do that differently.

  ---------------------------------------------------------------------------------------
  -- Subfunction to convert the blob we extract to XML.
  ---------------------------------------------------------------------------------------
  FUNCTION Convert_Blob_To_Xml 
  (
    p_blob BLOB
  ) RETURN XmlType
  IS 
    v_clob    CLOB;
v_varchar VARCHAR2(32767);
v_start   PLS_INTEGER := 1;
v_buffer  PLS_INTEGER := 32767;
x         xmltype;
  BEGIN
  
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
  
FOR i IN 1 .. CEIL(DBMS_LOB.GETLENGTH(p_blob) / v_buffer) LOOP
  
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_blob,
                                                         v_buffer,
                                                         v_start));

DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);

v_start := v_start + v_buffer;
END LOOP;

v_clob := replace (v_clob,'boolean-val','text-val');
      v_clob := replace (v_clob,'number-val','text-val');
v_clob := replace (v_clob,'date-val','text-val');


x := xmltype.createxml(v_clob);
Return (x);
  
  END Convert_Blob_To_Xml;


The XML Query is build as follows

 CURSOR C_XML_Data 
  (
    p_xml_data XMLType
  )
  IS
     WITH XmlTypeAsTab as (select 
p_xml_data 
 xml from dual)  
    SELECT attribs.attr_id 
,      attribs.attr_val
    FROM   XmlTypeAsTab x 
    , xmltable ('/user-data/global-inst/attr' passing x.xml 
columns attr_id  varchar2(4000) path './@id'
,
        attr_val varchar2(4000) path './text-val'
) attribs
    ;

eBS R12 Upload BLOB/URL as Attachment

I recently had a request on how to upload attachments in eBS from a file. So I dug into my old code and found this logic I once built for storing attachments (URL and files) you upload through an Apex interface as an eBS attachment. We linked it to a party id (stored in field :p300_party_id in Apex) and used a specific category.


Definition of entities
Login as Application Developer and navigate to Attachments. In our case we want to link the attachment directly to the party and we were also using Customers Online. So we used the IMC_ATTACHMENTS entity and the party_id as primary key. We used hard coded category Miscellaneous, but of course you can also choose the category.

Note that we did not have to show the attachments in a form in eBS again (just in Apex), but we wanted it to be available later in Contact Center for example. In that case we should have chosen the entity Parties instead (which also uses the PARTY_ID as unique id).

Create attachment
Now this is the code to create the attachment. For an URL you don't need the actual blob, but for a file you do. We use API's to create the attachment except for the actual BLOB in FND_LOBS. Apparently there is no public API for this (

How to Load Attachments Via API into Service Requests? (Doc ID 394811.1))

declare
l_rowid rowid;
l_attached_document_id number;
l_document_id number;
l_media_id number;
l_category_id number := 1; -- MISC
l_pk1_value fnd_attached_documents.pk1_value%TYPE := :p300_party_id; -- Unique id of entity it is attached to
l_description fnd_documents_tl.description%TYPE := :p300_description;
l_filename fnd_documents_tl.file_name%TYPE;
l_seq_num number;
l_mime_type varchar2(240);
l_datatype_id number;

 -- This was specific for blobs we uploaded in APEX. Based on the name of the file
 -- we created an attachment in eBS. Of course you can also fetch your blobs
 -- from somewhere else like a file system or the database.
 cursor c_blob
 is
select blob_content
, filename
, mime_type
from wwv_flow_file_objects$
where name = :p300_file_name
;

l_blob c_blob%rowtype;

BEGIN

  -- In Apex we allowed for adding URLs and actual file upload. So depending
  -- on the entry (URL or not) we created different data types.

IF :p300_url IS NOT NULL
THEN
  l_datatype_id := 5; -- Web page
  l_media_id := null;
ELSE
  l_datatype_id := 6;
  OPEN c_blob;
  FETCH c_blob INTO l_blob;
  CLOSE c_blob;

  l_filename := l_blob.filename;

  select fnd_lobs_s.nextval into l_media_id from dual;

-- Determine mime type
  l_mime_type := l_blob.mime_type;
  
END IF;

If l_datatype_id IN ( 5,6)
THEN

select FND_DOCUMENTS_S.nextval
into   l_document_id
from   dual;

select FND_ATTACHED_DOCUMENTS_S.nextval
into   l_attached_document_id
from   dual;

select nvl(max(seq_num),0) + 10
into   l_seq_num
from   fnd_attached_documents
where  pk1_value = l_pk1_value  -- Your unique ID, we used PARTY_ID
and  entity_name = 'IMC_ATTACHMENTS'; -- This depends on your setup of course

fnd_documents_pkg.insert_row
( X_ROWID                        => l_rowid
, X_DOCUMENT_ID                  => l_document_id
, X_CREATION_DATE                => sysdate
, X_CREATED_BY                   => fnd_global.user_id
, X_LAST_UPDATE_DATE             => sysdate
, X_LAST_UPDATED_BY              => fnd_global.user_id
, X_LAST_UPDATE_LOGIN            => 0
, X_DATATYPE_ID                  => l_datatype_id --5 -- Web Page
, X_CATEGORY_ID                  => l_category_id
, X_SECURITY_TYPE                => 2
, X_PUBLISH_FLAG                 => 'Y'
, X_USAGE_TYPE                   => 'O'
, X_LANGUAGE                     => 'US'
, X_DESCRIPTION                  => l_description 
, X_FILE_NAME                    => l_filename
, X_MEDIA_ID                     => l_media_id
-- R12
, X_URL                          => :p300_URL
);

fnd_documents_pkg.insert_tl_row
( X_DOCUMENT_ID                  => l_document_id
, X_CREATION_DATE                => sysdate
, X_CREATED_BY                   => fnd_global.user_id
, X_LAST_UPDATE_DATE             => sysdate
, X_LAST_UPDATED_BY              => fnd_global.user_id
, X_LAST_UPDATE_LOGIN            => fnd_global.login_id
, X_LANGUAGE                     => 'US'
, X_DESCRIPTION                  => l_description
-- Removed in R12
--, X_FILE_NAME                    => l_filename
--, X_MEDIA_ID                     => l_media_id
, X_TITLE                       => l_filename
);

--dbms_output.put_line (''+Document id :  ''|| l_attached_document_id);

fnd_attached_documents_pkg.insert_row
( X_ROWID    => l_rowid
, X_ATTACHED_DOCUMENT_ID         => l_attached_document_id
, X_DOCUMENT_ID                  => l_document_id
, X_CREATION_DATE                => sysdate
, X_CREATED_BY                   => fnd_global.user_id
, X_LAST_UPDATE_DATE             => sysdate
, X_LAST_UPDATED_BY              => fnd_global.user_id
, X_LAST_UPDATE_LOGIN            => fnd_global.login_id
, X_SEQ_NUM                      => l_seq_num
, X_ENTITY_NAME                  => 'IMC_ATTACHMENTS'
, X_COLUMN1                      => null
, X_PK1_VALUE                    => l_pk1_value
, X_PK2_VALUE                    => null
, X_PK3_VALUE                    => null
, X_PK4_VALUE                    => null
, X_PK5_VALUE                    => null
, X_AUTOMATICALLY_ADDED_FLAG     => 'N'
, X_DATATYPE_ID                  => l_datatype_id
, X_CATEGORY_ID                  => l_category_id
, X_SECURITY_TYPE                => 2
, X_PUBLISH_FLAG                 => 'Y'
, X_LANGUAGE                     => 'US'
, X_DESCRIPTION                  => l_description
, X_FILE_NAME                    => l_filename
, X_MEDIA_ID                     => l_media_id
-- R12
, X_URL                        => :p300_URL
, X_TITLE                        => l_description
);


IF l_media_id IS NOT NULL
THEN
INSERT INTO FND_LOBS
(
  File_Id
, File_Name
, File_Content_Type
, File_Data
, Upload_Date
, Expiration_Date
, Program_Name
, Program_Tag
, Language
, Oracle_Charset
, File_Format
)
VALUES
(
  l_media_id
, l_filename
, l_mime_type
, l_blob.blob_content
, sysdate
, null
, null
, null
, 'US'
, 'UTF8'
, 'binary'
);

END IF; -- URL has no file'

commit;

END IF;

END;


Logic to fetch a file as BLOB from the file system
In our case the files were loaded into Apex wwv_flow_file_objects, but in some cases if you want to apply the logic from a database, you may need to fetch the files from the file system.
Note that in that case usually your code runs on the database server and your files are most likely on the application server.
What we usually do is either use a shared directory or use a host script to copy the file from the application server to $APPLPTMP (which is usually shared) and then call your SQL Package / Script from the host script.

To load the file from an accessible file location use code like this. Note that YOUR_DIRECTORY is a directory in DBA_DIRECTORIES which refers to an actual file location. You create directories using the create directory statement.

DECLARE

  l_bfile   BFILE;
  l_blob    BLOB;
  dir_name  VARCHAR2(240) := 'YOUR_DIRECTORY';
  dir_path  VARCHAR2(240) := '&1';
  file_name VARCHAR2(240) := '&2';

BEGIN

  l_bfile := BFILENAME(dir_name, file_name);

IF (dbms_lob.fileexists(l_bfile) = 1) THEN
      dbms_output.put_line('File Exists');
      
      dbms_output.put_line ('Size: ' || dbms_lob.getlength(l_bfile));
      
      dbms_lob.createtemporary (l_blob,true);
        
      dbms_lob.fileopen( l_bfile, dbms_lob.FILE_READONLY );
      dbms_output.put_line ('File is open for reading ...');
      
      dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength(l_bfile),1,1 );
      dbms_output.put_line ('Load from file ...');
       
       
      dbms_lob.fileclose( l_bfile );
      dbms_output.put_line ('Close file ...');

END IF; 

-- So now you have your file in l_blob and you can insert it into a database for example or create your attachment with the logic above.

END;

Fetch profile options

To find profile options set on different levels you can use the following query. You can filter on level (application, responsibility, user, etc) if necessary.

SELECT AP.Application_Short_Name

,      OP.PROFILE_OPTION_NAME
,      OP_T.USER_PROFILE_OPTION_NAME
,      DECODE (OV.Level_Id,10001,'Site', 10002,'Applicatie',10003, 'Autorisatie', 10004,'Gebruiker') level_Id
,      DECODE (OV.Level_Id,10002,

(SELECT A.Application_Short_Name FROM FND_APPLICATION A WHERE A.Application_Id = OV.Level_Value)
                          ,10003,

(SELECT R.Responsibility_Name FROM FND_RESPONSIBILITY_TL R, FND_RESPONSIBILITY RR WHERE R.Responsibility_Id = OV.Level_Value   AND OV.Level_Value_Application_Id = R.Application_Id AND R.Language = USERENV ('LANG')

AND R.Responsibility_Id = RR.Responsibility_Id AND R.Application_Id = RR.Application_Id  )
                          ,10004,

(SELECT U.User_Name FROM FND_USER U WHERE U.User_Id = OV.Level_Value)
                          , OV.Level_Value) Level_Value
,      OV.Level_Value
,      OV.Profile_Option_Value
FROM FND_PROFILE_OPTION_VALUES    OV
,    FND_PROFILE_OPTIONS          OP
,    FND_PROFILE_OPTIONS_TL       OP_T
,    FND_APPLICATION              AP
WHERE OV.Profile_Option_id          = OP.Profile_Option_id
AND   OP.Profile_Option_Name        = OP_T.PROFILE_OPTION_NAME
AND   OV.Application_Id             = AP.Application_Id
;

Find eBS table based on value in column

Sometimes I know the value that is used somewhere in a screen, but I have no idea what tables are behind it. So I wrote a dynamic sql script to run through a number of tables to find the table and column that holds the value.

Run it using the value you are searching for and a restriction on the tables.
For example

@FINDVALUE 3004 AR

To find all AR tables that have any column with a value 3004. Currently I only look for CHARs and NUMBERs, but of course you can expand if desired.


SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF

DECLARE
  p_table_name   VARCHAR2(240) := '&&2';
  p_value        VARCHAR2(240) := '&&1';
  v_statement    VARCHAR2(8000);
  v_base_statement VARCHAR2(8000);
  v_found        VARCHAR2(240);
  TheResult      NUMBER;
 
  CURSOR C_Tables
  IS
  SELECT Table_Name
  FROM   ALL_TABLES
  WHERE  Table_Name like p_table_name || '%';
 
  CURSOR C_Table_Cols
  (
    cp_table_name VARCHAR2
  )
  IS
  SELECT Column_Name, Table_Name, Data_Type
  FROM   ALL_TAB_COLUMNS
  WHERE Table_Name = cp_table_name
  AND   Data_Type IN ('NUMBER', 'VARCHAR2')
  ;
 
 
BEGIN

  DBMS_OUTPUT.Put_Line ('List of tables with value '|| p_value);
  DBMS_OUTPUT.Put_Line ('---------------------------------------------------------------------');

  <<Table_Def>>
  FOR T IN C_Tables
  LOOP

      v_base_statement := 'SELECT 1 FROM ' || T.Table_Name || ' WHERE ';
     
      <<Table_Cols>>
      FOR C IN C_Table_Cols (cp_table_name => T.Table_Name)
      LOOP
        IF C.Data_Type = 'NUMBER'
        THEN
          v_statement := v_base_statement || C.Column_Name || ' = ' || p_value;
        ELSIF C.Data_Type = 'VARCHAR2'
        THEN
          v_statement := v_base_statement || C.Column_Name || ' = ''' || p_value || '''';
        END IF;
        TheResult := 0;
        BEGIN
          --dbms_output.put_line ('Testing ... ' || v_statement);
          execute immediate (v_statement) INTO TheResult;
          --dbms_output.put_line ('... '||  v_statement || ' returns TRUE');
          --dbms_output.put_line ('Result: ' || TheResult);
          EXCEPTION
            WHEN Others THEN
              --dbms_output.put_line ('Error: ' || SQLERRM);
              TheResult := 0;
        END;
       
        IF TheResult = 1
        THEN
          v_found := T.Table_Name || '.' || C.Column_Name || ' (' || C.Data_Type || ')';
          DBMS_OUTPUT.Put_Line (v_found);
        END IF;
      END LOOP Table_Cols;
     
  END LOOP Table_Def;
 
END;
/

Another sample for the same issue is as follows from Saubhik

SELECT   DISTINCT
                 SUBSTR(:val, 1, 11) "Searchword",
                  SUBSTR(table_name, 1, 14) "Table",
                 SUBSTR(t.COLUMN_VALUE.getstringval(), 1, 50) "Column/Value"
      FROM     cols,
                   table(XMLSEQUENCE(DBMS_XMLGEN.getxmltype(   'select '
                                                           || column_name
                                                           || ' from '
                                                           || table_name
                                                        || ' where (UPPER('''
                                                         || :val
                                                         || ''')=UPPER('
                                                        || '))').EXTRACT('ROWSET/ROW/*'))) t
    WHERE    table_name IN ('EMP', 'DEPT','EMPLOYEES') --limiting the table names, you can omit this.
     ORDER BY "Table"

  /

Create table using dynamic sql

Create table using dynamic sql

When installing new tables for an eBS customization we have to install/re-install tables usually multiple times. Especially for re-usable products we like to install at multiple customers and sites you don't want all kinds of error messages in your installation script that the table already exists and you also do not want to create a CREATE and an UPDATE script for the same table. You simply want to be able to re-run the script without error messages and that keeps your tables up to date.

This dynamic sql script allows you to create the table and re-run it multiple times. If the columns already exist, it will not do anything and otherwise it adds the columns.

Table creation script

The initial script is for example something like this for a simple table with two columns. It assumes you create it under a custom user and grant it to APPS.

    CONNECT &&3/&&4
   
    SET SERVEROUTPUT ON SIZE 1000000
    SET LINESIZE 240
    SET VERIFY OFF
    SET FEEDBACK OFF
   


    DECLARE

    /****************************************************************************
    --  HISTORY
    --  ============ === ================ ========================================
    -- 11 Jul 2015  1.0  P Koertshuis     Created
    ****************************************************************************/

      v_statement         VARCHAR2(2000);
      v_exists            VARCHAR2(10);
      v_tab_name          VARCHAR2(80) := 'XXX_MY_TABLE';
      v_version           VARCHAR2(80) := '15.7.1 - 11-Jul-2015';
      v_error             VARCHAR2(240);
      v_up_to_date        VARCHAR2(240) := '... Table definition is up to date.';
        v_release           VARCHAR2(80) := '15.7.1';
      v_release_date      VARCHAR2(80) := '11-Jul-2015';
      v_main_release      VARCHAR2(80) := '15.7.1';
      v_release_desc      VARCHAR2(240) := 'Initial creation';
      v_null              VARCHAR2(1);
      v_info              VARCHAR2(240);



      CURSOR C_Columns
      IS
      SELECT 1 Colnr,'ITEM_NUMBER' Column_Name,'ITEM_NUMBER VARCHAR2(100) NOT NULL' Column_Full  FROM DUAL
UNION SELECT 2 Colnr,'ITEM_DESCRIPTION' Column_Name,'ITEM_DESCRIPTION VARCHAR2(100)' Column_Full  FROM DUAL
      ORDER BY 1
      ;
     
      CURSOR C_Tab
      IS
      SELECT Column_Name
      FROM   ALL_TAB_COLUMNS
      WHERE  Table_Name = v_tab_name
      AND    Owner = upper ('&&3')
      ;
     
    BEGIN

      ---------------------------------------------------------------------------------------------
      -- Check whether table exists.
      ---------------------------------------------------------------------------------------------

      SELECT ''
      INTO   v_exists
      FROM   ALL_TABLES
      WHERE  Table_Name = v_tab_name
      AND    Owner = upper ('&&3')
      ;
     
      DBMS_OUTPUT.Put_Line ('Table ' || v_tab_name || ' exists. Updating table definition to ' || v_version);
     
      ---------------------------------------------------------------------------------------------
      -- If exists, update table statement.
      ---------------------------------------------------------------------------------------------
     
      FOR C IN C_Columns
      LOOP
      BEGIN
     
        SELECT ''
        INTO   v_exists
        FROM   ALL_TAB_COLUMNS
        WHERE  Table_Name = v_tab_name
        AND    Upper (Column_Name) = Upper (C.Column_Name)
        AND    Owner = upper ('&&3')
        ;
       
        EXCEPTION
          WHEN No_Data_Found
          THEN
          BEGIN
            v_statement := 'ALTER TABLE ' || v_tab_name || ' ADD ' || C.Column_Full;
            DBMS_OUTPUT.Put_Line ('... Adding column ' || C.Column_Name);
            v_up_to_date := NULL;
           
            execute immediate (v_statement);
           
            EXCEPTION
            WHEN Others THEN
              v_error := SQLERRM;
              DBMS_OUTPUT.Put_Line ('Error on updating table ' || v_tab_name || ' with column ' || C.Column_Name || ': ' || v_error);
          END;
          WHEN Others THEN
            v_error := SQLERRM;
            DBMS_OUTPUT.Put_Line ('Error on finding column ' || C.Column_Name);
     
      END;
     
      END LOOP;
     
      ---------------------------------------------------------------------------------------------
      -- Any additional updates
      ---------------------------------------------------------------------------------------------
     
      /*
      v_statement := 'UPDATE ' || v_tab_name ||
      ' SET MULTI_ORG_FLAG = ''N'' WHERE Org_Id IS NOT NULL AND NVL (MULTI_ORG_FLAG,''Y'') <> ''N'' ';
     
      execute immediate (v_statement);
     
      v_statement := 'UPDATE ' || v_tab_name ||
      ' SET MULTI_ORG_FLAG = ''Y'' WHERE Org_Id IS NULL AND NVL (MULTI_ORG_FLAG,''N'') <> ''Y'' ';
     
      execute immediate (v_statement); 
      */
     

      ---------------------------------------------------------------------------------------------
      -- Check whether you are installing an older version ..
      ---------------------------------------------------------------------------------------------
   
      FOR T IN C_Tab
      LOOP
        IF T.Column_Name NOT IN
        ('ITEM_NUMBER'
,'ITEM_DESCRIPTION')
        THEN
          DBMS_OUTPUT.Put_Line ('...Column ' || T.Column_Name || ' is defined in a newer version.');
          v_up_to_date := '...Newer version of ' || v_tab_name || ' is already installed. Please uninstall and reinstall to make the module is working as expected.';
        END IF;
      END LOOP;
     
      IF v_up_to_date IS NOT NULL
      THEN
        DBMS_OUTPUT.Put_Line (v_up_to_date);
      END IF;
     
     
      commit;
     
      EXCEPTION
      WHEN No_Data_Found THEN
      BEGIN
     
        ---------------------------------------------------------------------------------------------
        -- Create table.
        ---------------------------------------------------------------------------------------------
        DBMS_OUTPUT.Put_Line ('Creating table ' || v_tab_name || ' version ' || v_version);
       
        v_statement := 'CREATE TABLE ' || v_tab_name || Chr(10) || '(';
       
        v_exists := ' ';
        FOR C IN C_Columns
        LOOP
          v_statement := v_statement || v_exists || C.Column_Full;
          v_exists := ' , ';
        END LOOP;
       
        v_statement := v_statement || ')';
       
        execute immediate (v_statement);
       

     
      commit;
       
       
        EXCEPTION
          WHEN Others tHEN
            v_error := SQLERRM;
            DBMS_OUTPUT.Put_Line ('Error on creating table ' || v_tab_name || ': ' || v_error);
     
      END;
      WHEN Others THEN
        v_error := SQLERRM;
        DBMS_OUTPUT.Put_Line ('Error on finding table ' || v_tab_name || ': ' || v_error);

    END;
    /
    GRANT ALL ON  XXX_MY_TABLE TO &&1;

    PROMPT

Now while running it, it will also prompt you if you have a table in the system with more columns (a newer version exists). The blue parts are the bits you change if you change your table definition.
For example if you add a column the new script could be something like

    CONNECT &&3/&&4
   
    SET SERVEROUTPUT ON SIZE 1000000
    SET LINESIZE 240
    SET VERIFY OFF
    SET FEEDBACK OFF
   


    DECLARE

    /****************************************************************************
    --  HISTORY
    --  ============ === ================ ========================================
    -- 11 Jul 2015  1.0  P Koertshuis     Created
    ****************************************************************************/

      v_statement         VARCHAR2(2000);
      v_exists            VARCHAR2(10);
      v_tab_name          VARCHAR2(80) := 'XXX_MY_TABLE';
      v_version           VARCHAR2(80) := '15.7.1 - 11-Jul-2015';
      v_error             VARCHAR2(240);
      v_up_to_date        VARCHAR2(240) := '... Table definition is up to date.';
        v_release           VARCHAR2(80) := '15.7.1';
      v_release_date      VARCHAR2(80) := '11-Jul-2015';
      v_main_release      VARCHAR2(80) := '15.7.1';
      v_release_desc      VARCHAR2(240) := 'Initial creation';
      v_null              VARCHAR2(1);
      v_info              VARCHAR2(240);



      CURSOR C_Columns
      IS
      SELECT 1 Colnr,'ITEM_NUMBER' Column_Name,'ITEM_NUMBER VARCHAR2(100) NOT NULL' Column_Full  FROM DUAL
UNION SELECT 2 Colnr,'ITEM_DESCRIPTION' Column_Name,'ITEM_DESCRIPTION VARCHAR2(100)' Column_Full  FROM DUAL
UNION SELECT 3 Colnr, 'NEW_COLUMN' Column_Name,'NEW_COLUMN NUMBER NOT NULL' FROM DUAL
      ORDER BY 1
      ;
     
      CURSOR C_Tab
      IS
      SELECT Column_Name
      FROM   ALL_TAB_COLUMNS
      WHERE  Table_Name = v_tab_name
      AND    Owner = upper ('&&3')
      ;
     
    BEGIN

      ---------------------------------------------------------------------------------------------
      -- Check whether table exists.
      ---------------------------------------------------------------------------------------------

      SELECT ''
      INTO   v_exists
      FROM   ALL_TABLES
      WHERE  Table_Name = v_tab_name
      AND    Owner = upper ('&&3')
      ;
     
      DBMS_OUTPUT.Put_Line ('Table ' || v_tab_name || ' exists. Updating table definition to ' || v_version);
     
      ---------------------------------------------------------------------------------------------
      -- If exists, update table statement.
      ---------------------------------------------------------------------------------------------
     
      FOR C IN C_Columns
      LOOP
      BEGIN
     
        SELECT ''
        INTO   v_exists
        FROM   ALL_TAB_COLUMNS
        WHERE  Table_Name = v_tab_name
        AND    Upper (Column_Name) = Upper (C.Column_Name)
        AND    Owner = upper ('&&3')
        ;
       
        EXCEPTION
          WHEN No_Data_Found
          THEN
          BEGIN
            v_statement := 'ALTER TABLE ' || v_tab_name || ' ADD ' || C.Column_Full;
            DBMS_OUTPUT.Put_Line ('... Adding column ' || C.Column_Name);
            v_up_to_date := NULL;
           
            execute immediate (v_statement);
           
            EXCEPTION
            WHEN Others THEN
              v_error := SQLERRM;
              DBMS_OUTPUT.Put_Line ('Error on updating table ' || v_tab_name || ' with column ' || C.Column_Name || ': ' || v_error);
          END;
          WHEN Others THEN
            v_error := SQLERRM;
            DBMS_OUTPUT.Put_Line ('Error on finding column ' || C.Column_Name);
     
      END;
     
      END LOOP;
     
      ---------------------------------------------------------------------------------------------
      -- Any additional updates
      ---------------------------------------------------------------------------------------------
     
      /*
      v_statement := 'UPDATE ' || v_tab_name ||
      ' SET MULTI_ORG_FLAG = ''N'' WHERE Org_Id IS NOT NULL AND NVL (MULTI_ORG_FLAG,''Y'') <> ''N'' ';
     
      execute immediate (v_statement);
     
      v_statement := 'UPDATE ' || v_tab_name ||
      ' SET MULTI_ORG_FLAG = ''Y'' WHERE Org_Id IS NULL AND NVL (MULTI_ORG_FLAG,''N'') <> ''Y'' ';
     
      execute immediate (v_statement); 
      */
     

      ---------------------------------------------------------------------------------------------
      -- Check whether you are installing an older version ..
      ---------------------------------------------------------------------------------------------
   
      FOR T IN C_Tab
      LOOP
        IF T.Column_Name NOT IN
        ('ITEM_NUMBER'
,'ITEM_DESCRIPTION'
'NEW_COLUMN')
        THEN
          DBMS_OUTPUT.Put_Line ('...Column ' || T.Column_Name || ' is defined in a newer version.');
          v_up_to_date := '...Newer version of ' || v_tab_name || ' is already installed. Please uninstall and reinstall to make the module is working as expected.';
        END IF;
      END LOOP;
     
      IF v_up_to_date IS NOT NULL
      THEN
        DBMS_OUTPUT.Put_Line (v_up_to_date);
      END IF;
     
     
      commit;
     
      EXCEPTION
      WHEN No_Data_Found THEN
      BEGIN
     
        ---------------------------------------------------------------------------------------------
        -- Create table.
        ---------------------------------------------------------------------------------------------
        DBMS_OUTPUT.Put_Line ('Creating table ' || v_tab_name || ' version ' || v_version);
       
        v_statement := 'CREATE TABLE ' || v_tab_name || Chr(10) || '(';
       
        v_exists := ' ';
        FOR C IN C_Columns
        LOOP
          v_statement := v_statement || v_exists || C.Column_Full;
          v_exists := ' , ';
        END LOOP;
       
        v_statement := v_statement || ')';
       
        execute immediate (v_statement);
       

     
      commit;
       
       
        EXCEPTION
          WHEN Others tHEN
            v_error := SQLERRM;
            DBMS_OUTPUT.Put_Line ('Error on creating table ' || v_tab_name || ': ' || v_error);
     
      END;
      WHEN Others THEN
        v_error := SQLERRM;
        DBMS_OUTPUT.Put_Line ('Error on finding table ' || v_tab_name || ': ' || v_error);

    END;
    /
    GRANT ALL ON  XXX_MY_TABLE TO &&1;

    PROMPT

You can also create the script after you have created tables in the database already. In that case use the following code to extract the first part (the UNION) and the second part (the list of columns) and copy them into the script.

Extract unions

To extract the first part use this code. Remove the first union before you copy it into the script.

SELECT 'UNION SELECT ' || rownum || ' Colnr,''' || column_name || ''' Column_Name,''' || column_Name || ' ' || data_type || decode (data_type,'DATE',null,'NUMBER', null,'(' || data_length || ')') ||
decode (nullable,'N',' NOT NULL ') || ''' Column_Full FROM DUAL'
FROM  ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'XXX_MY_TABLE'
;

Extract column list

SELECT ',''' || Column_Name || ''''
FROM  ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'XXX_MY_TABLE'
;

Execution 

To run the script simply use

@XXX_MY_TABLE.tbl apps <appspwd> customuser <custompwd>

Read XML into PL/SQL table

Read XML into PL/SQL table

When you get passed an XML type variable and you need to split this into a PL/SQL table (or an actual table) you can use code like this.

First we have a cursor that queries my XML Type.


CURSOR C_XML_lines (cp_xml_lines XMLType)
     IS
     SELECT EXTRACTVALUE(VALUE(P),'/LINE/LINE_NUMBER/text()') AS LINE_NUMBER,
            EXTRACTVALUE(VALUE(P),'/LINE/ITEM_NUMBER/text()') AS ITEM_NUMBER,
            EXTRACTVALUE(VALUE(P),'/LINE/QUANTITY/text()') AS QUANTITY,
            EXTRACTVALUE(VALUE(P),'/LINE/ARTICLE/text()') AS ARTICLE,
            EXTRACTVALUE(VALUE(P),'/LINE/LIST_PRICE/text()') AS LIST_PRICE,
            EXTRACTVALUE(VALUE(P),'/LINE/UNIT_PRICE/text()') AS UNIT_PRICE,
            EXTRACTVALUE(VALUE(P),'/LINE/TOTAL_PRICE/text()') AS TOTAL_PRICE,
            EXTRACTVALUE(VALUE(P),'/LINE/TAX_PERC/text()') AS TAX_PERC,
            EXTRACTVALUE(VALUE(P),'/LINE/TAX_AMOUNT/text()') AS TAX_AMOUNT    FROM   TABLE(XMLSEQUENCE(EXTRACT(cp_xml_lines,'/LINES/LINE'))) P;

Then we look through our lines and put the lines in a PL/SQL table.

l_index :=1;
  l_orderlines_tbl.delete;

  <<lines>>
  FOR r in c_xml_lines (cp_xml_lines => l_xml_lines)
  loop

  l_orderlines_Tbl(l_index).line_number := to_number(r.line_number);
  l_orderlines_Tbl(l_index).item_number := trim (r.item_number);
  l_orderlines_Tbl(l_index).quantity    := to_number(r.quantity);
  l_orderlines_Tbl(l_index).article        := r.article;
  l_orderlines_Tbl(l_index).list_price  := to_number(r.list_price);
  l_orderlines_Tbl(l_index).unit_price  := to_number(r.unit_price);
  l_orderlines_Tbl(l_index).total_price := to_number(r.total_price);
  l_orderlines_Tbl(l_index).tax_perc    := to_number(r.tax_perc);
  l_orderlines_Tbl(l_index).tax_amount  := to_number(r.tax_amount);

  l_index := l_index+1;

END LOOP Lines;

But note ....
TABLE(XMLSequence.... and ExtractValue are deprecated functionality and were replaced in 10g by XMLTABLE functionality).

So our new query would be something like this

with myxmltable as (select xmltype('<?xml version="1.0" encoding="UTF-8"?>
    <order>
      <order_number>1</order_number>
   <cust_number>1001</cust_number>
   <customer_name>My Customer</customer_name>
   <lines>
     <line_number>1</line_number>
  <item_number>A1000</item_number>
  <item_description>Some test item</item_description> 
   </lines>
   <lines>
     <line_number>2</line_number>
  <item_number>B1000</item_number>
  <item_description>Some other test item</item_description> 
   </lines>
 </order>') xml
 from dual)
select  orders.order_number
,  orders.cust_number
,  orders.customer_name
,  lines.line_number
,  lines.item_number
,  lines.item_description
from  myxmltable t
,xmltable('/order'
                   passing t.xml
                  columns order_number    number     path './order_number'
                          ,cust_number     number     path './cust_number'
                          ,customer_name        varchar2(240)    path './customer_name'
                          ,lines      xmltype        path '.'
                  ) orders
,xmltable('/order/lines'
                   passing orders.lines
                  columns line_number    number     path './line_number'
                          ,item_number     varchar2(35)   path './item_number'
                          ,item_description     varchar2(240)    path './item_description'                         
                  ) lines
;

Compound trigger for mutating table

Compound trigger for mutating table

For some this is sliced cookie ;-), but I hadn't encountered mutating table for a long time and was still set on creating a package and three triggers (before update/insert, before update/insert for each row and after update/insert) to collect in a pl/sql table the changes to perform some kind of action ... so something like this (yeah the code is not very useful, it was just for testing purposes)

Old style

create table xxx_compound_test (nr number, action_date date);
insert into xxx_compound_test values (1,null);
insert into xxx_compound_test values (2,null);
insert into xxx_compound_test values (3,null);
create or replace package xxx_compound_test_pkg
as
  type
    r_mail_test is record
    (
      nr        number
    , recipient varchar2(240)
    , message   varchar2(240)
    );
   
  type
    t_mail_test is table of r_mail_test index by binary_integer;
   
  g_mail_test t_mail_test;
  g_nr_of_values number;
  procedure init;
  procedure add_value (added_value r_mail_test);
end;
/
create or replace package body xxx_compound_test_pkg
as

  procedure init
  is
  begin
    g_mail_test.delete;
    g_nr_of_values := 0;
  end;
 
  procedure add_value (added_value r_mail_test)
  is
  begin
    g_nr_of_values := g_nr_of_values + 1;
    dbms_output.put_line ('Adding value ' || g_nr_of_values || ' for nr ' || added_value.nr);
    g_mail_test (g_nr_of_values).nr := added_value.nr;
    g_mail_test (g_nr_of_values).recipient := added_value.recipient;
    g_mail_test (g_nr_of_values).message := added_value.message;
  end;
end;
/

create or replace trigger xxx_compound_test_bu before update on xxx_compound_test
begin
  xxx_compound_test_pkg.init;
end;
/
create or replace trigger xxx_compound_test_bru before update on xxx_compound_test
for each row
declare
  l_added_value xxx_compound_test_pkg.r_mail_test;
 
begin
  l_added_value.recipient := 'Test ' || :new.nr;
  l_added_value.message   := 'Test message';
  l_added_value.nr        := :new.nr;
 
  xxx_compound_test_pkg.add_value (l_added_value);
end;
/
create or replace trigger xxx_compound_test_au after update on xxx_compound_test
begin
  -- do your mail thingy
  dbms_output.put_line ('Nr of records: ' || xxx_compound_test_pkg.g_nr_of_values);
 
  <<mails>>
  for i in xxx_compound_test_pkg.g_mail_test.first .. xxx_compound_test_pkg.g_mail_test.last
  loop
    dbms_output.put_line ('Sending mail to ' || xxx_compound_test_pkg.g_mail_test (i).recipient);  
  end loop mails;
 
end;
/

New style

But a compound trigger is much simpler since you only need one trigger and no package to maintain ..

create or replace trigger xxx_mail_test_comp for update of action_date on xxx_compound_test
compound trigger
  type
    r_mail_test is record
    (
      nr        number
    , recipient varchar2(240)
    , message   varchar2(240)
    );
   
  type
    t_mail_test is table of r_mail_test index by binary_integer;
   
  g_mail_test t_mail_test;
  g_nr_of_values number;
  l_added_value r_mail_test;
  procedure init
  is
  begin
    g_mail_test.delete;
    g_nr_of_values := 0;
  end init;
 
  procedure add_value (added_value r_mail_test)
  is
  begin
    g_nr_of_values := g_nr_of_values + 1;
    dbms_output.put_line ('Adding value ' || g_nr_of_values || ' for nr ' || added_value.nr);
    g_mail_test (g_nr_of_values).nr := added_value.nr;
    g_mail_test (g_nr_of_values).recipient := added_value.recipient;
    g_mail_test (g_nr_of_values).message := added_value.message;
  end add_value;
  
  BEFORE STATEMENT IS
  BEGIN
    init;
  END BEFORE STATEMENT;
 
  AFTER EACH ROW IS
  BEGIN
    l_added_value.recipient := 'Test ' || :new.nr;
    l_added_value.message   := 'Test message';
    l_added_value.nr        := :new.nr;
 
    add_value (l_added_value);
  END AFTER EACH ROW;
 
  AFTER STATEMENT IS
  BEGIN
    dbms_output.put_line ('Nr of records: ' || g_nr_of_values);
   
    <<mails>>
    for i in g_mail_test.first .. g_mail_test.last
    loop
      dbms_output.put_line ('Sending mail to ' || g_mail_test (i).recipient);  
    end loop mails;
  END AFTER STATEMENT;
END;
/