Oracle PL/SQL Syntax issue/question

I have about 20 select statements written by another developer where I need to make them into views.

In about 8 of them, I recieve the error:
"ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
Error at Line: 8 Column: 13"

That's right at the point where the two open parenthesis - EXISTS sits in each statement.

Any ideas on whether this is valid sytax.  I believe this code came from Business Objects and might be some of that syntax??

If so, ideas on how to correct this with Oracle syntax?  

Thanks in advance!!

---here's an example select statement that gets the error above:


SELECT DISTINCT
  RES_GSE_PERSON_TYPE.NAME,
  RES_GSE_SRM_RESOURCES.PERSON_TYPE
FROM
  (
  SELECT DISTINCT r.id RESOURCE_ID
    FROM   srm_resources r
    WHERE ((EXISTS (SELECT user_id
                    FROM   cmn_sec_assgnd_obj_perm_r_v
                    WHERE  object_type = 'RECORD'
                    AND    object_code = 'PRJ_RESOURCE'
                    AND    permission_code = 'ResourceViewManagement'
                    AND    component_code = 'PRJ'
                    AND    user_id = (SELECT u.id
                                      FROM   cmn_sec_users u, srm_resources r, odf_ca_resource odfr
                                      WHERE  u.id = r.user_id
                                      AND    r.id = odfr.id
                                      AND   (UPPER(u.user_name) = UPPER(@variable('BOUSER'))
                                      OR     UPPER(odfr.bo_user) = UPPER(@variable('BOUSER'))))))
           OR
          (EXISTS (SELECT object_instance_id
                   FROM   cmn_sec_assgnd_obj_perm_v
                   WHERE  object_instance_id = r.id
                   AND    object_type = 'RECORD'
                   AND    object_code = 'PRJ_RESOURCE'
                   AND    permission_code = 'ResourceViewManagement'
                   AND    component_code = 'PRJ'
                   AND    user_id = (SELECT u.id
                                     FROM   cmn_sec_users u, srm_resources r, odf_ca_resource odfr
                                     WHERE  u.id = r.user_id
                                     AND    r.id = odfr.id
                                     AND   (UPPER(u.user_name) = UPPER(@variable('BOUSER'))
                                     OR     UPPER(odfr.bo_user) = UPPER(@variable('BOUSER')))))))
  )  RES_GSE_RESOURCE_SECURITY,
  (
  SELECT LOOKUP_CODE, LOOKUP_ENUM, ID, NAME
FROM CMN_LOOKUPS_V
WHERE LOOKUP_TYPE = 'SRM_RESOURCE_TYPE' AND LANGUAGE_CODE = 'en'
  )  RES_GSE_PERSON_TYPE,
  SRM_RESOURCES  RES_GSE_SRM_RESOURCES,
  (
  SELECT RECORD_ID RESOURCE_ID,
       OBST.NAME OBS_TYPE_NAME,
       OBSU.LEVEL1_NAME,
       OBSU.LEVEL2_NAME,
       OBSU.LEVEL3_NAME,
       OBSU.LEVEL4_NAME,
       OBSU.LEVEL5_NAME,
       OBSU.LEVEL6_NAME,
       OBSU.LEVEL7_NAME,
       OBSU.LEVEL8_NAME,
       OBSU.LEVEL9_NAME,
       OBSU.LEVEL10_NAME,
       OBSU.PATH OBS_PATH,
       OBSU.OBS_UNIT_ID
FROM   PRJ_OBS_ASSOCIATIONS OBSA, NBI_DIM_OBS OBSU, PRJ_OBS_TYPES OBST, PRJ_OBS_OBJECT_TYPES TYPES
WHERE  OBSA.TABLE_NAME = 'SRM_RESOURCES'
AND    OBSA.UNIT_ID = OBSU.OBS_UNIT_ID
AND    OBSU.OBS_TYPE_ID = OBST.ID
AND    OBST.ID = TYPES.TYPE_ID
AND    TYPES.TABLE_NAME = 'SRM_RESOURCES'
  )  RES_GSE_OBS_STRUCTURE
WHERE
  ( RES_GSE_PERSON_TYPE.ID(+)=RES_GSE_SRM_RESOURCES.PERSON_TYPE  )
  AND  ( RES_GSE_SRM_RESOURCES.ID=RES_GSE_OBS_STRUCTURE.RESOURCE_ID(+)  )
  AND  ( RES_GSE_RESOURCE_SECURITY.RESOURCE_ID=RES_GSE_SRM_RESOURCES.ID  )
  AND  
  (
   RES_GSE_OBS_STRUCTURE.LEVEL5_NAME  IN  ( 'MIG Admin - CAD'  )
   OR
   RES_GSE_OBS_STRUCTURE.LEVEL4_NAME  IN  ( 'MIG'  )
  )
Weller0123Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Are you running that as-is?

"@variable('BOUSER')" is not valid 'oracle' syntax.

Try just using a string literal.

Change:
AND   (UPPER(u.user_name) = UPPER(@variable('BOUSER'))

to:
AND   (UPPER(u.user_name) = 'BOUSER'

Do this for all the rest.
0
 
Weller0123Author Commented:
Thanks!!!!    I cannot try this now, but that certainly makes sense and is certainly an issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.