CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle11goracle-sqldeveloper
Mando
Mando

Reputation: 11712

How to declare and pass user typed table in oracle sql developer

I have defined my table user type:

create or replace TYPE My_Type AS OBJECT 
( 
  Field1 NVARCHAR2(20),
  Field2 NVARCHAR2(20)
);

create or replace TYPE My_Type_Table
AS TABLE OF My_Type;

Then I defined stored procedure:

PROCEDURE sp_GetSomething
(
  table_in My_Type_Table,
  cur_our SYS_REFCURSOR 
);

I was able to successfully call this procedure from the code but not from Sql Develoder. I'm not sure how to define and pass table type input parameter. I'm trying to use something like this, but it doesn't work:

DECLARE
  table_in My_Type_Table;
  cur_our SYS_REFCURSOR;
BEGIN
   table_in := My_Type_Table(My_Type('field 1 value', 'field 2 value'));

  package.sp_GetSomething(
    table_in => table_in ,
    cur_our => cur_our 
  );
  :cur_our := cur_our ;
END;

And the error is:

> ORA-06550: line 11, column 20: 
> PLS-00382: expression is of wrong type

Please advise.

Upvotes: 0

Views: 1231

Answers (1)

MihaiC
MihaiC

Reputation: 1583

Try to define input table as:

SELECT My_Type(Field1,Field2)
  BULK COLLECT INTO table_in
  FROM (SELECT 'Field1 Value' AS Field1, 'Field2 Value' AS Field2 FROM DUAL);

Upvotes: 1

Related Questions