CODEWITHSUNDEEP
oracle-databasesyntax-error
ggwp
ggwp

Reputation: 77

PL/SQL: ORA-00936: missing expression :: Where Am I going wrong in the code?

I need to add a new record in my database or update it if it is already present. I have used the IF else clause. I have a SQL query for my oracle database as below : 

DECLARE EXISTS number;
BEGIN
SELECT count(*) INTO EXISTS
FROM reports
WHERE report_id=27;

IF(EXISTS =0) THEN
INSERT INTO reports (REPORT_ID,TITLE,CATEGORY,DISPLAY_ORDER,QUERY,DESCRIPTION,CONTENT_SEQ,DELD,ADMIN_ID,DATE_UPD)
VALUES (27,
        'Check user entered keywords have valid resource link',
        'zData checks',
        9120,
        'Select Rk.Resource_Id As "Resource IDs", Rp.Display_Title As "Title", St.Title As "Type", 
        Decode(Rp.Deld, Null, ''Missing resource'',''Resource deleted (DELD is '' || Rp.Deld || '')'') As "Reason",
        rk.keyword,L.Email As "Owner" from resource_keywords rk
        Left Join Resources Rp On rk.Resource_Id = Rp.Resource_Id
        Left Join Special_Types St On Rp.Special_Id = St.Special_Id
        Left Join Login L On rk.Admin_Id = L.Login_Id
        Where Rk.Deld = 0 And (Rp.Resource_Id Is Null Or Rp.Deld Is Null Or Rp.Deld <> 0);',
        'Check resource_keywords entries for valid and not deleted resource_id.',
        1,
        0,
        1,
        CURRENT_TIMESTAMP);

ELSE
UPDATE reports
SET TITLE = 'Check user entered keywords have valid resource link',
    CATEGORY = 'zData checks',
    DISPLAY_ORDER= 9120,
    QUERY ='Select Rk.Resource_Id As "Resource IDs", Rp.Display_Title As "Title", St.Title As "Type", 
                Decode(Rp.Deld, Null, ''Missing resource'',''Resource deleted (DELD is '' || Rp.Deld || '')'') As "Reason",
                rk.keyword,L.Email As "Owner" from resource_keywords rk
                Left Join Resources Rp On rk.Resource_Id = Rp.Resource_Id
                Left Join Special_Types St On Rp.Special_Id = St.Special_Id
                Left Join Login L On rk.Admin_Id = L.Login_Id
                Where Rk.Deld = 0 And (Rp.Resource_Id Is Null Or Rp.Deld Is Null Or Rp.Deld <> 0);',
    DESCRIPTION='Check resource_keywords entries for valid and not deleted resource_id.',
    CONTENT_SEQ=1,
    DELD=0,
    ADMIN_ID=1,
    DATE_UPD = CURRENT_TIMESTAMP
WHERE REPORT_ID = 27;
END IF;
END;

This throws below errors in ORACLE SQL developer: 

Error report:
ORA-06550: line 3, column 22:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 7, column 11:
PLS-00103: Encountered the symbol "=" when expecting one of the following:

   (
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

Where am I going wrong ?

Upvotes: 1

Views: 4711

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

ORA-06550: line 3, column 22: PL/SQL: ORA-00936: missing expression

Oracle is clearly telling you where the error is. You just need to look at the error details, specially the line number.

Line 3 will take you to SELECT count(*) INTO EXISTS. Column 22 will take you to EXISTS. It is a reserved keyword, so you cannot use it directly.

If you really want to use it, then enclose it within double-quotation marks. For example,

SQL> DECLARE
  2    "EXISTS" NUMBER;
  3  BEGIN
  4    SELECT COUNT(*) INTO "EXISTS" FROM emp;
  5    dbms_output.put_line("EXISTS");
  6  END;
  7  /
14

PL/SQL procedure successfully completed.

SQL>

However, I would insist not to use reserved keywords. Use a different variable name.

Upvotes: 2

Related Questions