| 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
