Skip to Content

SAP DBTech JDBC: [359]: string is too long [359] (range 3) string is too long exception

I am getting following error when I call my stored proc. I made changes to the datatype but not helping much. The goal is to create a big long SQL on the fly joining 19 tables.

I am getting following error

Here is the stored Proc

createprocedure sapecd.dynamic_tables() language sqlscript as

ctr integer;

vl_cnt integer;

sql_query_union nvarchar(5000);

v2 nvarchar(500);

begin

ctr:=1;

sql_query_union:='';

selectcount(*) into vl_cnt FROM TABLES WHERE schema_name like'SAPEC%'AND table_name like'/1FC/0ZSSM47A%';

WHILE ctr < :vl_cnt DO

select sqlquery into v2 from (

SELECT table_name,schema_name, '(select * from ' || schema_name || '."' || table_name || '")'as sqlquery, row_number() over (orderby table_name) as row_num FROM TABLES

WHERE schema_name like'SAPEC%'AND table_name like'/1FC/0ZSSM47A%') where row_num=:ctr;

sql_query_union := sql_query_union || v2 || ' union all ';

ctr:=ctr+1;

ENDWHILE;

select sqlquery into v2 from (

SELECT table_name,schema_name, 'select * from ' || schema_name || '."' || table_name || '"'as sqlquery, row_number() over (orderby table_name) as row_num FROM TABLES

WHERE schema_name like'SAPEC%'AND table_name like'/1FC/0ZSSM47A%') where row_num=:vl_cnt;

-- sql_query_union := sql_query_union || v2;

sql_query_union := v2;

EXEC sql_query_union;

end

qh0mc.jpeg (18.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 07, 2017 at 11:49 PM

    I guess you run into the error because of how you manage your while loop counter ctr. If you want to access the current value you need to use the colon.

    WHILE :ctr < :vl_cnt DO
      ...
    ctr:=:ctr+1;
    ENDWHILE;

    But there is a better way to iterate over a result set. See my example code below:

    create procedure all_users() as header only;
    alter procedure all_users() 
    as
    begin
    declare sqlcmd nvarchar(5000) :='';
    declare cursor mytabs for 
        select  to_nvarchar('(select * from "' || schema_name || '"."' || table_name || '")') as seltext
        from tables
        where table_name like 'CUSERS_';
     for c as mytabs do
        if sqlcmd = '' then
            sqlcmd := c.seltext;
        else
            sqlcmd := :sqlcmd ||' UNION ALL ' || c.seltext ;
        end if;
     end for;
     select sqlcmd from dummy;
     execute immediate :sqlcmd;
    end;

    In my example, there are 8 different CUSERSx tables (CUSER1 ... CUSER9).

    The cursor creates a "SELECT * FROM <schema>.<table>" string for each one of them and concatenates them via UNION ALL with the already existing sqlcmd string.

    Finally, it selects the string from dummy (so that you can review it) and runs and executes it.

    No need for window functions here or complicated loop handling.

    What's left is the question: how are you going to use the result from your EXEC/EXEC IMMEDIATE call?

    Dynamic SQL results cannot be assigned to table variables...

    Add comment
    10|10000 characters needed characters exceeded