• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8904
  • Last Modified:

oracle function to return rowtype value

I have created a function to return a rowtype value.


CREATE OR REPLACE function GET_ITEMDETAILS( ITEMID IN NUMBER)
RETURN itemDetailview%ROWTYPE
IS
rs itemDetailview%ROWTYPE;
BEGIN
select * into rs from itemDetailview where item_id =ITEMID ;
return rs;
end GET_ITEMDETAILS;

On execution i get an error PL/SQL: ORA-00913: too many values.

DECLARE
       item itemDetailview%ROWTYPE;
    BEGIN
       SELECT GET_ITEMDETAILS(6) INTO item FROM dual;
 
       DBMS_OUTPUT.PUT_LINE(item.item_name);
 
   END;


thanks for help in advance.
When i execute it

0
alphamn
Asked:
alphamn
  • 3
1 Solution
 
MikeOM_DBACommented:

You need to use "REF CURSOR" to return a set of rows.
0
 
MikeOM_DBACommented:

-- OR -- create and populate a 'VARRAY' type.
0
 
MikeOM_DBACommented:

-- OR -- Table of RECORD type.
0
 
slightwv (䄆 Netminder) Commented:
I agree with Mike if the error wasn't an ORA-913 and was:
ORA-01422: exact fetch returns more than requested number of rows

As long as the select in the function will ONLY ever return 1 row, you can do it.  Just don't use a 'select ... into ...' in the call.
---------------------------------------
drop table tab1;
create table tab1 ( item_id number, item_name number );

insert into tab1 values(1,2);
commit;


CREATE OR REPLACE function GET_ITEMDETAILS( ITEMID IN NUMBER)
RETURN tab1%ROWTYPE
IS
rs tab1%ROWTYPE;
BEGIN
select * into rs from tab1 where item_id =ITEMID ;
return rs;
end GET_ITEMDETAILS;
/

show errors

DECLARE
       item tab1%ROWTYPE;
BEGIN
       item := GET_ITEMDETAILS(1);
 
       DBMS_OUTPUT.PUT_LINE(item.item_name);
END;
/

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now