Dynamically pass column names or column id in a PL/SQL procedure
Posted on 2011-10-07
I have a series of tables with similar table structures.
I need to pass the table names and its columns dynamically as parameters to a procedure which have to do insert, update and delete.
I want a unique procedure to be able to do INSERT, UPDATE and DELETE for all the tables
The procedure should return 0 in case it is successful or the oracle code error in case of failure.
The tables can have the structure:
Colum name ID DATA TYPE
T_CD 1 VARCHAR2
T_DSC 2 VARCHAR2
The column with suffix CD always have the Column ID = 1 and the second always have 2 .
The size of T_DSC may vary.
The signature of the procedure is mt(p_tablename, p_id, p_dsc, retcode OUT integer, ERRCODE OUT varchar2).
I have defined it as
mt(p_tablename USER_TABLES.TABLE_NAME%type, p_id VARCHAR2, p_dsc VARCHAR2, retcode OUT integer, ERRCODE OUT varchar2).
Is it there a way I can have a definition of the data types for the column names similar to the definition of tables
That is can I have a signature as
mt(p_tablename USER_TABLES.TABLE_NAME%type, p_id p_tablename.column_id=1%type, p_dsc p_tablename.column_id=2%type, retcode OUT integer, ERRCODE OUT varchar2).
p_tablename.column_id=1%type is to identify that I would like the type to be like the column with column_id = 1. Similarly for p_tablename.column_id=2%type
When I do the insert I have the following position insert statement
Insert into p_tablename values (‘w1’, ‘party’);
However for an update the situation is abit different I cann’t use the column_id to update the values.
A series of IF can be use to check the tablenames and update the tables columns but I would like to avoid it if is possible.
How can I accomplish the procedure.