Home » SQL & PL/SQL » SQL & PL/SQL » uneven rows saved (oracle 10g)
uneven rows saved [message #418330] Fri, 14 August 2009 03:57 Go to next message
owais.kh
Messages: 7
Registered: August 2009
Junior Member
Hi,
I have two users X and Y.Both run an SQL query containing a function 'F'.When X runs the statement and saves the data all 100000 rows are saved.However when Y runs the same statement only first 6200 rows are saved.
Incidently the row count for the statement from both users is same.
Could you help in identifying the problem with user Y
Re: uneven rows saved [message #418333 is a reply to message #418330] Fri, 14 August 2009 04:01 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
They are different procedures? Different rowcounts and you have made an error in the count? The flying spaghetti monster has come down and taken the remaining rows to be with him for eternity? You are working with a version of Oracle that contains a bug when running the kind of code that you are running? the list goes on.
Re: uneven rows saved [message #418334 is a reply to message #418330] Fri, 14 August 2009 04:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Based on the information provided, probably not.

Are both users running queries off the same data, or does each user have their own copy of the data?

Does the function by any chance have a 'WHEN OTHERS THEN NULL' exception handler in it?

Can we see the code for the function and the structure of the tables?

Can you reproduce the problem?
Re: uneven rows saved [message #418336 is a reply to message #418334] Fri, 14 August 2009 04:08 Go to previous messageGo to next message
owais.kh
Messages: 7
Registered: August 2009
Junior Member
please find my revert below.

1.Both run quries against same set of data.

2.There is no such exception handler in the code

3.Function code is below
CREATE OR REPLACE FUNCTION Getbilledact(actno IN NUMBER ,subscr IN NUMBER, subscr_res IN NUMBER) RETURN VARCHAR2 IS
stat VARCHAR2(10);
ACCT NUMBER(10):=1;
BEGIN

BEGIN
SELECT oiim.balance_account_no
INTO stat
FROM OPEN_ITEM_ID_MAP OIIM
WHERE oiim.account_no=actno
AND oiim.subscr_no=subscr AND oiim.subscr_no_resets=subscr_res
AND oiim.charge_element_type IN (0,3);
EXCEPTION
WHEN NO_DATA_FOUND THEN
ACCT := 0;
END;

IF ACCT = 0 THEN
BEGIN
SELECT oiim.balance_account_no
INTO stat
FROM OPEN_ITEM_ID_MAP oiim
WHERE oiim.account_no=actno AND oiim.subscr_no = 0
AND oiim.charge_element_type IN (0,3);
EXCEPTION WHEN NO_DATA_FOUND THEN
stat := actno;
END;

END IF;
RETURN stat;
END;
/
Re: uneven rows saved [message #418338 is a reply to message #418330] Fri, 14 August 2009 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Judging by your initial description I would assume the problem is in the code that calls the function rather than the function itself.

So post that code - and please use code tags, if you're not sure how have a look at the orafaq forum guide.
Re: uneven rows saved [message #418341 is a reply to message #418330] Fri, 14 August 2009 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually are you sure the two selects in the function can only return at most one row every time they're run?

The fact that you've got an IN in the where clause makes me suspect there's a possibility of getting a too_many_rows error.
Re: uneven rows saved [message #418356 is a reply to message #418341] Fri, 14 August 2009 06:12 Go to previous messageGo to next message
owais.kh
Messages: 7
Registered: August 2009
Junior Member
ya cent percent sure,no issues with user X til date,its only user Y which is giving far less rows
Re: uneven rows saved [message #418364 is a reply to message #418330] Fri, 14 August 2009 07:02 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
And without seeing the code involved we really can't help you any further.
Re: uneven rows saved [message #418378 is a reply to message #418356] Fri, 14 August 2009 07:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you show us the SQL that you're running that generates these results.

Re: uneven rows saved [message #418379 is a reply to message #418336] Fri, 14 August 2009 07:50 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Your FROM clauses to do not reference a schema, therefore they use local tables, unless you have public synonyms, so you are not selecting from the same table.

I'm going with cookiemonster's assessment that you received a TOO_MANY_ROWS exception.
Re: uneven rows saved [message #418520 is a reply to message #418379] Mon, 17 August 2009 00:30 Go to previous messageGo to next message
owais.kh
Messages: 7
Registered: August 2009
Junior Member
We are using public synonyms only and none of the objects used in the fucntion are owned either by X or Y.

The exception you are talking about cannot choose a specific user to occur as X is fetching all the rows easily
Re: uneven rows saved [message #418522 is a reply to message #418520] Mon, 17 August 2009 00:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Show us the relevant parts (call, point where the "save" is done, exception handling) of the calling code.
Re: uneven rows saved [message #418528 is a reply to message #418330] Mon, 17 August 2009 01:10 Go to previous messageGo to next message
owais.kh
Messages: 7
Registered: August 2009
Junior Member
This is how i call the function,i.e via a simple sql statement

SELECT
(Getbilledact(cmf.account_no,s.subscr_no,s.subscr_no_resets)) billed_ac_no
from cmf,service s
WHERE s.parent_account_no=CMF.account_no;

One more thing when i include the order by clause the query returns no rows from user Y while as user X is able to generate sorted list as usual

Re: uneven rows saved [message #418535 is a reply to message #418528] Mon, 17 August 2009 02:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What order by? I don't see an order by.
What happens if you remove the function call, how many rows are returned for X and Y in that case?
Re: uneven rows saved [message #418541 is a reply to message #418528] Mon, 17 August 2009 03:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You seem to be saying this:
Quote:
If I run query A for user Y then I get a certain number of rows returned. If I add an ORDER BY clause onto the query and make no other changes, then when I run the query for user Y I get a different number or rows returned.

If this is the case then there is an Oracle bug somewhere, and you need to raise this with Metalink - an Order By clause is incapable of affecting the number of rows returned.

Re: uneven rows saved [message #418578 is a reply to message #418520] Mon, 17 August 2009 07:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
owais.kh wrote on Mon, 17 August 2009 01:30

The exception you are talking about cannot choose a specific user to occur as X is fetching all the rows easily


I do not know what this means.
Re: uneven rows saved [message #418580 is a reply to message #418330] Mon, 17 August 2009 08:01 Go to previous messageGo to next message
owais.kh
Messages: 7
Registered: August 2009
Junior Member
never mind this issue has been resolved now thanks everyone for their inputs.
Re: uneven rows saved [message #418583 is a reply to message #418580] Mon, 17 August 2009 08:11 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And what was the issue, so others may learn?
Re: uneven rows saved [message #418584 is a reply to message #418583] Mon, 17 August 2009 08:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd be interested to know what the cause of this was too.

I'd be very suprised if it turned out to be an Oracle bug - I think it's much more likely to be caused by one of your users not looking at the same data as the other.
Re: uneven rows saved [message #418607 is a reply to message #418580] Mon, 17 August 2009 11:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
owais.kh wrote on Mon, 17 August 2009 15:01
never mind this issue has been resolved now thanks everyone for their inputs.

Sorry to say, but that is no way to finish a thread like this. With so many people trying to help you out, the least you could do is tell all of them what the issue was, and not let them ask you to explain it.
Re: uneven rows saved [message #418706 is a reply to message #418330] Tue, 18 August 2009 04:22 Go to previous messageGo to next message
owais.kh
Messages: 7
Registered: August 2009
Junior Member
sincere apologies for that,in fact my naivety with the forums in general is the reason behind the delay.

Coming to the resolution part.
There were in fact two object by the same name but different code.The funcion user X was calling belonged to him while as the function that Y was calling belonged to some one else but that function was given public access.So crux of the matter is that they seemed like using the same object but in reality they were not.

Sorry again for bothering you guys on this frivolity
Re: uneven rows saved [message #418748 is a reply to message #418706] Tue, 18 August 2009 08:21 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
owais.kh wrote on Tue, 18 August 2009 05:22

Sorry again for bothering you guys on this frivolity


It's not frivolity. Mistakes like this happen to many people. I have no doubt this type of problem will come up here for someone again.

Frivolity; wow, I never heard that word used in that form before.
Previous Topic: Compare query speed
Next Topic: ORA-0090 : missing or invalid privilege (create user und give/grant him privilege)
Goto Forum:
  


Current Time: Fri Dec 02 14:18:57 CST 2016

Total time taken to generate the page: 0.12882 seconds