Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Missing Expression Error

Posted on 2012-03-27
7
Medium Priority
?
1,171 Views
Last Modified: 2012-03-27
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;
/
0
Comment
Question by:Kelvin Sparks
7 Comments
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 800 total points
ID: 37770494
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
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37770510
I still get the same error. WR_Type will be a varchar2 datatype - does that change anything?

Kelvin
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 400 total points
ID: 37770517
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37770577
- ah yes. i miss that. thanks wasimibm :)
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37770585
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 total points
ID: 37770858
>>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
 
LVL 22

Author Closing Comment

by:Kelvin Sparks
ID: 37772869
All good now thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question