7 Replies Latest reply: Oct 5, 2012 11:04 PM by ranit B RSS

    help needed in forming dynamic insert statement

    user11942774
      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
          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
            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
              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
                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
                  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
                    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
                      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