7 Replies Latest reply: Oct 18, 2013 2:40 PM by 409f9b56-08be-470f-bb32-a119123a4530 RSS

    Problem creating a procedure that contains a merge statement

    409f9b56-08be-470f-bb32-a119123a4530

      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;