Home » SQL & PL/SQL » SQL & PL/SQL » Need to retrieve object elements (Oracle DB 10g Release 10.2.0.3.0 - Prod, Linux)
Need to retrieve object elements [message #441775] Wed, 03 February 2010 01:30 Go to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Hi Friends,

Am having a scenario like below.

Created the below types.

CREATE OR REPLACE TYPE scott.o_emp_type AS OBJECT (
   empno   NUMBER,
   ename   VARCHAR2 (10),
   job     VARCHAR2 (9)
);

CREATE OR REPLACE TYPE scott.t_emp_type AS TABLE OF o_emp_type;


CREATE OR REPLACE TYPE scott.o_dept_type AS OBJECT (
   deptno        NUMBER,
   dname         VARCHAR2 (14),
   loc           VARCHAR2 (13),
   tt_emp_type   t_emp_type
);

CREATE OR REPLACE TYPE scott.t_dept_type AS TABLE OF o_dept_type;


Having a block like,

DECLARE
   c              sys_refcursor;
   tt_dept_type   t_dept_type;
BEGIN
   OPEN c FOR
      SELECT CAST
                (MULTISET
                    (SELECT d.deptno deptno, d.dname dname, d.loc loc,
                            CAST
                               (MULTISET (SELECT e.empno a, e.ename b,
                                                 e.job c
                                            FROM emp e
                                           WHERE e.deptno = d.deptno) AS t_emp_type
                               ) details
                       FROM dept d
                    ) AS t_dept_type
                ) dept_details
        FROM DUAL;

   FETCH c
    INTO tt_dept_type;

   FOR i IN 1 .. tt_dept_type.COUNT
   LOOP
   NULL;
   END LOOP;
END;


How do I retrieve all the elements from the ref cursor.
Or do I have to follow some other approach for doing this.

Please advice.

Regards,
Marlon.
Re: Need to retrieve object elements [message #441779 is a reply to message #441775] Wed, 03 February 2010 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do I retrieve all the elements from the ref cursor.

What do you mean by "retrieve"? display? pass to the caller?...

Please post your questions in "SQL & PL/SQL" forum not in "Suggestions & Feedback" which is for YOU to post suggestion or feedback on OraFAQ.

Regards
Michel
Re: Need to retrieve object elements [message #441784 is a reply to message #441779] Wed, 03 February 2010 01:49 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
I have to pass this as ref cursor to java application.

But for testing purpost how do I loop through that ref cursor and display it.

Regards,
Marlon.
Re: Need to retrieve object elements [message #441788 is a reply to message #441784] Wed, 03 February 2010 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you want a solution for the final issue or for test?
For test "just" use dbms_output package to display the records.
For final solution, do not declare a ref cursor inside the block, replace it by a bind variable (that is "?" in Java) and do not fetch, just open.

Regards
Michel
Re: Need to retrieve object elements [message #441797 is a reply to message #441775] Wed, 03 February 2010 02:47 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Sorry Michel.

For test,I dont know how to display since an object type containts another object type inside it. Please can anyone explain me with an example.

For final issue, I have a procedure with OUT parameter as sys_refcursor so that my java application can access that.

One more doubt I dont know how java programs going to handle this object type for displaying the records on the web page.

Please advice.

Regards,
Marlon.
Re: Need to retrieve object elements [message #441803 is a reply to message #441797] Wed, 03 February 2010 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I dont know how to display since an object type containts another object type inside it.

You cannot, you have to display each field (concatenated or on multiple lines as you want, but each one by one).

Quote:
One more doubt I dont know how java programs going to handle this object type for displaying the records on the web page.

I don't know either but it is described in the documentation.

Regards
Michel
Re: Need to retrieve object elements [message #441817 is a reply to message #441775] Wed, 03 February 2010 04:34 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
I think marlon_loyolite is trying/want to get the Nested table data in one row along with main table and and pass it to Java application.


CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
/

CREATE TABLE department (
    name     VARCHAR2(20),
    director VARCHAR2(20),
    office   VARCHAR2(20),
    courses  CourseList)
    NESTED TABLE courses STORE AS courses_tab;

INSERT INTO department
    (name, director, office, courses)
    VALUES
    ('English', 'Lynn Saunders', 'Breakstone Hall 205', CourseList(
    'Expository Writing',
    'Film and Literature',
    'Modern Science Fiction',
    'Discursive Writing',
    'Modern English Grammar',
   'Introduction to Shakespeare',
   'Modern Drama',
   'The Short Story',
   'The American Novel'));

commit;

select NAME,DIRECTOR,OFFICE,COLUMN_VALUE FROM department t1, table(t1.courses ) t2;

NAME                 DIRECTOR             OFFICE               COLUMN_VALUE                  
-------------------- -------------------- -------------------- ------------------------------
English              Lynn Saunders        Breakstone Hall 205  Expository Writing            
English              Lynn Saunders        Breakstone Hall 205  Film and Literature           
English              Lynn Saunders        Breakstone Hall 205  Modern Science Fiction        
English              Lynn Saunders        Breakstone Hall 205  Discursive Writing            
English              Lynn Saunders        Breakstone Hall 205  Modern English Grammar        
English              Lynn Saunders        Breakstone Hall 205  Introduction to Shakespeare   
English              Lynn Saunders        Breakstone Hall 205  Modern Drama                  
English              Lynn Saunders        Breakstone Hall 205  The Short Story               
English              Lynn Saunders        Breakstone Hall 205  The American Novel            



is this you want marlon_loyolite ?

Because if you just select From departmnet then you get the data which can not be well readable in java

 SELECT * FROM department;

NAME                 DIRECTOR             OFFICE
-------------------- -------------------- --------------------
COURSES
----------------------------------------------------------------------------------------------------
English              Lynn Saunders        Breakstone Hall 205
COURSELIST('Expository Writing', 'Film and Literature', 'Modern Science Fiction', 'Discursive Writin
g', 'Modern English Grammar', 'Introduction to Shakespeare', 'Modern Drama', 'The Short Story', 'The
 American Novel')










[Updated on: Wed, 03 February 2010 04:37]

Report message to a moderator

Re: Need to retrieve object elements [message #441819 is a reply to message #441817] Wed, 03 February 2010 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Because if you just select From departmnet then you get the data which can not be well readable in java

Are you sure of this?
It is well read by SQL*Plus why can't it be well read by a Java program?

Regards
Michel

[Updated on: Wed, 03 February 2010 04:44]

Report message to a moderator

Re: Need to retrieve object elements [message #441911 is a reply to message #441819] Wed, 03 February 2010 20:46 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Michel Cadot wrote on Wed, 03 February 2010 04:41
Quote:
Because if you just select From departmnet then you get the data which can not be well readable in java

Are you sure of this?

It is well read by SQL*Plus why can't it be well read by a Java program?

Regards
Michel


Yes,In java they get the record as record-set ( which is Array), so if you return following record-set to Java it will be difficult to extract COURSELIST from record-set ( may be one inner for loop for each row ).
 SELECT * FROM department;

NAME                 DIRECTOR             OFFICE
-------------------- -------------------- --------------------
COURSES
----------------------------------------------------------------------------------------------------
English              Lynn Saunders        Breakstone Hall 205
COURSELIST('Expository Writing', 'Film and Literature', 'Modern Science Fiction', 'Discursive Writin
g', 'Modern English Grammar', 'Introduction to Shakespeare', 'Modern Drama', 'The Short Story', 'The
 American Novel'


if you return following way it will be normal record-set and it will be easy to manage or traverse.

select NAME,DIRECTOR,OFFICE,COLUMN_VALUE FROM department t1, table(t1.courses ) t2;

NAME                 DIRECTOR             OFFICE               COLUMN_VALUE                  
-------------------- -------------------- -------------------- ------------------------------
English              Lynn Saunders        Breakstone Hall 205  Expository Writing            
English              Lynn Saunders        Breakstone Hall 205  Film and Literature           
English              Lynn Saunders        Breakstone Hall 205  Modern Science Fiction        
English              Lynn Saunders        Breakstone Hall 205  Discursive Writing            
English              Lynn Saunders        Breakstone Hall 205  Modern English Grammar        
English              Lynn Saunders        Breakstone Hall 205  Introduction to Shakespeare   
English              Lynn Saunders        Breakstone Hall 205  Modern Drama                  
English              Lynn Saunders        Breakstone Hall 205  The Short Story               
English              Lynn Saunders        Breakstone Hall 205  The American Novel            



Re: Need to retrieve object elements [message #441996 is a reply to message #441775] Thu, 04 February 2010 05:37 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Thank you friends.

Tell me one thing, I have a basic doubt/reason behind why we go for nested table inside a table instead of creating a new table by referring the department table.

What's the real advantage of having a nested table inside a table rather than creating another table.

Please advice me.

Regards,
Marlon.
Re: Need to retrieve object elements [message #441997 is a reply to message #441996] Thu, 04 February 2010 05:40 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What's the real advantage of having a nested table inside a table rather than creating another table.

In my opinion, none. This has only drawbacks and no advantage.
But some like to have some object-like feature.

Regards
Michel
Previous Topic: using self join to get such order,why?
Next Topic: SQL Query and OR Operation
Goto Forum:
  


Current Time: Mon Dec 05 02:53:52 CST 2016

Total time taken to generate the page: 0.08461 seconds