CODEWITHSUNDEEP
oracle-databaseplsql
user3847566
user3847566

Reputation: 53

Oracle Pl/Sql Procedure Help Merge

I have a procedure created and i am using a merge query inside it. when i compile the procedure, there is no error and when i try to execute it giving me below error. Can someone help in solving this.

Code:

    CREATE OR REPLACE PROCEDURE DEVICE.Check1
    IS
    BEGIN
    MERGE INTO DEVICE.APP_C_CATEGORY A
        USING (SELECT market_segment_id,
                      market_segment_name,
                      UPDATE_USER,
                      UPDATE_DATE
                 FROM CUST_INTEL.MSE_MARKET_SEGMENT_MASTER@SOURCE_CUST_INTEL.ITG.TI.COM
                WHERE market_segment_id NOT IN ('120', '130', '100')) B
           ON (A.APP_CATEGORY_ID = B.market_segment_id
               AND A.APP_CATEGORY_NAME = B.market_segment_name)
    WHEN MATCHED
    THEN
      UPDATE SET A.DESCRIPTION = B.market_segment_name,
                 A.PARENT_APP_AREA_ID = NULL,
                 A.RECORD_CHANGED_BY = B.UPDATE_USER,
                 A.RECORD_CHANGE_DATE = B.UPDATE_DATE
    WHEN NOT MATCHED
    THEN
      INSERT     (A.APP_CATEGORY_NAME,
                  A.DESCRIPTION,
                  A.TYPE,
                  A.PARENT_APP_AREA_ID,
                  A.RECORD_CHANGED_BY,
                  A.RECORD_CHANGE_DATE)
          VALUES (B.market_segment_name,
                  B.market_segment_name,
                  1,
                  NULL,
                  B.UPDATE_USER,
                  B.UPDATE_DATE);

    COMMIT;
    EXCEPTION
    WHEN OTHERS
    THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
      DBMS_OUTPUT.PUT_LINE (SQLCODE);
    END;
    /

Error: when i am executing

    BEGIN 
    DEVICE.CHECK1;
    COMMIT; 
    END;

the following errors occur:

ORA-06550: line 2, column 10:
PLS-00302: component 'CHECK1' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Upvotes: 2

Views: 793

Answers (1)

Alex Poole
Alex Poole

Reputation: 191360

This is nothing to do with the merge, it isn't getting as far as actually executing your procedure. From the create procedure statement you have a schema called DEVICE. Since the error message is only complaining about CHECK1, not DEVICE.CHECK1, you also appear to have a package called DEVICE. Your anonymous block is trying to find a procedure called CHECK1 within that package, not at schema level.

If you are connected as the device schema owner (user) when you execute this, just remove the schema prefix:

BEGIN 
  CHECK1;
  COMMIT; 
END;
/

Upvotes: 4

Related Questions