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  |
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  |
John Watson
Messages: 8929
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 #658957 is a reply to message #658956] |
Mon, 02 January 2017 04:34 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
grpatwari wrote on Mon, 02 January 2017 10:30We 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 |
John Watson
Messages: 8929
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 |
 |
Michel Cadot
Messages: 68641
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:08Barbara Boehmer wrote on Fri, 19 August 2016 02:45Change:
...
to:
...
No! Never do that, never concatenate input values.
2 main reasons:
- you kill your SGA and then performances
- SQL injection
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 11:06:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
