PL SQL: How to populate a cursor in loop

Question

I have a scenario to implement where in we will have an SP with two parameters 1) In parameter of array of Varchar2 2) Cursor -- Out parameter

We have populate the cursor only for each Value passed in array and this opened/populated cursor will be used by Java. But what i have found till now is, if we have to process an array then it should have to be done using loop.

For ex:

Open Cursor_Name
For 
For index In Arrar_Parameter.FIRST .. Arrar_Parameter.LAST
LOOP
    SELECT * FROM EMP WHERE EmpId = Arrar_Parameter[i] -------> This needs to be looped and not sure if this will work
END LOOP

Can we have some thing like this

Open Cursor_Name
For 
SELECT * FROM EMP WHERE EmpId IN (Arrar_Parameter values) ------> To fetch/put all the array values at once without loop.

Kindly suggest hot to populate cursor in this scenario

You said it's an array though; so is it a `varray` or a nested table? Well, I've covered both in my answer hopefully. — Alex Poole, Aug 04 '16 at 09:03

score 1 · Accepted Answer · answered Aug 04 '16 at 09:02

1

Assuming your array parameter is a schema-level varray or nested table, you can use a table collection expression:

The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting.

This uses a built-in varray type but you can substitute your own:

create procedure procedure_name (
  array_parameter sys.odcivarchar2list, cursor_name out sys_refcursor
) as
begin
  open cursor_name for
    select e.*
    from table (array_parameter) a
    join emp e on e.empid = a.column_value;
end;
/

You can also use in if you prefer:

create procedure procedure_name (
  array_parameter sys.odcivarchar2list, cursor_name out sys_refcursor
) as
begin
  open cursor_name for
    select *
    from emp
    where empid in (select column_value from table (array_parameter));
end;
/

If it is a nested table you can also use the member of syntax:

create type my_varchar2_table as table of varchar2(30);
/

create procedure procedure_name (
  array_parameter my_varchar2_table, cursor_name out sys_refcursor
) as
begin
  open cursor_name for
    select *
    from emp
    where empid member of array_parameter;
end;
/

answered Aug 04 '16 at 09:02

Alex Poole

183,384
11
179
318

Thanks a ton @Alex Poole, just need one more suggestion....if we create this array of VARCHAR2 in db and when JAVA application calls this SP with an array of string in JAVA, so this implementation of SP will work?Kindly suggest – PS078 Aug 04 '16 at 10:49
1

@PS078 - I'm not quite sure what you mean; [this answer](http://stackoverflow.com/a/21034016/266304) shows how to convert a Java array to a `varray` collection. It's for numbers but it works the same way for strings. – Alex Poole Aug 04 '16 at 11:32
Thanks a lot @Alex Poole. I shall try implementing your suggestions and ask you know in case find any problem. Thanks :) – PS078 Aug 04 '16 at 12:05
Hello @Alex Poole, Kindly suggest here, I am getting error(PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR) while populating Ref_cursor, the code is like this OPEN Ref_cur_Price details FOR SELECT * FROM VW_PRODUCTS_PRICES WHERE ORIG_Id MEMBER OF Nested_Table_ids; VW_PRODUCTS_PRICES is a View Nested_Table_ids is a table of Varchar2(100) One more point. ORIG_Id column of view is of NVARCHAR(200) type, can this data type mismatch also throw error. – PS078 Aug 08 '16 at 10:55
They need to be the same type, I'm not sure if it will allow implicit conversion between varchar2 and nvarchar2 though. Have you declared the data type for `nested_table_ids` as a PL/SQL type, or at schema level (with `create type ...`)? – Alex Poole Aug 08 '16 at 11:26
Hello @Alex Poole, I have created the type at Package level like this, create or replace PACKAGE DATA_PREP_PKG AUTHID DEFINER IS TYPE nested_table_ids IS TABLE OF NVARCHAR2(200); Also i have tried with converting the nested table type as NVARCHAR2(200), but still getting same error. – PS078 Aug 08 '16 at 11:29
Error is: PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR – PS078 Aug 08 '16 at 11:30
1

[The type *has* to be declared at schema level](http://stackoverflow.com/a/17039852/266304) for you to use it in an SQL query. The built-in type I used in my example is defined as an SQL type, not a PL/SQL type. – Alex Poole Aug 08 '16 at 11:38
Thanks a lot @Alex Poole, it worked after creating the Type at schema level – PS078 Aug 08 '16 at 12:45
Hello @Alex Poole, I am willing to learn PLSQL collections concepts in depth and for this I request you to please suggest any source/tutorial to start from scratch. Thanks in advance. – PS078 Aug 10 '16 at 08:59
1

@PS078 - recommendations are off-topic. But [start with the documentation](https://docs.oracle.com/cloud/latest/db112/LNPLS/composites.htm#LNPLS005). – Alex Poole Aug 10 '16 at 09:35
Thanks @Alex Poole – PS078 Aug 10 '16 at 10:34

PL SQL: How to populate a cursor in loop

1 Answers1