• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

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

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.
0
diteps06
Asked:
diteps06
  • 2
2 Solutions
 
sdstuberCommented:
you can't have the parameter types be dynamic.  parameters must be known at compile time.

however...

if your tables all have similar structure.  You could use one of them as a representative for all of them

mt(p_tablename USER_TABLES.TABLE_NAME%type,  
p_id  YOUR_TABLE.YOUR_FIRST_COLUMN%type,
 p_dsc  YOUR_TABLE.YOUR_SECOND_COLUMN%type,
  retcode OUT  integer,  
ERRCODE OUT varchar2)



0
 
sdstuberCommented:
also note,  the column-based types in parameters are a bit misleading
because the details of the columns aren't enforced

for example -
if your column is varchar2(20),  
the parameter is still just VARCHAR2, it doesn't inherit the column's limits
0
 
diteps06Author Commented:
I think I would use the VARCHAR2  in the signature of the procedure to the columns.

As regards the UPDATE  I intend to use 2 internally variables  P_tmp_id  and P_tmp_dsc  define  as varchar2 variables which return the column names and use  in the DML statement.

P_tmp_id =  Select column_name from all_tab_columns where table_name=p_tablename AND COLUMN_ID=1
P_tmp_dsc =  Select column_name from all_tab_columns where table_name=p_tablename AND COLUMN_ID=2

update V_TABLENAME
set P_tmp_dsc =V_DSC
where  P_tmp_id = V_ID;
commit;
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now