Thanks for the question, vishnudas.

Asked: January 12, 2019 - 9:45 am UTC

Last updated: January 18, 2019 - 3:01 pm UTC

Version: 11.2.0.1.0

Viewed 10K+ times! This question is

You Asked

HI happy new year...

i have a procedure which executes a big big big query...i will share the behavior of my procedure

CREATE OR REPLACE PROCEDURE DUMMY_PROC(DATASET OUT SYS_REFCURSOR,FSID NUMBER) as
STR VARCHAR2(32000);
BEGIN
STR:=' SELECT (SOME COLUMNS FROM SOME MULTIPLE TABLES) WHERE D_ID= '||FSID||' '  

DBMS_OUTPUT.ENABLE(1000000) ;
DBMS_OUTPUT.PUT_LINE ( STR ) ;
OPEN DATASET FOR STR ;
END DUMMY_PROC;

The given procedure has the same functionality with my original proc(it has 1245 lines in total)
but when i execute the procedure i'am getting the following error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

If am right the maximum size for varchar2 datatype is 32767.But my dynamic query exceeds this limit, is there any way to accomplish this??

and Connor said...

You can just use a clob, eg

SQL> create table t ( x int );

Table created.

--
-- 500 concatenations less than 32k, so its ok
--
SQL> declare
  2    c varchar2(32700);
  3    rc sys_refcursor;
  4  begin
  5    c := 'select * from t where 1=1';
  6    for i in 1 .. 500
  7    loop
  8      c := c || 'union all select * from t where 1=1';
  9    end loop;
 10    open rc for c;
 11  end;
 12  /

PL/SQL procedure successfully completed.

--
-- 1000 concatenations takes us over the limit
--

SQL> declare
  2    c varchar2(32700);
  3    rc sys_refcursor;
  4  begin
  5    c := 'select * from t where 1=1';
  6    for i in 1 .. 1000
  7    loop
  8      c := c || 'union all select * from t where 1=1';
  9    end loop;
 10    open rc for c;
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8

--
-- but a clob is fine
--

SQL> declare
  2    c  clob;
  3    rc sys_refcursor;
  4  begin
  5    c := 'select * from t where 1=1';
  6    for i in 1 .. 1000
  7    loop
  8      c := c || 'union all select * from t where 1=1';
  9    end loop;
 10    open rc for c;
 11  end;
 12  /

PL/SQL procedure successfully completed.

But I stress....you've fixed that problem, but do you really want to be throwing SQL statements that are more then 32k long at the database? That's the same as a 10 page document...

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

vishnudas, January 14, 2019 - 7:32 am UTC

it is a reporting proc for a whole month with different condition ,That is why it is huge.
Anyway thank you for your support. i have fixed the problem at ease.

January 16, 2019 - 2:52 am UTC

glad we could help

or with view

Racer I., January 17, 2019 - 7:04 am UTC

Hi,

If your select is static you can also put it into a view and then select * from view where id = ? in your procedure or directly in your client without going through a procedure.

regards,

January 18, 2019 - 3:01 pm UTC

Yep, views are a great way to hide complexity. It's also worth checking if this really needs dynamic SQL instead of a static SQL statement.

Send to Mail

Muhammad Mehedi Hasan, February 10, 2019 - 3:59 am UTC

I want to send mailing those data. but I can't. clob does not support. Can any body help to me.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

or with view

Send to Mail

More to Explore

PL/SQL demos

PL/SQL docs

Getting ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

or with view

Send to Mail

More to Explore

PL/SQL demos

PL/SQL docs