Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic array as SP input (11.0.2.10)
Dynamic array as SP input [message #662263] |
Sun, 23 April 2017 02:09 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
In my schema I have the following package:
CREATE OR REPLACE Package MYSCHEMA.Proce_Params_Array As
Type T_FieldValue Is Table Of Proce_Params_Table.FieldValue%Type Index By Pls_Integer;
End Proce_Params_Array;
And in PLSQL I use it:
CREATE OR REPLACE PROCEDURE MYSCHEMA.P_Add_Organization
(
ARRAY_inParams Proce_Params_Array.T_FieldValue,
--....
This way works perfect for multi-row single dimension arrays as input to SP while add, update, delete.
Currently I have the need to pass 2-dimension array for the same purpose where the number of columns is known for each case but I dont need to create a type for each case. I need to pass a dynamic collection or something like a record set where the number of columns vary each time.
Can this be done?
Thanks,
Ferro
|
|
|
|
|
|
Re: Dynamic array as SP input [message #662268 is a reply to message #662267] |
Sun, 23 April 2017 04:58 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Dear Michel,
Thanks for your reply.
1- I mean any form of 2-dimensional data container compatible with any supported Oracle type and passing it as an input parameter to an Oracle stored procedure without having to pre-define an object/type.
2- Here is the XML example I have and want to replace:
PROCEDURE P_ADD_RG_ORGANIZATION
( XMLDS XMLTYPE,
P_ORG_ID IN OUT Number,
P_CURRENT_USER Number,
P_MODULE_NAME Varchar2,
P_ErrorCode Out Varchar2)
/*
This Stored Procedure ...
This SP depends on xml data table for ...:
<dsRG>
<RG_ORGANIZATION>
<FK_ACTIVITY_ID></FK_ACTIVITY_ID>
<REG_DATE></REG_DATE>
<ESTABLISHMENT_DATE></ESTABLISHMENT_DATE>
<FK_ACTIVITY_ID></FK_ACTIVITY_ID>
<NAME_ENG></NAME_ENG>
<BOX_OFFICE></BOX_OFFICE>
<PHONE></PHONE>
<FAX></FAX>
<ADDRESS></ADDRESS>
<ABOUNT_ORG></ABOUNT_ORG>
<ORG_OBJECTIVES></ORG_OBJECTIVES>
</RG_ORGANIZATION>
</dsRG>
-- 0.1 save PROCEDURE parameters for logging
-- 0.3 - get organization sequence number.
-- 0.4 insert new RG organization
-- 0.7 - Log the event
Author:
Date:
*/
IS
P_DB_ERROR Varchar2(1000);
P_PARAMS Varchar2(1000);
P_USER_ID Number(10);
BEGIN
--0.1 save PROCEDURE parameters for logging
P_PARAMS :=
'[ P_ORG_ID = ' || P_ORG_ID
|| ' - P_CURRENT_USER = ' || P_CURRENT_USER
|| ' - P_MODULE_NAME = ' || P_MODULE_NAME
|| ']';
P_ErrorCode := '';
-- 0.3 - get organization sequence number.
SELECT RG_ORGANIZATION_SEQ.NEXTVAL INTO P_ORG_ID FROM DUAL;
-- 0.4 insert new RG organization
INSERT INTO RG_ORGANIZATION(ORGANIZATION_ID, FK_ACTIVITY_ID, REG_DATE, ESTABLISHMENT_DATE, ABOUNT_ORG, ORG_OBJECTIVES, IS_VALID_ORG,
CREATED_ON, NAME_ENG, BOX_OFFICE, PHONE, FAX, ADDRESS)
Select
P_ORG_ID, X.FK_ACTIVITY_ID, To_Date(X.REG_DATE, 'DD/MM/YYYY'), To_Date(X.ESTABLISHMENT_DATE, 'DD/MM/YYYY'), X.ABOUNT_ORG, X.ORG_OBJECTIVES, 0,
SysDate, X.NAME_ENG, X.BOX_OFFICE, X.PHONE, X.FAX, X.ADDRESS
FROM XMLTABLE('/dsRG/RG_ORGANIZATION' PASSING XMLDS
COLUMNS
NAME_ENG VARCHAR2(100) PATH '/RG_ORGANIZATION/NAME_ENG',
BOX_OFFICE VARCHAR2(15) PATH '/RG_ORGANIZATION/BOX_OFFICE',
PHONE VARCHAR2(15) PATH '/RG_ORGANIZATION/PHONE',
FAX VARCHAR2(15) PATH '/RG_ORGANIZATION/FAX',
ADDRESS VARCHAR2(1024) PATH '/RG_ORGANIZATION/ADDRESS',
ABOUNT_ORG VARCHAR2(1024) PATH '/RG_ORGANIZATION/ABOUNT_ORG',
ORG_OBJECTIVES VARCHAR2(1024) PATH '/RG_ORGANIZATION/ORG_OBJECTIVES',
REG_DATE VARCHAR2(30) PATH '/RG_ORGANIZATION/REG_DATE',
ESTABLISHMENT_DATE VARCHAR2(30) PATH '/RG_ORGANIZATION/ESTABLISHMENT_DATE',
FK_ACTIVITY_ID NUMBER(5) PATH '/RG_ORGANIZATION/FK_ACTIVITY_ID') X;
-- 0.7 - Log the event
/*
EXCEPTION WHEN OTHERS THEN
--fill error varchar
P_DB_ERROR := SQLCODE || '-' || SQLERRM;
-- call P_LOG_ERROR to log the exception error to event log table
P_LOG_ERROR(P_CURRENT_USER, P_DB_ERROR, P_MODULE_NAME, P_PARAMS);
-- raise exception for caller
RAISE;
*/
END P_ADD_RG_ORGANIZATION;
In this example, I dont have to declare any object/type in order to pass the input parameter XMLDS and it holds 2-dimensional data and can be used in any similar procedure with different number of columns (as the mapping is done inside the SP as per the example). To me, XML technique is different than the Array technique used in single dimensional data cases and its PLSQL code is less readable. I am trying to find an answer to point 1 so I can replace XMLDS with another type without having to create object/type for each SP case.
Thanks,
Ferro
|
|
|
|
Re: Dynamic array as SP input [message #662306 is a reply to message #662304] |
Mon, 24 April 2017 06:23 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
So where are two dimensions and where is dynamic in your example? Assume you were able to pass "input parameter to an Oracle stored procedure without having to pre-define an object/type". Then what? How will you process that parameter if you have no idea what attributes its type has? You will have to get type definition and loop through every attribute checking what it attribute name, data type, length, precision, etc. Huge overhead. Seems like you are trying to invent bicycle with square wheels.
SY.
|
|
|
Re: Dynamic array as SP input [message #662321 is a reply to message #662306] |
Mon, 24 April 2017 23:45 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
[ALIGN=left]@Solomon Yakobson
Quote:So where are two dimensions and where is dynamic in your example?
In my example, the input parameter XMLDS carries 2-dimensional data and its format is described in the SP documentation.
Quote:How will you process that parameter if you have no idea what attributes its type has?
The attributes are known for each SP. In my example (P_ADD_RG_ORGANIZATION) maps assumed/agreed XML structure into table fields. In case of another SP, the same parameter XMLDS will also be passed which presents another XML structure according to each SP.
So I use a single input type (XMLTYPE) that does not require pre-defining any object in Oracle and use it to pass the different/dynamic 2-dimentional data need for each SP using XML structure.
Quote:You will have to get type definition and loop through every attribute checking what it attribute name, data type, length, precision, etc. Huge overhead. Seems like you are trying to invent bicycle with square wheels.
Can you please clarify this point more? At the beginning this was my exact point of view preferring to pass a defined object/type at both Oracle and .net levels only to find XML more flexible and requiring no pre-definition. I truly want to learn something on how to chose between those two approaches.
Many thanks,
Ferro
[Updated on: Mon, 24 April 2017 23:55] Report message to a moderator
|
|
|
|
Re: Dynamic array as SP input [message #662332 is a reply to message #662321] |
Tue, 25 April 2017 08:29 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
OraFerro wrote on Tue, 25 April 2017 00:45In my example, the input parameter XMLDS carries 2-dimensional data and its format is described in the SP documentation.
Your XML is one-dimensional. It is array of organizations. Each organization is a structure,not array, of scalar attributes.
OraFerro wrote on Tue, 25 April 2017 00:45
The attributes are known for each SP. In my example (P_ADD_RG_ORGANIZATION) maps assumed/agreed XML structure into table fields. In case of another SP, the same parameter XMLDS will also be passed which presents another XML structure according to each SP.
Here we go. All you know you are looking for attribute but you don't know path to it. So I'd stick with XML and use asterisk in the path, e.g. '/*/RG_ORGANIZATION'.
SY.
|
|
|
|
|
Re: Dynamic array as SP input [message #662658 is a reply to message #662357] |
Tue, 09 May 2017 01:58 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Dear Solomon Yakobson,
Quote:Not sure what is less readable in Option 2
What I meant is that when using option 1 (defined Oracle object) there is no need for mapping the XML fields to table fields inside the SP body.
Thanks,
Ferro
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:31:05 CDT 2024
|