Home » SQL & PL/SQL » SQL & PL/SQL » An interesting problem: same query doesn't running on different database. (Database 10 g, Database 9i)
icon1.gif  An interesting problem: same query doesn't running on different database. [message #429057] Sun, 01 November 2009 23:09 Go to next message
omaribais
Messages: 34
Registered: October 2009
Location: Bangladesh
Member

Hi all,

I am a developer, In my pc i use oracle 10g db and in our client side they use 9i db.

Recently i create a view for finding last present day of worker. our attendance table have more than 250000 data, and leave table contain 2000 data.

now I make a query on those tables,


SELECT pi.emp_no,pi.attn,pi.sal_period
FROM (SELECT va.emp_no, MAX (va.attn_dt) attn,TO_CHAR (va.attn_dt, 'YYYYMM') sal_period
FROM attn_tbl va
GROUP BY va.emp_no,TO_CHAR (va.attn_dt, 'YYYYMM')) pi
WHERE NOT EXISTS (SELECT 1
FROM v_leave_dtl
WHERE emp_no = pi.emp_no

and TO_CHAR (day, 'YYYYMM')=pi.sal_period
GROUP BY emp_no,TO_CHAR (day, 'YYYYMM')
HAVING MAX (DAY) > pi.attn)



The above query take 1.2 secs on my laptop. But when I execute it on oracle 9i database it return " ORA-03113 end-of-file on communication channel " and close the session.

Please help me , it is urgent.

Thanks & Regards
Omar
Re: An interesting problem: same query doesn't running on different database. [message #429058 is a reply to message #429057] Sun, 01 November 2009 23:21 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>The above query take 1.2 secs on my laptop. But when I execute it on oracle 9i database it return " ORA-03113 end-of-file on communication channel "

The bug in V9 is fixed in V10.
The solution is to upgrade the V9 to V10.
Re: An interesting problem: same query doesn't running on different database. [message #429060 is a reply to message #429058] Sun, 01 November 2009 23:24 Go to previous messageGo to next message
omaribais
Messages: 34
Registered: October 2009
Location: Bangladesh
Member

Dear BlackSwan,

Thanks for your quick reply. Can you give me suggestion about alternate way of this query.

Thanks again
- Omar
Re: An interesting problem: same query doesn't running on different database. [message #429062 is a reply to message #429060] Sun, 01 November 2009 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00600/ORA-07445/ORA-03113 are generic errors for Oracle bug => search on Metalink and/or call Oracle support

Regards
Michel

[Updated on: Sun, 01 November 2009 23:27]

Report message to a moderator

Re: An interesting problem: same query doesn't running on different database. [message #429066 is a reply to message #429062] Sun, 01 November 2009 23:30 Go to previous messageGo to next message
omaribais
Messages: 34
Registered: October 2009
Location: Bangladesh
Member

Thanks Michel, But I need an alternate way to do my query. Have u any suggestion?

-Omar
Re: An interesting problem: same query doesn't running on different database. [message #429068 is a reply to message #429066] Sun, 01 November 2009 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>I need an alternate way to do my query. Have u any suggestion?

post DDL for tables.
post DML for test data.
post expected/desired results.

Without above, you solve problem on your own because we do not have enough details to assist.
Re: An interesting problem: same query doesn't running on different database. [message #429072 is a reply to message #429068] Mon, 02 November 2009 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition post all these formatted.
Read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: An interesting problem: same query doesn't running on different database. [message #429076 is a reply to message #429072] Mon, 02 November 2009 00:53 Go to previous messageGo to next message
omaribais
Messages: 34
Registered: October 2009
Location: Bangladesh
Member

Dear BlackSwan,

I send my table and insert script please check it.

My desired output is,


EMP_NO ATTN SAL_PERIOD

2 9/30/2009 200909
1 9/30/2009 200909
8 9/30/2009 200909
3 9/30/2009 200909
1 10/31/2009 200910
2 10/31/2009 200910
3 10/31/2009 200910


Please help me. Thanks to all for reply.
-Omar
Re: An interesting problem: same query doesn't running on different database. [message #429080 is a reply to message #429076] Mon, 02 November 2009 01:16 Go to previous messageGo to next message
manu_jariwala
Messages: 20
Registered: August 2005
Location: Surat
Junior Member

