Re: Newbie: 'identifier must be declared' when deleting from table

From: TurkBear <john.greco_at_dot.state.mn.us>
Date: Wed, 16 Jul 2003 12:52:29 -0500
Message-ID: <ms3bhv0nm71lshb8ue1eatfshf46t1fns2@4ax.com>

"Ian McCall" <ian_at_eruvia.org> wrote:

>Hello.
>
>I'm looking at Oracle after having used Sybase for a few years. I'm trying
>to put a script together that deletes from a table where a particular field
>is today's date in YYYYMMDD format.
>
>The SQL I'm using is:
>
>declare
> v_today varchar(8);
>begin
> v_today := to_char(sysdate, 'YYYYMMDD');
> delete from target_table where archive_date = v_today;
>end;
>
>I always get the following error:
> PLS-00201: identifier 'target_table' must be declared
>
>Obviously, target_table is a table and isn't meant to be a declared
>variable. Could someone please point out what I'm doing wrong and how to fix
>the syntax of this?
>
>
>Thanks in advance for any information,
>Ian
>

Be sure you are logged in the owner of that table ( or that a public synonym exists). Otherwise, the code looks good - my example:

SQL> select * from drop_me;

Test1
Test2
Test3

SQL> declare
  2 v_today varchar(8);
  3 begin
  4 v_today := to_char(sysdate,'YYYYMMDD');   5 delete from drop_me where F1 <> v_today;   6 end;
  7 /

PL/SQL procedure successfully completed.

SQL> select * from drop_me;

no rows selected

SQL>

Received on Wed Jul 16 2003 - 12:52:29 CDT