How to insert into table where variable -1

Question

I'm having problems while trying to insert into a table by selecting from another table with the dates as a condition. The date will be retrieved from another table and not from Oracle, because the application stores its own system date (i.e. today can be 10 years into the future - 20201124). Then, the condition for the date is where ASOF = TODAY-1 and DATE between TODAY and TODAY +1.

Here is what I tried to do:

declare
   v_TODAY number;
begin 
   select TODAY into v_TODAY from LOCATION;
   execute immediate 'truncate table table_EOD';
   execute immediate 'insert into table_EOD (key1, key2, ASOF, IDATE)
   select key1, key2, ASOF, IDATE 
      from table
      where ASOF = to_char(to_date(v_TODAY)-1) 
      and DATE between v_TODAY and to_char(to_date(v_TODAY)+1)';
end;
/

The error that I've received is:

Error report:
ORA-00904: "V_TODAY": invalid identifier
ORA-06512: at line 6
00904. 00000 -  "%s: invalid identifier"

Can you let me know what's wrong? Thanks.

Your question is about Oracle more than variables. Please select specific tags when possible. — assylias, Nov 15 '12 at 08:41
This is going to fail badly if you have more than one row in the table `LOCATION`. Also why don't you use a real `DATE` column for "today"? — , Nov 15 '12 at 08:43

Ben · Answer 1 · 2012-11-15T09:03:43.060

The error you've got is correct v_today is not a valid identifier within the scope of the EXECUTE IMMEDIATE statement. You should be using a bind variable instead:

declare
   v_TODAY number;
begin 
   select TODAY into v_TODAY from LOCATION;
   execute immediate 'truncate table table_EOD';
   execute immediate 'insert into table_EOD (key1, key2, ASOF, IDATE)
   select key1, key2, ASOF, IDATE 
      from table
      where ASOF = to_char(to_date(:1)-1) 
      and DATE between :1 and to_char(to_date(:1)+1)'
     using v_today, v_today, v_today;
end;
/

Two further points. Firsly, if you have more than one row in LOCATION then your select into ... will not work. This is because v_today can only hold one value. The exception TOO_MANY_ROWS will be raised; you will have selected too many rows!

Secondly, there's no need for the second EXECUTE IMMEDIATE. You only have to do DML in an execute immediate if an object you're referencing doesn't exist prior to the compilation of the block or if you're dynamically generating the SQL.

You still have to do the truncate inside and EXECUTE IMMEDIATE statement as this is DDL. This leaves you with the following:

declare
   v_TODAY number;
begin 

   select TODAY into v_TODAY from LOCATION; -- Still wrong!
   execute immediate 'truncate table table_EOD';

   insert into table_EOD (key1, key2, ASOF, IDATE)
   select key1, key2, ASOF, IDATE 
     from table
    where ASOF = to_char(to_date(v_TODAY)-1) 
      and DATE between v_TODAY and to_char(to_date(v_TODAY)+1);
end;
/

Okay, from your comment I've just realised that you actually want today not some random date and that you're using a number as a date in LOCATION, which is the cause of your error. Why don't you just use Oracle's [sysdate] for today's date?

declare
   v_TODAY date := sysdate;
begin 

   execute immediate 'truncate table table_EOD';

   insert into table_EOD (key1, key2, ASOF, IDATE)
   select key1, key2, ASOF, IDATE 
     from table
    where ASOF = trunc(sysdate) - 1
      and DATE between trunc(v_TODAY) and trunc(v_TODAY) + 1;
end;
/

If you want to use your own dates you will have to work out a way of putting a date instead of a number in LOCATION.

score 0 · Answer 2 · answered Nov 15 '12 at 08:52

0

No need for PL/SQL or dynamic SQL here.

Something like this should do it:

truncate table table_eod;

insert into table table_eod (key1, key2, asof, idate)
with today_data as (
   select today as v_today
   from location
)
select t.key1, t.key2, t.ASOF, t.IDATE 
from table t
  join today_data td on t.ASOF = to_char(to_date(td.v_TODAY)-1) 
      and DATE between td.v_TODAY and to_char(to_date(td.v_TODAY)+1);

I'm not sure if this will work correctly if the table LOCATION contains more than one row though.

answered Nov 15 '12 at 08:52

After removing the execute immediate or using the above, I am getting this instead. Could it be a problem with my usage of to_date or to_char? P/s: select TODAY from LOCATION where current = 'Y'; gives 20121124 This is because the users of the system has the ability to control dates in the system (e.g. the system can process data as if 10 years into the future). Error report: ORA-01861: literal does not match format string – feef24 Nov 15 '12 at 09:16
That is why I need to do this to_char(to_date(td.v_TODAY)-1) to change the number into a date > then change it back into number again... – feef24 Nov 15 '12 at 09:18
@feef24: why on earth are you using a number/varchar to represent dates? Use a proper `DATE` column and you don't need all this casting/converting around. – Nov 15 '12 at 09:22
yeah... I know... but it's a system that's been around for 20 years, so it's not at my level to change something that's been around for long.. I'm not getting errors after trying your suggestions. Also, to_date was missing this, where yyyymmdd is the current format of the date: (to_date(td.v_TODAY,'yyyymmdd')-1) Now I just need to figure out why is this running but returning 0 records. – feef24 Nov 15 '12 at 09:42

How to insert into table where variable -1

2 Answers2