Solved

Accept multiple values from Java into an PL/SQL procedure

Posted on 2009-04-06
6
819 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

770 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