CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
user2197712
user2197712

Reputation: 267

Need to put TRY - CATCH in oracle stored procedure

I have created SP for creating unique index on multiple tables.

Now i need to create Try-catch (exception handling) for this, like incase index not created this should be catch ....

For example i have 200 tables and only 1 tables is giving error, Then 199 tables should be created with index and catch log have that one error-ed tables name.

Please help .

DECLARE
CURSOR C_TABLE IS
SELECT INPUT_TABLE,HISTORY_TABLE FROM FUNCTIONS WHERE TARGET_SYS IN ('ABC','DEC') AND ACTIVE_FLAG='Y';

SQL_CREATE_INX VARCHAR2(200);
SQL_TABLE_NAME VARCHAR2(200);

BEGIN
-- INPUT TABLE
    FOR I IN C_TABLE
    LOOP

        SQL_CREATE_INX:='CREATE UNIQUE INDEX CLM1.AUDIT_SUB_SITE_INX ON '||I.INPUT_TABLE||' (AUDIT_NBR , SUB_AUDIT_NBR , STATE) ';
        SQL_TABLE_NAME:=I.INPUT_TABLE;
        EXECUTE IMMEDIATE SQL_CREATE_INX;

        DBMS_OUTPUT.PUT_LINE('INDEX DONE : '||SQL_TABLE_NAME);

    END LOOP;
    DBMS_OUTPUT.PUT_LINE('INDEXES CREATED FOR ALL INPUT TABLES');

    -- OUTPUT TABLE
    FOR H IN C_TABLE
    LOOP

        SQL_CREATE_INX:='CREATE UNIQUE INDEX CLM1.AUDIT_SUB_SITE_INX ON '||H.HISTORY_TABLE||' (AUDIT_NBR , SUB_AUDIT_NBR , STATE)  ';
        SQL_TABLE_NAME:=H.HISTORY_TABLE;
        EXECUTE IMMEDIATE SQL_CREATE_INX;

        DBMS_OUTPUT.PUT_LINE('INDEX DONE : '||SQL_TABLE_NAME);

    END LOOP;
    DBMS_OUTPUT.PUT_LINE('INDEXES CREATED FOR ALL OUTPUT TABLES');


END;

Upvotes: 1

Views: 19434

Answers (2)

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

In oracle handling of exceptions is like below 

Declare
--declaration 
BEGIN
   --executable_section

EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END

By editing your procedure with exception block is below

    DECLARE
    CURSOR C_TABLE IS
    SELECT INPUT_TABLE,HISTORY_TABLE FROM FUNCTIONS WHERE TARGET_SYS IN ('ABC','DEC') AND ACTIVE_FLAG='Y';
    var1 number;
    var2 number;
    SQL_CREATE_INX VARCHAR2(200);
    SQL_TABLE_NAME VARCHAR2(200);

    BEGIN
    -- INPUT TABLE
        FOR I IN C_TABLE
        LOOP
    select 1 into var1 from user_tables where table_name=upper(i.INPUT_TABLE)
  if var1=1
 then
            SQL_CREATE_INX:='CREATE UNIQUE INDEX CLM1.AUDIT_SUB_SITE_INX ON '||I.INPUT_TABLE||' (AUDIT_NBR , SUB_AUDIT_NBR , STATE) ';
            SQL_TABLE_NAME:=I.INPUT_TABLE;
            EXECUTE IMMEDIATE SQL_CREATE_INX;

            DBMS_OUTPUT.PUT_LINE('INDEX DONE : '||SQL_TABLE_NAME);
    end if;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('INDEXES CREATED FOR ALL INPUT TABLES');

        -- OUTPUT TABLE
        FOR H IN C_TABLE
        LOOP
     select 1 into var2 from user_tables where table_name=upper(h.HISTORY_TABLE)
  if var2=1
 then
            SQL_CREATE_INX:='CREATE UNIQUE INDEX CLM1.AUDIT_SUB_SITE_INX ON '||H.HISTORY_TABLE||' (AUDIT_NBR , SUB_AUDIT_NBR , STATE)  ';
            SQL_TABLE_NAME:=H.HISTORY_TABLE;
            EXECUTE IMMEDIATE SQL_CREATE_INX;

            DBMS_OUTPUT.PUT_LINE('INDEX DONE : '||SQL_TABLE_NAME);
    end if;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('INDEXES CREATED FOR ALL OUTPUT TABLES');
    exception 
   WHEN OTHERS THEN
    Dbms_output.put_line(sqlerrm ||' error occured with this error code '||SQLCODE);

END;

Some Predefined exceptions in oracle and edit the plsql block with your exception name.

Upvotes: 1

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

You need to enclose all EXEC IMMEDIATE into BEGIN .. END

  FOR (.... )
  LOOP
      /*
       *  Other Statements
       */
      BEGIN  /* try */
            EXECUTE IMMEDIATE SQL_CREATE_INX;

            /* If it is success the below output happens */
            DBMS_OUTPUT.PUT_LINE('INDEX DONE : '||SQL_TABLE_NAME);
      EXCEPTION /*catch */
      /* It is like catch(Exception e) All execeptions go here..*/
      WHEN OTHERS THEN
          /* Log your error message here.. SQLERRM has it..*/
          DBMS_OUTPUT.PUT_LINE('DDL FAILED FOR '||SQL_TABLE_NAME||'::FAILED WITH ERROR::'||SQLERRM);
      END;
       /*
        *  Other Statements
        */
   END LOOP;

Upvotes: 3

Related Questions