How to handle unknown number of variables [message #633479] |
Thu, 19 February 2015 08:30 |
|
Nuggit
Messages: 5 Registered: February 2015 Location: Stockholm, Sweden
|
Junior Member |
|
|
Hi!
Lets see how I can describe this problem in the easist way.
please bare with me as im pretty new to PL SQL... I have a background in java-programming so Im at least rather famililar with programming...
The thing im trying to accomplish is...
I have created a function in our information system, that collects selected rows from a order and processes a return for these.
(the function to create these function is built into our information system)
So the PL SQL code I have written does this
DECLARE
order_number VARCHAR2(20);
order_row VARCHAR2(20);
part_number VARCHAR2(20);
BEGIN
order_number := &order_number
order_row :=&order_row
part_number :=&part_number
str :='order_number'||order_number||'order_row'||order_row||'part_number'||part_number;
TEST_PACKAGE_PROCESS_ORDER.START_PROCESS(str);
What this does is that it sets my variables order_number, order_row and part_number to the content of the database view for the order shown on the screen in the information system.
This works just fine as long as only 1 row is selected...
the str that is passed on to my next procedure looks like this "order_number12345order_row6789part_number0987"
If I select more than 1 row in the information system. (which shall be possible).
I need the str to become order_number12345order_row6789part_number0987order_number6789order_row9876part_number1234
Just add on the next row at the end of the STR
so STR + str...
However... the problem is... the information from the information system comes one after another... So I need to make a loop like
"while new information is coming
do STR + STR
WHEN no information has come in 5 seconds, continue
Any ideas how to solve this?
I hope my explanation is okay!
Thanks for your help
and bare in mind im a beginnner;-)
|
|
|
|
|
|
Re: How to handle unknown number of variables [message #633484 is a reply to message #633479] |
Thu, 19 February 2015 08:59 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Well, I would suggest that your approach is wrong. PL/SQL is a procedural language: among other procedural strunctures, it has loops. I would read the orders into a cursor, and loop through them processing them one by one with separate calls to your START_PROCESS procedure. Is it possible that you are trying to implement a Java-ish object oriented approach? Not a good idea!
There is also the possibility that you do not need PL/SQL at all. Often programmers new to SQL do not realize that it is always best to process the set of rows with a single SQL statement if possible, and not use any procedural structures.
|
|
|
|
Re: How to handle unknown number of variables [message #633487 is a reply to message #633486] |
Thu, 19 February 2015 09:12 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I don't understand. As Black Swam asked some time ago, just what is the data structure from which these rows are coming? How do they get to your Oracle user process? What is your user process?
(by the way, there is a problem with your keyboard: it inserts multiple "..." characters.)
|
|
|
|
Re: How to handle unknown number of variables [message #633493 is a reply to message #633488] |
Thu, 19 February 2015 10:47 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Sorry, man, I have no idea of what you are describing. Can you just show the structure of your tables? Or the view? And please say what your user process, your "information system", is. Is it, for example, something written in Java and running in a WebLogic container? Or a PHP program with the OCI8 module? How are you invoking your PL/SQL code? How are you passing any parameters?
|
|
|
|
Re: How to handle unknown number of variables [message #633535 is a reply to message #633517] |
Fri, 20 February 2015 15:33 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Is this package and procedure something that you have control over, meaning you get to write it? Or is it something that already exists which you have to use and there is no way to not use it?
Since you said you have written the function I assume that means you get to create whatever code you want to make this work. With that in mind, the first option might be to write a wrapper for your function. Unfortunately not knowing what your data looks like and where you are getting your inputs from there is little in the way of specifics we can offer you.
It sounds to me like you want to pass an array to your function. If so why not just do that. As was suggested, an array has multiple "similar" approaches.
1. delimited string of value sets
2. refcursor
3. PL/SQL associative array
4. nested table type (collections)
5. xml data
Any of these approaches would allow you to pass multiples of a set of values. Each has is strengths and weaknesses of course so you pick the method that offers the best overall fit for your particular needs.
1. delimited string of value sets
In this approach, there are two general approaches and they are the same thing but center on how you map elements. In the first approach the sets you are passing are always the same in which case it is implied with the mapping is and you always pass data that fits the map. In the second approach you say what elements are and then you pass them. The second approach is more flexible but it also requires your receiving code to understand how to handle the maps. For example:
a) if you are always going to pass in these three items in this order (order_number,order_row,part_number) then all the code knows this so you just pass delimited data using two different delimiters:
"1,1,ABQ24X:1,2,PTZ91Y:1,3,PTZ92Y:2,1,AB24X"
b) or if you will be passing different item sets then you just do the same thing but the very first item is the map.
"order_number,order_row,part_number:1,1,ABQ24X:1,2,PTZ91Y:1,3,PTZ92Y:2,1,ABQ24X"
Naturally when you start doing B, you can get as sophisticated as you want. Even to the point of defining different kinds of rows and nesting of rows and nesting of elements. The key is that you provide some kind of map and then your data follows the map. Ultimately this leads you to XML in a sense cause this is basically what XML does only real badly.
This method however is very limited for two reasons:
1. string variables have limits in size (4000 bytes) (bigger on 12c as I recall but still an upper bound), and CLOBS are wieldy and hard to use if you use them to get around the size limit issue.
2. string manipulations are requires to parse the incoming data and get the pieces out and this is expensive compared to other methods.
------------------------------------------------------------------------------------------------------------------------------------- ------------
2. refcursor
Refcursor method has variants too. But they mostly just involve how you get the query to the code that needs it. Somewhere along the way you open a refcursor and take "dynamic" data. You can read up on refcursors to find out more but here is a quick example.
declare
sql_v varchar2(32000) := 'select order_number,order_row,part_number from yourtable where some_where_clause';
c1 sys_refcursor;
results_v ???
begin
open c1 for sql_v;
results_v := call_your_function (c1_p in sys_refcursor);
end;
/
The point is a refcursor is fully dynamic so you can pass any list of elements you need. You have to work out how to handle the results as well but you get the idea I hope.
------------------------------------------------------------------------------------------------------------------------------------- ------------
3. PL/SQL associative array
Here you define a package associative array and then fill it with data and pass it around.
declare
type r1 is record (order_number integer,order_row integer,part_number varchar2(10));
type t1 is table of r1 index by binary_integer;
a1 t1;
i binary_integer;
results_v ???;
begin
1 := 1;
a1(i).order_number = 1;
a1(i).row_number = 1;
a1(i).part_number := 'ABQ24X';
1 := 2;
a1(i).order_number = 1;
a1(i).row_number = 2;
a1(i).part_number := 'PTZ91Y';
1 := 3;
a1(i).order_number = 1;
a1(i).row_number = 3;
a1(i).part_number := 'PTZ92Y';
1 := 4;
a1(i).order_number = 2;
a1(i).row_number = 1;
a1(i).part_number := 'ABQ24X';
result_v := call_your_function (a1);
end;
/
------------------------------------------------------------------------------------------------------------------------------------- ------------
4. nested table type (collections)
Same thing as 3 almost except you use database types instead of pl/sql types.
create or replace type o1 is object (order_number integer,order_row integer,part_number varchar2(10))
/
create or replace type c1 is table of o1
/
declare
c1_v c1 := c1();
results_v ???;
begin
select cast(multiset(select * from (
select 1,1,'ABQ24X' from dual union all
select 1,2,'PTZ91Y' from dual union all
select 1,3,'PTZ92Y' from dual union all
select 2,1,'ABQ24X' from dual
)
) as c1
)
into c1_v
from dual;
results_v := call_your_function (c1_v);
end;
/
Note I have used WHOLE OBJECT ASSIGNMENT in order to fill the nested table C1_V.
------------------------------------------------------------------------------------------------------------------------------------- ------------
5. xml data
Much to wordy for me to bother with an example. Unless someone told you, you have to use it, then I usually would not. I leave it to you to work this one out if you need it.
In the end, you see it all comes down to how you want to package data for sending and receiving. You are essentially defining messages to be passed around between pieces of code. You use whatever techniques make the most sense for your needs in both collecting the input data, and taking back the output (results).
Kevin
[Updated on: Fri, 20 February 2015 16:05] Report message to a moderator
|
|
|
|
|
|
Re: How to handle unknown number of variables [message #633845 is a reply to message #633713] |
Thu, 26 February 2015 02:34 |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
Thanks Kevin
I decided to add an xml example, just in case ...
Declare
type r1 is record (order_number integer,order_row integer,part_number varchar2(10));
type t1 is table of r1 index by binary_integer;
a1 t1;
--
xmlvar XmlType := XmlType('<?xml version="1.0" encoding="ISO-8859-1"?>
<Orders>
<Order>
<OrdNum>1</OrdNum>
<OrdLine>
<OrdRow>1</OrdRow>
<PartNum>ABQ24X</PartNum>
</OrdLine>
<OrdLine>
<OrdRow>2</OrdRow>
<PartNum>PTZ91Y</PartNum>
</OrdLine>
<OrdLine>
<OrdRow>3</OrdRow>
<PartNum>PTZ92Y</PartNum>
</OrdLine>
</Order>
<Order>
<OrdNum>2</OrdNum>
<OrdLine>
<OrdRow>1</OrdRow>
<PartNum>ABQ24X</PartNum>
</OrdLine>
</Order>
</Orders>
'
);
-- results_v ???;
Procedure ExtractXml(
xmlp In XmlType,
t_nt Out t1
) Is
Begin
Select t.OrdNum,
u.OrdRow,
u.PartNum
Bulk Collect Into t_nt
From XmlTable('/Orders/Order'
passing xmlp
Columns OrdNum Number(2) Path 'OrdNum',
xLig XmlType Path 'OrdLine'
) t,
XmlTable('OrdLine'
passing t.xLig
Columns OrdRow Number(2) Path 'OrdRow',
PartNum Varchar2(10) Path 'PartNum'
) u;
/*
For i In 1..t_nt.Count() Loop
Dbms_output.put_line(t_nt(i).order_number||' '||t_nt(i).order_row||' '||t_nt(i).part_number);
End Loop;
*/
End;
Begin
ExtractXml(xmlvar, a1);
-- result_v := call_your_function (a1);
End;
|
|
|