dynamis sql UPDATE statement with join

Hi,

I don't understand following, Just wonder if it is possible to do what I am trying to do.

I have following procedure:

PROCEDURE TEST_V_PROC
        ( table_name IN varchar2)
   is

        sqlstmt varchar2(10000):= '';
   BEGIN         sqlstmt := 'UPDATE ' || table_name || ' a set
(

ADDR1
)
=
(

select b.addr1
from gcd_dw.lb_individuals_eu_vw b
where b.individual_id = a.individual_id
)';

        insert into logs values(sysdate,sqlstmt);
        execute immediate 'commit';

        execute immediate sqlstmt;
        execute immediate 'commit';

END; -- Procedure

This procedure takes table_name and tries to execute the UPDATE statement on it.
When I run it get following error:

ORA-00942: table or view does not exist ORA-06512: at "schamaname.TEST_V_PROC", line 21

line 21: execute immediate sqlstmt;

for test I put the insert statement before to insert to logs table the created sqlstmt.
It looks OK, and runs fine:
UPDATE LMXXXXX_UNIQUE_CONTACTS a set
(

ADDR1
)
=
(

select b.addr1
from gcd_dw.lb_individuals_eu_vw b
where b.individual_id = a.individual_id
)

What is the problem here?

Is it not possible to run the above update from within PL/SQL procedure?

Thanks for any hints how to do that
chris Received on Tue Jun 19 2007 - 08:23:48 CDT