Hey,

I m creating your script in my Oracle9i Enterprise Edition Release 9.2.0.8.0. But I cann't get any error. your script working fine with return results.

Give us brief detail about it. We cann't get exact problem u said that...



Re: An interesting problem: same query doesn't running on different database. [message #429081 is a reply to message #429080] Mon, 02 November 2009 01:21 Go to previous messageGo to next message
omaribais
Messages: 34
Registered: October 2009
Location: Bangladesh
Member

Thanks for your reply.
The problem not in script. It is in following Sql query,


SELECT pi.emp_no,pi.attn,pi.sal_period
FROM (SELECT va.emp_no, MAX (va.attn_dt) attn,TO_CHAR (va.attn_dt, 'YYYYMM') sal_period
FROM attn_tbl va
GROUP BY va.emp_no,TO_CHAR (va.attn_dt, 'YYYYMM')) pi
WHERE NOT EXISTS (SELECT 1
FROM v_leave_dtl
WHERE emp_no = pi.emp_no

and TO_CHAR (day, 'YYYYMM')=pi.sal_period
GROUP BY emp_no,TO_CHAR (day, 'YYYYMM')
HAVING MAX (DAY) > pi.attn)
Re: An interesting problem: same query doesn't running on different database. [message #429082 is a reply to message #429080] Mon, 02 November 2009 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first detail is the version number WITH 4 DECIMALS for BOTH sides.

Regards
Michel
Re: An interesting problem: same query doesn't running on different database. [message #429093 is a reply to message #429082] Mon, 02 November 2009 02:04 Go to previous messageGo to next message
omaribais
Messages: 34
Registered: October 2009
Location: Bangladesh
Member

sorry Michel i am not clear. Please tell in brief.
Re: An interesting problem: same query doesn't running on different database. [message #429095 is a reply to message #429093] Mon, 02 November 2009 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which versions do you run? on client? on server? with 4 decimals like 10.2.0.4.
Do you understand?

Regards
Michel
Re: An interesting problem: same query doesn't running on different database. [message #429101 is a reply to message #429095] Mon, 02 November 2009 02:37 Go to previous messageGo to next message
omaribais
Messages: 34
Registered: October 2009
Location: Bangladesh
Member

I need to run it in database 9i. I don't know which version of 9i database using by our client.
- Omar
Re: An interesting problem: same query doesn't running on different database. [message #429102 is a reply to message #429101] Mon, 02 November 2009 02:42 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then find out. Without the exact versions, there is no point in looking at anything else.
Re: An interesting problem: same query doesn't running on different database. [message #429103 is a reply to message #429102] Mon, 02 November 2009 02:45 Go to previous messageGo to next message
omaribais
Messages: 34
Registered: October 2009
Location: Bangladesh
Member

The version is 9.2.0.1.0
-omar
Re: An interesting problem: same query doesn't running on different database. [message #429104 is a reply to message #429103] Mon, 02 November 2009 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So upgrade to the last patchset.

Regards
Michel
Re: An interesting problem: same query doesn't running on different database. [message #429106 is a reply to message #429104] Mon, 02 November 2009 02:58 Go to previous messageGo to next message
omaribais
Messages: 34
Registered: October 2009
Location: Bangladesh
Member

I am unable to do this.
For This reason i need alternate sql of my previous sql.
If possible please help me.
Re: An interesting problem: same query doesn't running on different database. [message #429111 is a reply to message #429106] Mon, 02 November 2009 03:21 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well. If your car doesn't work, and you find out you have no gas, then the way to go it to put some gas into it.

If you can't put any gas into it, then the alternate solutions would be to push it, or to attach a horse in the front. But first you would to have to ask yourself REALLY hard *why* you can't just fill it up.
Re: An interesting problem: same query doesn't running on different database. [message #429114 is a reply to message #429111] Mon, 02 November 2009 03:47 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Actually, your very first question should be "Am I sure it is out of gas"
Previous Topic: ORA-00604/ORA-06502 on create package, validates after recompile
Next Topic: SQL Select statement - Returning Dummy row if no results (merged)
Goto Forum:
  


Current Time: Wed Sep 28 02:20:56 CDT 2016

Total time taken to generate the page: 0.07131 seconds