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

Posted on 2011-10-07
Last Modified: 2012-06-21
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.
Question by:diteps06
    LVL 73

    Assisted Solution

    you can't have the parameter types be dynamic.  parameters must be known at compile time.


    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,  
      retcode OUT  integer,  
    ERRCODE OUT varchar2)

    LVL 73

    Accepted Solution

    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
    LVL 1

    Author Comment

    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;

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Read about achieving the basic levels of HRIS security in the workplace.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now