This discussion is archived
7 Replies Latest reply: Oct 5, 2012 9:04 PM by ranit B RSS

help needed in forming dynamic insert statement

user11942774 Newbie
Currently Being Moderated
HI,

I am trying to construct a insert statement as given below.
Please help me as to how to form this and where am i going wrong?
i want the columns names to be auto populated when a table name is given.

output should be like:
==============

insert into tablename
(
col1,col2,col3..coln)
values
(col1, col2, col3..coln);

declare
v_sql varchar2(5000);
cnt number :=0;
col_count number; 
name varchar2(5000);  

TYPE string_assarrtype IS TABLE OF VARCHAR2 ( 25 ) INDEX BY VARCHAR2 ( 20 );
arr string_assarrtype;
BEGIN
   select count(column_name) into col_count from user_tab_columns 
  where
  table_name = 'PS_D_RE_BCSREGIONMAPPING';
   
   v_sql:='insert into tablename (' ;
 
     for i  in 1..arr(col_count)
      loop
       v_sql:='v_sql' ||' '||arr(i);
      DBMS_OUTPUT.PUT_lINE(v_sql);
     END LOOP;
 END;
  • 1. Re: help needed in forming dynamic insert statement
    sb92075 Guru
    Currently Being Moderated
    I suggest that you stick with static SQL until after you know what you are doing with it.

    What you posted does not come close to constructing valid INSERT statement.
  • 2. Re: help needed in forming dynamic insert statement
    6363 Guru
    Currently Being Moderated
    Your output is not a valid insert statement.

    Where are the values to be inserted to the unknown table supposed to come from?

    This is a really bad idea.
  • 3. Re: help needed in forming dynamic insert statement
    user11942774 Newbie
    Currently Being Moderated
    Hi,

    Actually i am trying to use this logic for writing a audit trigger(as the same logic is used for diff tables) where the statement for insert should like below:
    Since this is a repetitive statement used i am trying to pass in the audit_table name so that the columns are populated.
    Please help me on this..

    insert into audittable
    (col1, col2..coln)
    values
    (:new.col1, :new.col2..)
  • 4. Re: help needed in forming dynamic insert statement
    user11942774 Newbie
    Currently Being Moderated
    Below is the actual code which i am trying to write:
    Since the below trigger logic is same for around 25/30 tables i wanted to construct a generic procedure which will take input parameter as tablename and auit_table and form the insert statement which will populate the column names which are different for different tables.
    Please guide...
    CREATE OR REPLACE PROCEDURE make_trigger(  tablename  in  VARCHAR2,
                                             auditable  in   VARCHAR2                                                                                   
    AS
    v_sql varchar(10000);
    
    begin
    
    v_sql := 'DROP TRIGGER  TRD_D_AUDIT_D'||tablename;
     dbms_output.put_line (v_sql );
    
    v_sql:='CREATE OR REPLACE TRIGGER TRG_D_AUDIT_D_'||tablename||' '||
    'BEFORE INSERT OR UPDATE OR DELETE ON 'tablename||' '||' REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW';
    
    dbms_output.put_line (v_sql );
    
    v_sql:= 'DISABLE
    DECLARE
    v_Operator        psoprdefn.oprid%type;
    v_Sysdate        Date;
    v_Actn            varchar2(1);
    V_AUDIT_RECNAME    PSRECDEFN.RECNAME%type;
    
    BEGIN';
    
    dbms_output.put_line (v_sql );
    
    v_sql:= 'If (Inserting) Then
       v_Actn    :='||'''||'A'||'''||';'||
       'Insert into '||auditable||'
                        ('||
                       AUDIT_RECNAME,
                    AUDIT_ACTN,
                    AUDIT_OPRID,
                    AUDIT_STAMP,
                    D_USER_GROUP,
                    D_GROUP_DESC,
                    D_BCS_ID,
                    D_UBR_ID,
                    D_FILTER_ID,
                    D_NAME_ACCESS_TYPE,
                    D_GLOBAL_EMP_LIST
                   )
                        Values
                   (
                    V_AUDIT_RECNAME,
                    v_Actn,
                    v_Operator,
                    v_Sysdate,
                    :NEW.D_USER_GROUP,
                    :NEW.D_GROUP_DESC,
                    :NEW.D_BCS_ID,
                    :NEW.D_UBR_ID,
                    :NEW.D_FILTER_ID,
                    :NEW.D_NAME_ACCESS_TYPE,
                    :NEW.D_GLOBAL_EMP_LIST
                   );
    end if;
    
    end;
    Edited by: user11942774 on Oct 5, 2012 11:46 AM

    Edited by: user11942774 on Oct 5, 2012 8:09 PM

    Edited by: user11942774 on Oct 5, 2012 8:10 PM
  • 5. Re: help needed in forming dynamic insert statement
    sb92075 Guru
    Currently Being Moderated
    user11942774 wrote:
    Below is the actual code which i am trying to write:
    Since the below trigger logic is same for around 25/30 tables i wanted to construct a generic procedure which will take input parameter as tablename and auit_table and form the insert statement which will populate the column names which are different for different tables.
    Please guide...
    Column names below seem to be static & not different for different tables.
    CREATE OR REPLACE PROCEDURE make_trigger(  tablename  in  VARCHAR2,
    auditable  in   VARCHAR2                                                                                   
    AS
    v_sql varchar(10000);
    
    begin
    
    v_sql := 'DROP TRIGGER  TRD_D_AUDIT_D'||tablename;
    dbms_output.put_line (v_sql );
    
    v_sql:='CREATE OR REPLACE TRIGGER TRG_D_AUDIT_D_'||tablename||' '||
    'BEFORE INSERT OR UPDATE OR DELETE ON 'tablename||' '||' REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW';
    
    dbms_output.put_line (v_sql );
    
    v_sql:= 'DISABLE
    DECLARE
    v_Operator        psoprdefn.oprid%type;
    v_Sysdate        Date;
    v_Actn            varchar2(1);
    V_AUDIT_RECNAME    PSRECDEFN.RECNAME%type;
    
    BEGIN';
    
    dbms_output.put_line (v_sql );
    
    v_sql:= 'If (Inserting) Then
    v_Actn    :='||'''||'A'||'''||';'||
    'Insert into '||auditable||'
    ('||
    AUDIT_RECNAME,
    AUDIT_ACTN,
    AUDIT_OPRID,
    AUDIT_STAMP,
    D_USER_GROUP,
    D_GROUP_DESC,
    D_BCS_ID,
    D_UBR_ID,
    D_FILTER_ID,
    D_NAME_ACCESS_TYPE,
    D_GLOBAL_EMP_LIST
    )
    Values
    (
    V_AUDIT_RECNAME,
    v_Actn,
    v_Operator,
    v_Sysdate,
    :NEW.D_USER_GROUP,
    :NEW.D_GROUP_DESC,
    :NEW.D_BCS_ID,
    :NEW.D_UBR_ID,
    :NEW.D_FILTER_ID,
    :NEW.D_NAME_ACCESS_TYPE,
    :NEW.D_GLOBAL_EMP_LIST
    );
    end if;
    
    end;
    Edited by: user11942774 on Oct 5, 2012 11:46 AM

    Edited by: user11942774 on Oct 5, 2012 8:09 PM

    Edited by: user11942774 on Oct 5, 2012 8:10 PM
  • 6. Re: help needed in forming dynamic insert statement
    user11942774 Newbie
    Currently Being Moderated
    Hi Sb,
    The column names what i have put is static.
    How do i dynamically populate it.. please help:

    Below is an example for insert, where in the column names starting with D_ will be different for different tables.
    Is there a way to populate the columns and construct the insert statement during execution of procedure when i pass in input parameters as tablename to procedure.

    Please help...

    Insert into '||auditable||'
    ('||
    AUDIT_RECNAME,
    AUDIT_ACTN,
    AUDIT_OPRID,
    AUDIT_STAMP,
    D_USER_GROUP,
    D_GROUP_DESC,
    D_BCS_ID,
    D_UBR_ID,
    D_FILTER_ID,
    D_NAME_ACCESS_TYPE,
    D_GLOBAL_EMP_LIST
    )
    Values
    (
    V_AUDIT_RECNAME,
    v_Actn,
    v_Operator,
    v_Sysdate,
    :NEW.D_USER_GROUP,
    :NEW.D_GROUP_DESC,
    :NEW.D_BCS_ID,
    :NEW.D_UBR_ID,
    :NEW.D_FILTER_ID,
    :NEW.D_NAME_ACCESS_TYPE,
    :NEW.D_GLOBAL_EMP_LIST
    );

    Edited by: user11942774 on Oct 5, 2012 8:44 PM

    Edited by: user11942774 on Oct 5, 2012 8:46 PM
  • 7. Re: help needed in forming dynamic insert statement
    ranit B Expert
    Currently Being Moderated
    You can get column names for all tables by querying ALL_TAB_COLS

    check if this helps...
    SELECT column_name,data_type
    FROM all_tab_cols
    WHERE table_name='<table_name>'
    AND owner='<user_name>';
    I guess variable "auditable" is the table name... right?
    If yes, then use that in the above query.

    Ranit B.

    Edited by: ranit B on Oct 6, 2012 9:32 AM
    -- added auditable

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points