Home » Developer & Programmer » JDeveloper, Java & XML » Need help (Oracle DB 10g Release 10.2.0.3.0 - Prod, Linux)
Need help [message #440520] Mon, 25 January 2010 06:13 Go to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Hi Friends,

Having a scenario like below.

SELECT 1, (SELECT '100'
FROM DUAL
UNION
SELECT '101'
FROM DUAL
UNION
SELECT '103'
FROM DUAL)
FROM DUAL
UNION ALL
SELECT 2, (SELECT '200'
FROM DUAL
UNION
SELECT '201'
FROM DUAL
UNION
SELECT '203'
FROM DUAL)
FROM DUAL;

For a single row, am having muliple records.
How do I return this from Oracle to Java.
Can it be done thr' ref cursor/collections.

Please suggests me some good approach with example
so that my java application displays the query resultset.

Regards,
Marlon.
Re: Need help [message #440523 is a reply to message #440520] Mon, 25 January 2010 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT 1, (SELECT '100'
  2  FROM DUAL
  3  UNION
  4  SELECT '101'
  5  FROM DUAL
  6  UNION
  7  SELECT '103'
  8  FROM DUAL)
  9  FROM DUAL
 10  UNION ALL
 11  SELECT 2, (SELECT '200'
 12  FROM DUAL
 13  UNION
 14  SELECT '201'
 15  FROM DUAL
 16  UNION
 17  SELECT '203'
 18  FROM DUAL)
 19  FROM DUAL;
SELECT 1, (SELECT '100'
           *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

SQL> SELECT 1, b
  2  from (SELECT '100' b
  3  FROM DUAL
  4  UNION
  5  SELECT '101'
  6  FROM DUAL
  7  UNION
  8  SELECT '103'
  9  FROM DUAL),
 10  DUAL
 11  UNION ALL
 12  SELECT 2, b
 13  from (SELECT '200' b
 14  FROM DUAL
 15  UNION
 16  SELECT '201'
 17  FROM DUAL
 18  UNION
 19  SELECT '203'
 20  FROM DUAL ),
 21  DUAL
 22  /
         1 B
---------- ---
         1 100
         1 101
         1 103
         2 200
         2 201
         2 203

6 rows selected.

Regards
Michel
Re: Need help [message #440535 is a reply to message #440523] Mon, 25 January 2010 07:38 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Thanks Friend.

I need to return a query like below,

SELECT d.deptno, CAST(MULTISET(SELECT e.empno a, e.ename b,e.job c FROM emp e WHERE e.deptno = d.deptno) as t_details_tab) details
FROM dept d

How do I return from Oracle to Java and how my java application going to retrive this resultset.

Regards,
Marlon
Re: Need help [message #440536 is a reply to message #440535] Mon, 25 January 2010 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is not a SQL question but a Java one.
I move the topic to the appropriate forum.

Regards
Michel
Re: Need help [message #440537 is a reply to message #440535] Mon, 25 January 2010 07:57 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The basics of that are explained in the FAQ and the tutorials linked in it.
Re: Need help [message #440538 is a reply to message #440535] Mon, 25 January 2010 07:57 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
You have so many friends at Java forum and they will answer if You post there And they are waiting too..

see this...Executing a query from Java


sriram Smile
Re: Need help [message #440542 is a reply to message #440535] Mon, 25 January 2010 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you can have access to Tom Kyte previous book ("Expert one-on-one"), in chapter 19 he showed how to pass any kind of parameter or array of parameters in his "demo_passing_pkg" package.

Regards
Michel

Re: Need help [message #440543 is a reply to message #440535] Mon, 25 January 2010 08:39 Go to previous message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at Can I Pass a nested table to Java from a pl/sql procedure

Regards
Michel
Previous Topic: xmltype conversion
Next Topic: Push Button Prompt Changes
Goto Forum:
  


Current Time: Wed Sep 28 16:02:58 CDT 2016

Total time taken to generate the page: 0.17902 seconds