Home » SQL & PL/SQL » SQL & PL/SQL » Convert Object return type columns (Oracle 10g)
Convert Object return type columns [message #448869] Thu, 25 March 2010 08:13 Go to next message
vikasarorait
Messages: 9
Registered: August 2009
Junior Member
Hi,

I have created a object type as

create type emp_obj_dtl as OBJECT (ename varchar2(50),mgr NUMBER)


create type emp_dtl_obj_typ as TABLE of emp_obj_dtl


Using the these object i have created on function as

CREATE OR REPLACE FUNCTION emp_test_func (peno NUMBER)
   RETURN emp_dtl_obj_typ
AS
   lv_emp_dtl   emp_dtl_obj_typ := emp_dtl_obj_typ ();
BEGIN
   SELECT emp_dtl_obj_typ(emp_obj_dtl (ename, mgr))
     INTO lv_emp_dtl
     FROM emp
    WHERE empno = peno;

   RETURN lv_emp_dtl;
END;

Now if i am executig query as

SELECT empno, emp_test_func (empno) emp_dtls
  FROM emp 

It is returning me the data as

EMPNO |  EMP_DTLS 
7500  | (DATASET)
7382  | (DATASET)
7569  | (DATASET)
7800  | (DATASET)

But I want the result set as

EMPNO | ENAME | MGR 
7500  | SMITH | 7863
7382  | JAMES | 7896
7569  | KING  | 7856
7800  | SANGR | 7456


Appricate your help to get the resultset as above.

thank you & Rgrds
VIKAS ARORA
Re: Convert Object return type columns [message #448871 is a reply to message #448869] Thu, 25 March 2010 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE OR REPLACE FUNCTION emp_test_func (peno NUMBER) RETURN emp_dtl_obj_typ pipelined 
  2  AS
  3  begin
  4    for rec in (select ename, mgr from emp where empno=peno)
  5    loop
  6      pipe row (emp_obj_dtl (rec.ename, rec.mgr));
  7    end loop;
  8  end;
  9  /

Function created.

SQL> SELECT empno, x.ename, x.mgr from emp, table(emp_test_func (emp.empno)) x
  2  /
     EMPNO ENAME             MGR
---------- ---------- ----------
      7369 SMITH            7902
      7499 ALLEN            7698
      7521 WARD             7698
      7566 JONES            7839
      7654 MARTIN           7698
      7698 BLAKE            7839
      7782 CLARK            7839
      7788 SCOTT            7566
      7839 KING
      7844 TURNER           7698
      7876 ADAMS            7788
      7900 JAMES            7698
      7902 FORD             7566
      7934 MILLER           7782


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Thu, 25 March 2010 08:34]

Report message to a moderator

Re: Convert Object return type columns [message #448875 is a reply to message #448871] Thu, 25 March 2010 08:30 Go to previous messageGo to next message
vikasarorait
Messages: 9
Registered: August 2009
Junior Member
Hi Michel,

Thanks a lot for your great help.

Thanks & Rgrds,
Vikas Arora
Re: Convert Object return type columns [message #448926 is a reply to message #448869] Thu, 25 March 2010 17:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
There are many sites on the internet that can teach you more about the syntaxes of working with oracle objects. Specifically look for the following terms:

table(cast(multiset

Kevin
Re: Convert Object return type columns [message #448959 is a reply to message #448926] Fri, 26 March 2010 00:29 Go to previous message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or simply in Oracle documentation:
Database SQL Reference
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals
Application Developer's Guide - Object-Relational Features

Regards
Michel

[Updated on: Fri, 26 March 2010 00:29]

Report message to a moderator

Previous Topic: NONCLUSTERD Index Creation
Next Topic: Before and After trigger issue
Goto Forum:
  


Current Time: Fri Sep 30 15:51:08 CDT 2016

Total time taken to generate the page: 0.45822 seconds