Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

gather records in compound trigger and process them

TelluriumDBAJul 10 2019 — edited Jul 10 2019

i have the following table. when a record is being inserted, i need to make sure the latest record of a given batch is updated as isLatest = 1. the update also should update the previous isLatest to 0 as well.

tableA

(

batchNumber number(10),

runNumber number(10),

serialNumber  number(10),

isLatest number(1)

)

so i write a compound trigger

i need the trigger to be as follows:

after each row is

begin

<gather records which were inserted  to objectA>

end

after statement is

select a.batchNumber, a.runNumber, a.serialNumber from tableA a inner join objectA o on a.batchNumber = o.batchNumber and a.isLatest = 0;

the above will be collected to an object (objectB).

now, i need to filterout the latest records. so i do this :

select batchNumber, runNumber, serialumber, row_number() over( partition by batchNumber order by runNumver desc, serialNumber desc) as LatestRank from objectB

the above will be collected to objectC

now, object C has to be updated with the TableA

merge into TableA ta using (select * from objectC where LatestRank <>1) R

on (ta.batchNumber = r.batchNumber and ta.runNumber = r.runNumber and ta.serialNumber = r.serialNumber)

when matched then

update set a.isLatest = 0;

===================================

i am confused as what method to use for the "objects". i tried both ref cursor and table type but no luck. Appreciate any help in this regard.

Comments

top.gun

So there is nothing to vote for?

William Robertson

This seems to be a tip rather than a suggestion for Oracle to implement something new.

Chris Hunt

And your suggestion is?

I wouldn't bank on any of these options "improving the performance." The Oracle optimiser is pretty clever, and may well use the same query plan in all three cases.

Akshs

And your suggestion is?

I wouldn't bank on any of these options "improving the performance." The Oracle optimiser is pretty clever, and may well use the same query plan in all three cases.

Could you please go through the below link where I attached the explain plan.

Martin Preiss

as others I fail to see here a proposal of a new feature - since the optimizer is already able to do corresponding transforms internally.

Furthermore I think it's common knowledge that NOT IN will not return a result set when the subquery contains NULL values.

unknown-1040115

This may not be an actionable idea for the Database Ideas list...

May I suggest we adjourn to the space to continue the discussion.

LKR

Akshs

This may not be an actionable idea for the Database Ideas list...

May I suggest we adjourn to the space to continue the discussion.

LKR

Thanks for guiding me.

But I tried to publish in SQL PLSQL Space..But because of permission issue I'.m not able to publish...

unknown-1040115

Thanks for guiding me.

But I tried to publish in SQL PLSQL Space..But because of permission issue I'.m not able to publish...

I can move this on your behalf.

Regards,

LKR

Akshs

I can move this on your behalf.

Regards,

LKR

Thank you....

Akshs

I can move this on your behalf.

Regards,

LKR

Even I'm not able to send any message. Could you please publish the below document in SQL PL\SQL space.

Could  please accept the request, So I'm able to message you.

Akshs

I can move this on your behalf.

Regards,

LKR

Hi Laura,

I have just published a document. Kindly move it to SQL and PLSQL space.

Document path : -

Many Many Thanks

Akshs

1 - 11

Post Details

Added on Jul 10 2019
7 comments
487 views