OraFAQ Forum: SQL & PL/SQL » PL/SQL: numeric or value error: character string buffer too small

Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: numeric or value error: character string buffer too small (11.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PL/SQL: numeric or value error: character string buffer too small [message #656933]

Mon, 24 October 2016 06:08

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Hi,

I am running below code and getting below error. Please suggest me how to solve this.

create or replace FUNCTION test_f_direct (i_table_name IN VARCHAR2)
      RETURN		SYS_REFCURSOR
    AS
    v_ref SYS_REFCURSOR;
    v_length  number;
    v_sql        dbms_sql.varchar2a; 
    --v_createsql        dbms_sql.varchar2a;         
    v_propid 	NUMBER;    
    v_intCur        pls_integer;
    v_intIdx        pls_integer;
    v_intNumRows    pls_integer;    

    BEGIN
     EXECUTE IMMEDIATE
   	 'SELECT MAX (NVL(LENGTH (text_value), 4000)) FROM ' || i_table_name
   	 INTO v_length;
      v_intIdx := 1;     
 
     v_sql(v_intIdx) := 'SELECT project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';
     OPEN v_ref FOR 'SELECT distinct ATTRIBUTE_ID FROM ' || i_table_name;
     LOOP
     v_intIdx := v_intIdx +1;     
   	 FETCH v_ref INTO v_propid;
   	 EXIT WHEN v_ref%NOTFOUND;
   	 v_sql(v_intIdx) := v_sql(v_intIdx-1) || ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
   			|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE, 
            4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
            7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
   			|| v_length || '))"' || v_propid || '"';
     END LOOP;
     v_intIdx := v_intIdx +1;
     v_sql(v_intIdx) := v_sql(v_intIdx-1) || ' FROM ' || i_table_name ||
   	 ' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';

    v_intCur := dbms_sql.open_cursor;
    dbms_sql.parse(  c => v_intCur,  statement => v_sql,  lb => 1,  ub => v_intIdx,  lfflg => true,  language_flag => dbms_sql.native);
    v_intNumRows := dbms_sql.execute(v_intCur);
     RETURN v_ref;
END test_f_direct;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SHARCHTEST2.TEST_F_DIRECT", line 25
ORA-06512: at line 7

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #656934 is a reply to message #656933]

Mon, 24 October 2016 06:20

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Which line is line 25?
Post the actual query as it is parsed by dbms_sql.parse.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #656942 is a reply to message #656934]

Mon, 24 October 2016 08:36

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

I'm pretty sure it's not getting as far as dbms_sql.parse.
line 25 appears to be:

   	 v_sql(v_intIdx) := v_sql(v_intIdx-1) || ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid

v_sql is an array of varchar2(32767).
Looks like OP is building up a string of decodes and appending each one till it blows out the size limit. The length of the string is approx 300 char (it varies based on the length of the value of v_propid).

What does this give:

SELECT count(distinct ATTRIBUTE_ID) FROM <whatever table you're actually using>

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #656945 is a reply to message #656933]

Mon, 24 October 2016 09:18

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

You completely misunderstand dbms_sql.varchar2a. It is used when SQL text is long and allows splitting such SQL in chunks. Each element of dbms_sql.varchar2a is a chunk of SQL statement while you are concatenating previous chunks. Change:

   	 v_sql(v_intIdx) := v_sql(v_intIdx-1) || ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
   			|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE, 
            4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
            7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
   			|| v_length || '))"' || v_propid || '"';

To:

   	 v_sql(v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
   			|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE, 
            4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
            7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
   			|| v_length || '))"' || v_propid || '"';

SY.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #656946 is a reply to message #656945]

Mon, 24 October 2016 10:09

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

I did wonder why it was an array

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #656976 is a reply to message #656946]

Tue, 25 October 2016 00:02

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

I tried without using array but same error I am getting even I change to varchar2. If the sql is less than 32767 then I am not getting the below error. Please find the exact error as below.

create or replace FUNCTION test_f_direct (i_table_name IN VARCHAR2)
      RETURN		SYS_REFCURSOR
    AS
    v_ref SYS_REFCURSOR;
    v_length  number;
      v_sql		VARCHAR2(32767);    
    --v_sql        dbms_sql.varchar2a; 
    v_createsql        dbms_sql.varchar2a;         
    v_propid 	NUMBER;    
    v_intCur        pls_integer;
    v_intIdx        pls_integer;
    v_intNumRows    pls_integer;    

    BEGIN
     EXECUTE IMMEDIATE
   	 'SELECT MAX (NVL(LENGTH (text_value), 4000)) FROM ' || i_table_name
   	 INTO v_length;
      --v_intIdx := 1;     
 
     v_sql := 'SELECT project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';
     OPEN v_ref FOR 'SELECT distinct ATTRIBUTE_ID FROM ' || i_table_name;
     LOOP
     --v_intIdx := v_intIdx +1;     
   	 FETCH v_ref INTO v_propid;
   	 EXIT WHEN v_ref%NOTFOUND;
   	 v_sql := v_sql || ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid                        --line 25
   			|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE, 
            4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
            7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
   			|| v_length || '))"' || v_propid || '"';
     END LOOP;
     --v_intIdx := v_intIdx +1;
     CLOSE v_ref;
     v_sql := v_sql || ' FROM ' || i_table_name ||
   	 ' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';
    OPEN v_ref FOR v_sql;     
/*
  log_de_bug(11,'sql:'||v_sql);
    v_intIdx := 1;
    v_createsql(v_intIdx) := chr(9) || v_sql;
    --dbms_output.put_line('createSQL1:'||v_createsql(v_intIdx));  
    
    log_de_bug (8, 'createSQL:'||v_createsql(v_intIdx));  
    
    v_intCur := dbms_sql.open_cursor;
    dbms_sql.parse(  c => v_intCur,  statement => v_createsql,  lb => 1,  ub => v_intIdx,  lfflg => true,  language_flag => dbms_sql.native);
    v_intNumRows := dbms_sql.execute(v_intCur); */
     RETURN v_ref;
END test_f_direct;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SHARCHTEST2.TEST_F_DIRECT", line 26
ORA-06512: at line 7

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #656980 is a reply to message #656976]

Tue, 25 October 2016 01:21

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Which line is line 27?
Display the query as it is built.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #656988 is a reply to message #656980]

Tue, 25 October 2016 03:06

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why didn't you just follow Solomon's suggestion?

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #657198 is a reply to message #656988]

Tue, 01 November 2016 01:59

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Followed Solomon suggestion. Not at all executing but I am getting the error. Please find the details as below.
The line number 32 is:
v_sql(v_intIdx) := v_sql(v_intIdx-1) || ' FROM ' || i_table_name ||
' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';

ORA-01403: no data found
ORA-06512: at "SHARCHTEST2.TEST_F_DIRECT", line 32
ORA-06512: at line 7
Process exited.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #657203 is a reply to message #657198]

Tue, 01 November 2016 05:06

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

You have same issue and also math problem. Look at your loop. You execute:

v_intIdx := v_intIdx +1;

before

   	 EXIT WHEN v_ref%NOTFOUND;

Therefore v_intIdx = v_sql.count + 1 when you exit loop. And then, for some reason you do another

     v_intIdx := v_intIdx +1;

so now v_intIdx = v_sql.count + 2

And

     v_sql(v_intIdx) := v_sql(v_intIdx-1) || ' FROM ' || i_table_name ||
   	 ' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';

obviously fails with no data found since v_sql(v_intIdx-1) doesn't exist. Also, you are again concatenating with previous line while, as I already explained v_sql is an array where each element is consecutive chunk of SQL statement. So change:

     v_intIdx := v_intIdx +1;
     v_sql(v_intIdx) := v_sql(v_intIdx-1) || ' FROM ' || i_table_name ||
   	 ' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';

     v_sql(v_intIdx) := ' FROM ' || i_table_name ||
   	 ' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';

SY.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #658793 is a reply to message #657203]

Tue, 27 December 2016 06:16

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Sorry Solomon..

After long time I am working on the same requirement. I have changed the function but still I am getting the below error
"ORA-01002: fetch out of sequence". Please advice on this.

create or replace FUNCTION test_f_with_main_direct (i_table_name IN VARCHAR2)
      RETURN		SYS_REFCURSOR
    AS
    v_ref SYS_REFCURSOR;
    v_length  number;
    v_sql        dbms_sql.varchar2a; 
    --v_createsql        dbms_sql.varchar2a;         
    v_propid 	NUMBER;    
    v_intCur        pls_integer;
    v_intIdx        pls_integer;
    v_intNumRows    pls_integer;    

    BEGIN
     EXECUTE IMMEDIATE
   	 'SELECT MAX (NVL(LENGTH (text_value), 4000)) FROM ' || i_table_name
   	 INTO v_length;
      v_intIdx := 1;     
 
     v_sql(v_intIdx) := 'SELECT project_id,BUSINESSOBJECT_ID, INSTANCE_NAME, PARENT_INSTANCE_NAME';
     OPEN v_ref FOR 'SELECT distinct ATTRIBUTE_ID FROM ' || i_table_name;
     LOOP
     
   	 FETCH v_ref INTO v_propid;
   	 EXIT WHEN v_ref%NOTFOUND;
         v_intIdx := v_intIdx +1;      

   	 v_sql(v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
   			|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE, 
            4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
            7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ))) AS VARCHAR2('
   			|| v_length || '))"' || v_propid || '"';
     END LOOP;

     v_intIdx := v_intIdx +1;
     v_sql(v_intIdx) := ' FROM ' || i_table_name ||
   	 ' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME, PARENT_INSTANCE_NAME';

    v_intCur := dbms_sql.open_cursor;

    dbms_sql.parse(  c => v_intCur,  statement => v_sql,  lb => 1,  ub => v_intIdx,  lfflg => true,  language_flag => dbms_sql.native);
    v_intNumRows := dbms_sql.execute(v_intCur);
 
     RETURN v_ref;
END test_f_with_main_direct;

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #658818 is a reply to message #658793]

Tue, 27 December 2016 20:45

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

The following is my best guess at what you are trying to do, with a table and some data added for testing. This seems like a very roundabout way of doing things, but I suppose it may be a simplification of something more complex. It might help if you provided a sample table, data, desired results, and explanation of why you have chosen this method rather than just a simple select statement.

-- test table and test data:

SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
  2    (text_value	   VARCHAR2(15),
  3  	project_id	   NUMBER,
  4  	businessobject_id  NUMBER,
  5  	attribute_id	   NUMBER,
  6  	instance_name	   VARCHAR2(15),
  7  	attribute_type_id  NUMBER,
  8  	number_value	   NUMBER,
  9  	date_value	   DATE,
 10  	note_value	   VARCHAR2(15),
 11  	dropdown_value	   VARCHAR2(15),
 12  	boolean_value	   NUMBER,
 13  	reference_value    NUMBER,
 14  	link_value	   VARCHAR2(15),
 15  	datetime_value	   DATE,
 16  	parent_instance    NUMBER,
 17  	child_instance	   NUMBER)
 18  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO test_tab VALUES
  2    ('textval1', 1, 1, 1, 'instname1', 1, 1, SYSDATE, 'noteval1', 'dropdown1', 1, 1, 'linkval1', SYSDATE, 1, 1)
  3  /

1 row created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO test_tab VALUES
  2    ('textval2', 2, 2, 2, 'instname2', 2, 2, SYSDATE, 'noteval2', 'dropdown2', 2, 2, 'linkval2', SYSDATE, 2, 2)
  3  /

1 row created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO test_tab VALUES
  2    ('textval3', 3, 3, 3, 'instname3', 3, 3, SYSDATE, 'noteval3', 'dropdown3', 3, 3, 'linkval3', SYSDATE, 3, 3)
  3  /

1 row created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO test_tab VALUES
  2    ('textval1', 4, 4, 4, 'instname4', 4, 4, SYSDATE, 'noteval4', 'dropdown4', 4, 4, 'linkval4', SYSDATE, 4, 4)
  3  /

1 row created.

-- function:

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION test_f_direct
  2    (i_table_name IN VARCHAR2)
  3    RETURN		SYS_REFCURSOR
  4  AS
  5    v_length 	NUMBER;
  6    v_intIdx 	PLS_INTEGER;
  7    v_sql		DBMS_SQL.VARCHAR2A;
  8    v_ref		SYS_REFCURSOR;
  9    v_propid 	NUMBER;
 10    v_intCur 	PLS_INTEGER;
 11    v_intNumRows	PLS_INTEGER;
 12  BEGIN
 13    EXECUTE IMMEDIATE
 14  	 'SELECT MAX (NVL (LENGTH (text_value), 4000)) FROM ' || i_table_name INTO v_length;
 15    v_intIdx := 1;
 16    v_sql (v_intIdx) := 'SELECT project_id,businessobject_id,instance_name';
 17    OPEN v_ref FOR 'SELECT DISTINCT attribute_id FROM ' || i_table_name || ' ORDER BY attribute_id';
 18    LOOP
 19  	 FETCH v_ref INTO v_propid;
 20  	 EXIT WHEN v_ref%NOTFOUND;
 21  	 v_intIdx := v_intIdx +1;
 22  	 v_sql (v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
 23  	   || ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
 24  	   4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
 25  	   7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
 26  	   || v_length || '))"' || v_propid || '"';
 27    END LOOP;
 28    CLOSE v_ref;
 29    v_intIdx := v_intIdx +1;
 30    v_sql (v_intIdx) := ' FROM ' || i_table_name || ' GROUP BY project_id,businessobject_id,instance_name'
 31  	 || ' ORDER BY project_id,businessobject_id,instance_name';
 32    v_intCur := DBMS_SQL.OPEN_CURSOR;
 33    DBMS_SQL.PARSE
 34  	 (c		=> v_intCur,
 35  	  statement	=> v_sql,
 36  	  lb		=> 1,
 37  	  ub		=> v_intIdx,
 38  	  lfflg 	=> TRUE,
 39  	  language_flag => DBMS_SQL.NATIVE);
 40    v_intNumRows := DBMS_SQL.EXECUTE (v_intCur);
 41    v_ref := DBMS_SQL.TO_REFCURSOR (v_intcur);
 42    RETURN v_ref;
 43  END test_f_direct;
 44  /

Function created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.

-- usage of function:

SCOTT@orcl_12.1.0.2.0> SELECT test_f_direct ('TEST_TAB') FROM DUAL
  2  /

TEST_F_DIRECT('TEST_
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

PROJECT_ID BUSINESSOBJECT_ID INSTANCE_NAME   1        2        3        4
---------- ----------------- --------------- -------- -------- -------- --------
         1                 1 instname1       textval1
         2                 2 instname2                2
         3                 3 instname3                         Tue 27-D
         4                 4 instname4                                  noteval4

4 rows selected.


1 row selected.

SCOTT@orcl_12.1.0.2.0>

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #658819 is a reply to message #658793]

Tue, 27 December 2016 21:18

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Barbara already gave you the answer. Your code is missing DBMS_SQL cursor to REFCURSOR conversion: v_ref := DBMS_SQL.TO_REFCURSOR (v_intcur); As a results V_REF still points to 'SELECT DISTINCT attribute_id FROM ' || i_table_name || ' ORDER BY attribute_id'. And function already fetched all rows from it, so when V_REF is returned back to caller and caller issues another fetch from V_REF "fetch out of sequence" is raised:

SQL> create or replace
  2    function f1
  3      return sys_refcursor
  4      is
  5          v_cur sys_refcursor;
  6          v_dummy varchar2(1);
  7      begin
  8          open v_cur for 'select * from dual';
  9          loop
 10            fetch v_cur into v_dummy;
 11            exit when v_cur%notfound;
 12          end loop;
 13          return v_cur;
 14  end;
 15  /

Function created.

SQL> variable v_cur refcursor
SQL> exec :v_cur := f1;

PL/SQL procedure successfully completed.

SQL> print v_cur
ERROR:
ORA-01002: fetch out of sequence



no rows selected

SQL>

SY.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #658951 is a reply to message #658819]

Sun, 01 January 2017 23:41

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Thank you very much for all your support. Now it is working.

I will let you know if there is any problem.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #660147 is a reply to message #658951]

Thu, 09 February 2017 23:14

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Hi,

I am using the below function and would like to create the table with the below table structure and data before returning into the function. I am stuck up in the middle.

I am trying with open v_ref for.... but not able to continue. Please suggest me.

CREATE OR REPLACE FUNCTION test_f_direct
    (i_table_name IN VARCHAR2)
    RETURN		SYS_REFCURSOR
  AS
    v_length 	NUMBER;
    v_intIdx 	PLS_INTEGER;
    v_sql		DBMS_SQL.VARCHAR2A;
    v_ref		SYS_REFCURSOR;
    v_propid 	NUMBER;
    v_intCur 	PLS_INTEGER;
    v_intNumRows	PLS_INTEGER;
  BEGIN
    EXECUTE IMMEDIATE
  	 'SELECT MAX (NVL (LENGTH (text_value), 4000)) FROM ' || i_table_name INTO v_length;
    v_intIdx := 1;
    v_sql (v_intIdx) := 'SELECT project_id,businessobject_id,instance_name';
    OPEN v_ref FOR 'SELECT DISTINCT attribute_id FROM ' || i_table_name || ' ORDER BY attribute_id';
    LOOP
  	 FETCH v_ref INTO v_propid;
  	 EXIT WHEN v_ref%NOTFOUND;
  	 v_intIdx := v_intIdx +1;
  	 v_sql (v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
  	   || ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
  	   4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
  	   7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
  	   || v_length || '))"' || v_propid || '"';
    END LOOP;
    CLOSE v_ref;
    v_intIdx := v_intIdx +1;
    v_sql (v_intIdx) := ' FROM ' || i_table_name || ' GROUP BY project_id,businessobject_id,instance_name'
  	 || ' ORDER BY project_id,businessobject_id,instance_name';
    v_intCur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE
  	 (c		=> v_intCur,
  	  statement	=> v_sql,
  	  lb		=> 1,
  	  ub		=> v_intIdx,
  	  lfflg 	=> TRUE,
  	  language_flag => DBMS_SQL.NATIVE);
    v_intNumRows := DBMS_SQL.EXECUTE (v_intCur);
    v_ref := DBMS_SQL.TO_REFCURSOR (v_intcur);
    RETURN v_ref;
  END test_f_direct;
  /

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #660148 is a reply to message #660147]

Thu, 09 February 2017 23:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I have no idea what you desire.

DynamicSQL is a typical indicator of a seriously flawed design.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #660152 is a reply to message #660148]

Fri, 10 February 2017 03:02

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Saying you are not able to continue doesn't really give us any clues as to what problems you are having.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #660157 is a reply to message #656933]

Fri, 10 February 2017 03:28

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

I have tried with the below option but not able to do it.

CREATE OR REPLACE FUNCTION test_f_direct
    (i_table_name IN VARCHAR2)
    RETURN		SYS_REFCURSOR
  AS
    v_length 	NUMBER;
    v_intIdx 	PLS_INTEGER;
    v_sql		DBMS_SQL.VARCHAR2A;
    v_ref		SYS_REFCURSOR;
    v_propid 	NUMBER;
      v_format_table varchar2(100);
      v_tab_count number;
      v_createsql        dbms_sql.varchar2a;     
      v_intCur        pls_integer;
      v_intCur1        pls_integer;
      v_intIdx        pls_integer;
      v_intIdx1        pls_integer;
      v_intNumRows    pls_integer;

  BEGIN
    EXECUTE IMMEDIATE
  	 'SELECT MAX (NVL (LENGTH (text_value), 4000)) FROM ' || i_table_name INTO v_length;
    v_intIdx := 1;
    v_sql (v_intIdx) := 'SELECT project_id,businessobject_id,instance_name';
    OPEN v_ref FOR 'SELECT DISTINCT attribute_id FROM ' || i_table_name || ' ORDER BY attribute_id';
    LOOP
  	 FETCH v_ref INTO v_propid;
  	 EXIT WHEN v_ref%NOTFOUND;
  	 v_intIdx := v_intIdx +1;
  	 v_sql (v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
  	   || ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
  	   4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
  	   7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
  	   || v_length || '))"' || v_propid || '"';
    END LOOP;
    CLOSE v_ref;
    v_intIdx := v_intIdx +1;
    v_sql (v_intIdx) := ' FROM ' || i_table_name || ' GROUP BY project_id,businessobject_id,instance_name'
  	 || ' ORDER BY project_id,businessobject_id,instance_name';
    v_intCur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE
  	 (c		=> v_intCur,
  	  statement	=> v_sql,
  	  lb		=> 1,
  	  ub		=> v_intIdx,
  	  lfflg 	=> TRUE,
  	  language_flag => DBMS_SQL.NATIVE);
    v_intNumRows := DBMS_SQL.EXECUTE (v_intCur);
    v_ref := DBMS_SQL.TO_REFCURSOR (v_intcur);

---used for table creation with the above sql query structure start
v_format_table:= replace(i_table_name,'_T','_D');
  
    select count(*) into v_tab_count
    from user_tables 
    where table_name=v_format_table;
  
      if v_tab_count >=1 then
        execute immediate 'DROP TABLE ' ||v_format_table;
      end if;
      
      --Initialize the Temporary Clob variable to hold dynamic sql statments.
      v_intIdx := 1;
      v_createsql(v_intIdx) := 'CREATE TABLE '|| v_format_table;
     
      v_intIdx := v_intIdx + 1;
      v_createsql(v_intIdx) := chr(9) || ' AS '|| v_sql(v_intIdx);
      
      v_intCur := dbms_sql.open_cursor;
      dbms_sql.parse(  c => v_intCur,  statement => v_createsql,  lb => 1,  ub => v_intIdx,  lfflg => true,  language_flag => dbms_sql.native);
      v_intNumRows := dbms_sql.execute(v_intCur);
  
      dbms_sql.close_cursor(v_intCur);
---used for table creation with the above sql query structure end

    RETURN v_ref;
  END test_f_direct;
  /

Getting the below Error in line 70

dbms_sql.parse(  c => v_intCur1,  statement => v_createsql,  lb => 1,  ub => v_intIdx1,  lfflg => true,  language_flag => dbms_sql.native);

ORA-00928: missing SELECT keyword
ORA-06512: at "SYS.DBMS_SQL", line 1321
ORA-06512: at "SHARCHTEST2.DYN_TAB_CREATE_INSERT", line 70
ORA-06512: at "SHARCHTEST2.DYN_TAB_CREATE_INSERT", line 340
ORA-06512: at line 6

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #660158 is a reply to message #660157]

Fri, 10 February 2017 03:40

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

As always the rule for debugging dynamic sql is to output the variable containing the dynamic SQL so you can look at it and see if it makes sense.

I'll give you a clue - you're only using one row out of the v_sql array

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #660162 is a reply to message #660158]

Fri, 10 February 2017 05:08

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Yes.

It is taking from 'FROM clause' in the select statement. I kept the loop but still not able to keep. Please advice me on this.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #660163 is a reply to message #660162]

Fri, 10 February 2017 05:24

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well I wouldn't bother having two arrays, it's pointless complication.
I'd have one array, start it off with CREATE OR REPLACE and then append the rest of the needed SQL into it.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #660166 is a reply to message #660163]

Fri, 10 February 2017 08:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Design is seriously flawed.
When you CREATE TABLE (ab)using EXECUTE IMMEDIATE, then you can only access that table by (ab)using more dynamic SQL

Application objects should be static & fixed between application software version releases.

Please provide justification for creating new objects on the fly as part of normal application processing.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #660183 is a reply to message #660157]

Fri, 10 February 2017 15:56

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

As others have stated, I suspect that you may be doing things the hard way. However, the following contains enough minimal corrections to make it run. I have provided comments indicating new variables and section since the last demo I provided and different execution.

-- test table (test_tab) and test data (same as previous demo):

SCOTT@orcl_12.1.0.2.0> DESC test_tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEXT_VALUE                                         VARCHAR2(15)
 PROJECT_ID                                         NUMBER
 BUSINESSOBJECT_ID                                  NUMBER
 ATTRIBUTE_ID                                       NUMBER
 INSTANCE_NAME                                      VARCHAR2(15)
 ATTRIBUTE_TYPE_ID                                  NUMBER
 NUMBER_VALUE                                       NUMBER
 DATE_VALUE                                         DATE
 NOTE_VALUE                                         VARCHAR2(15)
 DROPDOWN_VALUE                                     VARCHAR2(15)
 BOOLEAN_VALUE                                      NUMBER
 REFERENCE_VALUE                                    NUMBER
 LINK_VALUE                                         VARCHAR2(15)
 DATETIME_VALUE                                     DATE
 PARENT_INSTANCE                                    NUMBER
 CHILD_INSTANCE                                     NUMBER

SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_tab
  2  /

TEXT_VALUE      PROJECT_ID BUSINESSOBJECT_ID ATTRIBUTE_ID INSTANCE_NAME
--------------- ---------- ----------------- ------------ ---------------
ATTRIBUTE_TYPE_ID NUMBER_VALUE DATE_VALUE      NOTE_VALUE      DROPDOWN_VALUE
----------------- ------------ --------------- --------------- ---------------
BOOLEAN_VALUE REFERENCE_VALUE LINK_VALUE      DATETIME_VALUE  PARENT_INSTANCE
------------- --------------- --------------- --------------- ---------------
CHILD_INSTANCE
--------------
textval1                 1                 1            1 instname1
                1            1 Fri 10-Feb-2017 noteval1        dropdown1
            1               1 linkval1        Fri 10-Feb-2017               1
             1

textval2                 2                 2            2 instname2
                2            2 Fri 10-Feb-2017 noteval2        dropdown2
            2               2 linkval2        Fri 10-Feb-2017               2
             2

textval3                 3                 3            3 instname3
                3            3 Fri 10-Feb-2017 noteval3        dropdown3
            3               3 linkval3        Fri 10-Feb-2017               3
             3

textval1                 4                 4            4 instname4
                4            4 Fri 10-Feb-2017 noteval4        dropdown4
            4               4 linkval4        Fri 10-Feb-2017               4
             4


4 rows selected.

-- function with minimal corrections:

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION test_f_direct
  2    (i_table_name IN VARCHAR2)
  3  	RETURN		SYS_REFCURSOR
  4  AS
  5    v_length 	NUMBER;
  6    v_intIdx 	PLS_INTEGER;
  7    v_sql		DBMS_SQL.VARCHAR2A;
  8    v_ref		SYS_REFCURSOR;
  9    v_propid 	NUMBER;
 10    v_intCur 	PLS_INTEGER;
 11    v_intNumRows	PLS_INTEGER;
 12  -- new variables:
 13    v_format_table	VARCHAR2(100);
 14    v_tab_count	NUMBER;
 15  BEGIN
 16    EXECUTE IMMEDIATE
 17  	 'SELECT MAX (NVL (LENGTH (text_value), 4000)) FROM ' || i_table_name INTO v_length;
 18  	 v_intIdx := 1;
 19  	 v_sql (v_intIdx) := 'SELECT project_id,businessobject_id,instance_name';
 20  	 OPEN v_ref FOR 'SELECT DISTINCT attribute_id FROM ' || i_table_name || ' ORDER BY attribute_id';
 21  	 LOOP
 22  	   FETCH v_ref INTO v_propid;
 23  	   EXIT WHEN v_ref%NOTFOUND;
 24  	   v_intIdx := v_intIdx +1;
 25  	   v_sql (v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
 26  	     || ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
 27  	      4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
 28  	      7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
 29  	      || v_length || '))"' || v_propid || '"';
 30  	 END LOOP;
 31  	 CLOSE v_ref;
 32  	 v_intIdx := v_intIdx +1;
 33  	 v_sql (v_intIdx) := ' FROM ' || i_table_name || ' GROUP BY project_id,businessobject_id,instance_name'
 34  	   || ' ORDER BY project_id,businessobject_id,instance_name';
 35  	 v_intCur := DBMS_SQL.OPEN_CURSOR;
 36  	 DBMS_SQL.PARSE
 37  	   (c		  => v_intCur,
 38  	    statement	  => v_sql,
 39  	    lb		  => 1,
 40  	    ub		  => v_intIdx,
 41  	    lfflg	  => TRUE,
 42  	    language_flag => DBMS_SQL.NATIVE);
 43  	 v_intNumRows := DBMS_SQL.EXECUTE (v_intCur);
 44  	 v_ref := DBMS_SQL.TO_REFCURSOR (v_intcur);
 45  -- new section:
 46  	 v_format_table:= REPLACE (i_table_name, '_T', '_D');
 47  	 SELECT COUNT (*) INTO v_tab_count FROM user_tables WHERE table_name = v_format_table;
 48  	 IF v_tab_count >= 1 THEN
 49  	   EXECUTE IMMEDIATE 'DROP TABLE ' || v_format_table;
 50  	 END IF;
 51  	 v_sql(1) := 'CREATE TABLE ' || v_format_table || ' AS ' || v_sql(1);
 52  	 v_intCur := DBMS_SQL.OPEN_CURSOR;
 53  	 DBMS_SQL.PARSE
 54  	   (c		  => v_intCur,
 55  	    statement	  => v_sql,
 56  	    lb		  => 1,
 57  	    ub		  => v_intIdx,
 58  	    lfflg	  => TRUE,
 59  	    language_flag => DBMS_SQL.NATIVE);
 60  	 v_intNumRows := DBMS_SQL.EXECUTE (v_intCur);
 61  	 DBMS_SQL.CLOSE_CURSOR (v_intCur);
 62  -- end of new section
 63    RETURN v_ref;
 64  END test_f_direct;
 65  /

Function created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.

-- exection (cannot execute from within SQL select statement due to DML, so use PL/SQL):

SCOTT@orcl_12.1.0.2.0> -- declare variable for results:
SCOTT@orcl_12.1.0.2.0> VARIABLE g_ref REFCURSOR
SCOTT@orcl_12.1.0.2.0> -- execute function:
SCOTT@orcl_12.1.0.2.0> BEGIN :g_ref := test_f_direct ('TEST_TAB'); END;
  2  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> -- display ref cursor returned:
SCOTT@orcl_12.1.0.2.0> PRINT g_ref

PROJECT_ID BUSINESSOBJECT_ID INSTANCE_NAME   1        2        3        4
---------- ----------------- --------------- -------- -------- -------- --------
         1                 1 instname1       textval1
         2                 2 instname2                2
         3                 3 instname3                         Fri 10-F
         4                 4 instname4                                  noteval4

4 rows selected.

SCOTT@orcl_12.1.0.2.0> --show new table created (test_dab) and data:
SCOTT@orcl_12.1.0.2.0> DESC test_dab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROJECT_ID                                         NUMBER
 BUSINESSOBJECT_ID                                  NUMBER
 INSTANCE_NAME                                      VARCHAR2(15)
 1                                                  VARCHAR2(8)
 2                                                  VARCHAR2(8)
 3                                                  VARCHAR2(8)
 4                                                  VARCHAR2(8)

SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_dab
  2  /

PROJECT_ID BUSINESSOBJECT_ID INSTANCE_NAME   1        2        3        4
---------- ----------------- --------------- -------- -------- -------- --------
         1                 1 instname1       textval1
         2                 2 instname2                2
         3                 3 instname3                         Fri 10-F
         4                 4 instname4                                  noteval4

4 rows selected.

Report message to a moderator

Re: PL/SQL: numeric or value error: character string buffer too small [message #660270 is a reply to message #660183]

Tue, 14 February 2017 00:16

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Thank you very much All. Now it is working fine.

Really I am learning so much things from orafaq site.

Report message to a moderator

Previous Topic:	Counting minutes in a date/time range
Next Topic:	What is Unknown in logical operator

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 17:23:43 CDT 2024