Create procedure in oracle DB for finding and deleting foreign key constraints for specific tables

Question

I am new to Oracle, I am trying to write a procedure in Oracle to delete foreign key constraints for a table. I have already done this for MySQL and its working. I am not sure with the syntax, apologies for that, but my query is working If I run it individually. I want to do the same thing(removing foreign key constraints) for multiple tables and don't want to write queries multiple times. hence first I am finding the foreign keys associated with that table , storing them in cursor and later removing all of the foreign keys by creating and executing drop constraints query associated with that table. following code is giving multiple errors to me.

CREATE OR REPLACE PROCEDURE removeConstraintsForTable(vTableName IN varchar2) IS
BEGIN
cName VARCHAR(2048);
sql_stmt VARCHAR2(2048);
CURSOR cur IS
          SELECT DISTINCT CONSTRAINT_NAME
          FROM ALL_CONSTRAINTS WHERE OWNER= sys_context('userenv','current_schema')
          AND TABLE_NAME = vTableName AND CONSTRAINT_TYPE='R';
BEGIN
    OPEN cur;
    LOOP
    FETCH cur INTO cName;
    EXIT WHEN cur%notfound;
    sql_stmt := CONCAT(CONCAT(CONCAT('ALTER TABLE ',vTableName),CONCAT(' DROP FOREIGN KEY ',cName)),';');
    SELECT sql_stmt FROM dual;
    INSERT INTO TEMP(Name) VALUES(sql_stmt);
    COMMIT;
    END LOOP;
END
/


CALL removeConstraintsForTable('table1');
CALL removeConstraintsForTable('table2');
CALL removeConstraintsForTable('table3');
CALL removeConstraintsForTable('table4');
COMMIT;

Answer 1

You have an extra BEGIN right at the start of your procedure, and the final END is missing a semicolon. You shouldn't really be using VARCHAR, and you could declare the cName variable using the data dictionary anyway; however an implicit loop will be simpler, as will using the concatenation operator || instead of nested CONCAT() calls, and the generated statement should not end in a semicolon:

create or replace procedure removeconstraintsfortable(p_table_name in varchar2) is
  sql_stmt varchar2(2048);
begin
  for rec in (
    select owner, constraint_name
    from all_constraints
    where owner = sys_context('userenv','current_schema')
    and table_name = p_table_name
    and constraint_type = 'R'
  )
  loop
    sql_stmt := 'ALTER TABLE "' || rec.owner || '"."' || p_table_name || '"'
      || ' DROP CONSTRAINT "' || rec.constraint_name || '"';

    insert into temp(name) values(sql_stmt);
  end loop;
  commit;
end;
/

As pointed out in comments, the generated statement should be drop constraint.

I'm not sure why you're inserting into a table or where you execute the statement, but you can do it all in one if you prefer:

create or replace procedure removeconstraintsfortable(p_table_name in varchar2) is
  sql_stmt varchar2(2048);
begin
  for rec in (
    select owner, constraint_name
    from all_constraints
    where owner = sys_context('userenv','current_schema')
    and table_name = p_table_name
    and constraint_type = 'R'
  )
  loop
    sql_stmt := 'ALTER TABLE "' || rec.owner || '"."' || p_table_name || '"'
      || ' DROP CONSTRAINT "' || rec.constraint_name || '"';

    dbms_output.put_line(sql_stmt);
    execute immediate sql_stmt;
  end loop;
end;
/

The dbms_output call just shows you the generated statement(s), before execute immediate executes it, well, immediately.

Quick demo; very basic table set-up:

create table t42 (id number primary key);
create table t43 (id number references t42 (id));

select table_name, constraint_name, constraint_type
from all_constraints
where table_name in ('T42', 'T43');

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
T43                            SYS_C00138153                  R
T42                            SYS_C00138152                  P

Then call the procedure which shows the generated statement:

set serveroutput on
exec removeConstraintsForTable('T43');

ALTER TABLE "STACKOVERFLOW"."T43" DROP CONSTRAINT "SYS_C00138153"

PL/SQL procedure successfully completed.

and then check the constraint has gone:

select table_name, constraint_name, constraint_type
from all_constraints
where table_name in ('T42', 'T43');

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
T42                            SYS_C00138152                  P

Answer 2

I fixed a number of syntax issues for you. Try this.

CREATE OR REPLACE PROCEDURE removeConstraintsForTable(vTableName IN varchar2) IS
    cName VARCHAR2(30); -- identifiers are max 30 chars
    sql_stmt VARCHAR2(2048);
    CURSOR cur IS
          SELECT DISTINCT CONSTRAINT_NAME
          FROM USER_CONSTRAINTS 
          WHERE TABLE_NAME = vTableName AND CONSTRAINT_TYPE='R';
BEGIN
    OPEN cur;
    LOOP
    FETCH cur INTO cName;
    EXIT WHEN cur%notfound;
    sql_stmt := 'ALTER TABLE ' || vTableName || ' DROP CONSTRAINT ' || cName;
    INSERT INTO RANGERADMIN1.TEMP(Name) VALUES(sql_stmt);
    END LOOP;
    COMMIT;
END removeConstraintsForTable;
/

call removeConstraintsForTable('table1');
call removeConstraintsForTable('table2');
call removeConstraintsForTable('table3');
call removeConstraintsForTable('table4');
-- COMMIT; -- not necessary