Solved

Accept multiple values from Java into an PL/SQL procedure

Posted on 2009-04-06
6
824 Views
Last Modified: 2013-12-07
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
Comment
Question by:caldernet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 100 total points
ID: 24079412
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24079454
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
 
LVL 1

Accepted Solution

by:
caldernet earned 0 total points
ID: 24079742
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:ajexpert
ID: 24080411


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
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 200 total points
ID: 24080421
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
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 200 total points
ID: 24080596

 
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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to recover a database from a user managed backup
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question