Missing Expression Error

Hi,

I'm still trying to find my feet with Oracle, having come from a MS background.

I have this loop that is giving me grief. I get the message as follows when rying to run it - I am sure it is a imple syntax error, but cannot see it for the life of me.

Error report:
ORA-00936: missing expression
ORA-06512: at line 10
00936. 00000 -  "missing expression"
*Cause:    
*Action:


BEGIN  
FOR i IN (SELECT DISTINCT a.WR_Type
     FROM qaoc_WR_Valid_States a
     LEFT JOIN qaoc_Wr_Type b
      ON a.WR_Type = b.WR_Type
    WHERE b.WR_Type_Group_ID = 2) LOOP
   
      BEGIN

         EXECUTE IMMEDIATE 'DELETE qaoc_WR_Valid_States WHERE WR_Type = || i || AND WR_State IN ( ''NotUsed0'',''NotUsed1'' )';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 6 WHERE WR_Type = || i || AND WR_State = ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 7 WHERE WR_Type = || i || AND Sequence_No = 5';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 8 WHERE WR_Type = || i || AND Sequence_No = 6 AND WR_State <> ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 9 WHERE WR_Type = || i || AND WR_State = ''Closed''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 10 WHERE WR_Type = || i || AND WR_State = ''Cancelled''';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( || i ||, ''NotUsed0'', 4, NULL, NULL )';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( || i ||, ''NotUsed1'', 5, NULL, NULL )';

      END;
   END LOOP;
END;
/
LVL 23
Kelvin SparksAsked:
Who is Participating?
 
OP_ZaharinCommented:
EXECUTE IMMEDIATE 'DELETE qaoc_WR_Valid_States WHERE WR_Type = || i.WR_Type || AND WR_State IN ( ''NotUsed0'',''NotUsed1'' )';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 6 WHERE WR_Type = || i.WR_Type || AND WR_State = ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 7 WHERE WR_Type = || i.WR_Type || AND Sequence_No = 5';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 8 WHERE WR_Type = || i.WR_Type || AND Sequence_No = 6 AND WR_State <> ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 9 WHERE WR_Type = || i.WR_Type || AND WR_State = ''Closed''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 10 WHERE WR_Type = || i.WR_Type || AND WR_State = ''Cancelled''';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( || i.WR_Type ||, ''NotUsed0'', 4, NULL, NULL )';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( || i.WR_Type ||, ''NotUsed1'', 5, NULL, NULL )';

Open in new window



- you are missing the expression for "i" in the 2nd begin block. change it from "i" to i.WR_Type
0
 
Kelvin SparksAuthor Commented:
I still get the same error. WR_Type will be a varchar2 datatype - does that change anything?

Kelvin
0
 
Wasim Akram ShaikCommented:
yes, op_zaharin has pointed it correct, but you need to do something more besides that..

as you are using a loop variable to get it substituted properly you have to use something like this

EXECUTE IMMEDIATE 'DELETE qaoc_WR_Valid_States WHERE WR_Type = '|| i.WR_Type ||' AND WR_State IN ( ''NotUsed0'',''NotUsed1'' )';
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
OP_ZaharinCommented:
- ah yes. i miss that. thanks wasimibm :)
0
 
Kelvin SparksAuthor Commented:
OK, we're getting closer.

This is new error - current code below

Error report:
ORA-00904: "KPH": invalid identifier
ORA-06512: at line 10
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

What is "KPH"???


BEGIN  
FOR i IN (SELECT DISTINCT A.WR_TYPE
     FROM qaoc_WR_Valid_States a
     LEFT JOIN qaoc_Wr_Type b
      ON a.WR_Type = b.WR_Type
    WHERE b.WR_Type_Group_ID = 2) LOOP
   
      BEGIN

         EXECUTE IMMEDIATE 'DELETE qaoc_WR_Valid_States WHERE WR_Type = '|| i.WR_TYPE ||' AND WR_State IN ( ''NotUsed0'',''NotUsed1'' )';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 6 WHERE WR_Type = '|| i.WR_TYPE ||' AND WR_State = ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 7 WHERE WR_Type = '|| i.WR_TYPE ||' AND Sequence_No = 5';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 8 WHERE WR_Type = '|| i.WR_TYPE ||' AND Sequence_No = 6 AND WR_State <> ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 9 WHERE WR_Type = '|| i.WR_TYPE ||' AND WR_State = ''Closed''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 10 WHERE WR_Type = '|| i.WR_TYPE ||' AND WR_State = ''Cancelled''';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( '|| i.WR_TYPE ||', ''NotUsed0'', 4, NULL, NULL,1 )';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( '|| i.WR_TYPE ||', ''NotUsed1'', 5, NULL, NULL,1 )';

      END;
   END LOOP;
END;
/
0
 
slightwv (䄆 Netminder) Commented:
>>WR_Type will be a varchar2 datatype
>>What is "KPH"???

Likely one of the WR_TYPE values.

You need to add single quotes to it.  Do this for all of them.

...
WHERE WR_Type = '''|| i.WR_TYPE ||''' AND
...
0
 
Kelvin SparksAuthor Commented:
All good now thanks
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.