cancel
Showing results for 
Search instead for 
Did you mean: 

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

ketan_pavle
Associate
Associate
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor

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

ketan_pavle
Associate
Associate
0 Kudos

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

ketan_pavle
Associate
Associate
0 Kudos

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

ketan_pavle
Associate
Associate
0 Kudos

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

lbreddemann
Active Contributor
0 Kudos

HI Ketan

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

Cheers,

Lars

Answers (0)