Sysdate as char instead of date

1
votes

I have a query.

i have a config table AB where a row is marked as "sysdate-360"

col1 ||col2
AB   || sysdate-360
BC   || sysdate -2

When i write a procedure to get date value from the config table AB i used.

    v_date varchar(20);
    cursor c1 as
    select col2 from AB;
    then 

    for ab_date in c1
    loop
    select ab_date.col2 into v_date from dual;
v_sql := 'delete from any_table where load_date <='||v_date;
execute immediate v_sql ;
commit;
    end loop;

The procedure is compiled but when i execute I'm getting below error

ORA-01722: invalid number ORA-06512: at "procedure", line 46 ORA-06512: at line 1 01722. 00000 - "invalid number" *Cause:
*Action:

The sysdate -360 is considered as char but not as date since SYSDATE is itself a date right?

Please help.

3
sqloracleplsql
Presumably load_date is a date type. Your obfuscation is confusing things a bit as your cursor query doesn't look right - is it really from config_table where col1 = AB? and you haven't indicated which line corresponds to line 46 in the error. What data type is col1, as you've made it look like a varchar? If it is a string, is AB a number, and you aren't enclosing it in quotes, and there are col1 values that are not numbers? I think the error is from the cursor query, and nothing to do with the date.... - Alex Poole
I think you should get debug on the value in v_sql that you pass to execute immediate. This will likely reveal your problem, such as a missing space in v_sql. - WW.

3 Answers

2
votes

If you handle only expressions SYSDATE +/- N I'd suggest to modify the config table as follows

ID SYSDATE_OFFSET
-- --------------
AB           -360 
BC             -2

So you have a numeric offset to sysdate which can be queried this way:

select sysdate + (select SYSDATE_OFFSET from config where id = 'AB') s_360
from dual;

S_360           
-----------------
25.02.16 21:46:50

So you may open a cursor with the query above.

If you can't change the table - define a view that removes the string sysdate and converts to number!

2
votes

This looks quite fine to me. But why do you select into v_date? You get a string from the cursor which you can concatenate directly:

for ab_date in c1 loop
  v_sql := 'delete from any_table where load_date <=' || ab_date.col2;
  execute immediate v_sql ;
  commit;
end loop;

At last this is simply the concatanation of two strings 'delete from any_table where load_date <=' and 'sysdate-360' which makes 'delete from any_table where load_date <= sysdate-360' - exactly the SQL string you want.

(That would look even better with a proper column name such as date_expression instead of col2.)

More elaborate explanation: The cursor gets you the string 'sysdate-360'. The query select ab_date.col2 into v_date from dual; is simply v_date := ab_date.col2;. But is v_date a string? If not, you get a conversion error, because 'sysdate-360' is just a string, nothing more. If you expect the DBMS to see that the string contains 'sysdate' which also happens to be the name of a system variable for the current time and then convert this magically, you expect to much.

-1
votes

Try the to_date function

TO_DATE(v_date)