Home » SQL & PL/SQL » SQL & PL/SQL » Numeric or value error (oracle 10g)
Numeric or value error [message #439920] Wed, 20 January 2010 03:04 Go to next message
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 #439922 is a reply to message #439920] Wed, 20 January 2010 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
At line 24 (which one is it?) you try to insert a value that is larger than the target buffer.

Regards
Michel
Re: Numeric or value error [message #439924 is a reply to message #439920] Wed, 20 January 2010 03:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would guess that the value that you're selecting into V_NODE is longer than 15 characters.
Re: Numeric or value error [message #439950 is a reply to message #439920] Wed, 20 January 2010 05:40 Go to previous messageGo to next message
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 #439952 is a reply to message #439950] Wed, 20 January 2010 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, from waht you said you are inserting something into a variable.
What is at line 24?

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Numeric or value error [message #439962 is a reply to message #439920] Wed, 20 January 2010 05:55 Go to previous messageGo to next message
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.
Re: Numeric or value error [message #439963 is a reply to message #439962] Wed, 20 January 2010 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom wrote on Wed, 20 January 2010 10:24
I would guess that the value that you're selecting into V_NODE is longer than 15 characters.

Did you check this?

Regards
Michel
Re: Numeric or value error [message #439964 is a reply to message #439963] Wed, 20 January 2010 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance, REF_CONSTRAINT_T and INDEX_STATISTICS contain 16 characters.

Regards
Michel

[Updated on: Wed, 20 January 2010 06:07]

Report message to a moderator

Re: Numeric or value error [message #439966 is a reply to message #439920] Wed, 20 January 2010 06:14 Go to previous message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Machel,
Its a great help for me. Thank you very much.
Regards,
Madhavi.
Previous Topic: Multi rows convert into one row
Next Topic: Inconsistency in Interval Date Seconds handling?
Goto Forum:
  


Current Time: Sun Sep 25 14:31:15 CDT 2016

Total time taken to generate the page: 0.07171 seconds