CODEWITHSUNDEEP
oracle-databasevariablesdynamic-sql
Jswq
Jswq

Reputation: 776

How to assign variables using dynamic sql

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?

Upvotes: 0

Views: 387

Answers (1)

g00dy
g00dy

Reputation: 6778

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

Upvotes: 1

Related Questions