Re: Can't run "execute immediate" in PL/SQL

From: Kishore H <khejmady_at_melstar.com>
Date: 23 Dec 2002 02:09:20 -0800
Message-ID: <ed8ee3e4.0212230209.3b526f0d@posting.google.com>

TRY THIS PIECE OF CODE. HOPE THIS IS HELPFUL CREATE OR REPLACE procedure alter_cons(p_action Varchar2) IS

    CURSOR cons_list_cur
    IS
    SELECT constraint_name, table_name, status     FROM user_constraints
    WHERE constraint_type = 'R';     

     cons_stmt VARCHAR2(1000);
BEGIN
    FOR cons_list_rec IN cons_list_cur LOOP

            IF (  upper(p_action) = 'DISABLE' ) THEN
                 IF ( cons_list_rec.status = 'DISABLED') THEN
                      dbms_output.put_line('CONSTRAINT

'||cons_list_rec.constraint_name||' ON TABLE
'||cons_list_rec.table_name||' IS ALREADY '||cons_list_rec.status);

                      cons_stmt := '  ';
                 ELSE
                      cons_stmt :=  'ALTER TABLE

'||cons_list_rec.table_name||' disable constraint
'||cons_list_rec.constraint_name;

                      EXECUTE IMMEDIATE cons_stmt;
                      dbms_output.put_line('CONSTRAINT

'||cons_list_rec.constraint_name||' ON TABLE
'||cons_list_rec.table_name||' IS DISABLED');

                 END IF;
            ELSIF (  upper(p_action) = 'ENABLE' ) THEN
                 IF ( cons_list_rec.status = 'ENABLED') THEN
                      dbms_output.put_line('CONSTRAINT

'||cons_list_rec.constraint_name||' ON TABLE
'||cons_list_rec.table_name||' IS ALREADY '||cons_list_rec.status);

                      cons_stmt := '  ';
                 ELSE
                      cons_stmt :=  'ALTER TABLE

'||cons_list_rec.table_name||' enable constraint
'||cons_list_rec.constraint_name;

                      EXECUTE IMMEDIATE cons_stmt;
                      dbms_output.put_line('CONSTRAINT

'||cons_list_rec.constraint_name||' ON TABLE
'||cons_list_rec.table_name||' IS ENABLED');

                 END IF;
            ELSE
                dbms_output.put_line('INVALID INPUT');
            END IF;

    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
           dbms_output.put_line('ERROR '||sqlerrm); END alter_cons;
/

THIS IS THE OUTPUT AT MY END. SQL> execute alter_cons('ENABLE');
CONSTRAINT FK_DEPTNO ON TABLE EMP IS ENABLED PL/SQL procedure successfully completed.

SQL> execute alter_cons('ENABLE');
CONSTRAINT FK_DEPTNO ON TABLE EMP IS ALREADY ENABLED PL/SQL procedure successfully completed.

SQL> execute alter_cons('DISABLE');
CONSTRAINT FK_DEPTNO ON TABLE EMP IS DISABLED PL/SQL procedure successfully completed.

SQL> execute alter_cons('DISABLE');
CONSTRAINT FK_DEPTNO ON TABLE EMP IS ALREADY DISABLED PL/SQL procedure successfully completed. Received on Mon Dec 23 2002 - 04:09:20 CST