Home » SQL & PL/SQL » SQL & PL/SQL » Single select query or multiple cursor output as collection
Single select query or multiple cursor output as collection [message #619073] Thu, 17 July 2014 14:57 Go to next message
ORAGENASHOK
Messages: 215
Registered: June 2006
Location: Chennai
Senior Member
I want to populate records from all the tables ( 1 to many - to typ_info type) at the same time i want to give
the same type as output from procedure to jave code.

I could not copy the original tables and type.


create table t1 
( c1 number,
  c2 varchar2(100),
  c3 char
);

create table t2
(
  c4 number,
  c5 varchar2(10),
  c6 char
);

create table t3
(
  c7 number,
  c8 varchar2(200),
  c9 char
)

create table t4
(
  c10 number,
  c11 varchar2(100),
  c11 date
)


create type typ_t2 as object
(
  c2 varchar2(100),
  c3 char
);

create type typ_t2_det as table of typ_t2;

create type typ_t4 as object
(
  c11 varchar2(100),
  c11 date
)

create type typ4_t4_det as table of typ_t4

create type typ_info as object
(
  t_typ1 typ_t2_det,
  c7 number,
  c8 varchar2(200),
  c9 char,
  t_typ2 typ_t4_det
)

create type typ_info_det as table of typ_info



[LF MOVED topic from Designer to SQL & PL/SQL forum; this doesn't seem to be a Designer question. If it is, we'll move it back]

[Updated on: Fri, 18 July 2014 02:24] by Moderator

Report message to a moderator

Re: Single select query or multiple cursor output as collection [message #619094 is a reply to message #619073] Fri, 18 July 2014 00:50 Go to previous messageGo to next message
ORAGENASHOK
Messages: 215
Registered: June 2006
Location: Chennai
Senior Member
Any response ...
Re: Single select query or multiple cursor output as collection [message #619112 is a reply to message #619094] Fri, 18 July 2014 02:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Requirement is not completely clear. Insert statements are missing.

If you want to have all the records from all the tables into a common collection, then it is not possible the way you stated above since the table structure as well as their data types are different, else UNION ALL would have worked for you. In other words, using MULTISET operators you can combine the collections, however, they MUST be of similar TYPE. In your case they are of different types.
Re: Single select query or multiple cursor output as collection [message #619118 is a reply to message #619073] Fri, 18 July 2014 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59978
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is a very bad idea to go to this way.
Use the standard relational rows and properly handle them in your Java program.
This work should not be done in the database.

In addition, you MUST return a REF CURSOR to your application and NOT collections (and even less collections of collections...).

Re: Single select query or multiple cursor output as collection [message #619150 is a reply to message #619118] Fri, 18 July 2014 07:40 Go to previous message
ORAGENASHOK
Messages: 215
Registered: June 2006
Location: Chennai
Senior Member
Thanks Michel and kumar,

I tried in this way unlucky and have decided and drafted using refcursor.
Previous Topic: Using Case statement after "AND"
Next Topic: Searching a string and replacing with value
Goto Forum:
  


Current Time: Thu Dec 18 07:49:16 CST 2014

Total time taken to generate the page: 0.10608 seconds