This discussion is archived
14 Replies Latest reply: Oct 12, 2012 9:00 AM by BluShadow RSS

Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.

957828 Newbie
Currently Being Moderated
Hi,
I am writing a procedure as -
Create or replace procedure as testing(v_type IN VARCHAR2, v_ssid IN ID_LIST,
v_from_date IN DATE, v_to_date IN DATE,
io_cursor OUT SYS_REFCURSOR)

AS
BEGIN
If v_type = 'NONE' THEN

BEGIN
For i IN 1..v_ssid.count LOOP
OPEN io_cursor for
Select id,curr,sum(value)
From curr_summ
where id=v_ssid(i)
and tr_date between to_date (v_from_date) and to_date (v_to_date)
GROUP by id, curr ;
END LOOP ;
END ;
END IF ;

END ;
/

Create or replace type ID_LIST AS VARRAY(500) of number(10) ;
/

I have created above procedure to execute from java for passing varray to the procedure but it is returning the result set for last value from the varray.
For Eg.
I am passing 15, 105, 2335 as varray for v_ssid, with all other proper parameters, the procedure returns result set to java for v_ssid 23365

What should I add to the procedure to get result set for all values passed.

Help is really appreciated.
Thanks in advance.
  • 1. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    Stew Ashton Expert
    Currently Being Moderated
    You are opening one ref cursor per ssid, then passing the last cursor to the java program.

    Your DBA will not like you ;)

    You need to return ONE ref cursor with all the rows in it.

    Here is a simple example of one method to do this. If you understand the method, you should be able to apply it to your situation.
    create or replace procedure TESTING(
      V_TYPE in varchar2,
      v_ssid IN sys.odcinumberlist,
      IO_CURSOR OUT SYS_REFCURSOR
    ) AS
    BEGIN
      open IO_CURSOR for
      Select v_type type, column_value ssid from table(v_ssid);
    END ;
    /
    VARIABLE RC REFCURSOR;
    EXEC TESTING('A', SYS.ODCINUMBERLIST(1,2), :RC);
    print :rc;
    Personally, I would prefer a FUNCTION that returns a ref cursor.

    P.S. The calling program must explicitly close the cursor.

    Edited by: Stew Ashton on Oct 6, 2012 12:20 PM
  • 2. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    957828 Newbie
    Currently Being Moderated
    Thanks stew.. But I i am liitle bit confussed.
    according to your reply I tried to remove an oracle refcursor for every id and I created object with 4 variables whihc are needed in O/p.
    then I created PLSQL table of the object type.

    in for loop selected every id for the conditions from input vars and bulk collected into the table type.

    Then I opened a sysrefcursor for that table tuype AS--

    Create or replace type LIST AS VARRAY(500) of number(10) ;
    /



    CRETE OR REPLACE PROCEDURE PROC1(ID LIST,
    FR_DATE DATE,
    TO_DATE DATE,
    P_REC out SYS_REFCURSOR)

    CREATE OR REPLACE OBJECT ITAB OF(
    ID... DATATYPE---
    CURR..
    COUNT);

    cREATE OR REPLACE TABLE LOCAL OF TYPE itab;

    FOR i IN ID.FIRST..ID.LAST LOOP
    SELECT LOCAL(ID,CURR,SUM(COUNT)) BULK COLLECT INTO local
    FROM CURR_SUMM
    WHERE ---;

    END LOOP;

    OPEN P_REC FOR
    SELECT * FROM LOCAL;

    END;

    When I execute this proc from DB passing varray as input and p_rec as O/p it gives me error
    ORA PLS00306 Wrong no or types of argumemnts in call
    ORA 06550 - line no 10 col 3
    where I call the procedure

    and from java code this displays still then last records recorset from the sy srefcursor

    Declare
    v_t LIST;
    I_curr SYS_REFCURSOR;
    id - NUMBER;
    curr VARCHAR2;
    cnt NUMBER;

    BEGIN
    v_t := list();
    v_t.extend(10); -----here i get error
    v_t(1) :=150;
    v_t(2) := 2150;
    v_t(3) := 1520;

    proc1(v_t,'25-APR-2012','31_DEC-2012',p_rec=>I_curr); -- this is line where I get error ORA PLS 306.
    LOOP
    FETCH I_CURR INTO id,curr,cnt
    exit when I_curr%notfound;
    DBMS_OUTPUT.PUT_LINE(id||'--'||curr||'--'||cnt);
    end loop;
    close I_curr;

    END;

    and if procedure is executed from JAVA it returns the resultset for last value 1520.

    and when I wrote the proc inside the package even then its giving error while calling the proc from java ORA PLS306.

    Please tell me why this is happening.

    Thank you so much.
    this will really will be helpful to me to resolve the problem I am facing.
  • 3. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    Stew Ashton Expert
    Currently Being Moderated
    Let's take this one step at a time.

    You are still doing a loop. You started with a loop and I said to replace that with one SELECT statement.

    If you open a REF CURSOR with one SELECT statement, you are done. You just pass that back to the calling program and the calling program does the fetches. Most of your code can just disappear, starting with that LOCAL table!

    Why are you still doing a loop?

    Can you provide CREATE TABLE and INSERT statements for the input data, then explain in words what output you are trying to pass back to the calling program? We are getting lost in your solution, which seems way too complicated, so let's get back to the original problem.
  • 4. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    957828 Newbie
    Currently Being Moderated
    Thanks stew again..
    but with one select statement java code is able to fetch one last row only and why I am doing loop is I am passing more than 1 values in varray type input parameter.

    I will explain you the problem.

    I need to pass 2 dates, 1 varchar2 and one set of values which may contain more than 1000 values for this I have taken varray type and one OUT p_rec as SYS_REFCURSOR which will fetch 5 values in toral from the table.

    to get through all the values passed in this varray type (id IN ID_LIST) I am looping.
    when I tried not to open a ref cursor for every fetch as per your earlier suggestion I wanted to store somewhere those values so I created PLSQL table type,
    bulk collected all values for varray I am passing and as per other conditions from Input parameters.
    FOR i in 1 ..id.count LOOP
    SELECT LOCAL_TAB(......) BULK COLLECT INTO local_type from curr_sum where id = id(i) and condi 2.....
    END LOOP
    THEn I tried to open a REFCURSOR and pass values back to calling JAVA program (OPEN p_rec for select * from table(local_type)) but this also passed only single value which is last from the varray list to the calling program.
    Also this proc when wrapped into package and tested through calling java code gave me error PLS00306 wrong type or no of arguments in call.

    I am not familier how to use 'sysodcinnumberlist'.

    Please help.
  • 5. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    rp0428 Guru
    Currently Being Moderated
    >
    I am not familier how to use 'sysodcinnumberlist'.
    >
    That is just one of several predefined VARRAYs that you can use without having to create one of your own.
    SQL> desc sys.odcinumberlist  sys.odcinumberlist VARRAY(32767) OF NUMBER
    SQL> desc sys.odcivarchar2list  sys.odcivarchar2list VARRAY(32767) OF VARCHAR2(4000)
    SQL> desc sys.ODCIDATELIST  sys.ODCIDATELIST VARRAY(32767) OF DATE
    SQL> desc sys.dbms_debug_vc2coll  sys.dbms_debug_vc2coll TABLE OF VARCHAR2(1000)
    SQL>  
    The VARRAY you pass in can be queried like a table so just use it in your cursor and get rid of the loop.

    Instead of this
    For i IN 1..v_ssid.count LOOP
    OPEN io_cursor for
    Select id,curr,sum(value)
    From curr_summ
    where id=v_ssid(i)
    and tr_date between to_date (v_from_date) and to_date (v_to_date)
    GROUP by id, curr ;
    END LOOP ;
    Just use something like this (untested)
    OPEN io_cursor for
    Select id,curr,sum(value)
    From curr_summ
    where id in (select value from table (v_ssid))
    and tr_date between to_date (v_from_date) and to_date (v_to_date)
    GROUP by id, curr ;
    The thing is you could just construct that query in Java and execute it directly without even needing the function
    String query = 'Select id,curr,sum(value) From curr_summ where id in (' +
      '15, 105, 2335) and tr_date between to_date (?) and to_date (?) GROUP by id, curr' ;
    Then set your parameters and execute the query.
  • 6. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    957828 Newbie
    Currently Being Moderated
    Thanks a lot but I cannot use IN for

    where id in (select value from table (v_ssid))

    as there would be more than 1000 values.

    or will this work for more than 1000 values as normal query fails after this LIMIT if we qare using sys.odcinumberlist ?

    as

    desc sys.odcinumberlist sys.odcinumberlist VARRAY(32767) OF NUMBER

    Thanks a Lot.
  • 7. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    Stew Ashton Expert
    Currently Being Moderated
    You didn't provide CREATE TABLE and INSERT statements, so I will try:
    drop table curr_sum;
    create table curr_sum (id, curr, value, tr_date) as
    select mod(level-1, 100), mod(level-1,10), 10, trunc(sysdate)-mod(level-1,10)
    from dual connect by level <= 1000;
    
    Select id,curr,sum(value)
    From curr_sum
    where tr_date between trunc(sysdate-9.1) and trunc(sysdate-8.5)
    GROUP by id, curr 
    order by id, curr;
    
    ID CURR SUM(VALUE)
    -- ---- ----------
     9    9        100 
    19    9        100 
    29    9        100 
    39    9        100 
    49    9        100 
    59    9        100 
    69    9        100 
    79    9        100 
    89    9        100 
    99    9        100
    Now I will try to show you how to return a REF CURSOR, using an array with 10000 entries. Here is the function:
    create or replace function get_curr_sum (
      p_id_list sys.odcinumberlist,
      p_from_date curr_sum.tr_date%type,
      p_to_date curr_sum.tr_date%type
    ) return sys_refcursor is
      io_cursor sys_refcursor;
    begin
      OPEN io_cursor for
      Select id,curr,sum(value) sum_value
      From curr_sum
      where id in (select column_value from table (p_id_list))
      and tr_date between p_from_date and p_to_date
      GROUP by id, curr 
      order by id, curr;
      return io_cursor;
    end;
    /
    Now let's test:
    variable refcur refcursor
    declare
      l_id_list sys.odcinumberlist := sys.odcinumberlist();
    begin
      l_id_list.extend(10000);
      for i in 1..10000 loop
        l_id_list(i) := i;
      end loop;
      :refcur := get_curr_sum(l_id_list, trunc(sysdate-9.1), trunc(sysdate-8.5));
    end;
    /
    print :refcur
    
    ID   CURR   SUM_VALUE 
    ---- ------ ----------
    9    9      100       
    19   9      100       
    29   9      100       
    39   9      100       
    49   9      100       
    59   9      100       
    69   9      100       
    79   9      100       
    89   9      100       
    99   9      100
  • 8. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    rp0428 Guru
    Currently Being Moderated
    >
    Thanks a lot but I cannot use IN for

    where id in (select value from table (v_ssid))

    as there would be more than 1000 values.
    >
    There will ultimately be 'some' limit to what is practical.

    But the reality is that for your query you can still just deal with this in Java.

    You can easily just use 100 values at a time, fetch the results, and then do the next 100.

    For your query the results for each of your '1000' values is completely independent.

    So write a Java method that loops through your list of values and works with them in batches of 10, 100, or whatever to do your query, get the results, process the results, go do the next batch.

    No need for custom types, stored procedures or ref cursors at all.

    Keep it simple.
  • 9. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    957828 Newbie
    Currently Being Moderated
    Thank you so much ,
    I will try to fetch this in a batch from java and will definately reply after I test this.

    Thank you so much again.
  • 10. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    957828 Newbie
    Currently Being Moderated
    Thank you so much.
    I tried to put the example for my function signature and actual table.
    The function worked for my data but now problem is, stakeholders are not ready to call proc for every 1000 values in a loop.
    is there any other solution?
    as I explaied before -
    create object with fields we want in O/p then creating a table type and dumping values for all id's with other fields may be millions
    and then fetching from there using refcursor?

    I tried this but this is not giving me the results.. says -
    NO ROWS SELECTED.

    Please help me out to resolve this.
  • 11. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    rp0428 Guru
    Currently Being Moderated
    >
    The function worked for my data but now problem is, stakeholders are not ready to call proc for every 1000 values in a loop.
    is there any other solution?
    >
    I gave you a solution above if you have the data in an array that you pass in. That solution will work for as large an array as you can get Java to pass to the back end.

    Your issue is how to get a large number of values to the back-end. For 'millions' you are not going to be able to pass an array or an 'in' list as part of a query. You will need some other approach. How could a user select from 'millions' of values to use anyway; that isn't even realisic.

    So if your array approach will deal with the number of elements you need to pass from the front end use the solution I posted above.

    Otherwise you need to provide more information
    1. What is the maximum number of values that need to be passed from the front end to the procedure?
    2. What is the source of these values? Are they already in the DB somewhere?
    3. What type of information are these values? Are they being selected from a drop-down list? What is the data source for the list?
  • 12. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    957828 Newbie
    Currently Being Moderated
    So if your array approach will deal with the number of elements you need to pass from the front end use the solution I posted above.

    Otherwise you need to provide more information
    1. What is the maximum number of values that need to be passed from the front end to the procedure?
    2. What is the source of these values? Are they already in the DB somewhere?
    3. What type of information are these values? Are they being selected from a drop-down list? What is the data source for the list?


    please find the info below...


    1. java will be passing more than 1000 values for id column from front end.
    java has a form from whihc user enter some info like to_date and from date and his/her user id gets stored in a table curr_sum in our example.
    thus we want procedure as the qery was failing when they were passin 2000/3000 values for id, between perticular date from a select query invoked from JAVA.

    2. values are picjked from a table whihc is in DB.

    3. for some perticular type (radio button), user id (ID) gets stored and user will select 2 dates (from_date, to_date) from the form. its online form so there may be n no. of users. and earlier it failed for the same reason han 1000 values) when java prog was calling a simple select from there code.

    Thank you so much again.

    Edited by: 954825 on Oct 11, 2012 8:39 PM
  • 13. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    rp0428 Guru
    Currently Being Moderated
    >
    1. java will be passing more than 1000 values for id column from front end.
    java has a form from whihc user enter some info like to_date and from date and his/her user id gets stored in a table curr_sum in our example.
    thus we want procedure as the qery was failing when they were passin 2000/3000 values for id, between perticular date from a select query invoked from JAVA.
    >
    Sorry - I'm still not understanding this.

    Where does Java get the 'more than 1000 values' from? That is my question.

    Are these 1000+ values stored in the DB somewhere?

    If so, why is Java querying them from the DB and them passing them back to the DB?

    Why can't the procedure just get the 1000+ values from the table in the DB where they are stored?
  • 14. Re: Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
    BluShadow Guru Moderator
    Currently Being Moderated
    rp0428 wrote:
    >
    1. java will be passing more than 1000 values for id column from front end.
    java has a form from whihc user enter some info like to_date and from date and his/her user id gets stored in a table curr_sum in our example.
    thus we want procedure as the qery was failing when they were passin 2000/3000 values for id, between perticular date from a select query invoked from JAVA.
    >
    Sorry - I'm still not understanding this.

    Where does Java get the 'more than 1000 values' from? That is my question.
    I think his answer number 2 above tells us that... and yes, the values appear to be queried from the database to Java and then Java is trying to pass all those back into another query on the database.

    Poor design it looks like.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points