?
Solved

Accept multiple values from Java into an PL/SQL procedure

Posted on 2009-04-06
6
Medium Priority
?
827 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 400 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 800 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 800 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
The viewer will learn how to implement Singleton Design Pattern in Java.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses
Course of the Month9 days, 5 hours left to enroll

764 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