Passing UDT as a SP parameter [message #666111] |
Mon, 16 October 2017 05:16 |
|
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 |
Solomon Yakobson
Messages: 3275 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.
|
|
|
|
|
|