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 |
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 |
|
Barbara Boehmer
Messages: 9097 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 |
|
Barbara Boehmer
Messages: 9097 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 #655018 is a reply to message #655014] |
Fri, 19 August 2016 00:55 |
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 #655054 is a reply to message #655014] |
Fri, 19 August 2016 15:02 |
|
Barbara Boehmer
Messages: 9097 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 |
|
Barbara Boehmer
Messages: 9097 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 #658969 is a reply to message #655006] |
Mon, 02 January 2017 23:20 |
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.
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:45:18 CDT 2024
|