Handling multiple row exception
Date: Fri, 16 Jul 1999 10:06:35 +0200
From: Warwick Gill WarwickG@langeberg.co.za
Subject: GEN: SQL exception handling
Hi all
I am having a problem in that EFT payments are not automatically reconciled
in cash management because the bank does not return the Oracle number in
their statements. I am attempting to handle this by writing a script to
update the bank recon interface table with the EFT numbers before they are
imported into the recon tables.
It is working correctly at the moment, but I would like to be able to handle
the case where the same vendor has been paid the same amount on the same day
twice. This will naturally return more than one row in the update clause,
clausing the program to terminate. What I would like to do is not update
this record and continue updating the other EFT records in the interface
table. I do not want to script to terminate at that stage, normally or
abnormally. I have thought of using the too_many_rows exception to trap the
error, but where to go to from there, I am unsure.
Thanks in advance
Warwick
Date: Fri, 16 Jul 1999 10:42:42 +0100
From: Reyne Chris-TUK585 Chris.Reyne@motorola.com
Subject: RE: SQL exception handling
You could try the following
UPDATE xxxxxxxx
SET xxxxxxxxxx = xxxxxxxxxx
WHERE xxxxxxxxxxxxxxxxxxxxxx
AND ROWNUM = 1;
I hope this isn't too simplistic
Regards,
Chris
Chris.Reyne@Motorola.com
Tel: +44 (0)1256 484765
Fax: +44 (0)1256 484212
Date: Fri, 16 Jul 1999 12:07:07 +0200
From: Warwick Gill WarwickG@langeberg.co.za
Subject: RE: SQL exception handling
Thanks for the suggestion. The condtion AND ROWNUM = 1 will set the value to
the first value found. I would like to set it to null if more than one row
is returned.
Thanks
Warwick
Date: Fri, 16 Jul 1999 13:56:57 +0100
From: Reyne Chris-TUK585 Chris.Reyne@motorola.com
Subject: RE: SQL exception handling
If you mean you would like the second and subsequent ones to be set to Null,
you could have a second pass saying
UPDATE xxxxxxxx
SET xxxxxxxxxx = NULL
WHERE xxxxxxxxxxxxxxxxxxxxxx
AND ROWNUM > 1;
Or if you want all them set to null howzabout:
UPDATE xxxxxxxx
SET xxxxxxxxxx = NULL
WHERE xxxxxxxxxxxxxxxxxx IN
(SELECT xxxxxxxxxx
FROM xxxxxx
WHERE xxxxxxxxxxx = xxxxxxxxxxxx
AND ROWNUM > 1);
Otherwise, I give up!
Good luck
Chris
Date: Fri, 16 Jul 1999 15:10:18 +0200
From: Warwick Gill WarwickG@langeberg.co.za
Subject: RE: SQL exception handling
that's not quite what I meant but it did give me an idea. Thanks.
Date: Fri, 16 Jul 1999 09:12:33 -0500
From: Shane Bentz SBentz@IVCF.ORG
Subject: RE: SQL exception handling
Please correct me if I'm wrong ( 'cause I want to learn why... ), but
couldn't you put an exception after the update statement to do nothing....
if you are doing a loop, then this code should just go on to the next ones
and do nothing with the ones which give you a too_many_rows exception.
Please let me know if I'm not understanding your situation or my suggestion.
Thanx.
... preliminary code ...
begin
update table
set column = 'xxxxx'
where condition;
exception
when too_many_rows
then null;
end;
... rest of code ...
Date: Fri, 16 Jul 1999 10:45:50 -0400
From: "Terri Williamson/MIS/HQ/KEMET/US" TerriWilliamson@kemet.com
Subject: RE: SQL exception handling
Shane,
Updates work on more than one row, so there would be no exception created
for too_many_rows. It would just update all of the rows that matched the
where clause.
-Terri Williamson
KEMET Electronics
Date: Fri, 16 Jul 1999 10:53:19 -0400
From: "Vainberg, Lily" Lily_Vainberg@compuware.com
Subject: RE: SQL exception handling
You could use the count option and check its value, but that would probably
slow you down... Unless you don't care.
Something like that:
SELECT COUNT(*) INTO CNT
WHERE xxxxxxx (your vendor and invoice number clause);
IF CNT = 1 THEN
UPDATE xxxxxx
END IF;
Date: Mon, 19 Jul 1999 10:00:28 +0200
From: Warwick Gill WarwickG@langeberg.co.za
Subject: RE: SQL exception handling
Okay, now I understand the problem. My script has the following form:
begin
update table
set column = (select xxxxx
from table
where condition)
where condition;
end;
The inner select is what can return more than one row in some cases. In this
case I require the program to ignore the error and carry on updating.
Thanks in advance
Warwick
Date: Mon, 19 Jul 1999 10:32:44 -0600
From: "Snyder, Stephanie" Snyder.Stephanie@tci.com
Subject: RE: SQL exception handling
Shane's answer was correct. For your script you would need to have a
subroutine within your cursor loop. The subroutine will have the exception
and handle the error for the current row and then processing will continue
through the loop (thus updating remaining records).
Begin
Open cursor
Loop
Begin
update table
set column = (select xxxxx
from table
where condition)
where condition;
Exception
When too_many_rows
process error
End;
End Loop
End;
Hope this helps!
From: Warwick Gill [mailto:WarwickG@langeberg.co.za]
Sent: Monday, July 19, 1999 12:33 AM
Subject: RE: SQL exception handling
I'm not sure if my understanding is correct or not but I thought that an
exception automatically exits the procedure. All that handling it does is
allows the procedure to exit normally and not abnormally. If this works then
I'll kick myself because that was my original thought.
Could anybody provide clarity on whether or not the code below will continue
updating the remaining records, or exit the procedure?
Thanks
Warwick
Date: Mon, 19 Jul 1999 11:26:26 -0700
From: "Bullard, James" James.Bullard@fluke.com
Subject: RE: SQL exception handling
I am under that same impression. I believe in order to truly continuing
execution, you would need to imbed an anonymous block that has an exception
handling section to capture the error and continue to execute the outer
(enclosing block).
Thanks,
James
Date: Mon, 19 Jul 1999 13:37:21 -0500
From: "Logan, Ernie" Ernie_Logan@bmc.com
Subject: RE: SQL exception handling
Which is exactly what Shane did in his example. His begin..exception..end
enclose only the select. If you do this, (enclose an individual statement in
begin..exception..end) you will execute the very next statement after the
end.
Date: Mon, 19 Jul 1999 14:52:52 -0400
From: "Vainberg, Lily" Lily_Vainberg@compuware.com
Subject: RE: SQL exception handling
My understanding of the problem is that Warwick did not want to go the next
statement in his program. He wanted to proceed to the next RECORD instead.
Is that correct?
That's why I suggested counting records matching specific vendor and invoice
criteria. Of course, you'll need to have a cursor first, then count
identical records per record from your cursor. If the count = 1 - then
update.
Date: Tue, 20 Jul 1999 08:45:35 +0200
From: Warwick Gill WarwickG@langeberg.co.za
Subject: RE: SQL exception handling
Thanks for all the solutions, but I am not using a cursor (Its WAY too
slow). I have however solved my problem by putting in a where clause with
the same conditions as the select fot the update clause.
Date: Tue, 20 Jul 1999 09:31:20 -0400
From: "Mark W. Farnham" mwf@rsiz.com
Subject: RE: SQL exception handling
Two points regarding this thread:
1) When setting a column or column set to values retrieved from another
query you always need exactly one row. If your query can possibly retrieve
multiple rows then you either don't care which values are used, in which
case you can add "and rownum =1" to the query, or you do care, in which
case the too many rows exception block can be used to handle the situation.
2) One of the proposed solutions contained the predicate fragment "and
rownum > 1" which it should be noted never is satisfied. ROWNUM cannot be
used to skip records, as it is the count of records which have indeed been
retrieved. Once it is not satisfied further delivery of records and
processing of the query is preempted.
How to get Subaccount with its Main account
Date: Sat, 17 Jul 1999 17:26:58 +0300
From: Bashir Ali Alib@savola.com
Subject: GL Subaccount
Hi all,
I am trying to fetch subaccounts from FND_FLEX_VALUES, FND_FLEX_VALUE_SETS.
How can I get the subaccount with its main account.. could any one tell me
the relation field.. or is it store in some other table
Thanks in advance
Regards
Date: Mon, 19 Jul 1999 08:34:40 -0400
From: "Jagannathan, Ravi" RJagannathan@prcnet.com
Subject: RE: GL Subaccount
If you are talking about parent and children accounts, use
FND_FLEX_VALUE_CHILDREN_V. It is a view. We have a standard report that
gives you children values for the parent; but the report will give you all
values for the segment. We have customized the report with an additional
parameter (Parent value).
Thanks
Ravi Jagannathan (MIS)
Tel : (305) 816 4831
Date: Mon, 19 Jul 1999 17:00:43 +0300
From: Bashir Ali Alib@savola.com
Cc: "Jagannathan, Ravi" RJagannathan@prcnet.com
Subject: RE: GL Subaccount
Thanks Ravi for the tips...
You are right, I am looking for parent & child accounts....
By what name is this standard report defined in Oracle (assuming oracle
standard reports).
Rgrds
Bashir
Date: Tue, 20 Jul 1999 08:39:34 -0400
From: "Jagannathan, Ravi" RJagannathan@prcnet.com
Subject: RE: GL Subaccount
Bashir,
If I am not wrong it is 'Rollup Detail Listing' or Summary....... Sorry I am
not 100% sure. Check the SRS.
Thanks
Ravi Jagannathan (MIS)
Tel : (305) 816 4831
Calling fast formula PL/SQL from Apps
Date: Wed, 21 Jul 1999 16:14:29 +0200
From: Bert Vandenbussche Bert.Vandenbussche@argus-is.com
Subject: PAYROLL: Calling Fast Formula in PL\SQL
Hi,
We are trying to call fast formulas in a PL\SQL procedure. We are that far
that when executing the PL\SQL wrapper outside Oracle Applications we get
correct results, executing the called FF formula correct. But once we try
to integrate our wrapper in Applications (as a User Defined Function),
the results we had outside Applications disappear....
Anybody has a clue, tips and tricks that can help us further?
Thanks,
Bert
How to select 2 most recently created vendor_contact_ids
Date: Fri, 23 Jul 1999 15:15:55 -0400
From: Priti Dhall pdhall@bbn.com
Subject: SQL question
Hi,
How can I select the 2 most recently created vendor_contact_ids from the
po_vendor_contacts table for each vendor_site_id?
I can select the most recent using max(rowid), how do I get the 2nd latest?
Thanks in advance,
Priti
Date: Fri, 23 Jul 1999 12:58:22 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: RE: SQL question
There is the creation_date column and the last_update_date column. The
last_update_date
column is a NOT NULL column. Depending on what you are looking for.
Date: Fri, 23 Jul 99 14:29:47 -0800
From: guru_pal@guthy-renker.com
Subject: Re: SQL question
If u really want two rows only you can try this.
select po_header_id from po_headers_all
where rowid=(select max(rowid) from po_headers_all)
union
select po_header_id from po_headers_all
where rowid=(select max(rowid) from po_headers_all
where rowid <> (select max(rowid) from po_headers_all))
Date: Sat, 24 Jul 1999 09:02:13 +0400
From: mzaveri@eppco.co.ae
Subject: Re: SQL question
Actually this common requirement, there is a generalized solution
available.
E.g. - This is last two connected users :-
SQL l
1 select * from v$session a where
2 3 (select count(*) from v$session b
3* where b.sid=a.sid)
SQL
You may replace a.sid and b.sid with rowid for your table or alternatively
if you have the primary key.
In your case if it is po_headers_all, than you can try po_header_id
(11.0.3) in place of sid.
Mehul Zaveri
Oracle DBA
EPPCO-DUBAI
Date: Mon, 26 Jul 1999 15:18:46 -0400
From: "Mark W. Farnham" mwf@rsiz.com
Subject: RE: SQL question
Relying on max(rowid) to be the most recent row supposes many givens,
among them:
1 free list
1 free list group
not enough deletes to put any block back on a free list for re-use.
no parallel insert operations
no dropped file ids from dropped files being reused when you add a file
There may be more ways to disrupt the correlation of the most recent
insert with the max(rowid), but these few should be enough to convince
you to avoid relying on this correlation.
Most of the Oracle applications tables have "WHO" columns which contain
various row identification information such as which user did it and when.
I don't know an apps database or the TRMs handy at the moment, but I
think vendor tables have the "WHO" columns you need.
Mark
Concurrent Manager not running SQL Script but otherwise completes successfully
Date: Tue, 27 Jul 1999 09:35:58 -0400
From: "Sankala, Dinesh" Dinesh.Sankala@lgeenergy.com
Subject: sql script
Hi
I am runnig a sql script as a concurrent programme which creates a record in
po_requisitions_interface and updates order_entry_interface tables
but the script , its a simple script with no parameters.
its running fine when I run that in sql ,
but its not doing any thing when run the same from apps.
but the concurrent request is finishing successfully .
any ideas /help greatly appreciated
Thanks in advance
kumar malay
Date: Tue, 27 Jul 1999 10:09:54 -0500
From: "Vikram Reddy" Vikram_Reddy@intervoice.com
To: oraapps-l@cpa.qc.ca, Dinesh.Sankala@lgeenergy.com
Subject: Re: sql script
I guess you missed terminating the program
with a '/'.
Thanks,
Vikram.
Date: Tue, 27 Jul 1999 14:45:16 -0400
From: "Vallapareddy, Krishna" kvallapareddy@noblestar.com
Subject: RE: ORAAPPS-L digest 3470
Have you committed your work in your sql script ?
Regards,
Krishna Vallapareddy
Date: Thu, 29 Jul 1999 08:45:03 CDT
From: "vk kumar" vkompally@hotmail.com
Subject: Re: sql script
Hi Dinesh,
Did you get the solution . If possible send me a script . I think you have
defined
1.Conc. Executable
2.Conc. Program/ define
and Request set for it to run in Apps.
Bye ...
Vinod ..
Date: Thu, 29 Jul 1999 10:00:43 -0400
From: "Sankala, Dinesh" Dinesh.Sankala@lgeenergy.com
Subject: RE: sql script
Hi
I got it as we had mis defined few parameters hence it does so