2 Replies Latest reply on May 7, 2012 8:19 AM by indra budiantho

    Error While run the Procedure

    fame
      Hi,
      This is my procedure,

      create or replace
      PROCEDURE PRC_DELETE_TABLE(
      P_TABLE_NAME IN VARCHAR2,
      P_OUT OUT VARCHAR2) IS

      CURSOR C_PRIMARY_KEY(CP_TABLE_NAME VARCHAR2) IS
      SELECT CONSTRAINT_NAME
      FROM USER_CONSTRAINTS
      WHERE TABLE_NAME = P_TABLE_NAME
      AND CONSTRAINT_TYPE = 'P';

      CURSOR C_CHILD_TABLE(CP_CONSTRAINT_NAME VARCHAR2) IS
      SELECT TABLE_NAME
      FROM USER_CONSTRAINTS
      WHERE R_CONSTRAINT_NAME = CP_CONSTRAINT_NAME;

      LC_SQL VARCHAR2(4000);
      LC_CONSTRAINT_NAME VARCHAR2(100);

      BEGIN

      OPEN C_PRIMARY_KEY(P_TABLE_NAME);
      FETCH C_PRIMARY_KEY INTO LC_CONSTRAINT_NAME;
      CLOSE C_PRIMARY_KEY;

      FOR I IN C_CHILD_TABLE(LC_CONSTRAINT_NAME)
      LOOP

      FOR J IN C_PRIMARY_KEY(I.TABLE_NAME)
      LOOP

      FOR K IN C_CHILD_TABLE(J.CONSTRAINT_NAME)
      LOOP
      IF K.TABLE_NAME IS NULL THEN
      LC_SQL := 'DELETE FROM '||I.TABLE_NAME;
      EXECUTE IMMEDIATE LC_SQL;
      ELSE
      LC_SQL := 'DELETE FROM '||K.TABLE_NAME;
      EXECUTE IMMEDIATE LC_SQL;
      END IF;
      END LOOP;

      END LOOP;

      END LOOP;

      LC_SQL := 'DELETE FROM '||P_TABLE_NAME;

      EXECUTE IMMEDIATE LC_SQL;

      P_OUT := 'Record Deleted Successfully';

      EXCEPTION
      WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001,'Delete Tables Procedure : - ' || SQLERRM);
      END;


      If i run the procedure it shows below error,

      DECLARE
      P_OUT VARCHAR2(50);

      BEGIN
      PRC_DELETE_TABLE('SAM_A',P_OUT );

      DBMS_OUTPUT.PUT_LINE ('O/P :- ' ||P_OUT );
      END ;


      Error :

      Error report:
      ORA-20001: Delete Tables Procedure : - ORA-06511: PL/SQL: cursor already open
      ORA-06512: at "SHIPLUS_CMS.PRC_DELETE_TABLE", line 54
      ORA-06512: at line 5


      Regards,
      Fame
        • 1. Re: Error While run the Procedure
          Karthick2003
          EXCEPTION
            WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001,'Delete Tables Procedure : - ' || SQLERRM);
          END;
          This is a very bad coding practice.

          Remove the EXCEPTION WHEN OTHERS clause and run the code again. That will tell you whate exactly you are going wrong.
          1 person found this helpful
          • 2. Re: Error While run the Procedure
            indra budiantho
            try this..
            /* Formatted on 2012/05/07 15:08 (Formatter Plus v4.8.8) */
            CREATE OR REPLACE PROCEDURE prc_delete_table (p_table_name IN VARCHAR2, p_out OUT VARCHAR2)
            IS
              CURSOR c_primary_key (cp_table_name VARCHAR2)
              IS
                SELECT constraint_name
                  FROM user_constraints
                 WHERE table_name = p_table_name AND constraint_type = 'P';
            
              CURSOR c_child_table (cp_constraint_name VARCHAR2)
              IS
                SELECT table_name
                  FROM user_constraints
                 WHERE r_constraint_name = cp_constraint_name;
            
              CURSOR more_c_child_table (cp_constraint_name VARCHAR2)
              IS
                SELECT table_name
                  FROM user_constraints
                 WHERE r_constraint_name = cp_constraint_name;
            
              lc_sql               VARCHAR2 (4000);
              lc_constraint_name   VARCHAR2 (100);
            BEGIN
              OPEN c_primary_key (p_table_name);
            
              FETCH c_primary_key
               INTO lc_constraint_name;
            
              CLOSE c_primary_key;
            
              FOR i IN c_child_table (lc_constraint_name)
              LOOP
                FOR j IN c_primary_key (i.table_name)
                LOOP
                  FOR k IN more_c_child_table (j.constraint_name)
                  LOOP
                    IF k.table_name IS NULL
                    THEN
                      lc_sql := 'DELETE FROM ' || i.table_name;
            
                      EXECUTE IMMEDIATE lc_sql;
                    ELSE
                      lc_sql := 'DELETE FROM ' || k.table_name;
            
                      EXECUTE IMMEDIATE lc_sql;
                    END IF;
                  END LOOP;
                END LOOP;
              END LOOP;
            
              lc_sql := 'DELETE FROM ' || p_table_name;
            
              EXECUTE IMMEDIATE lc_sql;
            
              p_out := 'Record Deleted Successfully';
            EXCEPTION
              WHEN OTHERS
              THEN
                raise_application_error (-20001, 'Delete Tables Procedure : - ' || SQLERRM);
            END;