• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 840
  • Last Modified:

Accept multiple values from Java into an PL/SQL procedure

I am writing a PL/SQL procedure which (in part) should accept a series of values from a Java front-end for for an item which should be used to limit values to return.

For example, my procedure might return a list of clients, and a user might want to see all clients, or only some clients from one or more states.

My PL/SQL procedure could accept a comma delimited string (eg. "ME,IA,NY,TX" which would return clients from Maine, Iowa, New York and Texas) , or an empty string to get back all clients fron all states.

I'm being told that my procedure could/should accept things called "collections" or "objects" which apparently would make it easier and/or better for the Java programmers.

Is this true, and if so, how should my parameters be coded and how can I strip out the values passed?
0
caldernet
Asked:
caldernet
4 Solutions
 
mrjoltcolaCommented:
Here is a way to do what you are asking. However, for simple cases (1 to 3 parameters) it might be

http://knol.google.com/k/franck-pachot/oracle-passing-a-list-as-bind-variable/17uabcrki6uux/2#

And an Ask Tom article with much detail.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

This is an Oracle specific approach.

Also, if you are passing in collections, you need to make sure to test performance with the max number a user might specify.
0
 
mrjoltcolaCommented:
I meant to finish with : for simple cases it may be easier to maintain a SQL statement with UNION or OR but that does work against the Oracle performance in a high volume environment. I would make sure to test the explain plans and compare cases using dynamic SQL with OR or IN clauses, vs the collections. Make sure you know which one forks best for your typical case, and even your 99% of cases. Don't tune for the 1%
0
 
caldernetAuthor Commented:
Unless I'm missing something (not impossible) or don't understand something (quite possible) or didn't explain the initial problem properly (also quite possible), it appears that in all examples on these pages, the PL/SQL procedure/function is simply accepting a string value.

In one case I see the following:
     create or replace
         procedure isIn_procedural( p_array1 in numArray,
                                 p_array2 in numArray,
                                 p_print in boolean default false )

but this give me an error:
     Error(2,40): PLS-00201: identifier 'NUMARRAY' must be declared

I'm fine with accepting a strin value:
     CREATE OR REPLACE procedure search_str (in_search_str IN varchar2)

If that is what I have to do....I was just being told I could do better...I'm not the most experienced PL/SQL programmer in the world.

Thanks.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
ajexpertCommented:


This can be done.  You can accept comma delimited string as one of the input parameter (state codes) and the output should be a ref cursor which is nothing but the list of clients residing in that state.

Here is the sample procedure



CREATE OR REPLACE PROCEDURE pr_list_clients(p_states IN VARCHAR2, p_clients SYS_REFCURSOR)
 
AS
 
--Since you are new to PL/SQL i have done in simplified manner.
 
--get the list of clients if the state is specified
CURSOR c1 IS 
SELECT * FROM <client_table>
WHERE <client_state> = p_state
 
--get the list of clients if the state is NOT specified or is null or empty string
CURSOR c2 IS 
SELECT * FROM <client_table>
WHERE <client_state> = p_state
 
BEGIN
 
-- return the list of clients to Java thru ref cursor
 
    IF pr_list_clients IS NOT NULL THEN
       OPEN p_clients FOR C1;
    ELSE
       OPEN p_clients FOR C2;
    END IF;
 
EXCEPTION 
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      
 
END;

Open in new window

0
 
ajexpertCommented:
CORRECTED CODE
CREATE OR REPLACE PROCEDURE pr_list_clients(p_states IN VARCHAR2, p_clients SYS_REFCURSOR)
 
AS
 
--Since you are new to PL/SQL i have done in simplified manner.
 
--get the list of clients if the state is specified
CURSOR c1 IS 
SELECT * FROM <client_table>
WHERE <client_state> = p_state
 
--get the list of clients if the state is NOT specified or is null or empty string
CURSOR c2 IS 
SELECT * FROM <client_table>;
 
 
BEGIN
 
-- return the list of clients to Java thru ref cursor
 
    IF pr_list_clients IS NOT NULL THEN
       OPEN p_clients FOR C1;
    ELSE
       OPEN p_clients FOR C2;
    END IF;
 
EXCEPTION 
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      
 
END;

Open in new window

0
 
sventhanCommented:

 
also you could try something like below...
create or replace procedure test_ref( refc out sys_refcursor) is
begin
open refc for select col1, col2 from (select 1 col1, 2 col2 from dual union all select 3 col1, 4 col2 from dual);
end;
/
Declare
Refcur Sys_refcursor;
Type My_rec Is Record(col1 Number, col2 number);
Type My_rec_tab IS TABLE OF My_rec INDEX BY BINARY_INTEGER;
Rec My_rec_tab;
Begin
Dbms_output.enable(null);
Test_ref(Refcur);
Fetch Refcur bulk collect Into Rec;
For i in rec.first..rec.last loop
Dbms_output.Put_line(Rec(i).col1||' '||Rec(i).col2);
End loop;
End;
/
 You can also use an OS variables in SQL Developer or Sql*Plus:
Variable refcur refcursor
exec test_ref(:refcur);
print refcur

 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now