Solved

Accept multiple values from Java into an PL/SQL procedure

Posted on 2009-04-06
6
815 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
6 Comments
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 14

Expert Comment

by:ajexpert
Comment Utility


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
Comment Utility
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
Comment Utility

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now