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 Go to next message
OraFerro
Messages: 331
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 #662264 is a reply to message #662263] Sun, 23 April 2017 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You need to overload the procedure with each type you want to pass.

Re: Dynamic array as SP input [message #662265 is a reply to message #662264] Sun, 23 April 2017 03:11 Go to previous messageGo to next message
OraFerro
Messages: 331
Registered: July 2011
Senior Member
Hi Michel,

This unfortunately goes along with all the readings I have done.
1- I even tried to search how to pass recordset from .net to PLSQL SP but failed.
2- Does this make XML a better option (as it does not require any predefined types or previous declaration in XML schema)?

Thanks,
Ferro
Re: Dynamic array as SP input [message #662267 is a reply to message #662265] Sun, 23 April 2017 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1. Post this question in our Windows forum, defining what you mean by "recordset" (array? cursor?) for the moment I don't see the relation with your original question.
2. No, most likely not, but you have to post a clear and complete example of what you want to do, what "pass" mean (in? out? in out?).

Re: Dynamic array as SP input [message #662268 is a reply to message #662267] Sun, 23 April 2017 04:58 Go to previous messageGo to next message
OraFerro
Messages: 331
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 #662304 is a reply to message #662268] Mon, 24 April 2017 05:37 Go to previous messageGo to next message
OraFerro
Messages: 331
Registered: July 2011
Senior Member
@Michel

Generally, if we assumed that there is no other way but to create an object/type in order to pass a 2-dimension array to the SP. Do you view this technique better than the above XML example? Why?

Thanks,
Ferro
Re: Dynamic array as SP input [message #662306 is a reply to message #662304] Mon, 24 April 2017 06:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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 Go to previous messageGo to next message
OraFerro
Messages: 331
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 #662331 is a reply to message #662321] Tue, 25 April 2017 08:24 Go to previous messageGo to next message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
while it doesn't handle BLOB or CLOB, have you looked into using the oracle ANYDATA column type?
Re: Dynamic array as SP input [message #662332 is a reply to message #662321] Tue, 25 April 2017 08:29 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
OraFerro wrote on Tue, 25 April 2017 00:45
In 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 #662353 is a reply to message #662332] Wed, 26 April 2017 02:24 Go to previous messageGo to next message
OraFerro
Messages: 331
Registered: July 2011
Senior Member
@Bill B
@Solomon Yakobson and all,

I guess the actual question behind the post is the best way (easier, more readable code, and more secure) to pass 2-dimentional data to SP.
1- Using an oracle defined object/type
2- Using XML
3- Using generic types such as ANYDATA

What do you think?
Option 1 looks orthodox and everything is predefined, however it requires extra preparatory work at Oracle and app development levels.
Option 2 looks easier but it produces less readable code
Option 3 is a I guess similar to option 2

What are the other options? What do experts go for/recommend?

Thanks,
Ferro


Re: Dynamic array as SP input [message #662357 is a reply to message #662353] Wed, 26 April 2017 06:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Option 3 is that huge overhead I was referring to. Not sure what is less readable in Option 2.

SY.
Re: Dynamic array as SP input [message #662658 is a reply to message #662357] Tue, 09 May 2017 01:58 Go to previous message
OraFerro
Messages: 331
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
Previous Topic: how to add to the right side of my table, the colums and data of 2 other tables .
Next Topic: Outer Join on Table
Goto Forum:
  


Current Time: Sun Oct 21 23:34:08 CDT 2018