Solved

Missing Expression Error

Posted on 2012-03-27
7
1,139 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 200 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 100 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle rollup query 3 50
unable to get sorting resultset 15 68
Getting a return value from an IN OUT parameter in Oracle? 7 44
PL SQL Search Across Columns 4 19
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now