469,268 Members | 1,017 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,268 developers. It's quick & easy.

Dynamic SQL in table SQL UDFs

In a DB2 V8.1 FP4 database I am trying to create a table SQL UDF that
is to return a contents of a temporary table with in this UDF:
create function getitemdata(pint int)
returns table
(
hostid smallint,
owid bigint
)
specific getitemdata
MODIFIES SQL DATA
NO EXTERNAL ACTION
begin atomic
declare v_temp_sql varchar(256);

set v_temp_sql =
'declare global temporary table '||
'item_data_tmp '||
'( '||
' hostid smallint, '||
' owid bigint '||
') '||
'on commit preserve rows not logged with replace' ;

execute immediate v_temp_sql;

<...inserting into session.item_data_tmp here...>

return select * from session.item_data_tmp;
end
@

It fails returning:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "execute immediate v_temp_sql;
<...inse" was
found following "mpuser_8k_01' ; ". Expected tokens may include:
"<space>". LINE NUMBER=25. SQLSTATE=42601

Are dinamic SQL statements (EXECUTE/PREPARE) not allowed in table SQL
UDFs (FP4)? Any workaround?

Thanks,
-Eugene
Nov 12 '05 #1
1 2190
No can do. Only the statements listed in "Compound Statement (Dynamic)"
are allowed in inline SQL PL. The idea is to keep it small and slick.
I would propose to use a persistent table and some unique "token" to
privatize access to the table by session.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Ralph | last post: by
7 posts views Thread by Yaro | last post: by
3 posts views Thread by Alexandre H. Guerra | last post: by
5 posts views Thread by jcccs05 | last post: by
12 posts views Thread by Serman D. | last post: by
reply views Thread by Helmut Tessarek | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.