Re: plsql err

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Wed, 25 Jan 2006 00:14:37 +0200
Message-ID: <6e49b6d00601241414n647d2878rdeb6de4454ae5adf@mail.gmail.com>

table name cannot be aused in such way i.e. as bind variable. Here is link I'v already sent privately, but it seems it would'n hurt also to public :)
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:227413938857

here is also code snippet:

00:10:57 gints@> create table a1(a number);

Table created.

Elapsed: 00:00:00.07

00:11:19 gints@> declare
00:11:42   2    tb varchar2(2) := 'a1';
00:11:42   3    nm number := 1;
00:11:42   4  begin
00:11:42   5    execute immediate 'insert into :a values (:b)' using tb, nm;
00:11:42   6  end;
00:11:43   7  /

declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 5

Elapsed: 00:00:00.01
00:11:44 gints@> ed
Wrote file afiedt.buf

  1 declare
  2 tb varchar2(2) := 'a1';
  3 nm number := 1;
  4 begin
  5 execute immediate 'insert into a1 values (:b)' using nm;   6* end;
00:11:59 gints@> /

PL/SQL procedure successfully completed.

Gints

On 1/24/06, GovindanK <gkatteri_at_fastmail.fm> wrote:
> You would generate sql commands on the fly ; Subsequently you can do
> either of the following:
> 1. Either spool them out and run them separately
> 2. Store them in a varchar2 variable and execute the string
> 3. Execute immediate .. update :tname set :cname = :value using parm1 ,
> parm2 , parm3.
>
> HTH
>
> GovindanK
> Certified DBA
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 24 2006 - 16:14:37 CST