Home » SQL & PL/SQL » SQL & PL/SQL » How to handle unknown number of variables
How to handle unknown number of variables [message #633479] Thu, 19 February 2015 08:30 Go to next message
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 #633480 is a reply to message #633479] Thu, 19 February 2015 08:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>So the PL SQL code I have written does this

I do not believe you since STR variable is never declared.

This "design" is flawed because it does not scale.

>However... the problem is... the information from the information system comes one after another.
While above is likely true, what data structure is the actual data source?
Re: How to handle unknown number of variables [message #633481 is a reply to message #633479] Thu, 19 February 2015 08:42 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hello!

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.
Re: How to handle unknown number of variables [message #633482 is a reply to message #633481] Thu, 19 February 2015 08:46 Go to previous messageGo to next message
Nuggit
Messages: 5
Registered: February 2015
Location: Stockholm, Sweden
Junior Member
My idea was to summon the part of the code that didnt work to give you an idea about what my problem was instead of posting 400 lines of code that works...
But I guess that was wrong...
Re: How to handle unknown number of variables [message #633484 is a reply to message #633479] Thu, 19 February 2015 08:59 Go to previous messageGo to next message
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 #633486 is a reply to message #633484] Thu, 19 February 2015 09:08 Go to previous messageGo to next message
Nuggit
Messages: 5
Registered: February 2015
Location: Stockholm, Sweden
Junior Member
Of course this is possible and probably pretty accurate...

Yes a cursor would probably be the way to go... however I dont know how to do this and "know" when the information system wont send any more rows...
If I do a loop then I need something to tell me when to stop the loop and procede...

If I was to say use ALL rows for the current order, then it wouldnt be a problem... just select the rows for the current order...
Also if I knew that always 3 rows are selected... but I dont... anywhere from 1 to 10000 rows might be selected... and the only way to know that I guess is to wait until there is nothing more coming from the information system...

Re: How to handle unknown number of variables [message #633487 is a reply to message #633486] Thu, 19 February 2015 09:12 Go to previous messageGo to next message
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 #633488 is a reply to message #633487] Thu, 19 February 2015 09:24 Go to previous messageGo to next message
Nuggit
Messages: 5
Registered: February 2015
Location: Stockholm, Sweden
Junior Member
the & -variables are "inside" the information system to tell it what columns needs to be extracted



so the &order_no tells the system to take the value from the order_no column in the current view (the view that is shown to the user of the information system).

my function uses a view called CUSTOMER_ORDERS,

so the &order_no catches the value of the order_no for the order the user is currently viewing



(maybe that was already clear for you)

so too me its not really blind variables since it would be the same as doing

SELECT order_no into order_num FROM CUSTOMER_ORDERS where order_no ='O123456';



the same with

&part_no its the same as SELECT PART_NO into part_no FROM CUSTOMER_ORDERS where order_no='O123456';

And im terribly sorry for the ...
Re: How to handle unknown number of variables [message #633493 is a reply to message #633488] Thu, 19 February 2015 10:47 Go to previous messageGo to next message
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 #633517 is a reply to message #633493] Fri, 20 February 2015 00:37 Go to previous messageGo to next message
Nuggit
Messages: 5
Registered: February 2015
Location: Stockholm, Sweden
Junior Member
Im sorry, but I have not idea in what language language the information system is built in.
Re: How to handle unknown number of variables [message #633535 is a reply to message #633517] Fri, 20 February 2015 15:33 Go to previous messageGo to next message
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 #633693 is a reply to message #633535] Tue, 24 February 2015 04:45 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Hi Kevin

Could you just add few words about why you would not consider the use of XML in this case?

Thanks for your time.
Re: How to handle unknown number of variables [message #633709 is a reply to message #633693] Tue, 24 February 2015 06:25 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
It is merely an opinion. XML to me is way too wordy to be a good data transfer language.

Then again I think JAVA was a huge mistake too since to me JAVA is like taking a step backwards in time.

This of course brings into question anything I say since I have just said I think two mechanisms adopted by the world as primary ways of doing our business, were mistakes. What makes me right and the rest of the world wrong? Who knows. But a man is entitiled to his opinion.

I use XML (and Java) when the Boss says I aboslutely have to, otherwise I avoid each with gusto.

Kevin
Re: How to handle unknown number of variables [message #633713 is a reply to message #633709] Tue, 24 February 2015 06:34 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
There are things worse than Java. Think of C++. Think multiple inheritance....
Re: How to handle unknown number of variables [message #633845 is a reply to message #633713] Thu, 26 February 2015 02:34 Go to previous message
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;


Previous Topic: creation of records with respect to the delimited data from a column of base table
Next Topic: Order by with WITH clause
Goto Forum:
  


Current Time: Fri Apr 26 05:35:46 CDT 2024