Home » SQL & PL/SQL » SQL & PL/SQL » Passing UDT as a SP parameter (11.0.2.10)
Passing UDT as a SP parameter [message #666111] Mon, 16 October 2017 05:16 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi All,

In my following example I use a cursor to get data from an SQL statement and want to pass the cursor to fetch the cursor into a defined table in order to pass it to another SP which takes the table type as an input for insert.
The fetch statement fails due to: ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
Can you please help with what I am doing wrong?

My example:


create table test_MTable 
  (
   Mtype_id number(10) ,
   MType_Name VARCHAR2(50)
);

create or replace TYPE My_type AS OBJECT
(
   Mtype_id number (10),
   MType_Name VARCHAR2 (50)
);

create or replace TYPE table_Mtype AS TABLE OF My_type;





create or replace PROCEDURE test_Insert_MType (
                            MType_items table_Mtype,
                            confirmation_num OUT VARCHAR2)
   IS
  confirmation_num1 varchar2(50);
begin

     for i in 1..MType_items.count loop
      INSERT INTO test_MTable (Mtype_id, Mtype_Name) 
      SELECT MType_items(i).Mtype_id, MType_items(i).Mtype_Name
        FROM DUAL ;    
     end loop;

  confirmation_num1:= '1';
 end;


create or replace PROCEDURE test_call_ins_MType (confirmation_num OUT VARCHAR2)
   IS
  confirmation_num1 varchar2(50);
  rec_MyType SYS_REFCURSOR;
  tab_MyType table_Mtype;
begin
  open rec_MyType for
    select number0, name from employee;
 
  fetch rec_MyType bulk collect into tab_MyType; -----<----- Fails due to Return types of Result 
                                                             --Set variables or query do not match

  test_Insert_MType(tab_MyType,confirmation_num1);
 
  confirmation_num1:= '1';
 end;

Thanks,
Ferro

[Updated on: Mon, 16 October 2017 05:18]

Report message to a moderator

Re: Passing UDT as a SP parameter [message #666113 is a reply to message #666111] Mon, 16 October 2017 06:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Meditate over:

  open rec_MyType for
    select number0, name from employee;

versus:

  open rec_MyType for
    select My_type(number0,name) from employee;

And naming cursor rec_MyType is misleading.

SY.
Re: Passing UDT as a SP parameter [message #666128 is a reply to message #666113] Mon, 16 October 2017 23:30 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
@Solomon Yakobson

Thanks a lot for your feedback, right your are, in fact it was a typo (rec instead of cur).

But what I am really trying to understand is the reason why we needed the casting. In my previous post http://www.orafaq.com/forum/m/662749/#msg_662749 the call to SP
P_get_src_test(rc);
was done returning a sys_refcursor which was directly used in a similar fetch statement
fetch rc bulk collect into res;

Thanks,
Ferro
Re: Passing UDT as a SP parameter [message #666131 is a reply to message #666128] Tue, 17 October 2017 03:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Because in this thread you are using a SQL table of objects and in the other a PL/SQL table of records.
Re: Passing UDT as a SP parameter [message #666132 is a reply to message #666131] Tue, 17 October 2017 03:26 Go to previous message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
@Cookiemonster
Thanks a lot
Previous Topic: How to extract out huge result set in xml format as .xml file
Next Topic: Restrictions on DDL-triggers in PL/SQL
Goto Forum:
  


Current Time: Thu Mar 28 09:56:24 CDT 2024