Home » Developer & Programmer » JDeveloper, Java & XML » ORA-29400 and ORA-22922 errors (Oracle 10g with xml)
ORA-29400 and ORA-22922 errors [message #445041] Thu, 25 February 2010 06:19
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Iam having objects metadata in the form of xml (With child nodes)in oracle table. Now i want to convert it as DDL using METADATA API.
For the above requirement,i got select the xml from oracle table and keep in one variable. Then select the nodes and corresponding xml & keep in variables. When selecting like that, root node (ROWSET) is not coming along with total xml for each node. So i append the root node to each child node by using XMLELEMENT and XMLAGG functions.
Before append, i didn't get any error (iam able to select xml data for each node). Whaen appending i got below errors.
This function works fine in one database. For another database i got below errors.
I keep part (upto appending root node)of my code.
When executing the function, it throws two types of errors like table or view doesnot exists and non exististent lob value.
Please tell me where iam wrong.
SQL> CREATE OR REPLACE FUNCTION F_Metadata_Import1
  2     RETURN XMLTYPE
  3  AS
  4     V_Xml       XMLTYPE;
  5     V_Xml_Ind   XMLTYPE;
  6     V_Node      VARCHAR2 (25);
  7     V_Cnt       NUMBER := 1;
  8     V_Part1     VARCHAR2 (15);
  9     V_Part2     VARCHAR2 (15);
 10  BEGIN
 11  DBMS_OUTPUT.PUT_LINE('Before execution ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
 12     -- Get metadata from source database.
 13        SELECT   Repos
 14       INTO   V_Xml
 15       FROM   DDI.DDI_REPOS_T
 16      WHERE   Obj_Id = 13;
 17   DBMS_OUTPUT.PUT_LINE('get xml from ddi table');
 18     SELECT   '/ROWSET/ROW[' INTO V_Part1 FROM DUAL;
 19  
 20     SELECT   ']' INTO V_Part2 FROM DUAL;
 21  DBMS_OUTPUT.PUT_LINE('loop starts');
 22     LOOP
 23        -- Get the each dependent object metadata and node.
 24        BEGIN
 25        DBMS_OUTPUT.PUT_LINE('get the node');
 26           SELECT   Xml, Nodes
 27             INTO   V_Xml_Ind, V_Node
 28             FROM   (SELECT   t2.COLUMN_VALUE.GETROOTELEMENT () Nodes, Xml
 29         FROM   (SELECT   EXTRACT (V_XML,
 30                                                 V_Part1 || V_Cnt || V_Part2)
 31                                           Xml
 32                                 FROM   DUAL) t,
 33                              TABLE (XMLSEQUENCE (t.xml.EXTRACT ('//node()'))) t2)
 34            WHERE   ROWNUM = 1 AND Nodes NOT IN ('ROWSET', 'ROW');
 35        EXCEPTION
 36           WHEN NO_DATA_FOUND
 37           THEN
 38              EXIT;
 39        END;
 40        DBMS_OUTPUT.PUT_LINE('Get the nodes and corresponding xml');
 41        DBMS_OUTPUT.PUT_LINE('Node is '|| v_node);
 42        -- RETURN V_XML_IND;
 43        -- Append root node to xml data.
 44        SELECT   XMLELEMENT ("ROWSET", XMLAGG(V_Xml_Ind)) INTO   V_Xml_Ind FROM DUAL;
 45        RETURN V_XML_IND;
 46        DBMS_OUTPUT.PUT_LINE('After execution ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
 47    end loop;
 48  
 49  END;
 50  /

Function created.

SQL> SELECT F_Metadata_Import1 FROM DUAL;
ERROR:
ORA-29400: data cartridge error
ORA-00942: table or view does not exist
ORA-06512: at "TUNER.F_METADATA_IMPORT1", line 44



no rows selected

SQL> /
ERROR:
ORA-29400: data cartridge error
ORA-22922: nonexistent LOB value
ORA-06512: at "TUNER.F_METADATA_IMPORT1", line 44



no rows selected

SQL> /
ERROR:
ORA-29400: data cartridge error
ORA-00942: table or view does not exist
ORA-06512: at "TUNER.F_METADATA_IMPORT1", line 44


regards,
Madhavi.
Previous Topic: Oracle Jdev 11g
Next Topic: jdbc driver for enterprise user
Goto Forum:
  


Current Time: Fri Mar 29 07:50:19 CDT 2024