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;
/
