Home » SQL & PL/SQL » SQL & PL/SQL » Recover more than one row in a function
Recover more than one row in a function [message #190316] Wed, 30 August 2006 03:24 Go to next message
Oriol_e9g
Messages: 9
Registered: July 2006
Junior Member
I have learned to recover a row in a function, with a code like this.

 
SQL> create type obj_1 is object (name varchar2(10), id number);
  2  /


SQL> create type tab_of_obj_1 is table of obj_1;
  2  /


SQL> create or replace function get_obj
  2  return tab_of_obj_1
  3  is
  4   t1 tab_of_obj_1 := tab_of_obj_1(obj_1('Example',1));
  5  begin
  6   return t1;
  7  end;
  8  /


SQL> select * from table(get_obj);

NAME               ID
---------- ----------
Example             1


All correct, but now, I would like to recover a multiple-rows, and I have done this test:

CREATE TABLE TEST_TABLE (
name VARCHAR2(10 BYTE), id number
);
/


INSERT  INTO TEST_TABLE (name, id) VALUES (‘one’,1);
INSERT  INTO TEST_TABLE (name, id) VALUES (‘two’,2);
INSERT  INTO TEST_TABLE (name, id) VALUES (‘two’,3);
COMMIT;
/


CREATE OR REPLACE FUNCTION Get_Obj(in_var TEST_TABLE.name%TYPE) RETURN tab_of_obj_1
  IS
   t1 tab_of_obj_1;
   data TEST_TABLE%ROWTYPE;
  BEGIN
   SELECT * INTO data FROM TEST_TABLE WHERE name=in_var;
   t1:=TAB_OF_OBJ_1(OBJ_1(data.name,data.id));
   RETURN t1;
  END;
   /


then, With this function, for example, if I do:

select * from TABLE (get_Obj ('one')) 
or 
select * from TABLE (cast(get_Obj ('one') as tab_of_obj_1))

NAME               ID
---------- ----------
one             1


ok… but, if I do:
select * from TABLE (get_Obj ('two')) 
or 
select * from TABLE (cast(get_Obj ('two') as tab_of_obj_1))

ORA-01422: exact fetch returns more than requested number of rows


There is some way to do a function that can recover a table of several rows? Whit a output like this:

NAME ID
---------- ----------
two 2
two 3
Re: Recover more than one row in a function [message #190326 is a reply to message #190316] Wed, 30 August 2006 04:10 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

I think You can get some inspiration here:

http://asktom.oracle.com/pls/ask/f?p=4950:8:11242811429428828885::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:210612357425

Br
Kim
Previous Topic: adding time to dates
Next Topic: What is the best way to do this???
Goto Forum:
  


Current Time: Mon Dec 05 13:11:51 CST 2016

Total time taken to generate the page: 0.13090 seconds