Numeric or value error [message #439920] |
Wed, 20 January 2010 03:04  |
madhavi babburi
Messages: 117 Registered: May 2009 Location: Hyderabad
|
Senior Member |
|
|
Hi,
I got numeric or value error when i execute my function. Please tell me where iam wrong.
My function is
CREATE OR REPLACE FUNCTION F_IMPORT
RETURN CLOB
AS
V_XML XMLTYPE;
XML XMLTYPE;
V_XML_IND XMLTYPE;
V_XML_IND_NEW XMLTYPE;
V_Node VARCHAR2(15);
V_CNT NUMBER(3) :=1;
Handle number(20);
Th NUMBER(20);
VAR1 sys.KU$_MULTI_DDLS;
VAR2 sys.KU$_DDLs;
VAR3 cloB;
V_Total CLOB;
V_PART1 VARCHAR2(2000) ;
V_PART2 VARCHAR2(2000) ;
Obj_Type VARCHAR2(30);
begin
select repository into v_xml from ddi_repos_t where object_id = 7;
select '/ROWSET/ROW[' into v_part1 from dual;
select ']' into v_part2 from dual;
LOOP
SELECT xml, NODES INTO V_XML_IND, V_NODE FROM (select t2.column_value.getrootelement() nodes , xml
from (select extract (v_xml,V_Part1 || V_Cnt || V_Part2) xml FROM DUAL) t,
table(xmlsequence(t.xml.extract('//node()'))) t2 )
WHERE ROWNUM =1 AND NODES NOT IN ('ROWSET','ROW') ;
EXIT WHEN V_NODE IS NULL;
DBMS_OUTPUT.PUT_LINE(V_NODE);
IF V_Node = 'TABLE_T' THEN
Obj_type := 'TABLE' ;
ELSIF V_Node = 'TABLE_DATA_T' THEN
Obj_type := 'TABLE_DATA' ;
ELSIF V_Node = 'OBJGRANT_T' THEN
Obj_type := 'OBJECT_GRANT';
ELSIF V_Node = 'INDEX_T' THEN
Obj_type := 'INDEX' ;
ELSIF V_Node = 'CONSTRAINT_T' THEN
Obj_type := 'CONSTRAINT' ;
ELSIF V_Node = 'I_STAT_T' THEN
Obj_type := 'INDEX_STATISTICS' ;
ELSIF V_Node = 'COMMENT_T' THEN
Obj_type := 'COMMENT' ;
ELSIF V_Node = 'T_STAT_T' THEN
Obj_type := 'TABLE_STATISTICS' ;
ELSIF V_Node = 'TRIGGER_T' THEN
Obj_type := 'TRIGGER' ;
END IF;
SELECT XMLELEMENT("ROWSET", XMLAGG(V_XML_IND)) INTO V_XML_IND_NEW FROM DUAL;
DBMS_OUTPUT.PUT_LINE(OBJ_TYPE);
IF OBJ_TYPE = 'TABLE_DATA' THEN NULL;
ELSE
Handle := DBMS_METADATA.OPENW(Obj_type);
th := DBMS_METADATA.ADD_TRANSFORM (handle,'DDL');
-- DBMS_METADATA.SET_TRANSFORM_PARAM(TH,'SEGMENT_ATTRIBUTES',TRUE);
-- DBMS_METADATA.SET_TRANSFORM_PARAM(TH,'STORAGE',TRUE);
-- DBMS_METADATA.SET_TRANSFORM_PARAM(TH,'TABLESPACE',TRUE);
DBMS_METADATA.SET_TRANSFORM_PARAM(TH,'SQLTERMINATOR',TRUE);
DBMS_OUTPUT.PUT_LINE('Upto this fine');
VAR1 := DBMS_METADATA.CONVERT(handle, v_xml_ind_new);
VAR2 := VAR1(1).DDLS;
VAR3 := VAR2(1).ddltext;
V_Total := Var3;
dbms_metadata.close(handle);
END IF;
V_CNT := V_CNT +1;
END LOOP;
RETURN VAR3;
END;
when i execute like
select f_import from dual; my error is...
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.F_IMPORT", line 24
Regards,
Madhavi.
|
|
|
|
|
Re: Numeric or value error [message #439950 is a reply to message #439920] |
Wed, 20 January 2010 05:40   |
madhavi babburi
Messages: 117 Registered: May 2009 Location: Hyderabad
|
Senior Member |
|
|
Hi,
Actually iam not inserting anything into any table. Just extracting one child node from a variable (Having xmldata) and apply getrootnode to get node names. Then converted xml to DDL as per node name and then stored in a variable of clob datatype. But i dont know why this error came.
I got error at line 24. Line 24 is..
SELECT xml, NODES INTO V_XML_IND, V_NODE FROM (select t2.column_value.getrootelement() nodes , xml
from (select extract (v_xml,V_Part1 || v_cnt || V_Part2) xml FROM DUAL) t,
table(xmlsequence(t.xml.extract('//node()'))) t2 )
WHERE ROWNUM =1 AND NODES NOT IN ('ROWSET','ROW') ;
Regards,
Madhavi.
[Updated on: Wed, 20 January 2010 05:43] Report message to a moderator
|
|
|
|
Re: Numeric or value error [message #439962 is a reply to message #439920] |
Wed, 20 January 2010 05:55   |
madhavi babburi
Messages: 117 Registered: May 2009 Location: Hyderabad
|
Senior Member |
|
|
Hi Machel,
Sorry for inconvinience.
my code is
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE FUNCTION TEST.F_IMPORT
2 RETURN clob
3 AS
4 V_XML XMLTYPE;
5 XML XMLTYPE;
6 V_XML_IND XMLTYPE;
7 V_Node VARCHAR2(15);
8 V_CNT NUMBER :=1;
9 Handle number;
10 Th NUMBER;
11 VAR1 sys.KU$_MULTI_DDLS;
12 VAR2 sys.KU$_DDLs;
13 VAR3 cloB;
14 V_Total CLOB;
15 V_Total2 CLOB;
16 OBJ_TYPE VARCHAR2(25);
17 V_PART1 VARCHAR2(2000) ;
18 V_PART2 VARCHAR2(2000) ;
19 v_Length NUMBER;
20 begin
21 DBMS_OUTPUT.PUT_LINE('START THE PROGRAM');
22 select repository into v_xml from ddi_repos_t where object_id = 7;
23 DBMS_OUTPUT.PUT_LINE('KEEP THE XML IN A VARIABLE');
24 select '/ROWSET/ROW[' into v_part1 from dual;
25 select ']' into v_part2 from dual;
26 LOOP
27 SELECT xml, NODES INTO V_XML_IND, V_NODE FROM (select t2.column_value.getrootelement() nod
28 from (select extract (v_xml,V_Part1 || v_cnt || V_Part2) xml FROM DUAL) t,
29 table(xmlsequence(t.xml.extract('//node()'))) t2 )
30 WHERE ROWNUM =1 AND NODES NOT IN ('ROWSET','ROW') ;
31 EXIT WHEN V_NODE IS NULL or v_cnt > 92;
32 DBMS_OUTPUT.PUT_LINE(V_NODE);
33 IF V_Node = 'TABLE_T' THEN
34 Obj_type := 'TABLE' ;
35 ELSIF V_Node = 'TABLE_DATA_T' THEN
36 Obj_type := 'TABLE_DATA' ;
37 ELSIF V_Node = 'OBJGRANT_T' THEN
38 Obj_type := 'OBJECT_GRANT';
39 ELSIF V_Node = 'INDEX_T' THEN
40 Obj_type := 'INDEX' ;
41 ELSIF V_Node = 'CONSTRAINT_T' THEN
42 Obj_type := 'CONSTRAINT' ;
43 ELSIF V_Node = 'I_STAT_T' THEN
44 Obj_type := 'INDEX_STATISTICS' ;
45 ELSIF V_Node = 'COMMENT_T' THEN
46 Obj_type := 'COMMENT' ;
47 ELSIF V_Node = 'REF_CONSTRAINT_T' THEN
48 Obj_type := 'REF_CONSTRAINT' ;
49 ELSIF V_Node = 'TRIGGER_T' THEN
50 Obj_type := 'TRIGGER' ;
51 ELSIF V_Node = 'T_STAT_T' THEN
52 Obj_type := 'TABLE_STATISTICS' ;
53 END IF;
54 SELECT XMLELEMENT("ROWSET", XMLAGG(V_XML_IND)) INTO V_XML_IND FROM DUAL;
55 DBMS_OUTPUT.PUT_LINE(V_NODE);
56 -- RETURN V_XML_IND;
57 IF OBJ_TYPE NOT IN ('TABLE_DATA') THEN
58 Handle := DBMS_METADATA.OPENW(OBJ_TYPE);
59 DBMS_OUTPUT.PUT_LINE('Open the handle' || handle);
60 th := DBMS_METADATA.ADD_TRANSFORM(HANDLE, 'DDL');
61 DBMS_OUTPUT.PUT_LINE('Add the tranform');
62 DBMS_METADATA.SET_TRANSFORM_PARAM(TH,'SQLTERMINATOR',TRUE);
63 VAR1 := DBMS_METADATA.CONVERT(handle, v_xml_ind);
64 DBMS_OUTPUT.PUT_LINE('Converting XML to DDL');
65 VAR2 := VAR1(1).DDLS;
66 VAR3 := VAR2(1).ddltext;
67 IF V_Total is null then v_total := var3;
68 else v_total := v_total || var3 ;
69 end if;
70 ELSE NULL;
71 END IF;
72 V_CNT := V_CNT +1;
73 END LOOP;
74 DBMS_OUTPUT.PUT_LINE('Display the o/p');
75 RETURN V_Total;
76 SELECT LENGTH(V_Total) INTO V_Length from dual;
77 --RETURN V_Length;
78* END;
SQL> /
Function created.
My error is...
SQL> select f_import from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.F_IMPORT", line 27
no rows selected
One more point is i got o/p (not total, some part) when i keep v_cnt > 91 (line no 31).
Regards,
Madhavi.
|
|
|
|
|
|