Home » SQL & PL/SQL » SQL & PL/SQL » union all in pro *C (oracle)
icon14.gif  union all in pro *C [message #281834] Mon, 19 November 2007 08:07 Go to next message
boopathi.it
Messages: 9
Registered: November 2007
Location: chennai
Junior Member

I am trying to use the UNION ALL in a CURSOR declaration. It compiles fine and
when we run the program it fetches only the rows corresponding to the first
query in the cursor statement. We do not get any rows corresponding to the
second query. But we know that second query can fetch some rows if executed in the ORACLE environment and we could not find any
specific ways of using union all in the cursors declarations.

Any help on this greatly appreciated.


[Updated on: Mon, 19 November 2007 22:39]

Report message to a moderator

Re: union all in pro *C [message #281837 is a reply to message #281834] Mon, 19 November 2007 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you don't post any information, how can you expect we can help you?

Regards
Michel
Re: union all in pro *C [message #281915 is a reply to message #281837] Tue, 20 November 2007 00:19 Go to previous messageGo to next message
boopathi.it
Messages: 9
Registered: November 2007
Location: chennai
Junior Member

I am trying to use the UNION ALL(in select query) in a CURSOR declaration.(Pro*C environment) It compiles fine and
when we run the program it fetches only the rows corresponding to the first
query in the cursor statement. We do not get any rows corresponding to the
second query. But we know that second query can fetch some rows if executed in the ORACLE environment and we could not find any
specific ways of using union all in the cursors declarations.

Any help on this greatly appreciated.
Re: union all in pro *C [message #281916 is a reply to message #281915] Tue, 20 November 2007 00:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You didn't provide any additional information; your second post is almost the same as the first one.

Saying that
Quote:

we know that second query can fetch some rows

is OK, but - does it really return some records? Did you test SELECT statement (two SELECTs separately to make sure both of them return some data, and then UNION ALL of those selects, checking that returned data set is identical to previously fetched data)?
Re: union all in pro *C [message #281917 is a reply to message #281915] Tue, 20 November 2007 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same answer as Daniel Morgan gave you on usenet comp.databases.oracle:
Quote:

No Oracle version number
No DDL
No DML

How would you like us to help you with the problem: Guess?


Regards
Michel
Re: union all in pro *C [message #281926 is a reply to message #281917] Tue, 20 November 2007 01:01 Go to previous messageGo to next message
boopathi.it
Messages: 9
Registered: November 2007
Location: chennai
Junior Member

oracle version:9.2.0.8.0


I am trying to combine UNION ALL for 150 times in the pro*c for a cursor. (DML)

But it compile fine and when i am try to run the program it execute the first query only. while i am executing in manual manner for each query it gives some fine rows.

There is any limitation to use the UNION ALL in Pro*C cursor.


Advance thanks for best reply.



Re: union all in pro *C [message #281928 is a reply to message #281926] Tue, 20 November 2007 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

No Oracle version number
No DDL
No DML
No code

How would you like us to help you with the problem: Guess?

Regards
Michel
Re: union all in pro *C [message #281933 is a reply to message #281928] Tue, 20 November 2007 01:28 Go to previous messageGo to next message
boopathi.it
Messages: 9
Registered: November 2007
Location: chennai
Junior Member

code is like this...

select name,age,amount from tabel1
union all
select name,age,amount from tabel2
union all
select name,age,amount from tabel3
union all
select name,age,amount from tabel4
Re: union all in pro *C [message #281936 is a reply to message #281933] Tue, 20 November 2007 01:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What answer are you after?
What answer do you think we will be able to offer, other than 'No, there is no limit to the number of union alls in Pro*C' or 'Yes, there is a limit'?
I don't know of any limit; would find it very strange if there was a limit for Pro*C queries that does not hold up for straight sql.
With the info you provided, there is no way we can answer in any more detail.
Re: union all in pro *C [message #282041 is a reply to message #281834] Tue, 20 November 2007 09:29 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
My crystal ball says that you are using a parameter in the where clause of the query and that PRO*C is not populating the paramater(s) the way you think it is.
Previous Topic: OPtions
Next Topic: Extract from unconventional XML.
Goto Forum:
  


Current Time: Fri Dec 09 23:30:32 CST 2016

Total time taken to generate the page: 0.09205 seconds