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')

Oracle: Rename a column in a table

Oracle provides "alter table" syntax to rename data columns in-place in this form:

ALTER TABLE
table_name
RENAME COLUMN
old_column_name
TO
new_column_name;

Here are the example of Oracle "alter table" syntax to rename data columns:

ALTER TABLE
pick_line
RENAME COLUMN
"VOLUME"
TO
"volume";

ORA-01000: maximum open cursors limit exceeded

If you are getting ORA-01000 exception, it means you need to review your design or you need to optimize your code. You need to make sure that all the open cursors are being closed after they are finished being used in application.

To check amount of open cursors in Oracle simple run this query:

select count(*) from v$open_cursor;

To increase/decrease the amount of open cursors limit run the following query:

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

The default value for OPEN_CURSORS is 50, but Oracle recommends that you set this to at least 500 for most applications. Some applications may need more, eg. High volume web applications.

Only increase the OPEN CURSOR limit if your application really needs it, otherwise focus on releasing the unused cursors in your code. For example, In Java close ResultSet or Statement or Connection object as soon as you are finished with them.