Skip to Content
0

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

Feb 07, 2017 at 11:10 PM

542

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Lars Breddemann
Feb 07, 2017 at 11:49 PM
1

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...

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Thanks Lars. It worked like a charm. Fantastic. Appreciate your help

0

Is there any way to use the same store procedure and its logic in the content section of HANA. I tried but in vain. Can you please let me know the logic to implement the above solution in HANA content section

0

HI Ketan

please don't add question to an existing one. Instead please post a new question.

Cheers,

Lars

0

dynamic SQL doesn't work in content folder. Can someone help some way to use dynamic sql procedure in content section

0