Home » SQL & PL/SQL » SQL & PL/SQL » Insert the data into dynamically created table (2 threads merged by bb) (11.2.0.4)
Insert the data into dynamically created table (2 threads merged by bb) [message #655006] Thu, 18 August 2016 18:25 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi All,

I am using 11.2.0.4 and I have created the table dynamically with time and fixed number of columns through database package. Now I would like to insert the data into the table by passing the parameter values.

Please advice and provide other better way if any.

create or replace package dynamic_table_generation is

  procedure dynamic_table_create(i_projid number, i_busObjid number, 
                i_attrId varchar2, i_lang_id number, i_startdate varchar2, i_enddate varchar2, 
                i_norowsstart number,i_norowsend number,i_instance varchar2, i_attr_type_id number);

end dynamic_table_generation;
/

create or replace package body dynamic_table_generation is


procedure dynamic_table_insert(i_projid number, i_busObjid number, 
                i_attrId varchar2, i_lang_id number, i_startdate varchar2, i_enddate varchar2, 
                i_norowsstart number,i_norowsend number,i_instance varchar2, i_attr_type_id number, 
                v_tabname varchar2) is
  
  v_column_list     VARCHAR2(4096) := null;
  v_insert_list     VARCHAR2(16096);
  v_ref_cur_columns VARCHAR2(16096) := null;
  v_ref_cur_query   VARCHAR2(16000);
  v_ref_cur_output  VARCHAR2(16000) := null;
  v_column_name     VARCHAR2(256);
  v_query varchar2(32767);
 /* CURSOR c1 IS
       SELECT column_name, data_type FROM user_tab_columns
       WHERE table_name = v_tabname ORDER BY column_id;*/
  refcur            sys_refcursor; 
  
  begin
    
SELECT 'INSERT INTO ' || table_name ||' VALUES ' ||'(' ||
  'i_projid' ||' ,' ||
 'i_busObjid' ||','|| 
 'i_attrId' ||','|| 
 'i_lang_id' ||','||
 'to_char(i_startdate'||','|| '''dd-mm-yyyy'''||')' ||','||
 'to_char(i_enddate'||','|| '''dd-mm-yyyy'''||')' ||','||
 'i_norowsstart' ||','||
 'i_norowsend' ||','||
 'i_instance' ||','||
 'i_attr_type_id'
AS "Create Table Script" into v_query
FROM user_tables WHERE TABLE_NAME=v_tabname;

execute immediate v_query;

DBMS_OUTPUT.PUT_LINE (v_query); 
end dynamic_table_insert;
  
procedure dynamic_table_create(i_projid number, i_busObjid number, 
                i_attrId varchar2, i_lang_id number, i_startdate varchar2, i_enddate varchar2, 
                i_norowsstart number,i_norowsend number,i_instance varchar2, i_attr_type_id number) is

t_n number;
v_tabname varchar2(50);
begin
  t_n := dbms_utility.get_time;

dbms_output.put_line('BEFORE TABLE: '||t_n);

execute immediate 'CREATE TABLE V_DATA_'||t_n ||'( PROJECT_ID NUMBER, BUSINESSOBJECT_ID NUMBER(4),
                       ATTRIBUTE_ID varchar2(400), LANGUAGE_ID NUMBER(2), START_DATE DATE, END_DATE DATE, START_NO_ROWS NUMBER(10), 
                       END_NO_ROWS NUMBER(10), INSTANCENAME VARCHAR2(50), ATTRIBUTE_TYPE_ID NUMBER(10))';

select table_name into v_tabname from user_tables where table_name='V_DATA_'||t_n;

dbms_output.put_line('after TABLE: '||v_tabname);
                       
dynamic_table_insert(i_projid, i_busObjid, i_attrId, i_lang_id, i_startdate, i_enddate, 
                i_norowsstart,i_norowsend,i_instance, i_attr_type_id,v_tabname);                       
                       
end  dynamic_table_create; 

end dynamic_table_generation;
/

[Updated on: Thu, 18 August 2016 19:12] by Moderator

Report message to a moderator

Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655008 is a reply to message #655006] Thu, 18 August 2016 19:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Change:

