How to get data from a pl/sql function if a parameter have more than one value in function with single parameters

Question

I am developing a procedure in pl/sql that get a data set from a cursor executed in a function. For example:

Function:

f_process_data(id_process IN NUMBER, id_product IN NUMBER)

Returns :

v_result_cursor sys_refcursor;

But the problem is that in the search of the cursor I need to send at time more than one id_product. Something like this:

id_product: 1240 (sausages) id_product: 1260 (ham)

¿How can I send (or get) more than one product in the function?

I understood that it's possible with a loop, but how?

Something like this(?):

v_sausage := 1240;
v_ham := 1260;

LOOP    
   IF v_count = v_sausage OR v_count = v_ham THEN 
   v_result_cursor := f_process_data(1, p_id_product);
   END IF;
   FETCH v_result_cursor
   INTO v_id, v_id_product;
   EXIT WHEN v_result_cursor%NOTFOUND;
END LOOP;

I expected that v_id and v_id_product has been recorded in a collection including the results from the id_product 1240 and 1260.

But, if the business rules changes and is not only 2 products, how about 100 or 1000 products?

Note: the cursor from the function it's not possible to modify, the id_product must be sent one by one.

CURSOR cu_get_value_products IS
SELECT value FROM supplies WHERE id = p_id and id_product = p_id_product;

