Jump to content

[SOLVED] Procedures in Oracle


Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.