CODEWITHSUNDEEP
javasqloracle-database
vinod
vinod

Reputation: 1202

How to read Values from TYPE in Oracle for the Collection (Map) type

In oracle i have created 

create type INTERFACE_VALUES as OBJECT (
IVKEY VARCHAR2(32),
IVVALUE VARCHAR2(250)

);

and 

create type T_INTERFACE_VALUES as TABLE OF INTERFACE_VALUES;

and from java side i am passing one MAP as input to Stored procedure by converting map into Array.

and i have created one One Stored Procedure for accepting this map as input parameter.

PROCEDURE S_MAP_PARAMETERS(   pVALUES IN T_INTERFACE_VALUES   ) AS
 ???

END S_MAP_PARAMETERS;

so i need to read pVALUES in SQL how do i do that?

Thanks

Upvotes: 0

Views: 2555

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

In SQL

SELECT ivkey, ivvalue
  FROM TABLE( pValues );

would work. If you really mean to ask how to access the collection in PL/SQL, assuming the collection is dense.

FOR i IN 1..pValues.count
LOOP
  dbms_output.put_line( 'Key = ' || pValues(i).ivkey ||
                        ' Value = ' || pValues(i).ivvalue );
END LOOP;

If you expect some elements of the collection to be missing (which seems odd given your choice of PL/SQL collection), the loop gets a bit more complicated since you've got to call FIRST and NEXT to iterate through the elements.

If you want to create a PL/SQL construct that is more similar to your Map in Java, you'd want something like

CREATE TYPE map_t
  AS TABLE OF VARCHAR2(250)
      INDEX BY VARCHAR2(32)

Upvotes: 1

Jafar Kofahi
Jafar Kofahi

Reputation: 763

Nested table types in Oracle are not key-value collections. So you need to loop through the table pValues then for each object you can access the attributes IVVALUE and IVKEY, you can do that in multiple ways:

1- via a cursor using the TABLE function

CURSOR iterate as
SELECT *
  FROM TABLE(pValues)

Then in your code

For Rec in iterate
loop
    --for example
    DBMS_OUTPUT.PUT_LINE(rec.IVVALUE)
end loop;

2- looping one item at a time, using the nested tables count function:

For indx in 1..pValues.COUNT
Loop
 --access it using indx pvalues(indx)
 --for example
 DBMS_OUTPUT.PUT_LINE(pvalues(indx).IVVALUE)

end loop

Upvotes: 1

Related Questions