ora 904 being encountered
I have created a table xyz with the column:
sdate DATE;
IT has values stored:
17-NOV-0009
13-MAY-0009
13-NOV-0009
I Created a Stored procedure to update certain values as:
create or replace
PROCEDURE SP_rectify
(table_name IN VARCHAR2, ddate IN VARCHAR2)
AS
nddate DATE;
tdate DATE;
qquery VARCHAR2(32000);
uupdate VARCHAR2(32000);
ln_rowcount NUMBER;
ln_months_to_add NUMBER;
ln_errorcode NUMBER;
lv_errormsg VARCHAR2(200);
BEGIN
nddate := TO_DATE(pv_sum_enddate,'DD-MON-YYYY');
qquery := 'select COUNT(*) INTO ln_rowcount
FROM '|| table_name||'
WHERE sdate = '||ddate||'';
EXECUTE IMMEDIATE qquery;
ln_months_to_add := 2000*12;
tdate := add_months(nddate, ln_months_to_add);
uupdate := 'UPDATE '||table_name||' SET sdate = '||tdate||'
WHERE summary_date = '||ddate||'';
EXECUTE IMMEDIATE uupdate;
IF SQL%ROWCOUNT = ln_rowcount THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
EXCEPTION
WHEN OTHERS THEN
BEGIN
ROLLBACK;
END;
lv_errormsg := SQLERRM;
ln_errorcode := SQLCODE;
DBMS_OUTPUT.PUT_LINE(ln_errorcode || ' - ' || lv_errormsg);
raise_application_error(-20004,'Update Error');
END SP_RECTIFY;
when i execute this SP from an ananymous pL/SQL block as :
declare
pt varchar2(100) := 'xyz';
ld date := '17-NOV-0009';
BEGIN
sp_rectify_date_values(pt,ld);
end;
I Get an error as:
Error report:
ORA-20004: Update Error
ORA-06512: at "ODS.SP_RECTIFY_DATE_VALUES", line 51
ORA-06512: at line 5
17-NOV-2009
-904 - ORA-00904: "NOV": invalid identifier
( the line numbers in the error reports are not exact now as i pasted the code removing dbms_Output statements)
WHat might be the cause of this error? ora904 stands for invalid identifier, col name but I am unable to figure out why ts saying that "NOV" invalid identifier.