Home » Fusion Middleware & Colab Suite » SOA Suite, BPEL and OWSM » Fetching data from base table without trigger
Fetching data from base table without trigger [message #345378] Wed, 03 September 2008 04:57 Go to next message
suraj_an
Messages: 15
Registered: February 2008
Junior Member
Hi

I'm not sure which thread to post my query in.

Basically, whenever a new row or an existing row in an Oracle Standard (Base) table, say AAA, is inserted (or updated) with a status X, I need to fetch those records immediately whenever those insertion or updation operations are performed.
This can ideally be done using triggers.

But I need to perform the above without using DB triggers. I do not wish to use triggers on Oracle base tables.

I want to know if there is an alternate method of fetching these records.

One more thing. These records after being fetched must be passed on to a BPEL process. I thought of using BPEL to directly poll the table, but it wouldnt work if the volume of data is huge, as it would result in a full table scan.

Thanks a lot.
Re: Fetching data from base table without trigger [message #345381 is a reply to message #345378] Wed, 03 September 2008 05:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you don't want to use triggers then you'll need to call the code to do this from every place that can insert or update the tables inquestion, and work out some way of ensuring that this code is called every time anythign inserts or updates in future.

Use triggers - that's what they're there for,
Re: Fetching data from base table without trigger [message #345384 is a reply to message #345381] Wed, 03 September 2008 05:18 Go to previous messageGo to next message
suraj_an
Messages: 15
Registered: February 2008
Junior Member
Thanks for the reply. Thing is its a stand alone table and does not reference any other table. So I can only take action after the operation has been performed on this table.

I have heard that there is something called Business Events in Oracle that can do something similar to this. Can it be used in my case. Can someone enlighten?
Re: Fetching data from base table without trigger [message #345385 is a reply to message #345384] Wed, 03 September 2008 05:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That sounds like an Oracle Applications thing - I know nothing about it.
Re: Fetching data from base table without trigger [message #345386 is a reply to message #345384] Wed, 03 September 2008 05:22 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
So use After Insert Or Update Trigger what's the problem
in that.

Regards,
Rajat
Re: Fetching data from base table without trigger [message #345390 is a reply to message #345386] Wed, 03 September 2008 05:39 Go to previous messageGo to next message
suraj_an
Messages: 15
Registered: February 2008
Junior Member
That's what I said previously. I do not wish to use DB triggers on an Oracle base(standard) table
Re: Fetching data from base table without trigger [message #345401 is a reply to message #345390] Wed, 03 September 2008 07:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm not quite sure what the difference between a table and a 'base (standard)' table is, but we'll gloss over that.

Why don't you want to use triggers?

If you don't want to put a trigger on the table to fire some code when there are inserts or updates on the table, then (as I said earlier) you will have to ensure that that code gets called in every place in your application where data in this table is inserted or updated.
Re: Fetching data from base table without trigger [message #345404 is a reply to message #345378] Wed, 03 September 2008 07:36 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
suraj_an wrote on Wed, 03 September 2008 05:57

Basically, whenever a new row or an existing row in an Oracle Standard (Base) table, say AAA, is inserted (or updated) with a status X, I need to fetch those records immediately


I don't even think triggers are correct. What does "fetching" a record mean? Triggers do stuff in the background. They cannot display any records back to you. for example, you update 10,000 rows, what do you expect to happen? display those 10,000 records? Cannot be done without another select operation.
Re: Fetching data from base table without trigger [message #345413 is a reply to message #345401] Wed, 03 September 2008 08:03 Go to previous messageGo to next message
suraj_an
Messages: 15
Registered: February 2008
Junior Member
Hi

By standard table I mean tables like hr_all_positions_f, ap_invoices etc, tables that are defined by Oracle.

My colleague said that its not a good practice to use DB triggers on such Oracle base tables, since they may already have some triggers on them defined by Oracle etc. Though I am not fully convinced by that argument.

Joy, It is possible to to call a PL/SQL procedure from inside a trigger and then insert (or any operation) those records into another table for example ( it would nt do a full table scan which I think you are suggesting). I just don't want to do it that way.
Re: Fetching data from base table without trigger [message #345417 is a reply to message #345413] Wed, 03 September 2008 08:44 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you don't want to create a trigger on a "base table", perhaps you could use a materialized view instead. Something like this example (fetching changes on the Scott's DEPT table): first, let's create our testing environment:
SQL> create materialized view log on dept;

Materialized view log created.

SQL> create materialized view mv_dept
  2  refresh fast on commit
  3  as select * from dept;

Materialized view created.

SQL> create table log_dept (deptno number, datum date);

Table created.

SQL> create or replace trigger trg_mv_dept
  2    after insert or update on mv_dept
  3    for each row
  4  begin
  5    insert into log_dept
  6      (deptno, datum)
  7       values
  8      (:new.deptno, sysdate);
  9  end;
 10  /

Trigger created.

Here's how the DEPT table looks like at the moment, and what happens when we do some insert and update:
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NY
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> insert into dept (deptno, dname, loc)
  2    values (80, 'Test 1', 'Some location');

1 row created.

SQL> update dept set
  2    loc = 'New York'
  3  where deptno = 10;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from log_dept;

    DEPTNO DATUM
---------- -------------------
        80 03.09.2008 15:40:26
        10 03.09.2008 15:40:26

Another insert, a little bit later:
SQL> insert into dept (deptno, dname, loc)
  2    values (85, 'CSI', 'Miami');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from log_dept;

    DEPTNO DATUM
---------- -------------------
        80 03.09.2008 15:40:26
        10 03.09.2008 15:40:26
        85 03.09.2008 15:41:39

SQL>

As you can see, the LOG_DEPT table contains information about changes which were originally made on the "base table".

How does this sound?
Re: Fetching data from base table without trigger [message #345443 is a reply to message #345417] Wed, 03 September 2008 11:18 Go to previous messageGo to next message
suraj_an
Messages: 15
Registered: February 2008
Junior Member
Littlefoot, that's a great idea. Didn't think of it. I'll try it out.
Re: Fetching data from base table without trigger [message #345444 is a reply to message #345378] Wed, 03 September 2008 11:19 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I am not sure this will be of any use to you. For what it is worth check it out.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_cdcpub.htm#i80091
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_cdcsub.htm#i76950
http://www.oracle.com/technology/oramag/oracle/03-nov/o63tech_bi.html

Regards

Raj
Re: Fetching data from base table without trigger [message #345457 is a reply to message #345378] Wed, 03 September 2008 12:15 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
The problem is not that an Oracle applications table might have other triggers, it is that a patch might wipe out your new trigger. If you make sure to reapply the trigger after any patches, you would probably do fine.
Re: Fetching data from base table without trigger [message #345458 is a reply to message #345413] Wed, 03 September 2008 12:16 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
suraj_an wrote on Wed, 03 September 2008 09:03

Joy, It is possible to to call a PL/SQL procedure from inside a trigger and then insert (or any operation) those records into another table for example ( it would nt do a full table scan which I think you are suggesting). I just don't want to do it that way.



Nah, I was more interested in what you meant by "fetching" the records. Yes, you can do whatever you would like with them, but I thought maybe you meant you wanted to "display" them somewhere. Glad Littlefoot's idea works for you.
Re: Fetching data from base table without trigger [message #426499 is a reply to message #345378] Fri, 16 October 2009 00:40 Go to previous message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
Hi,

Oracle Applications has a standard functionality called "Business Events". Though its not available for all entity but you may check to see if it exists for your entity(table).

For Example there is a table hz_parties which can store a Person party or an Organization Party.

Following business events are fired when you perform insert/update on hz_parties table.

oracle.apps.ar.hz.Organization.create
oracle.apps.ar.hz.Organization.update
oracle.apps.ar.hz.Person.create
oracle.apps.ar.hz.Person.update

You can subscribe your plsql(or any other) code to the business event in Work flow responsibility and make plsql code instantiate BPEL.

Or make your bpel read the AQ WF_DEFERRED (which stores the Business events as CORRID "APPS:<bussiness event>"

NOTE : Business events are raised only when you create/update
record in base table using standard oracle API.

Idris
Previous Topic: Suite terminated when logging off Remotely
Next Topic: BPEL: wsdl file cut over
Goto Forum:
  


Current Time: Thu Mar 28 12:17:30 CDT 2024