Dynamic JSON Issue With Long Select Names

Bill
2012-10-16
2013-05-28
  • Bill
    Bill
    2012-10-16

    Hello,
    Thank you for all your hard work!
    I have come across an issue, and a resolution for the json dynamic package. The problem occurs when a select item exceeds 30 characters (combination of column names and functions). It is easiest described in an example:

    set serveroutput on;
    drop table data_modelers_love_long_names;
    create table data_modelers_love_long_names (
     long_column_names_also_loved varchar2(30)
    );
    insert into data_modelers_love_long_names values(' test');
    commit;
    declare
      ret json_list;
    begin
      ret := json_dyn.executeList('select ltrim(long_column_names_also_loved) from data_modelers_love_long_names');
      ret.print;
    end;
    /
    

    Table created.
    1 row created.
    Commit complete.

    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: dbms_sql.describe_columns overflow,
    col_name_len=35. Use describe_columns2
    ORA-06512: at "SYS.DBMS_SQL", line 1938
    ORA-06512: at "VMDBDEV.JSON_DYN", line 64
    ORA-06512: at line 4

    The fix is to follow the advice in the ERROR message.
    Don`t Ask: rcsdiff -r1.1 -r1.2  json_dyn.sql
    ===================================================================
    RCS file: RCS/json_dyn.sql,v
    retrieving revision 1.1
    retrieving revision 1.2
    diff -r1.1 -r1.2
    100c100
    <     l_dtbl dbms_sql.desc_tab;
    --
    >     l_dtbl dbms_sql.desc_tab2;
    119c119
    <     dbms_sql.describe_columns(l_cur, l_cnt, l_dtbl);
    --
    >     dbms_sql.describe_columns2(l_cur, l_cnt, l_dtbl);
    194c194
    <     l_dtbl dbms_sql.desc_tab;
    --
    >     l_dtbl dbms_sql.desc_tab2;
    215c215
    <     dbms_sql.describe_columns(l_cur, l_cnt, l_dtbl);
    --
    >     dbms_sql.describe_columns2(l_cur, l_cnt, l_dtbl);

    After mod and recompile,retest is good:

    [{
    "LTRIM(LONG_COLUMN_NAMES_ALSO_LOVED)" : "test"
    }]

    Cheers