CODEWITHSUNDEEP
oracle-databasevariablesinsertto-dateto-char
feef24
feef24

Reputation: 1

How to insert into table where variable -1

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.

Upvotes: 0

Views: 1883

Answers (2)

Ben
Ben

Reputation: 52863

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.

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 0

Related Questions