Home » SQL & PL/SQL » SQL & PL/SQL » Getting invalid identifier in DBMS_SQL.Parse (oracle 10g)
Getting invalid identifier in DBMS_SQL.Parse [message #281153] Thu, 15 November 2007 22:12 Go to next message
poonster
Messages: 2
Registered: November 2007
Junior Member
Hi guys, I need help in my stored proc. I have no problems in compiling the stored proc. But when I run it, I get the error

ORA-00904: "DATEEND": invalid identifier.

Here's my code:

DBMS_SQL.PARSE(cur, 'SELECT COUNT(*) INTO countTable FROM PurchaseDetails_' || month || ' WHERE
PurchaseDATE BETWEEN dateStart AND dateEnd', DBMS_SQL.NATIVE);

I've defined dateStart and dateEnd. But it keeps telling me that
ORA-00904: "DATEEND": invalid identifier. Is there a syntax error with my code? I've tried to hardcode the statement and just executing it without using DBMS_SQL.PARSE and it works.

SELECT COUNT(*) INTO countTable FROM PurchaseDetails_10 WHERE
PurchaseDATE BETWEEN dateStart AND dateEnd;
Re: Getting invalid identifier in DBMS_SQL.Parse [message #281155 is a reply to message #281153] Thu, 15 November 2007 22:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You have to test your generated statement in sqlplus, not from pl/sql.
dateend and datestart are probably pl/sql-variables? Those are not available in sql.

Why do you use dbms_sql? Nowadays we use execute immediate. It's far easier to use!
Re: Getting invalid identifier in DBMS_SQL.Parse [message #281264 is a reply to message #281153] Fri, 16 November 2007 04:47 Go to previous message
poonster
Messages: 2
Registered: November 2007
Junior Member
Hey Frank,

I got it. I didnt append the variable in a proper way, I've fixed that already. I'm still a newbie in Oracle, did a google on dynamic sql, and I got DBMS_SQL.

Thanks for your recommendation, I'm using execute immediate now.
Previous Topic: Questions on searching specific rows
Next Topic: using reslts from a function
Goto Forum:
  


Current Time: Sat Dec 03 06:07:35 CST 2016

Total time taken to generate the page: 0.14551 seconds