This discussion is archived
7 Replies Latest reply: Oct 18, 2013 12:40 PM by 409f9b56-08be-470f-bb32-a119123a4530 RSS

Problem creating a procedure that contains a merge statement

409f9b56-08be-470f-bb32-a119123a4530 Newbie
Currently Being Moderated

I have a merge statement (see below) that I have been running manually that I want to put into a procedure.

When I try to create the procedure I get the error: ORA:00942 - table or view does not exist. Again, this merge

statement runs fine when run manually.  I'd appreciate any guidance that an

 

Here's the ddl for creating the procedure:

 

create or replace procedure rpa_admin.load_park_info
is
s varchar(100);
BEGIN
    s := 'start';

        --BEGIN
  MERGE INTO RPA_ADMIN.SB_T_PARK_INFO  a
     USING  PA_DATA_DUMP_02032013.T_PARK_INFO b  
        ON (a.park_id = b.park_id)
  WHEN MATCHED
  THEN
     UPDATE SET
     a.PARK_NAME = b.PARK_NAME,
     a.CONTACT_FIRST_NAME = b.CONTACT_FIRST_NAME,
     a.CONTACT_MIDDLE_INI = b.CONTACT_MIDDLE_INI,
     a.CONTACT_LAST_NAME = b.CONTACT_LAST_NAME,
     a.CONTACT_SUFFIX = b.CONTACT_SUFFIX,
     a.ADDR_1 = b.ADDR_1,
     a.ADDR_2 = b.ADDR_2,
     a.ADDR_3 = b.ADDR_3,
     a.CITY = b.CITY,
     a.STATE_CODE = b.STATE_CODE,
     a.ZIP = b.ZIP,
     a.ZIP_EXTENSION = b.ZIP_EXTENSION,
     a.PHONE = b.PHONE,
     a.FAX = b.FAX,
     a.EMAIL = b.EMAIL,
     a.PARK_ADNL_MESSAGE = b.PARK_ADNL_MESSAGE,
     a.ACTIVE_FLAG = b.ACTIVE_FLAG,
     a.PARK_DIRECTIONS = b.PARK_DIRECTIONS,
     a.MODIFIED_BY = b.MODIFIED_BY,
     a.MODIFIED_DATE = b.MODIFIED_DATE,
     a.CREATED_DATE = b.CREATED_DATE,
     a.CREATED_BY = b.CREATED_BY,
     a.ENTITY_ID = b.ENTITY_ID,
     a.REGION_ID = b.REGION_ID
  WHEN NOT MATCHED
  THEN
     INSERT     (PARK_ID,
        PARK_NAME,
        CONTACT_FIRST_NAME,
        CONTACT_MIDDLE_INI,
        CONTACT_LAST_NAME,
        CONTACT_SUFFIX,
        ADDR_1,
        ADDR_2,
        ADDR_3,
        CITY,
        STATE_CODE,
        ZIP,
        ZIP_EXTENSION,
        PHONE,
        FAX,
        EMAIL,
        PARK_ADNL_MESSAGE,
        ACTIVE_FLAG,
        PARK_DIRECTIONS,
        MODIFIED_BY,
        MODIFIED_DATE,
        CREATED_DATE,
        CREATED_BY,
        ENTITY_ID,
        REGION_ID)
      VALUES (b.PARK_ID,
        b.PARK_NAME,
        b.CONTACT_FIRST_NAME,
        b.CONTACT_MIDDLE_INI,
        b.CONTACT_LAST_NAME,
        b.CONTACT_SUFFIX,
        b.ADDR_1,
        b.ADDR_2,
        b.ADDR_3,
        b.CITY,
        b.STATE_CODE,
        b.ZIP,
        b.ZIP_EXTENSION,
        b.PHONE,
        b.FAX,
        b.EMAIL,
        b.PARK_ADNL_MESSAGE,
        b.ACTIVE_FLAG,
        b.PARK_DIRECTIONS,
        b.MODIFIED_BY,
        b.MODIFIED_DATE,
        b.CREATED_DATE,
        b.CREATED_BY,
        b.ENTITY_ID,
        b.REGION_ID)
    WHERE b.PARK_ID IS NOT NULL;
       --END;
END load_park_info;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points