Possible duplicate of [Passing an array of data as an input parameter to an Oracle procedure](https://stackoverflow.com/questions/2885575/passing-an-array-of-data-as-an-input-parameter-to-an-oracle-procedure) — Serg, May 24 '19 at 16:56
@reymagnus How would you like to format the product parameter? As in... what is your preference? Do you want the parameter to be a comma separated list if product ids? `1001,1002,1058,2105` as a VARCHAR2? Do you want to instead pass an array/collection into your `f_process_data()` as a Function after first building up that collection with product ids? There are many solutions to your question. — Code Novice, May 24 '19 at 17:38
@Code Novice The cursor in the function gets only one id_product by time, and it's not possible to change. — reymagnus, May 24 '19 at 18:01
@reymagnus Ok so what you are wanting then... is to run the Cursor for every Product_ID that comes into the f_process_data() Function. If that is the case you will no longer be returning a ref_cursor and would need to return something else. If you are not able to alter the Cursor that is. So my question is now, can you change the Return type of your f_process_data() function to instead return a Collection of the data you wish to Parse? — Code Novice, May 24 '19 at 18:07
@reymagnus If you are not able to alter the f_process_data() function or the code within it you would then need to Loop over your Function using the Product_ids. Something like... `FOR r IN products.FIRST .. products.LAST LOOP f_process_data(products(r).product_id) END LOOP`. — Code Novice, May 24 '19 at 18:11
@reymagnus Is your Cursor within the f_process_data() function actually defined as `CURSOR cu_get_value_products IS ... ;`? If so how is that Cursor being Returned as a `SYS_REFCURSOR`? — Code Novice, May 24 '19 at 18:30
@Code Novice, the function f_process_data returns sys_refcursor, because cursor cu_get_value_products is fetched into sys_refcursor. The most important here is that this function and cursor is not allowed to modify. I vote for the solution with a loop in the procedure that invokes the function but I don't know if I need to store the id_products in a for example an array but what if then its needed 100 or 1000 id_products? — reymagnus, May 24 '19 at 18:43
@reymagnus Perhaps I'm just not privy as to how you are taking a static Explicit CURSOR defined within your function and then converting it to a SYS_REFCURSOR so that then it can be returned? How is this conversion performed? — Code Novice, May 24 '19 at 18:55
Because inside the function the cursor is fetched into a sys_refcursor, and the function returns sys_refcursor. I am not sure if I am clear in this point. — reymagnus, May 24 '19 at 19:52
@reymagnus Can you post a snippet of how the Static Cursor is being FETCHed into a sys_refcursor? Using the Pseudro code --> when I try to do `FETCH static_cursor INTO ref_cursor;` it fails. I'm simply curious how this is done. — Code Novice, May 24 '19 at 20:56
@Code Novice The function calls another function that calls another function that opens the cursor, something like f_open_cursor(query) returns sys_refcursor and inside this function only: OPEN cursor FOR query; The example than I posted is similar but is not exactly as original due to protect those code, sorry for that -_- — reymagnus, May 24 '19 at 21:29

score 1 · Accepted Answer · answered May 24 '19 at 20:25

With somewhat limited information the below is what I've come up with. I still have no clue what is happening with your STATIC Cursor which you mentioned in your question is defined in your f_process_data() Function. Since I don't know the full code in this function I simply wrote my own and declared the CURSOR as SYS_REFCURSOR as that is what the function returns.

Please let me know if this works or if I'm missing some important information. I feel like I'm lacking crucial information in order to provide a useful solution to you.

The mock-up table I created called Products contains the following columns and data. See image.

DECLARE

  /* Store Ref Cursor returned by f_process_data() Function */
  v_result_cursor   SYS_REFCURSOR;

  /* Declare Record so we can store the data FETCHed from the Cursor */
  rec_products      products%ROWTYPE;

  /* Declare a couple Product Variables for Proof of Concept */
  v_sausage         NUMBER;
  v_ham             NUMBER;

  /* Store output */
  n_id              NUMBER;
  v_id_product      VARCHAR2(100);

  /* Declare Type of TABLE NUMBER */
  TYPE  nt_type IS TABLE OF NUMBER;

  /* Create Array/Table/Collection of type nt_type to store product ids */
  nt_product_ids    nt_type;

  /* Returns a Ref Cursor based on the product_id used as Input to this function */
  FUNCTION f_process_data(p_id_process IN NUMBER, p_id_product IN NUMBER)
  RETURN SYS_REFCURSOR
  AS
    /* Declare Ref Cursor that will be Returned */
    rc_result_cursor   SYS_REFCURSOR;    

  BEGIN 
    /* Open Ref Cursor based on Product ID parameter */
    OPEN rc_result_cursor FOR SELECT * FROM products WHERE item_id = p_id_product;

    RETURN rc_result_cursor;

  END f_process_data
  ;

BEGIN

  /* Set Product Variables to IDs */
  v_sausage       := 2002;
  v_ham           := 2009;

  /* Store product ids into a Number Table so we can Loop thru it */
  nt_product_ids  :=  nt_type (v_sausage,v_ham);

  FOR r IN nt_product_ids.FIRST .. nt_product_ids.LAST
  LOOP
    /* Get Ref Cursor using SINGLE Product ID */
    v_result_cursor := f_process_data(1, nt_product_ids(r));

    LOOP

    FETCH v_result_cursor INTO rec_products;

    n_id            :=  rec_products.item_id;
    v_id_product    :=  rec_products.item;

    EXIT WHEN v_result_cursor%NOTFOUND;

    dbms_output.put_line('Product_id: ' || n_id);
    dbms_output.put_line('Product: ' || v_id_product);  

    END LOOP; /* Cursor Loop */

    /* Close Cursor */
    CLOSE v_result_cursor;

  END LOOP; /* Product IDs Loop */

EXCEPTION WHEN OTHERS
  THEN CLOSE v_result_cursor;

END;

As per your requirements I was able to create this solution without altering your Function - `FUNCTION f_process_data(p_id_process IN NUMBER, p_id_product IN NUMBER) RETURN SYS_REFCURSOR`... other than my adding `p_` as I like to reference my parameters in that manner. — Code Novice, May 24 '19 at 20:32
You are right. The cursor is static and this is the solution that I could apply. Thanks so much. — reymagnus, May 24 '19 at 20:52
I have tried to implement this and get this error: ORA-01001: invalid cursor. It crashes just before to enter second loop. — reymagnus, May 28 '19 at 02:37
@reymagnus Make sure the cursor is not being closed. If you attempt to Fetch from a Cursor that was previously closed this is one way you will get the error 'Invalid Cursor'. — Code Novice, May 28 '19 at 12:42
It's solved, for now. It was because to passing null values as a parameter in the function, but this error is rare. — reymagnus, May 28 '19 at 17:07

score 0 · Answer 2 · answered May 24 '19 at 18:44

0

In order to send more than one product to be processed at a time, create a user defined Table Type in your database called ProductList. You can then send a Product list into a stored procedure as a parameter.

answered May 24 '19 at 18:44

Roger

261
1
7

But, how it works if the function and cursor only receive one parameter value at a time? I am not familiar with lists as a parameter in a pl/sql function, for now... – reymagnus May 24 '19 at 18:48
Don't use a function or a cursor. – Roger May 24 '19 at 19:02
The use of function or cursor is not optional because this objects can not be modified or dropped and necessarily must use them. The global stored procedure invokes the function f_process_data and this function executes a cursor, that's It. – reymagnus May 24 '19 at 19:20

How to get data from a pl/sql function if a parameter have more than one value in function with single parameters

2 Answers2

Linked