VARCHAR2(32767) not able to handle strings in stored procedure

Question

I am concatenating string using cursor (to form query to execute later). Here, the query that will be formed is going to be way bigger that what VARCHAR2(32767) can handle. There fore, I am getting error on proc execution - ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

I used CLOB data type as well bu got error ORA-06502: PL/SQL: numeric or value error.

My code is here below:

CREATE OR REPLACE PROCEDURE sp_Market
IS
   Names VARCHAR2(32767);    
BEGIN
   DECLARE CURSOR cur IS  ('Select ID, Order_of, field_name  
   FROM   pld_medicare_config');
   BEGIN       
   FOR i IN cur
       LOOP
           Names := Names ||  i.sqql;
       END LOOP;    
   dbms_output.put_line(Names);
   END;   
END sp_Market;

How can I handle my string of queries and what data type is there to accomplish the task?

Answer 1

CLOB is OK (as far as I can tell); I doubt queries you store in there are that big.

Remove dbms_output.put_line call from the procedure; I suspect it is the one that raises the error.

Answer 2

I'm not sure how you got any runtime error, as your procedure won't compile.

The valid PL/SQL version would look something like this:

create or replace procedure sp_market is
    names varchar2(32767);
begin
    for r in (
        select id, order_of, field_name
        from   pld_medicare_config
    )
    loop
        names := names || ' ' || r.field_name;
    end loop;

    names := ltrim(names);

    dbms_output.put_line(names);
end sp_market;

If names needs to be longer, change the datatype to clob.

Answer 3

Use the CLOB datatype and append data using the dbms_lob.writeappend procedure. This is the reference (Oracle 18c).

The error probably origins with the dbms_output.put_line call. The procedure is defined for varchar2 arguments only which means that an implicit conversion takes place during the call. It will fail for clob contents longer than 32767 chars/bytes.

Alternatively you may declare a collection over varchar2(4000) and fill the collection elements sequentially:

CREATE OR REPLACE PROCEDURE sp_Market
IS
   TYPE tLongString IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
   cNames  tLongString;    
BEGIN
   DECLARE CURSOR cur IS Select ID, Order_of, field_name, sqql FROM pld_medicare_config;
   BEGIN       
   FOR i IN cur
       LOOP
           cNames(cNames.COUNT+1) := i.sqql;
       END LOOP;    
   END;   
END sp_Market;

Note

Rectified code, will compile now.