Adam Posted July 3, 2009 Share Posted July 3, 2009 Hey up guys, Working with some procedures in Oracle. I'm not brilliant with PL/SQL syntax yet so I'm a little stuck with this and lack the terminology to search for it properly on Google. What I'd like to do is run a select query that will hopefully return an `item_id`. If there is an item ID then I'd like to run an update, if not, an insert. My code looks like this: select item_id into vItemId from replaced_table where replace_field = pSitecode and replaced_field = pLang and replaced_field = pPageId and replaced_field = pGroupId and replaced_field = pRuleId and replaced_field = pTypeId and replaced_field = pSku; if vItemId <> null then ... else ... end if; However I'm getting a compile error in SQL developer with that, saying that the if statement is unreachable code? To be honest though I don't actually know how to test if the query returned a result, or to test if `vItemId` contains any data (and I'm not entirely sure what value it would have if there was no results returned - guessed null). Could anybody shed any light on this? NB. To be on the safe side of violating my contract I just replaced the table and field names. Thanks Adam Quote Link to comment https://forums.phpfreaks.com/topic/164668-solved-procedures-in-oracle/ Share on other sites More sharing options...
artacus Posted July 4, 2009 Share Posted July 4, 2009 You may want to review how null comparisons work (or don't in your case). Use IS NOT NULL or IS NULL instead. Also your life would be easier if you used the MERGE statement instead. http://www.psoug.org/reference/merge.html Quote Link to comment https://forums.phpfreaks.com/topic/164668-solved-procedures-in-oracle/#findComment-868610 Share on other sites More sharing options...
Adam Posted July 6, 2009 Author Share Posted July 6, 2009 Thanks a lot for the reply. I've been trying to use the merge statement but having a few troubles. This is the code I have so far: merge into merchandise_items mi using dual on (mi.item_id = pItemId) when matched then update set ... when not matched then select merchandise_container_seq.nextval into vItemId from dual; insert ( ... ) values ( ... ); I'm not exactly sure what the problem is, but all the field names, though left out where not essential, are correct and so shouldn't be causing the problem. In SQL developer I'm getting the following errors: Error(1166,5): PL/SQL: SQL Statement ignored > Refers to: "merge into merchandise_items mi" Error(1184,9): PL/SQL: ORA-00905: missing keyword > Refers to: "select merchandise_container_seq.nextval into vItemId from dual;" Error(1185,9): PL/SQL: SQL Statement ignored > Refers to: "insert" Error(1186,9): PL/SQL: ORA-00925: missing INTO keyword > Refers to: "(" - just underneath "insert" I'd be very grateful if you, or anyone else, could take another quick look and see if you can spot what the error is? Thanks again for your help, Adam Quote Link to comment https://forums.phpfreaks.com/topic/164668-solved-procedures-in-oracle/#findComment-869633 Share on other sites More sharing options...
Adam Posted July 6, 2009 Author Share Posted July 6, 2009 Sorted it. The problem was the sequence select; moved it before the merge and all the errors disappeared. Thanks for your advice! I've not quite finished the job yet but I think I'll be able to work out the rest on my own now, thanks again! Adam Quote Link to comment https://forums.phpfreaks.com/topic/164668-solved-procedures-in-oracle/#findComment-869639 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.