Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure: XML versus Array for multi-table insert (11.0.2.10)
| Stored Procedure: XML versus Array for multi-table insert [message #649819] |
Thu, 07 April 2016 03:31  |
 |
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
I have a table structure as follows:
Create table Test_Template
(
TemplateID number(3) primary key,
TemplateName varchar2(50)
);
Create table Test_Section
(
SectionID number(3) primary key,
SectionName varchar2(50)
);
Create table Test_Field
(
Field_id number(3) primary key,
Field_Name varchar2(50),
Field_Order number(3)
);
Create Table Test_Template_Section
(
Template_ID number(3) references Test_Template,
Section_ID number(3) references Test_Section,
Primary Key (Template_ID, Section_ID)
);
Create Table Test_Section_Field
(
Section_ID number(3) references Test_Section,
Field_ID number(3) references Test_Field,
Primary Key ( Section_ID, Field_ID )
);
I need to write a Stored Procedure that fills a template, its Section(s) and Field(s) in one call and I have the following options:
1- A SP that takes 3 Array parameters
a. 1-dimentional Array for Template (in the real case it has more attributes)
b. 2-dimentional Array(Template_ID, Section_ID)
c. 2-dimentional Array (Section_ID, Template_ID, Field_Order)
2- A SP that parses an XML file
a. I have experience in using XML but haven't used it with PLSQL before
b. I think I would use a label-based XML file such as
<Template>
<row>
<Template_Name>MyTemplate</ Template_Name >
<TemplateSection>
<SectionID>1</SectionID >
<SectionField>
<FieldID>3</FieldID>
<FieldOrder>0</FieldOrder>
<FieldID>30</FieldID>
<FieldOrder>2</FieldOrder>
<FieldID>7</FieldID>
<FieldOrder>1</FieldOrder>
</SectionField>
<SectionID>4</ SectionID >
<SectionField>
<FieldID>30</FieldID>
<FieldOrder>0</FieldOrder>
</SectionField>
<SectionID>9</ SectionID >
<SectionField>
<FieldID>30</FieldID>
<FieldOrder>0</FieldOrder>
</SectionField>
</ TemplateSection >
</row>
</Template>
My inquiry is:
1- If my objective is performance and PLSQL code readability and ease of tracing, and my application layer only deals with this DB, what is the recommended technique?
2- Is there any suggestion regarding the used techniques (correction to the way array or XML is used, enhancement...etc.)
3- Is there a better technique altogether?
Many thanks,
Ferro
|
|
|
|
|
|
| Re: Stored Procedure: XML versus Array for multi-table insert [message #650024 is a reply to message #649820] |
Tue, 12 April 2016 23:05  |
|
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Hi Michel and All,
Below is my updated trial, currently I managed to insert one template and only one template section, but could not use the same technique to insert one template, multiple template sections. I also read about the following but could not implement them:
- Changing extractvalue to extract, but it only worked with one field
- saving the whole xml content into a cursor then use it in insert - found it to be less readable and think its an extra step that can be avoided
I would like to know your suggestion.
My updated case:
Create table Test_Template
(
TemplateID number(3) primary key,
TemplateName varchar2(50),
CREATOR_ID number(3)
);
Create table Test_Section
(
SectionID number(3) primary key,
SectionName varchar2(50)
);
Create table Test_Field
(
Field_id number(3) primary key,
Field_Name varchar2(50)
);
Create Table Test_Template_Section
(
Template_ID number(3) references Test_Template,
Section_ID number(3) references Test_Section,
Order_id number(3) null,
CREATOR_ID number(3),
Primary Key (Template_ID, Section_ID)
);
Create Table Test_Section_Field
(
Section_ID number(3) references Test_Section,
Field_ID number(3) references Test_Field,
Order_id number(3),
CREATOR_ID number(3),
Primary Key ( Section_ID, Field_ID )
);
insert ALL
into TEST_FIELD values (1, 'MyField1')
into TEST_FIELD values (2, 'MyField2')
into TEST_SECTION values (1, 'MySection1')
into TEST_SECTION values (2, 'MySection2')
select * from dual;
-- Working XML sample with one template and 1 template section - please note it is not nested anymore as per my original example
<dsOra>
<TEMPLATE>
<TemplateName>Temp 1</TemplateName>
</TEMPLATE>
<TEMPLATE_SECTION>
<Section_ID>1</Section_ID>
<Order_id>1</Order_id>
</TEMPLATE_SECTION>
</dsOra>
-- XML sample with one template and 2 template section - Does not work with my code
<dsOra>
<TEMPLATE>
<TemplateName>Temp 1</TemplateName>
</TEMPLATE>
<TEMPLATE_SECTION>
<Section_ID>1</Section_ID>
<Order_id>1</Order_id>
</TEMPLATE_SECTION>
<TEMPLATE_SECTION>
<Section_ID>2</Section_ID>
<Order_id>2</Order_id>
</TEMPLATE_SECTION>
</dsOra>
CREATE OR REPLACE PROCEDURE P_TEST_ADD_TEMPLATE
(xml IN XMLType /*CLOB*/, i_creator_id in number default 0)
AS
I_template_id number(3);
BEGIN
select nvl(max(templateid),0) + 1 into I_template_id from TEST_TEMPLATE;
INSERT INTO Test_Template (TemplateID,TemplateName,CREATOR_ID)
SELECT
I_template_id AS TemplateID
,ExtractValue(column_value,'dsOra/TEMPLATE/TemplateName') AS TemplateName
,i_creator_id AS CREATOR_ID
FROM TABLE(XMLSequence( xml/*XMLType(xml)*/)) XMLDUMMAY;
INSERT INTO Test_Template_Section (Template_ID,Section_ID,Order_id,CREATOR_ID)
SELECT
I_template_id AS Template_ID
,ExtractValue(column_value,'dsOra/TEMPLATE_SECTION/Section_ID') AS Section_ID
,ExtractValue(column_value,'dsOra/TEMPLATE_SECTION/Order_id') AS Order_id
,i_creator_id AS CREATOR_ID
FROM TABLE(XMLSequence( xml/*XMLType(xml)*/)) XMLDUMMAY;
COMMIT;
END;
Many thanks,
Ferro
[Updated on: Tue, 12 April 2016 23:07] Report message to a moderator |
|
|
|
| Re: Stored Procedure: XML versus Array for multi-table insert [message #650029 is a reply to message #650024] |
Wed, 13 April 2016 00:56 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please see the modified procedure in the demonstration below and the sequence that it uses.
-- tables and data that you provided:
SCOTT@orcl> Create table Test_Template
2 (
3 TemplateID number(3) primary key,
4 TemplateName varchar2(50),
5 CREATOR_ID number(3)
6 );
Table created.
SCOTT@orcl>
SCOTT@orcl> Create table Test_Section
2 (
3 SectionID number(3) primary key,
4 SectionName varchar2(50)
5 );
Table created.
SCOTT@orcl>
SCOTT@orcl> Create table Test_Field
2 (
3 Field_id number(3) primary key,
4 Field_Name varchar2(50)
5 );
Table created.
SCOTT@orcl>
SCOTT@orcl> Create Table Test_Template_Section
2 (
3 Template_ID number(3) references Test_Template,
4 Section_ID number(3) references Test_Section,
5 Order_id number(3) null,
6 CREATOR_ID number(3),
7 Primary Key (Template_ID, Section_ID)
8 );
Table created.
SCOTT@orcl>
SCOTT@orcl> Create Table Test_Section_Field
2 (
3 Section_ID number(3) references Test_Section,
4 Field_ID number(3) references Test_Field,
5 Order_id number(3),
6 CREATOR_ID number(3),
7 Primary Key ( Section_ID, Field_ID )
8 );
Table created.
SCOTT@orcl> insert ALL
2 into TEST_FIELD values (1, 'MyField1')
3 into TEST_FIELD values (2, 'MyField2')
4 into TEST_SECTION values (1, 'MySection1')
5 into TEST_SECTION values (2, 'MySection2')
6 select * from dual;
4 rows created.
-- sequence and modified procedure:
SCOTT@orcl> CREATE SEQUENCE template_seq
2 /
Sequence created.
SCOTT@orcl> CREATE OR REPLACE PROCEDURE P_TEST_ADD_TEMPLATE
2 (xml IN XMLType,
3 i_creator_id in number default 0)
4 AS
5 I_template_id number(3);
6 BEGIN
7 SELECT template_seq.NEXTVAL INTO I_template_id FROM DUAL;
8 INSERT INTO Test_Template (TemplateID,TemplateName,CREATOR_ID)
9 SELECT I_template_id, TemplateName, i_creator_id
10 FROM XMLTABLE
11 ('/dsOra/TEMPLATE'
12 PASSING xml
13 COLUMNS
14 TemplateName VARCHAR2(50) PATH '/TEMPLATE/TemplateName');
15 INSERT INTO Test_Template_Section (Template_ID,Section_ID,Order_id,CREATOR_ID)
16 SELECT I_template_id, Section_ID, Order_id, i_creator_id
17 FROM XMLTABLE
18 ('/dsOra/TEMPLATE_SECTION'
19 PASSING xml
20 COLUMNS
21 Section_ID NUMBER PATH '/TEMPLATE_SECTION/Section_ID',
22 Order_id NUMBER PATH '/TEMPLATE_SECTION/Order_id');
23 END;
24 /
Procedure created.
SCOTT@orcl> SHOW ERRORS
No errors.
-- execution and results:
SCOTT@orcl> BEGIN
2 p_test_add_template (XMLTYPE(
3 '<dsOra>
4 <TEMPLATE>
5 <TemplateName>Temp 1</TemplateName>
6 </TEMPLATE>
7 <TEMPLATE_SECTION>
8 <Section_ID>1</Section_ID>
9 <Order_id>1</Order_id>
10 </TEMPLATE_SECTION>
11 <TEMPLATE_SECTION>
12 <Section_ID>2</Section_ID>
13 <Order_id>2</Order_id>
14 </TEMPLATE_SECTION>
15 </dsOra>'));
16 END;
17 /
PL/SQL procedure successfully completed.
SCOTT@orcl> COMMIT
2 /
Commit complete.
SCOTT@orcl> SELECT * FROM test_template
2 /
TEMPLATEID TEMPLATENAME CREATOR_ID
---------- -------------------------------------------------- ----------
1 Temp 1 0
1 row selected.
SCOTT@orcl> SELECT * FROM test_section
2 /
SECTIONID SECTIONNAME
---------- --------------------------------------------------
1 MySection1
2 MySection2
2 rows selected.
SCOTT@orcl> SELECT * FROM test_field
2 /
FIELD_ID FIELD_NAME
---------- --------------------------------------------------
1 MyField1
2 MyField2
2 rows selected.
SCOTT@orcl> SELECT * FROM test_template_section
2 /
TEMPLATE_ID SECTION_ID ORDER_ID CREATOR_ID
----------- ---------- ---------- ----------
1 1 1 0
1 2 2 0
2 rows selected.
SCOTT@orcl> SELECT * FROM test_section_field
2 /
no rows selected
|
|
|
|
| Re: Stored Procedure: XML versus Array for multi-table insert [message #650035 is a reply to message #650029] |
Wed, 13 April 2016 01:56 |
|
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Thanks a lot Barbara for the detailed example.
I will follow it, however I was working on something like:
-- For the second insert for template section inside the SP
FOR r
IN (
SELECT
ExtractValue(Value(p),'dsOra/TEMPLATE_SECTION/Section_ID') AS Section_ID
,ExtractValue(Value(p),'dsOra/TEMPLATE_SECTION/Order_id') AS Order_id
FROM TABLE(XMLSequence(Extract(xml,'/dsOra/TEMPLATE_SECTION'))) p
)
LOOP
INSERT INTO Test_Template_Section (Template_ID,Section_ID,Order_id,CREATOR_ID)
values (I_template_id, r.Section_ID, r.Order_id, i_creator_id);
END LOOP;
it compiles but always return the value of Section_ID as null!
I am not sure where I fail, it would be great if you can show me.
Many thanks,
Ferro
|
|
|
|
|
|
| Re: Stored Procedure: XML versus Array for multi-table insert [message #650038 is a reply to message #650035] |
Wed, 13 April 2016 02:38 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following is a correction of your code. The first two paths need to start with /TEMPLATE_SECTION/. However, as Michel said, processing things one row at a time from a cursor is slow way to do things. The code that I already provided would be better.
FOR r
IN (
SELECT
ExtractValue(Value(p),'/TEMPLATE_SECTION/Section_ID') AS Section_ID
,ExtractValue(Value(p),'/TEMPLATE_SECTION/Order_id') AS Order_id
FROM TABLE(XMLSequence(Extract(xml,'/dsOra/TEMPLATE_SECTION'))) p
)
LOOP
INSERT INTO Test_Template_Section (Template_ID,Section_ID,Order_id,CREATOR_ID)
values (I_template_id, r.Section_ID, r.Order_id, i_creator_id);
END LOOP;
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 09:58:12 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
