Friday, August 8, 2014

EBS 12.2 -- "APPS.AD_ZD_ADOP", line 543 // Adadmin problem

You may encounter following errors, while trying to use adadmin in EBS 12.2..

AD Administration error:
ORA-20010: ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00229: input source is empty
ORA-06512: at "APPS.AD_ZD_ADOP", line 543
ORA-06512: at line 1

These undocumented error messages are caused by the XML parsing..

When you anaylze the code, you will see the following query which encounters error while trying to get an xml element and the code raises exception accordingly..

  SELECT
                  fn.host node_name ,
                  aat.appl_top_id appl_top_id ,
                  EXTRACTVALUE(XMLType(TEXT),'//shared_file_system') is_shared
        FROM
                  fnd_nodes fn,
                  FND_OAM_CONTEXT_FILES focf,
                  fnd_product_groups fpg,
                  ad_appl_tops aat,
                  ad_releases ar
        WHERE     focf.NAME not in ('TEMPLATE','METADATA','config.txt') and focf.CTX_TYPE='A' and
                  (focf.status is null or upper(focf.status) in ('S','F')) and
                  EXTRACTVALUE(XMLType(focf.TEXT),'//file_edition_type') = 'run' and
                  focf.node_name=fn.host and
                  (fn.support_cp='Y' or fn.support_forms='Y' or
                   fn.support_web='Y' or fn.support_admin='Y') and
                  aat.appl_top_type='R' and aat.applications_system_name=fpg.applications_system_name and
                  aat.active_flag='Y' and
                  fpg.release_name=ar.major_version||'.'||ar.minor_version||'.'||ar.tape_version and
                  fpg.aru_release_name=ar.aru_release_name and
                  aat.name=EXTRACTVALUE(XMLType(focf.TEXT),'//APPL_TOP_NAME');


The function that causing the error is EXTRACTVALUE..
What happens is;
EXTRACTVALUE tries to extract a value from the context file stored in the database , but it finds the input empty.
Context Files were in fnd_oam_context_files tables, they are stored in the TEXT column as clobs..
So basically our query wants to read a CLOB and make some xml processing on it, but it finds the relevant clob empty.

Note that; here are plenty of context file contents stored in fnd_oam_context_files table, but the important one is the one that our query is trying to process..

Note that :
Ref: http://avdeo.com/
while updating context variables using OAM is that what it displays is gotten; by parsing the context file stored in fnd_oam_context_files table (Autoconfig at each run uploads the context file in this table). When we try to update the context file, OAM first update’s the status flag from ‘S’ to ‘H'(History) for our context file record, it then inserts another row for the same context file with status ‘S’. (OAM inserts another row instead of updating the existing row to maintain change history for each context file) It then requests the specific node’s FNDFS listener for updating the file on the file system (autoconfig uses the file on the file system). So we have to make sure that the FNDFS listeners are running on all the nodes before using OAM to update the context file.
When we reformat our query , and extrace the problematic xml operations, we see that;
Our problematic query is actually trying to read the context files that are returned from the following query;

select  * from FND_OAM_CONTEXT_FILES focf where
                  focf.NAME not in ('TEMPLATE','METADATA','config.txt') and focf.CTX_TYPE='A' and
                  (focf.status is null or upper(focf.status) in ('S','F'))

If it is the case This query probably return 2 records..
Open/Read the Text column of the record with status = F .. You will see that the TEXT column of that records is empty, and that is the problem.

Take a backup of the table , and delete that record.. Once the record is deleted, your problem will be fixed.. Then you can retry desired adadmin operation.

Following is a Bonus Track :)

Context Files are stored in the database.. The table that store the context files( historically) is "FND_OAM_CONTEXT_FILES"
Context files in this table is stored in a huge clob column named TEXT..
In TEXT column the context file is stored as is, that is in xml format ..

fnd_oam_context_files
----------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(512)
VERSION NOT NULL VARCHAR2(30)
PATH NOT NULL VARCHAR2(1024)
LAST_SYNCHRONIZED NOT NULL DATE
TEXT NOT NULL CLOB
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER(15)
LAST_UPDATE_LOGIN NOT NULL NUMBER(15)
NODE_NAME NOT NULL VARCHAR2(30)
STATUS VARCHAR2(1)
SERIAL_NUMBER NUMBER
EDIT_COMMENTS VARCHAR2(2000)
CTX_TYPE NOT NULL VARCHAR2(1)
So by quering the table and using the proper xml functions , the values in context file can be read.

For example, to gather the web port information of the E-Busines Suite system, following query can be used;

SELECT extractvalue(xmltype(text),'//web_port')
FROM fnd_oam_context_files where status='S';


Note that, the query should be modified in order to read the xml tags with childs.

No comments :

Post a Comment