DBMS_SQL.COLUMN_VALUE throwing - > PL/SQL: numeric or value error: character string buffer too small error

Question

I am trying to dynamically execute a SQL in anonymous block and fetch the records using ':::' delimiter for external program. It is failing when more than 13-15 records are fetched.

declare
    mysql varchar2(3000) := 'select sid,username,status,event,p1,p2,p3 from v$session where rownum<30';
    col DBMS_SQL.DESC_TAB;
    data varchar2(4000);
    state boolean;
        function sqlformat ( col in DBMS_SQL.DESC_TAB, col_cnt number, indx number) return varchar2 AS
            begin
             if indx = col_cnt then
                        data := col(indx).col_name;
            else
                        data := col(indx).col_name||':::';
            end if;
             return data;
            end sqlformat;
        function sqlexecute  ( mysql IN varchar2) return boolean AS
            handle number;
            col_cnt number;
            col DBMS_SQL.DESC_TAB;
            v_exec number;
            output varchar2(4000);
        begin
           handle := DBMS_SQL.OPEN_CURSOR;
           DBMS_SQL.PARSE(handle, mysql, DBMS_SQL.NATIVE);
           v_exec := DBMS_SQL.EXECUTE(handle);
           DBMS_SQL.DESCRIBE_COLUMNS(handle, col_cnt, col);
           FOR indx in 1..col_cnt
            loop
               data := sqlformat(col,col_cnt,indx);
               DBMS_SQL.define_column(handle, indx, col(indx).col_name,4000);
               output := output||data;
            end loop;
           dbms_output.put_line(output);
           dbms_output.NEW_LINE();
            while DBMS_SQL.fetch_rows(handle) > 0
           loop
               state:=True;
               output := NULL;
                data :=NULL;
                FOR indx in 1..col_cnt
               loop
                DBMS_SQL.COLUMN_VALUE(handle, indx, col(indx).col_name);
                data := sqlformat(col,col_cnt,indx);
                output := output||data;
                dbms_output.NEW_LINE();
            end loop;
           dbms_output.put_line(output);
            end loop;
           return state;
           end;
    begin
    state :=sqlexecute(mysql);
    end;
/

`SID:::USERNAME:::STATUS:::EVENT:::P1:::P2:::P3`
`1::::::ACTIVE:::rdbms ipc message:::100:::0:::0`
`3::::::ACTIVE:::rdbms ipc message:::300:::0:::0`
`4::::::ACTIVE:::rdbms ipc message:::300:::0:::0`
`5::::::ACTIVE:::DIAG idle wait:::3:::1:::0`
`6::::::ACTIVE:::watchdog main loop:::0:::20:::0`
`7::::::ACTIVE:::rdbms ipc message:::180000:::0:::0`
`8::::::ACTIVE:::rdbms ipc message:::100:::0:::0`
`9::::::ACTIVE:::rdbms ipc message:::300:::0:::0`
`10::::::ACTIVE:::ASM background timer:::0:::0:::0`
`11::::::ACTIVE:::Space Manager: slave idle wait:::1:::0:::0`
`13::::::ACTIVE:::class slave wait:::0:::0:::0`
`15::::::ACTIVE:::rdbms ipc message:::30000:::0:::0`
`17::::::ACTIVE:::Space Manager: slave idle wait:::2:::0:::0`

declare * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SYS.DBMS_SQL", line 1795 ORA-06512: at line 41 ORA-06512: at line 51

Massi FD · Accepted Answer · 2022-08-02 09:10:08Z

0

I suggest to try insert substr in each string objects to understand which variabile get the error:

data   := substr(col(indx).col_name,1,4000);
data   := substr(col(indx).col_name||':::',1,4000);
output := substr(output||data,1,4000);
output := substr(output||data,1,4000);

Thank you

answered Aug 2, 2022 at 9:10

Massi FD

3984 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hari Over a year ago

Thanks for the response. The data and output in each iteration is less than 200 bytes. Its not the issue i think.

Collectives™ on Stack Overflow

DBMS_SQL.COLUMN_VALUE throwing - > PL/SQL: numeric or value error: character string buffer too small error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related