Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ora 904 being encountered

ChaitanyaGoparajuAug 18 2010 — edited Aug 18 2010
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 15 2010
Added on Aug 18 2010
2 comments
717 views