CODEWITHSUNDEEP
sqloracle-databasedynamic-sql
Sunitha
Sunitha

Reputation: 135

Dynamic query in Cursor for loop

I am facing one issue with Dynamic query.I am trying to update one update statement by using dynamic SQL in cursor for loop.

Can you please help me how to execute this...

declare
statement varchar2(1000);
begin
for c1 in(select tenant_id from tenant where tenant_id!=0)
 loop
   for c2 in ( select alignment_id from customer_alignment where affiliation_id is null and tenant_id=c1.tenant_id)
     loop
        insert into t3 select 1 from dual;
         for c3 in ( select *
                     from ca_primary_address ca,customer_alignemnt ca where ca.affiliation_id is null
                     and ca.alignment_id=c2.alignment_id
                     and ca.customer_id=vw.customer_id
                     and ca.alignment_id=vw.alignment_id

                    )
               loop
                   statement :='update customer_alignment set affiliation_id='||vw.affiliation_id||' where customer_alignment_id='||customer_alignment_id||';'
                    execute immediate('begin '||STATEMENT||' end;');
               end loop;

     end loop;
        dbms_output.put_line('Tenant_id '||c1.tenant_id);
 end loop;
end;

Am getting below error msg while executing

ORA-06550: line 8, column 10: PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names

Please help me on this issue.

Many Many thanks for your help.

Sunitha..

Upvotes: 0

Views: 558

Answers (1)

DirkNM
DirkNM

Reputation: 2664

Try the following:

...
statement :='update customer_alignment set affiliation_id='||c3.affiliation_id||' where customer_alignment_id='||c3.customer_alignment_id;
execute immediate(statement);
...

You have to qualify your variables used in the update query string with the cursor variable.

Upvotes: 1

Related Questions