Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: dynamis sql UPDATE statement with join

Re: dynamis sql UPDATE statement with join

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Tue, 19 Jun 2007 06:41:41 -0700
Message-ID: <1182260501.311355.232480@u2g2000hsc.googlegroups.com>


On 19 jun, 15:23, ciapecki <ciape..._at_gmail.com> wrote:
> 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

"ORA-00942: table or view does not exist ORA-06512"

Grants through role perhaps?

HTH. Cheers.

Carlos. Received on Tue Jun 19 2007 - 08:41:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US