SELECT 'INSERT INTO ' || table_name ||' VALUES ' ||'(' ||
 'i_projid' ||' ,' ||
 'i_busObjid' ||','|| 
 'i_attrId' ||','|| 
 'i_lang_id' ||','||
 'to_char(i_startdate'||','|| '''dd-mm-yyyy'''||')' ||','||
 'to_char(i_enddate'||','|| '''dd-mm-yyyy'''||')' ||','||
 'i_norowsstart' ||','||
 'i_norowsend' ||','||
 'i_instance' ||','||
 'i_attr_type_id'
AS "Create Table Script" into v_query
FROM user_tables WHERE TABLE_NAME=v_tabname;

to:

SELECT 'INSERT INTO ' || table_name ||' VALUES (' ||
  i_projid       || ',' ||
  i_busObjid     || ',''' || 
  i_attrId       || ''',' || 
  i_lang_id      || ',''' ||
  i_startdate    || ''',''' ||
  i_enddate      || ''',''' ||
  i_norowsstart  || ''',' ||
  i_norowsend    || ',''' ||
  i_instance     || ''',' ||
  i_attr_type_id || ')'
into v_query
FROM user_tables WHERE TABLE_NAME=v_tabname;
Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655009 is a reply to message #655008] Thu, 18 August 2016 19:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
-- test run:

SCOTT@orcl_12.1.0.2.0> create or replace package dynamic_table_generation is
  2  
  3    procedure dynamic_table_create(i_projid number, i_busObjid number,
  4  		     i_attrId varchar2, i_lang_id number, i_startdate varchar2, i_enddate varchar2,
  5  		     i_norowsstart number,i_norowsend number,i_instance varchar2, i_attr_type_id number);
  6  
  7  end dynamic_table_generation;
  8  /

Package created.

SCOTT@orcl_12.1.0.2.0> 
SCOTT@orcl_12.1.0.2.0> create or replace package body dynamic_table_generation is
  2  
  3  
  4  procedure dynamic_table_insert(i_projid number, i_busObjid number,
  5  		     i_attrId varchar2, i_lang_id number, i_startdate varchar2, i_enddate varchar2,
  6  		     i_norowsstart number,i_norowsend number,i_instance varchar2, i_attr_type_id number,
  7  		     v_tabname varchar2) is
  8  
  9    v_column_list	 VARCHAR2(4096) := null;
 10    v_insert_list	 VARCHAR2(16096);
 11    v_ref_cur_columns VARCHAR2(16096) := null;
 12    v_ref_cur_query	 VARCHAR2(16000);
 13    v_ref_cur_output  VARCHAR2(16000) := null;
 14    v_column_name	 VARCHAR2(256);
 15    v_query varchar2(32767);
 16   /* CURSOR c1 IS
 17  	    SELECT column_name, data_type FROM user_tab_columns
 18  	    WHERE table_name = v_tabname ORDER BY column_id;*/
 19    refcur		 sys_refcursor;
 20  
 21    begin
 22  
 23  /*
 24  SELECT 'INSERT INTO ' || table_name ||' VALUES ' ||'(' ||
 25   'i_projid' ||' ,' ||
 26   'i_busObjid' ||','||
 27   'i_attrId' ||','||
 28   'i_lang_id' ||','||
 29   'to_char(i_startdate'||','|| '''dd-mm-yyyy'''||')' ||','||
 30   'to_char(i_enddate'||','|| '''dd-mm-yyyy'''||')' ||','||
 31   'i_norowsstart' ||','||
 32   'i_norowsend' ||','||
 33   'i_instance' ||','||
 34   'i_attr_type_id'
 35  AS "Create Table Script" into v_query
 36  FROM user_tables WHERE TABLE_NAME=v_tabname;
 37  */
 38  
 39  SELECT 'INSERT INTO ' || table_name ||' VALUES (' ||
 40    i_projid       || ',' ||
 41    i_busObjid     || ',''' ||
 42    i_attrId       || ''',' ||
 43    i_lang_id      || ',''' ||
 44    i_startdate    || ''',''' ||
 45    i_enddate      || ''',''' ||
 46    i_norowsstart  || ''',' ||
 47    i_norowsend    || ',''' ||
 48    i_instance     || ''',' ||
 49    i_attr_type_id || ')'
 50  into v_query
 51  FROM user_tables WHERE TABLE_NAME=v_tabname;
 52  
 53  execute immediate v_query;
 54  
 55  DBMS_OUTPUT.PUT_LINE (v_query);
 56  end dynamic_table_insert;
 57  
 58  procedure dynamic_table_create(i_projid number, i_busObjid number,
 59  		     i_attrId varchar2, i_lang_id number, i_startdate varchar2, i_enddate varchar2,
 60  		     i_norowsstart number,i_norowsend number,i_instance varchar2, i_attr_type_id number) is
 61  
 62  t_n number;
 63  v_tabname varchar2(50);
 64  begin
 65    t_n := dbms_utility.get_time;
 66  
 67  dbms_output.put_line('BEFORE TABLE: '||t_n);
 68  
 69  execute immediate 'CREATE TABLE V_DATA_'||t_n ||'( PROJECT_ID NUMBER, BUSINESSOBJECT_ID NUMBER(4),
 70  			    ATTRIBUTE_ID varchar2(400), LANGUAGE_ID NUMBER(2), START_DATE DATE, END_DATE DATE, START_NO_ROWS NUMBER(10),
 71  			    END_NO_ROWS NUMBER(10), INSTANCENAME VARCHAR2(50), ATTRIBUTE_TYPE_ID NUMBER(10))';
 72  
 73  select table_name into v_tabname from user_tables where table_name='V_DATA_'||t_n;
 74  
 75  dbms_output.put_line('after TABLE: '||v_tabname);
 76  
 77  dynamic_table_insert(i_projid, i_busObjid, i_attrId, i_lang_id, i_startdate, i_enddate,
 78  		     i_norowsstart,i_norowsend,i_instance, i_attr_type_id,v_tabname);
 79  
 80  end  dynamic_table_create;
 81  
 82  end dynamic_table_generation;
 83  /

Package body created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> exec dynamic_table_generation.dynamic_table_create(1, 2, 'a', 3, sysdate, sysdate, 4, 5, 'b', 6)
BEFORE TABLE: 18840184
after TABLE: V_DATA_18840184
INSERT INTO V_DATA_18840184 VALUES (1,2,'a',3,'Thu 18-Aug-2016','Thu
18-Aug-2016','4',5,'b',6)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select * from v_data_18840184;

PROJECT_ID BUSINESSOBJECT_ID
---------- -----------------
ATTRIBUTE_ID
--------------------------------------------------------------------------------
LANGUAGE_ID START_DATE      END_DATE        START_NO_ROWS END_NO_ROWS
----------- --------------- --------------- ------------- -----------
INSTANCENAME                                       ATTRIBUTE_TYPE_ID
-------------------------------------------------- -----------------
         1                 2
a
          3 Thu 18-Aug-2016 Thu 18-Aug-2016             4           5
b                                                                  6


1 row selected.

Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655012 is a reply to message #655006] Thu, 18 August 2016 20:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Now I would like to insert the data into the table by passing the parameter values.
I'll stipulate that you can get rows into these dynamically created table & I'll assume that COMMIT is issued so other sessions can "see" the new rows.
I foresee two potential problems.
How does the rest of the application know what are the new table_names?
How does the rest of the application know what data is in which table_name?


Just because you can do something, it does not necessarily follow that you should actually do what can be done.
IMO, dynamic tables should NEVER, Ever, never exist in production application.
Application objects should be known & fixed between application version releases.
Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655014 is a reply to message #655012] Thu, 18 August 2016 23:25 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you for your explanation.

When I execute the same procedure with the same input parameters in sql developer I am getting the error.I have attached the error screenshot.

Startdate and enddate in the procedure are varchar2. I hope if we pass 01-01-1970 as startdate instead of sysdate, we have convert into date format.


Please advice on this.
/forum/fa/13233/0/


[mod-edit: imaged inserted into message body by bb]

[Updated on: Fri, 19 August 2016 14:49] by Moderator

Report message to a moderator

Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655018 is a reply to message #655014] Fri, 19 August 2016 00:55 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

I have changed the script and it is working fine by using exec statement but not from SQL Developer.Used the below script.

SELECT 'INSERT INTO ' || table_name ||' VALUES (' ||
i_projid || ',' ||
i_busObjid || ',''' ||
i_attrId || ''',' ||
i_lang_id || ',' || ' to_date ( '''||
i_startdate || ''',' || '''mm-dd-yyyy''' || ')' || ',' ||
' to_date ( '''||
i_enddate || ''',' || '''mm-dd-yyyy''' || ')' || ',' ||
i_norowsstart || ',' ||
i_norowsend || ',''' ||
i_instance || ''',' ||
i_attr_type_id || ')'
into v_query
FROM user_tables WHERE TABLE_NAME=v_tabname;
Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655020 is a reply to message #655008] Fri, 19 August 2016 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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

Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655024 is a reply to message #655020] Fri, 19 August 2016 02:05 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you Michel.

Please provide any other way to insert the data dynamically.
Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655027 is a reply to message #655024] Fri, 19 August 2016 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please provide the business need of this.
What is the business requirement which leads you to this procedure?

Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655033 is a reply to message #655027] Fri, 19 August 2016 04:26 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Suppose users will be accessing the application in parallel and dynamic tables are created based on the userid with timestamp. Later the user enter the data based data selection. The selected data is to be inserted into the dynamically created table.
Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655035 is a reply to message #655033] Fri, 19 August 2016 04:50 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Couldn't you, rather, use a global temporary table for that purpose?
Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655046 is a reply to message #655035] Fri, 19 August 2016 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I'm quite sure this is what it needs.

Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655054 is a reply to message #655014] Fri, 19 August 2016 15:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I have been just trying to address each immediate error, doing minimal fixing to enable the code to run, without knowing what your whole process it.

When you are using a date, it is always best to pass a date to begin with, then you don't have to worry about formats after that point. If the code works in SQL*Plus, but not in SQL*Developer when you just pass it a varchar2, then that is because the two have different default date formats. If you are going to pass it a varchar2 in a standard format, then you need to use to_date to convert inside the program, not to_char. If you pass it a date, then it does not matter whether you have varchar2 or date in various parts of your program as it will do implicit conversions using the default format.

What I initially changed was correcting your quote marks and concatentations, so that your variable values and not the variable names were used in your concatenation, and passing date values.

The following changes the dynamic sql to use bind variables. I have marked the changed section along the left column. Note that I changed the execute immediate, as well as the select above it.

SCOTT@orcl_12.1.0.2.0> create or replace package dynamic_table_generation is
  2  
  3    procedure dynamic_table_create(i_projid number, i_busObjid number,
  4  		     i_attrId varchar2, i_lang_id number, i_startdate varchar2, i_enddate varchar2,
  5  		     i_norowsstart number,i_norowsend number,i_instance varchar2, i_attr_type_id number);
  6  
  7  end dynamic_table_generation;
  8  /

Package created.

SCOTT@orcl_12.1.0.2.0> 
SCOTT@orcl_12.1.0.2.0> create or replace package body dynamic_table_generation is
  2  
  3  
  4  procedure dynamic_table_insert(i_projid number, i_busObjid number,
  5  		     i_attrId varchar2, i_lang_id number, i_startdate varchar2, i_enddate varchar2,
  6  		     i_norowsstart number,i_norowsend number,i_instance varchar2, i_attr_type_id number,
  7  		     v_tabname varchar2) is
  8  
  9    v_column_list	 VARCHAR2(4096) := null;
 10    v_insert_list	 VARCHAR2(16096);
 11    v_ref_cur_columns VARCHAR2(16096) := null;
 12    v_ref_cur_query	 VARCHAR2(16000);
 13    v_ref_cur_output  VARCHAR2(16000) := null;
 14    v_column_name	 VARCHAR2(256);
 15    v_query varchar2(32767);
 16   /* CURSOR c1 IS
 17  	    SELECT column_name, data_type FROM user_tab_columns
 18  	    WHERE table_name = v_tabname ORDER BY column_id;*/
 19    refcur		 sys_refcursor;
 20  
 21    begin
 22  
 23  /*
 24  SELECT 'INSERT INTO ' || table_name ||' VALUES ' ||'(' ||
 25   'i_projid' ||' ,' ||
 26   'i_busObjid' ||','||
 27   'i_attrId' ||','||
 28   'i_lang_id' ||','||
 29   'to_char(i_startdate'||','|| '''dd-mm-yyyy'''||')' ||','||
 30   'to_char(i_enddate'||','|| '''dd-mm-yyyy'''||')' ||','||
 31   'i_norowsstart' ||','||
 32   'i_norowsend' ||','||
 33   'i_instance' ||','||
 34   'i_attr_type_id'
 35  AS "Create Table Script" into v_query
 36  FROM user_tables WHERE TABLE_NAME=v_tabname;
 37  
 38  execute immediate v_query;
 39  */
 40  
 41  --BEGINNING OF CHANGED SECTION:
 42  SELECT 'INSERT INTO ' || table_name ||' VALUES (:b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10)'
 43  INTO   v_query
 44  FROM   user_tables
 45  WHERE  table_name = v_tabname;
 46  
 47  EXECUTE IMMEDIATE v_query
 48    USING i_projid, i_busObjid, i_attrId, i_lang_id, i_startdate,
 49  	     i_enddate, i_norowsstart, i_norowsend, i_instance, i_attr_type_id;
 50  --END OF CHANGED SECTION:
 51  
 52  DBMS_OUTPUT.PUT_LINE (v_query);
 53  end dynamic_table_insert;
 54  
 55  procedure dynamic_table_create(i_projid number, i_busObjid number,
 56  		     i_attrId varchar2, i_lang_id number, i_startdate varchar2, i_enddate varchar2,
 57  		     i_norowsstart number,i_norowsend number,i_instance varchar2, i_attr_type_id number) is
 58  
 59  t_n number;
 60  v_tabname varchar2(50);
 61  begin
 62    t_n := dbms_utility.get_time;
 63  
 64  dbms_output.put_line('BEFORE TABLE: '||t_n);
 65  
 66  execute immediate 'CREATE TABLE V_DATA_'||t_n ||'( PROJECT_ID NUMBER, BUSINESSOBJECT_ID NUMBER(4),
 67  			    ATTRIBUTE_ID varchar2(400), LANGUAGE_ID NUMBER(2), START_DATE DATE, END_DATE DATE, START_NO_ROWS NUMBER(10),
 68  			    END_NO_ROWS NUMBER(10), INSTANCENAME VARCHAR2(50), ATTRIBUTE_TYPE_ID NUMBER(10))';
 69  
 70  select table_name into v_tabname from user_tables where table_name='V_DATA_'||t_n;
 71  
 72  dbms_output.put_line('after TABLE: '||v_tabname);
 73  
 74  dynamic_table_insert(i_projid, i_busObjid, i_attrId, i_lang_id, i_startdate, i_enddate,
 75  		     i_norowsstart,i_norowsend,i_instance, i_attr_type_id,v_tabname);
 76  
 77  end  dynamic_table_create;
 78  
 79  end dynamic_table_generation;
 80  /

Package body created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> begin
  2    dynamic_table_generation.dynamic_table_create
  3  	 (1, 2, 'a', 3,
  4  	  to_date('01-01-1970','dd-mm-yyyy'),
  5  	  to_date('01-01-1970','dd-mm-yyyy'),
  6  	  4, 5, 'b', 6);
  7  end;
  8  /
BEFORE TABLE: 25765620
after TABLE: V_DATA_25765620
INSERT INTO V_DATA_25765620 VALUES (:b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9,
:b10)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select * from v_data_25765620;

PROJECT_ID BUSINESSOBJECT_ID
---------- -----------------
ATTRIBUTE_ID
--------------------------------------------------------------------------------
LANGUAGE_ID START_DATE      END_DATE        START_NO_ROWS END_NO_ROWS
----------- --------------- --------------- ------------- -----------
INSTANCENAME                                       ATTRIBUTE_TYPE_ID
-------------------------------------------------- -----------------
         1                 2
a
          3 Thu 01-Jan-1970 Thu 01-Jan-1970             4           5
b                                                                  6


1 row selected.





Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655055 is a reply to message #655054] Fri, 19 August 2016 15:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here is an example of usage of a global temporary table:

SCOTT@orcl_12.1.0.2.0> CREATE GLOBAL TEMPORARY TABLE gt_tab
  2    (PROJECT_ID	   NUMBER,
  3  	BUSINESSOBJECT_ID  NUMBER(4),
  4  	ATTRIBUTE_ID	   varchar2(400),
  5  	LANGUAGE_ID	   NUMBER(2),
  6  	START_DATE	   DATE,
  7  	END_DATE	   DATE,
  8  	START_NO_ROWS	   NUMBER(10),
  9  	END_NO_ROWS	   NUMBER(10),
 10  	INSTANCENAME	   VARCHAR2(50),
 11  	ATTRIBUTE_TYPE_ID  NUMBER(10))
 12  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE gt_pkg
  2  AS
  3    PROCEDURE insert_data
  4  	 (i_projid	  number,
  5  	  i_busObjid	  number,
  6  	  i_attrId	  varchar2,
  7  	  i_lang_id	  number,
  8  	  i_startdate	  DATE,
  9  	  i_enddate	  DATE,
 10  	  i_norowsstart   number,
 11  	  i_norowsend	  number,
 12  	  i_instance	  varchar2,
 13  	  i_attr_type_id  number);
 14  END gt_pkg;
 15  /

Package created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE BODY gt_pkg
  2  AS
  3    PROCEDURE insert_data
  4  	 (i_projid	  number,
  5  	  i_busObjid	  number,
  6  	  i_attrId	  varchar2,
  7  	  i_lang_id	  number,
  8  	  i_startdate	  DATE,
  9  	  i_enddate	  DATE,
 10  	  i_norowsstart   number,
 11  	  i_norowsend	  number,
 12  	  i_instance	  varchar2,
 13  	  i_attr_type_id  number)
 14    IS
 15    BEGIN
 16  	 INSERT INTO gt_tab
 17  	   (PROJECT_ID, BUSINESSOBJECT_ID, ATTRIBUTE_ID, LANGUAGE_ID,
 18  	    START_DATE, END_DATE, START_NO_ROWS, END_NO_ROWS, INSTANCENAME, ATTRIBUTE_TYPE_ID)
 19  	 VALUES
 20  	   (i_projid, i_busObjid, i_attrId, i_lang_id, i_startdate, i_enddate,
 21  	    i_norowsstart, i_norowsend, i_instance, i_attr_type_id);
 22    END insert_data;
 23  END gt_pkg;
 24  /

Package body created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> begin
  2    gt_pkg.insert_data
  3  	 (1, 2, 'a', 3,
  4  	  to_date('01-01-1970','dd-mm-yyyy'),
  5  	  to_date('01-01-1970','dd-mm-yyyy'),
  6  	  4, 5, 'b', 6);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM gt_tab
  2  /

PROJECT_ID BUSINESSOBJECT_ID
---------- -----------------
ATTRIBUTE_ID
--------------------------------------------------------------------------------
LANGUAGE_ID START_DATE      END_DATE        START_NO_ROWS END_NO_ROWS
----------- --------------- --------------- ------------- -----------
INSTANCENAME                                       ATTRIBUTE_TYPE_ID
-------------------------------------------------- -----------------
         1                 2
a
          3 Thu 01-Jan-1970 Thu 01-Jan-1970             4           5
b                                                                  6


1 row selected.
Re: Insert the data into dynamically created table (2 threads merged by bb) [message #655097 is a reply to message #655035] Mon, 22 August 2016 04:03 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
Exactly. This is what we must do when we have business case for inserting a session specific data into a table. There is absolutely no need of dynamic table for this scenario.
Re: Insert the data into dynamically created table (2 threads merged by bb) [message #658969 is a reply to message #655006] Mon, 02 January 2017 23:20 Go to previous message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Yes. You are right if I insert the values directly into the table.

Suppose my requirement I would like to use the select query with joins from more than two tables by using the input values. Same query with Table name changes. Also changes the column values based on the table name.

Suppose,
I have created the table 

CREATE GLOBAL TEMPORARY TABLE gt_tab
      (PROJECT_ID	   NUMBER,
    	BUSINESSOBJECT_ID  NUMBER(4),
        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)

For Example: 

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,
  null "DATE_VALUE",
  NULL "TEXT_VALUE",
  NULL "NOTE_VALUE" ,
  NULL "NUMBER_VALUE",
  NULL "DROPDOWN_VALUE" ,
  null "REFERENCE_VALUE",
   NULL "LINK_VALUE",
  null "DATETIME_VALUE",
  d.boolean_value "BOOLEAN_VALUE",
  I.INSTANCE_CREATED_DATE,
  I.INSTANCE_UPDATED_DATE
FROM 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=i_buschildObjid
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 ;

INSTANCE_BOOLEAN_DATA table changes in the query and replaces 11 different tables. INSTANCE_BOOLEAN_DATA means Boolean_value will be populated, INSTANCE_DATE_DATA means date_value will be populated and remaining column values like "_VALUE" are null. This query is for 11 tables. I have 8 differenct queries to use to get the data.

Please advice.
Previous Topic: selecting the columns dynamically
Next Topic: Partitions
Goto Forum:
  


Current Time: Fri Mar 29 10:38:04 CDT 2024