vu3lmg
asked on
ORA-00936 Missing Expression, ORA-00972 identifier is too long
I am getting the above mentioned 2 errors on my PC (Windows 2000 server).
I have another PC which is Windows 2003 server which has same exact code and works fine.
My connect string looks like :
Provider=OraOLEDB.Oracle.1 ;Persist Security Info=False;User ID=bprod;Data Source=asbc;Extended Properties=""
********* Following query gives "ORA-00936 Missing Expression"
SELECT PK_ALERTS, ALERT_ID, ALERT_NAME, ALERT_CATEGORY, ALERT_TYPE, IS_ACTIVE,
IS_BLINKING, ALERT_TOOLTIP, ALERT_SOUND_FILE, DESCRIPTION, ALERT_DBLC_ACTION,
ALERT_ANIGIF_FILE, ALERT_CAPTION, ALERT_FG_COLOR, ALERT_BG_COLOR,
ALERT_USE_SOUND, ALERT_USE_SPEECH,
(SELECT COUNT(*) FROM USER_ALERTS WHERE ALERTS.ALERT_ID = USER_ALERTS.ALERT_ID
AND USER_ALERTS.USER_ID = 'AWILDA' ) AS CNT
FROM ALERTS WHERE IS_ACTIVE <> 0 ORDER BY ALERT_ID
********* Following query gives "ORA-00972 identifier is too long"
SELECT TASKS.* ,
( SELECT VALUE_DESCR FROM FIELDS_VALUES WHERE FIELD_TYPE = 'TaskSubM'
AND VALUE_CODE = TASKS.TASKSubMenuCode AND Active <> 0 ) AS TaskSubMenu
FROM TASKS WHERE ACTIVE <> 0
Both the queries when run from TOAD work fine.
The database is on Oracle9.
Regards
VU3LMG
I have another PC which is Windows 2003 server which has same exact code and works fine.
My connect string looks like :
Provider=OraOLEDB.Oracle.1
********* Following query gives "ORA-00936 Missing Expression"
SELECT PK_ALERTS, ALERT_ID, ALERT_NAME, ALERT_CATEGORY, ALERT_TYPE, IS_ACTIVE,
IS_BLINKING, ALERT_TOOLTIP, ALERT_SOUND_FILE, DESCRIPTION, ALERT_DBLC_ACTION,
ALERT_ANIGIF_FILE, ALERT_CAPTION, ALERT_FG_COLOR, ALERT_BG_COLOR,
ALERT_USE_SOUND, ALERT_USE_SPEECH,
(SELECT COUNT(*) FROM USER_ALERTS WHERE ALERTS.ALERT_ID = USER_ALERTS.ALERT_ID
AND USER_ALERTS.USER_ID = 'AWILDA' ) AS CNT
FROM ALERTS WHERE IS_ACTIVE <> 0 ORDER BY ALERT_ID
********* Following query gives "ORA-00972 identifier is too long"
SELECT TASKS.* ,
( SELECT VALUE_DESCR FROM FIELDS_VALUES WHERE FIELD_TYPE = 'TaskSubM'
AND VALUE_CODE = TASKS.TASKSubMenuCode AND Active <> 0 ) AS TaskSubMenu
FROM TASKS WHERE ACTIVE <> 0
Both the queries when run from TOAD work fine.
The database is on Oracle9.
Regards
VU3LMG
are you getting the errors in sql*plus, if so then paste the errors which will show which line the errors are for the above queries.
Thanks
Thanks
ASKER
a) As I mentioned initially, the database is Oracle9.
b) The problem must NOT be with sub queries because I have another Widows 2003 PC which runs same exact code to same exact database and these queries work fine.
Regards
VU3LMG
b) The problem must NOT be with sub queries because I have another Widows 2003 PC which runs same exact code to same exact database and these queries work fine.
Regards
VU3LMG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> b) The problem must NOT be with sub queries because I have another Widows 2003 PC which runs same exact code to same exact database and these queries work fine.
I meant: those statements are obviously correct, but maybe there's some problem with Your connection provider. As You say it cannot be problem with db itself, so try if any query would work, i.e. issue select * from dual and see if this would fail.
I meant: those statements are obviously correct, but maybe there's some problem with Your connection provider. As You say it cannot be problem with db itself, so try if any query would work, i.e. issue select * from dual and see if this would fail.
ASKER
Alright guys, finally found the problem. Oracle9i update was not installed.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
If this is the issue, You can try rewritting Your first code to:
SELECT ALERTS.PK_ALERTS, ALERTS.ALERT_ID, ALERTS.ALERT_NAME, ALERTS.ALERT_CATEGORY, ALERTS.ALERT_TYPE, ALERTS.IS_ACTIVE,
ALERTS.IS_BLINKING, ALERTS.ALERT_TOOLTIP, ALERTS.ALERT_SOUND_FILE, ALERTS.DESCRIPTION, ALERTS.ALERT_DBLC_ACTION,
ALERTS.ALERT_ANIGIF_FILE, ALERTS.ALERT_CAPTION, ALERTS.ALERT_FG_COLOR, ALERTS.ALERT_BG_COLOR,
ALERTS.ALERT_USE_SOUND, ALERTS.ALERT_USE_SPEECH, COUNT(*) AS CNT
FROM ALERTS
LEFT OUTER JOIN USER_ALERTS ON ALERTS.ALERT_ID = USER_ALERTS.ALERT_ID AND USER_ALERTS.USER_ID = 'AWILDA'
WHERE IS_ACTIVE <> 0
GROUP BY ALERTS.PK_ALERTS, ALERTS.ALERT_ID, ALERTS.ALERT_NAME, ALERTS.ALERT_CATEGORY, ALERTS.ALERT_TYPE, ALERTS.IS_ACTIVE,
ALERTS.IS_BLINKING, ALERTS.ALERT_TOOLTIP, ALERTS.ALERT_SOUND_FILE, ALERTS.DESCRIPTION, ALERTS.ALERT_DBLC_ACTION,
ALERTS.ALERT_ANIGIF_FILE, ALERTS.ALERT_CAPTION, ALERTS.ALERT_FG_COLOR, ALERTS.ALERT_BG_COLOR,
ALERTS.ALERT_USE_SOUND, ALERTS.ALERT_USE_SPEECH
ORDER BY ALERT_ID
and second to
SELECT TASKS.* ,
FIELD_VALUES.VALUE_DESCR AS TaskSubMenu
FROM TASKS
LEFT OUTER JOIN FIELDS_VALUES ON FIELDS_VALUES.VALUE_CODE = TASKS.TASKSubMenuCode AND FIELD_VALUES.Active <> 0 AND FIELD_TYPE = 'TaskSubM'
WHERE ACTIVE <> 0
(If You are sure that link always exists - both queries - feel free to change left outer join to inner join).