How to assign variables using dynamic sql

Question

I need to assign two variables(tmp_x and tmp_y) with dynamic sql, because I need to select the right table at runtime. the sql as below:

 updateSql:= 'select p.gis_x,p.gis_y into tmp_x,tmp_y from  publish_'
             ||splitCollection(indexs).city_no ||'.t_customer p 
             where p.customer_id=:1 and p.gis_x is not null and p.gis_y is not null';

 execute immediate updateSql using splitCollection(indexs).CUSTOMER_ID;

the compilation is OK ,but occur the runtime error about "lack of keyword", how can I fix that?

"My car is not working, please tell me how I can make it work." Do you see the problem with that request - and the analogy to the way you asked your question? HOW is it "not working"? What happens - do you get a compilation error? A runtime error? Does it run but produces no output, or the wrong output? — user5683823
– user5683823, Commented Oct 27, 2017 at 4:00
@mathguy sorry about My poor English, compilation is OK, and runtime error is "lack of keyword" in Chinese, I don't know if I translate right about it — Jswq
– Jswq, Commented Oct 27, 2017 at 4:33
OK, so the SQL SELECT statement generated in the code appears to be an invalid SQL query. Instead of "execute immediate", export the statement as text (use DBMS_OUTPUT.PUT_LINE(updateSql)) and inspect it and/or try to run it. Do you get the same error? Post the exact string updateSql here for more help. — user5683823
– user5683823, Commented Oct 27, 2017 at 4:41
I think you're just missing returning into clause. Remove that from the select statement into tmp_x,tmp_y and add it like so: execute immediate updateSql using splitCollection(indexs).CUSTOMER_ID into tmp_x,tmp_y;. — g00dy
– g00dy, Commented Oct 27, 2017 at 5:02

g00dy · Accepted Answer · 2017-10-27 06:28:05Z

1

So, following the comments:

This:

 updateSql:= 'select p.gis_x,p.gis_y into tmp_x,tmp_y from  publish_'
             ||splitCollection(indexs).city_no ||'.t_customer p 
             where p.customer_id=:1 and p.gis_x is not null and p.gis_y is not null';

 execute immediate updateSql using splitCollection(indexs).CUSTOMER_ID;

Needs to become:

 updateSql:= 'select p.gis_x,p.gis_y from  publish_'
             ||splitCollection(indexs).city_no ||'.t_customer p 
             where p.customer_id=:1 and p.gis_x is not null and p.gis_y is not null';

 execute immediate updateSql using splitCollection(indexs).CUSTOMER_ID RETURNING into tmp_x,tmp_y;

The difference is the into clause, which, when used with execute immediate should go in the actual statement and not be part of the Select statement.

Cheers

answered Oct 27, 2017 at 6:28

g00dy

6,7982 gold badges33 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to assign variables using dynamic sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related