Avatar of diteps06
Flag for United States of America

asked on 

Dynamically pass column names or column id in a PL/SQL procedure

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

 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.
Oracle DatabaseDatabases

Avatar of undefined
Last Comment

8/22/2022 - Mon