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

"Ian McCall" <ian (AT) eruvia (DOT) org> wrote:

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;

F1
----------
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>
----------------------------------------------------------------------------------

Ian McCall wrote:

It appears that you don't own the table. You must, in this case, use the fully
qualified object name which is:

schema_name dot table_name.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

"Daniel Morgan" <damorgan (AT) exxesolutions (DOT) com> wrote

to a field that in all likelyhood is a date?
begin
delete from target_table where archive_date> trunc(sysdate) and
archive_date<trunc(sysdate+1) ;
end;

Jim Kennedy wrote:

Good point ... dates in Oracle are not strings.

Do a DESCribe on the table.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

"Jim Kennedy" <kennedy-down_with_spammers (AT) no_spam (DOT) comcast.net> wrote in
message news:rpoRa.79756$ye4.58653 (AT) sccrnsc01 (DOT) ..
The field is an existing table, and is a varchar(8). It forms part of a
primary key, which is why dates were avoided (don't want to accidently add
timing resolution beyond that of one day).


Cheers,
Ian

Should be a date. Leaving it at midnight is perfectly acceptable. Dates
only take 7 bytes and give you flexibility to treat them as dates.
Jim

"Ian McCall" <ian (AT) eruvia (DOT) org> wrote

"Jim Kennedy" <kennedy-down_with_spammers (AT) no_spam (DOT) comcast.net> wrote in
message news:LetRa.81709$H17.25850 (AT) sccrnsc02 (DOT) ..
Understand your point, and I'd normally agree. The problem I'm trying to
avoid is catching bad coding that could mess up the primary key however.

With archive_date as a date a user could run insert into target_table
values(someValue, sysdate) and it would work. sysdate now has a resolution
including a time component, which means they could run that statement again
and it would work, rather than faily with a duplicate key error (the desired
behaviour). Enforcing varchar(8) does mean I could potentially get problems
like (someValue, 'herring') working instead of the YYYYMMDD I expected, but
at least it won't allow two values for the same day.


Cheers,
Ian

"Ian McCall" <ian (AT) eruvia (DOT) org> wrote

I'd deal with this with a check constraint

SQL> create table tab1(
2 col1 varchar2(200),
3 archive_date date);

Table created.

SQL> alter table tab1 add constraint pk_tab1 primary key (archive_date);

Table altered.

SQL> alter table tab1 add constraint ck_archdate
check(archive_date=trunc(archive_date));

Table altered.

SQL>
SQL> insert into tab1 values('somevalue',sysdate);
insert into tab1 values('somevalue',sysdate)
*
ERROR at line 1:
ORA-02290: check constraint (NIALL.CK_ARCHDATE) violated


SQL>
SQL> spool off

I assume Dan's answer about not owning the table answered your original
question


--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Ian McCall" <ian (AT) eruvia (DOT) org> wrote