CODEWITHSUNDEEP
sqloracle-databaseplsql
Oleg
Oleg

Reputation: 615

Passing table parameter using select clause in Oracle

I have 2 types:

   CREATE OR REPLACE TYPE id_type AS OBJECT
   (
     id NUMBER(19, 0)
   );

   CREATE OR REPLACE TYPE id_table AS TABLE OF id_type;

And I have a function A(param IN id_table).

Now, if I have another function using first one, how am I supposed to pass the param like that A(SELECT 1 FROM DUAL)? Can I only do it manually creating an id_table var, filling it and then passing to A() function?

Upvotes: 1

Views: 1293

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

You can do something like

DECLARE
  l_ids     id_table;
  l_return  <<data type>>;
BEGIN
  SELECT id_type( 1 )
    BULK COLLECT INTO l_ids
    FROM dual;
  l_return := a( l_ids );
END;

It's not obvious, though, why you have an id_type in this case. It would seem more logical to simply declare id_table 

CREATE OR REPLACE TYPE id_table
    AS TABLE OF NUMBER(19,0);

It's also not obvious whether you are really intending to populate the collection by selecting from dual. If you really want to have a single-element collection, you can simply initialize it. I'm guessing, however, that you really intend to populate the collection by querying a table other than DUAL in which case you'd prefer the BULK COLLECT

DECLARE
  l_ids     id_table := new id_table( id_type( 1 ) );
  l_return  <<data type>>;
BEGIN
  l_return := a( l_ids );
END;

Upvotes: 1

Related Questions