Passing Variable No of Arguments to Oracle Stored Procedure

If you want to pass variable number of arguments in a Oracle stored procedure, you can use array Type as stored procedure's parameter to accomplish this. Like in Java you can pass varargs as an array.

1- Create an Oracle nested table type in the database

CREATE OR REPLACE TYPE nt_type IS TABLE OF VARCHAR2(30);

2- Create a procedure in the database

CREATE OR REPLACE PROCEDURE nt_proc (nt nt_type) IS

i NUMBER;

BEGIN

FOR i IN nt.FIRST .. nt.LAST

LOOP

INSERT INTO nt_test(id, description) VALUES (i, nt(i));

END LOOP;

END nt_proc;

/

3- Create a test table, This is just for test purposes.

CREATE TABLE nt_test (id NUMBER, description VARCHAR2(30));

4- Create a module which defines the array of values

DECLARE

nt nt_type := nt_type(); -- nested table variable initialized to empty

BEGIN

nt := nt_type('Chester','Swindon','Corby','London','Swansea','Cardiff'); -- create your string of variables here

nt_proc(nt);

END;

/

SELECT * FROM nt_test;

Renaming Foreign Keys

There is an Oracle DDL command which will rename a Foreign Key constraint:
SQL>ALTER TABLE <tablename> RENAME CONSTRAINT <foreign_key> TO <foreign_key_new_name>;
eg
SQL>ALTER TABLE emp RENAME CONSTRAINT SYS_C0017017 TO FK_EMP_DEPT;
I have written an Oracle stored procedure which shows how this can be done:
1)
CREATE OR REPLACE PROCEDURE rename_foreign_key(p_table_name IN VARCHAR2, p_foreign_key IN VARCHAR2, p_foreign_key_new VARCHAR2)
IS
l_action VARCHAR2(2000) := NULL;
  
  CURSOR c_uc(p_table_name VARCHAR2, p_fk VARCHAR2, p_fk_new VARCHAR2)
  IS
    SELECT 'ALTER TABLE ' || uc.table_name || ' RENAME CONSTRAINT ' || uc.constraint_name || ' TO ' || p_fk_new
    FROM  user_constraints uc 
    WHERE uc.table_name = p_table_name
    AND   uc.constraint_name = p_fk
    AND   uc.constraint_type = 'R';
BEGIN NULL;
  OPEN c_uc(p_table_name, p_foreign_key, p_foreign_key_new);
  FETCH c_uc INTO l_action;
  CLOSE c_uc;
  EXECUTE IMMEDIATE l_action;
END;
/
2) Example: create a master table DEPT and a child table EMP
CREATE TABLE dept (
deptno NUMBER(4) PRIMARY KEY,
dname VARCHAR2(14),
loc VARCHAR2(13));

CREATE TABLE emp (
empno NUMBER(4) PRIMARY KEY,
ename VARCHAR2(10),
deptno NUMBER(2),
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));

3) Find the generated name for the foreign key in the EMP table
SELECT constraint_name FROM user_constraints WHERE table_name = 'EMP' AND constraint_type = 'R' AND generated = 'GENERATED NAME';
  --> SYS_C0017017
4) Rename SYS_C0017017 to FK_EMP_DEPT
SQL> EXEC rename_foreign_key('EMP','SYS_C0017017','FK_EMP_DEPT')