Exporting OAF Personalizations using SQL

Exporting OAF Personalizations using SQL 

In this article we shall see the query which will return all OAF personalizations done along with XML file.

Following query returns OAF personalizations along with a CLOB column which is the personalized page XML, this can be used for analyzing OAF personalizations done in any given instance:-

SQL query to export OAF personalizations along with personalization XML

SELECT   jpath.path_docid perz_doc_id,

         jdr_mds_internal.getdocumentname (jpath.path_docid) perz_doc_path,

         xxcust_getpern_doc_pkg.get_persn_doc

                 (jdr_mds_internal.getdocumentname (jpath.path_docid)

                 ) persn_doc

    FROM jdr_paths jpath

   WHERE jpath.path_docid IN (

            SELECT DISTINCT comp_docid

                       FROM jdr_components

                      WHERE comp_seq = 0

                        AND comp_element = 'customization'

                        AND comp_id IS NULL)

ORDER BY perz_doc_path;

We have used custom package xxcust_getpern_doc_pkg..get_persn_doc for getting personalization XML file in a CLOB. This custom package internally uses jdr_mds_internal.exportDocumentAsXML for getting personalization XML in 32k chunks.

Custom package code is given below:-

CREATE OR REPLACE PACKAGE BODY APPS.xxcust_getPern_doc_pkg AS

   -- function to get personalization document

   FUNCTION get_persn_doc(p_document_name IN VARCHAR2

                )

   RETURN CLOB

   IS

      isDone INTEGER;

      l_pern_clob clob := '';

      l_pern_info  VARCHAR2(32000);

   BEGIN

           dbms_lob.createtemporary(lob_loc => l_pern_clob, cache => true, dur => dbms_lob.call);

           l_pern_info := jdr_mds_internal.exportDocumentAsXML(isDone,p_document_name);

           dbms_lob.writeappend( l_pern_clob, length(l_pern_info), l_pern_info );

           WHILE (isDone = 0) LOOP

             l_pern_info := jdr_mds_internal.exportDocumentAsXML(isDone, NULL);

             dbms_lob.writeappend( l_pern_clob, length(l_pern_info), l_pern_info );

           END LOOP;

          

           RETURN l_pern_clob;

   END get_persn_doc;

END xxcust_getPern_doc_pkg;

/