Home » SQL & PL/SQL » SQL & PL/SQL » selecting the columns dynamically (11.2.0.4)
selecting the columns dynamically [message #658954] Mon, 02 January 2017 03:09 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi All,

I am using more than two queries in the procedure and it inserts the data into the dynamically created table. Query is used for 10 to 11 tables. Need to write 22 queries if I would like use the queries for all the tables in the procedure. Also changes the column values based on the table name. So I have used arrays instead of individual queries but I could not able to generate the column values in the procedure. Please advice am I doing a right way or is there any other way to do this.

For example, in the below package TAB_ARRAY is 'INSTANCE_DATE_DATA' then DATE_VALUE should be populated remaining values like NUMBER_VALUE, BOOLEAN_VALUE etc are NULL. These values are changed based on the table name.

create or replace package test_bidirect is

procedure dynamic_table_test_create(i_projid number, i_busparentObjid number, i_attrId varchar2, 
                   i_buschildObjid number, i_busmainObjid number, i_startdate varchar2, i_enddate varchar2,
                   i_tabname varchar2 );

end test_bidirect;

create or replace package body test_bidirect is

procedure dynamic_table_bidirect_insert(i_projid number, i_busparentObjid number, i_attrId varchar2, 
                     i_buschildObjid number, i_busmainObjid number, i_startdate varchar2, i_enddate varchar2,
                    v_tabname varchar2) is

    TYPE tab_array_type IS TABLE OF VARCHAR2(40) INDEX BY binary_integer;
    tab_array    tab_array_type;
     v_value NUMBER;
     v_sql1 varchar2(32767);
     v_sql2 varchar2(32767);
     
begin

    SELECT table_name BULK COLLECT INTO tab_array 
      from user_tables where table_name like'INSTANCE%DATA'; 
        
  FOR I in tab_array.first..tab_array.last LOOP
        
   -- parent FIELDS WITH PARENT AND CHILD data 
    SELECT 'INSERT INTO ' || table_name ||
      ' SELECT D.PROJECT_ID,
              d2.BUSINESSOBJECT_ID,
              i1.INSTANCE_NAME,
              i.instance_name "PARENT_INSTANCE",
              i3.instance_name "CHILD_INSTANCE",
              D.ATTRIBUTE_ID,
              D.REVISION,
              D.ATTRIBUTE_TYPE_ID,' ||
			' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_DATE_DATA'', ''D.DATE_VALUE'',NULL ) "DATE_VALUE" , ' ||
			' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_TEXT_DATA'', ''D.TEXT_VALUE'',NULL ) "TEXT_VALUE" , ' ||
			' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_NOTE_DATA'', ''D.NOTE_VALUE'',NULL ) "NOTE_VALUE" , ' ||
			' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_NUMBER_DATA'', ''D.NUMBER_VALUE'',NULL ) "NUMBER_VALUE" , ' ||
			' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_DROPDOWN_DATA'', ''D.DROPDOWN_VALUE'',NULL ) "DROPDOWN_VALUE" , ' ||
			' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_REFERENCE_DATA'', ''D.REFERENCE_VALUE'',NULL ) "REFERENCE_VALUE" , ' ||              
			' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_LINK_DATA'', ''D.LINK_VALUE'',NULL ) "LINK_VALUE" , ' ||              
			' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_DT_DATA'', ''D.DATETIME_VALUE'',NULL ) "DATETIME_VALUE" , ' ||              
			' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_BOOLEAN_DATA'', ''D.BOOLEAN_VALUE'',NULL ) "BOOLEAN_VALUE" , ' ||             
             ' I.INSTANCE_CREATED_DATE,
              I.INSTANCE_UPDATED_DATE 
        FROM ' ||TAB_ARRAY(I)|| ' D,' ||-- INSTANCE_BOOLEAN_DATA D,
            ' instance_reference_data d2,
              instance_reference_data d3,
              INSTANCES i1,
              instances I,
              instances i3
            WHERE D.PROJECT_ID       =I.PROJECT_ID
            AND D.BUSINESSOBJECT_ID  =I.BUSINESSOBJECT_ID
            AND D.INSTANCE_ID        =I.INSTANCE_ID
            AND D.LANGUAGE_ID        =1
            AND D.PROJECT_ID         = '||i_projid|| 
          ' AND D.BUSINESSOBJECT_ID='||i_busparentObjid || 
          ' AND d2.language_id       =1
            AND i1.language_id       =1
            AND I.language_id        =1
            AND d2.project_id        =d.project_id
            AND I.instance_id        =d2.reference_instance_id
            AND I.businessobject_id  =d2.ref_businessobject_id
            AND I.project_id         =d2.project_id
            AND d2.BUSINESSOBJECT_ID = ' ||i_busmainObjid ||
          ' AND d2.businessobject_id =i1.businessobject_id
            AND d2.project_id        =i1.project_id
            and d2.instance_id=i1.instance_id
            and d2.businessobject_id=d3.ref_businessobject_id
            and d2.instance_id=d3.reference_instance_id
            and d3.businessobject_id = ' ||i_buschildObjid ||
          ' and d3.instance_id=i3.instance_id
            and d3.BUSINESSOBJECT_ID=I3.BUSINESSOBJECT_ID 
            AND ( D.ATTRIBUTE_ID IN (' ||i_attrId || ')' || 
          ' OR ( NVL('''||i_attrId||''','||'''NULL'' )'|| ' = ''NULL'' ) )' ||
      ' AND I1.INSTANCE_CREATED_DATE between ' || 'NVL('||
      ' to_date ( '''|| i_startdate    || ''',' || '''mm-dd-yyyy''' || ')' || 
      ',' || 'I1.INSTANCE_CREATED_DATE' ||')'||
      ' AND ' || 'NVL('||
      ' to_date ( '''|| i_enddate    || ''',' || '''mm-dd-yyyy''' || ')' || 
      ',' || 'SYSDATE' ||')' 
     into v_sql1
     FROM user_tables WHERE TABLE_NAME=v_tabname;

   execute immediate v_sql1;
        
   commit;
   
   --main data WITH PARENT AND CHILD
    SELECT 'INSERT INTO ' || table_name ||
      ' SELECT D.PROJECT_ID,
            d2.BUSINESSOBJECT_ID,
            i.INSTANCE_NAME,
            i1.instance_name "PARENT_INSTANCE",
            i3.instance_name "CHILD_INSTANCE",
            D.ATTRIBUTE_ID,
            D.REVISION,
            D.ATTRIBUTE_TYPE_ID,' ||
          ' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_DATE_DATA'', ''D.DATE_VALUE'',NULL ) "DATE_VALUE" , ' ||
          ' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_TEXT_DATA'', ''D.TEXT_VALUE'',NULL ) "TEXT_VALUE" , ' ||
          ' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_NOTE_DATA'', ''D.NOTE_VALUE'',NULL ) "NOTE_VALUE" , ' ||
          ' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_NUMBER_DATA'', ''D.NUMBER_VALUE'',NULL ) "NUMBER_VALUE" , ' ||
          ' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_DROPDOWN_DATA'', ''D.DROPDOWN_VALUE'',NULL ) "DROPDOWN_VALUE" , ' ||
          ' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_REFERENCE_DATA'', ''D.REFERENCE_VALUE'',NULL ) "REFERENCE_VALUE" , ' ||              
          ' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_LINK_DATA'', ''D.LINK_VALUE'',NULL ) "LINK_VALUE" , ' ||              
          ' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_DT_DATA'', ''D.DATETIME_VALUE'',NULL ) "DATETIME_VALUE" , ' ||              
          ' DECODE(''' || TAB_ARRAY(I) || ''',' || ' ''INSTANCE_BOOLEAN_DATA'', ''D.BOOLEAN_VALUE'',NULL ) "BOOLEAN_VALUE" , ' ||
          ' I.INSTANCE_CREATED_DATE,
            I.INSTANCE_UPDATED_DATE
          FROM ' ||TAB_ARRAY(I)|| ' D,' || --INSTANCE_BOOLEAN_DATA D,
          ' instance_reference_data d2,
            instance_reference_data d3,
            INSTANCES i1,
            instances I,
            instances i3
          WHERE D.PROJECT_ID       =I.PROJECT_ID
          AND D.BUSINESSOBJECT_ID  =I.BUSINESSOBJECT_ID
          AND D.INSTANCE_ID        =I.INSTANCE_ID
          AND D.LANGUAGE_ID        =1
          AND D.PROJECT_ID         = '||i_projid|| 
        ' AND d.businessobject_id  = '||i_busmainObjid || 
        ' AND d2.language_id       =1
          AND i1.language_id       =1
          AND I.language_id        =1
          AND d2.project_id        =d.project_id
          AND I.instance_id        =d2.instance_id
          AND I.businessobject_id  =d2.businessobject_id
          AND I.project_id         =d2.project_id
          AND d2.ref_BUSINESSOBJECT_ID = ' ||i_busparentObjid ||
        ' and d2.businessobject_id ='||i_busmainObjid || 
        ' AND d2.ref_businessobject_id =i1.businessobject_id
          AND d2.project_id        =i1.project_id
          and d2.reference_instance_id=i1.instance_id
          and d.businessobject_id=d3.ref_businessobject_id
          and d.instance_id=d3.reference_instance_id
          and d3.businessobject_id=458
          and d3.ref_businessobject_id= '||i_busmainObjid ||
        ' and d3.instance_id=i3.instance_id
          and d3.BUSINESSOBJECT_ID=I3.BUSINESSOBJECT_ID
          AND ( D.ATTRIBUTE_ID IN (' ||i_attrId || ')' || 
          ' OR ( NVL('''||i_attrId||''','||'''NULL'' )'|| ' = ''NULL'' ) )' ||
        ' AND I1.INSTANCE_CREATED_DATE between ' || 'NVL('||
        ' to_date ( '''|| i_startdate    || ''',' || '''mm-dd-yyyy''' || ')' || 
        ',' || 'I1.INSTANCE_CREATED_DATE' ||')'||
        ' AND ' || 'NVL('||
        ' to_date ( '''|| i_enddate    || ''',' || '''mm-dd-yyyy''' || ')' || 
        ',' || 'SYSDATE' ||')' 
     into v_sql2
     FROM user_tables WHERE TABLE_NAME=v_tabname;
   
   execute immediate v_sql2;
        
   commit;

  END LOOP; 

  end dynamic_table_bidirect_insert;

    procedure dynamic_table_test_create(i_projid number, i_busparentObjid number, i_attrId varchar2, 
                   i_buschildObjid number, i_busmainObjid number, i_startdate varchar2, i_enddate varchar2,
                   i_tabname varchar2 ) is
    
    t_n number;
    v_tabname varchar2(50);
    v_tab_count number;
    begin
    
    select count(*) into v_tab_count
      from user_tables 
      where table_name=UPPER(i_tabname);
      
      if v_tab_count = 0 then
    
          execute immediate 'CREATE TABLE '||i_tabname ||'( PROJECT_ID NUMBER, BUSINESSOBJECT_ID NUMBER(4),INSTANCE_NAME VARCHAR2(50),
                              PARENT_INSTANCE VARCHAR2(50), CHILD_INSTANCE VARCHAR2(50), ATTRIBUTE_ID varchar2(400), REVISION NUMBER(10),
                              ATTRIBUTE_TYPE_ID NUMBER(4), DATE_VALUE VARCHAR2(400),  TEXT_VALUE VARCHAR2(400), 
                                 NOTE_VALUE VARCHAR2(400), NUMBER_VALUE VARCHAR2(400), DROPDOWN_VALUE VARCHAR2(400),
                                 REFERENCE_VALUE VARCHAR2(400), LINK_VALUE VARCHAR2(400), DATETIME_VALUE DATE, 
                                 BOOLEAN_VALUE VARCHAR2(400), INSTANCE_CREATED_DATE DATE, INSTANCE_UPDATED_DATE DATE)';
                                
      else
      execute immediate 'DROP TABLE ' ||i_tabname;
      
          execute immediate 'CREATE TABLE '||i_tabname ||'( PROJECT_ID NUMBER, BUSINESSOBJECT_ID NUMBER(4),INSTANCE_NAME VARCHAR2(50),
                              PARENT_INSTANCE VARCHAR2(50), CHILD_INSTANCE VARCHAR2(50), ATTRIBUTE_ID varchar2(400), REVISION NUMBER(10),
                              ATTRIBUTE_TYPE_ID NUMBER(4), DATE_VALUE VARCHAR2(400),  TEXT_VALUE VARCHAR2(400), 
                                 NOTE_VALUE VARCHAR2(400), NUMBER_VALUE VARCHAR2(400), DROPDOWN_VALUE VARCHAR2(400),
                                 REFERENCE_VALUE VARCHAR2(400), LINK_VALUE VARCHAR2(400), DATETIME_VALUE DATE, 
                                 BOOLEAN_VALUE VARCHAR2(400), INSTANCE_CREATED_DATE DATE, INSTANCE_UPDATED_DATE DATE)';
      
      end if;
      
    select table_name into v_tabname from user_tables where table_name=UPPER(i_tabname);
      
    dynamic_table_bidirect_insert(i_projid, i_busparentObjid, i_attrId, i_buschildObjid, i_busmainObjid, i_startdate, i_enddate, v_tabname);  

    end dynamic_table_test_create;                  

end test_bidirect;
Re: selecting the columns dynamically [message #658955 is a reply to message #658954] Mon, 02 January 2017 04:23 Go to previous messageGo to next message
John Watson
Messages: 7216
Registered: January 2010
Location: Global Village
Senior Member
It is likely that no-one will assist with this unless you can give a reason for attempting to do it. It will have to be a pretty good reason, because there are very few situations where using dynamic SQL (especially using DDL statements) is a necessary. Even fewer where it is a good idea.
Re: selecting the columns dynamically [message #658956 is a reply to message #658955] Mon, 02 January 2017 04:30 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
We are inserting the data from different tables into one table for reporting purpose due to this using dynamic table concept.
Re: selecting the columns dynamically [message #658957 is a reply to message #658956] Mon, 02 January 2017 04:34 Go to previous messageGo to next message
John Watson
Messages: 7216
Registered: January 2010
Location: Global Village
Senior Member
grpatwari wrote on Mon, 02 January 2017 10:30
We are inserting the data from different tables into one table for reporting purpose due to this using dynamic table concept.
Not good enough! You probably should not be doing that. Just run your reports against the source tables.
Re: selecting the columns dynamically [message #658958 is a reply to message #658956] Mon, 02 January 2017 04:37 Go to previous messageGo to next message
John Watson
Messages: 7216
Registered: January 2010
Location: Global Village
Senior Member
I should add:

I do realize that it must be irritating if you ask for help with something, and instead of getting the help, all you are told is "don't do it like that". If you prefer to ignore such advice, you are of course free so to do. But I certainly do not feel able to assist with implementing what seems to me to be a big mistake.
Re: selecting the columns dynamically [message #658960 is a reply to message #658958] Mon, 02 January 2017 06:16 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Given that he does this since many topics and always ignored your (and other) such advises, I think he will ignore this one once more and just want we provide a complete solution which Barbara will give him despite (for instance):

Michel Cadot wrote on Fri, 19 August 2016 08:08
Barbara Boehmer wrote on Fri, 19 August 2016 02:45
Change:
...
to:
...
No! Never do that, never concatenate input values.
2 main reasons:
- you kill your SGA and then performances
- SQL injection
Previous Topic: Problem accessing external tables over network
Next Topic: Insert the data into dynamically created table (2 threads merged by bb)
Goto Forum:
  


Current Time: Thu Jan 18 14:36:40 CST 2018

Total time taken to generate the page: 0.01797 seconds