ORA-00923: FROM keyword not found where expected error in oracle

Question

I have following Store procedure in Oracle 11g. I am passing the table name as parameter through ASP.net using c#. But while running the application i am getting the error "ORA-00923: FROM keyword not found where expected error in oracle".

PROCEDURE "ARCHIVE_FILTERDATA" ( ITYPE IN VARCHAR2, itableName IN VARCHAR2, cur_archive OUT sys_refcursor ) AS 

stmt clob; 

endstmt clob; 

BEGIN

IF ITYPE='Week' 
THEN stmt := 'DELETE FROM '|| itableName ||' WHERE CREATEDATE < (SELECT DATE_ADD(CURDATE(), INTERVAL , - 1, WEEK))'; 
EXECUTE IMMEDIATE stmt; 

END IF;

END;

So anybody have a solution please let me know ASAP. Thanks in Advance

Answer 1

You inner query will look alike:-

SELECT DATE_ADD(CURDATE(), INTERVAL , - 1, WEEK) FROM DUAL;

So plz correct your query.

Answer 2

You have three errors in your DELETE statement:

1. there is no date_add() function in Oracle (See the manual for a list of available functions)
2. there is no curdate() function in Oracle (See the manual for a list of available functions)
3. The sub-query is totally unnecessary. Plus: a select requires a from clause.

Putting it all together, your delete should look like this:

stmt := 'DELETE FROM '|| itableName ||' WHERE CREATEDATE < sysdate - interval ''1'' week'; 

(Note the duplicated single quotes inside the string literal).

Be aware that Oracle's DATE datatype always contains a time part. So sysdate - interval '1' week will return the date one week ago a the "current time". If you mean to include the complete day last week, you need to "remove" the time part by using trunc: trunc(sysdate) - interval '1' week.