John -- Thanks for the question regarding "How to find the column list of an "instead-of" triggering statement (update or insert)", version 10.2.1 (XE)

Submitted on 20-Oct-2009 10:43 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 11-Nov-2009 15:15

You Asked

Hi Tom,

I am using instead-of triggers in a little PoC. As part of the trigger logic I need to determine which columns the user specified (if any) in the triggering update or insert statement.

Do you know of a simple way to retrieve this?

The best I could manage was to query V$SQLTEXT and parse the statement. And because I'm trying to read V$SQLTEXT from within the triggering session, the "select... from V$SQLTEXT" is the statement that V$SESSION.SQL_ADDRESS points to. V$SESSION.PREV_SQL_ADDR often seems to point the the right one in Enterprise Edition (10.2.03), but for this purpose I'd be stuck with XE, and in any case I get the feeling it (PREV_SQL_ADDR) isn't going to return the correct statement reliably. I did get round this problem by putting the query in a procedure, and calling it across a loopback database link - and bingo, the procedure is run in a separate session, meaning that I do reliably get the correct statement. All this does seem a bit "clunky" though. If you know of a simpler/saner way to get at the column list I'd be grateful if you could explain it.

Thanks

John

and we said...


ops$tkyte%ORA10GR2> create table t
  2  as
  3  select *
  4    from all_users
  5  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view v
  2  as
  3  select user_id x, username y, created z
  4    from t
  5   where rownum = 1
  6  /

View created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger v
  2  instead of update on v
  3  begin
  4          if updating('X') then dbms_output.put_line( 'Update x' ); end if;
  5          if updating('Y') then dbms_output.put_line( 'Update y' ); end if;
  6          if updating('Z') then dbms_output.put_line( 'Update z' ); end if;
  7  end;
  8  /

Trigger created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> update v set x = x+1;
Update x

1 row updated.

ops$tkyte%ORA10GR2> update v set x = x+1, y = lower(y);
Update x
Update y

1 row updated.

ops$tkyte%ORA10GR2> update v set x = x+1, y = lower(y), z = z-1;
Update x
Update y
Update z

1 row updated.

ops$tkyte%ORA10GR2> update v set y = lower(y), z = z-1;
Update y
Update z

1 row updated.

ops$tkyte%ORA10GR2> update v set z = z-1;
Update z

1 row updated.

Reviews

Nearly there ... October 20, 2009 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader from UK

Thanks Tom, perfect for update statements. Should've thought of that one.... BUT (isn't there 
always): Is there a similarly easy method for an insert statement?

One of my requirements is to replicate use of column defaults. 

i.e. suppose a miserly default EMP.SAL of £1000.  Insert into EMP (EMPNO,ENAME,DEPTNO) values (...) 
- I need to detect that SAL has not been specified in the insert and so provide it when I insert 
into my "under-view" table structure.

Thanks again

John

PS good luck with the war on "IM speak".  I'm with you.

Followup October 22, 2009 - 5pm Central time zone:

no, there is not for inserts.

The only thing I can thing of would be if SAL was not NULL - then the fact it was null would be your flag.

But you would not be able to be sure on any nullable column, you would not know if they inserted null on purpose or if null was just the default.

question: what are you doing, triggers scare me.

October 21, 2009 - 7am Central time zone

Bookmark | Bottom | Top

Reviewer: Mark from UK

Pity that that PL/SQL function INSERTING() is not overloaded like UPDATING().

Thanks and an answer to your question October 23, 2009 - 7am Central time zone

Bookmark | Bottom | Top

Reviewer: John Callaghan from UK

Thanks Tom,

I'll have to stick with my Heath-Robinson method of acquiring the insert statement from V$SQLTEXT. 

I hear what you say about triggers - I generally tend to avoid them too.  

To answer your question:

What I am working on is an idea for processing all DML as physical inserts.  I couldn't come up 
with any other way of allowing a SQL update or delete statement to be processed as physical inserts 
only, without resorting to views on a data structure, populated by instead-of triggers.  I don't 
suppose it will be massively scalable (capital "M") but this is a PoC - trying to prove a point.  
And it doesn't seem to be performing too badly actually.  No doubt I'll be back for more help and 
advice on making the thing really scale if/when we get that far.  

I suppose ideally I would have the RDBMS process the statements directly in the kernel, but that 
kind of development is, frankly, beyond me.  Perhaps one day your Mr Mendelsohn might be persuaded 
to help. ;o)

Regards,

John

Followup October 23, 2009 - 2pm Central time zone:

have you looked at workspace manager?

or flashback data archive?

if your goal is to maintain a history?

October 26, 2009 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Oleksandr Alesinskyy

Do not want to be rude, but as for me"an idea for processing all DML as physical inserts" closely 
resembles "an idea of processing a defecation as eating".

Followup October 26, 2009 - 2pm Central time zone:

dung beetles wouldn't be upset by that.

But anyway, an idea for processing all DML as inserts (well, except for delete I guess, delete would just be an update) is manifested in workspace manager (single table) and flashback data archive (multiple segments, a bit more manageable) and I would not say the analogy holds.

Funny thing November 3, 2009 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: John Callaghan from UK

I find it odd that people who are about to be intentionally rude nearly always prefix their comment 
with "I don't mean to be rude, but.."  Anyhow.....

Thanks for the notes about workspace manager (yes, I did know about it) but my project is looking 
at making all versions of data available to all users of a given application "on-line" as it were. 

We did have quite an in-depth think about flashback, but were abruptly stopped when I realised that 
there would be no data in it.  Because we are most certainly insert-only (if you once update 
anything, you have over-ridden history and therefore lost it), the only data held by flashback 
would be the rowids of the inserted rows.  This is because flashback works on UNDO, not REDO as I 
understand it.  To undo an insert only requires the ROWID to delete (undo) it.  If it had worked on 
REDO, I'd have been really pleased to try and make use of it for our transaction-time dimension.  
The "real-world" time dimension is a different challenge though, and the 
"viewsandinsteadoftriggers" approach is providing a very promising mechanism, at least at this PoC 
stage.

Followup November 9, 2009 - 2pm Central time zone:

flashback data archive - not just flashback query.

flashback data archive allows you to query the data as it existed for months, years into the past.

It works by versioning the data in another segment - as you process a transaction, you generate undo. When you commit, a process (fbda) reads that undo and rolls back the change getting the version of the row before the update/delete. It then inserts that version into the archive.

Not sure what your concern on the insert only bit was, of course we have the row we need to query for a row that only has been inserted - we don't need to do anything special on that one?

We log the before image of all updated and deleted records in the archive and flashback query uses them (instead of undo) for the long term flashback data archive query.

can you explain why you think redo would solve something here? I'm very confused.

November 10, 2009 - 4am Central time zone

Bookmark | Bottom | Top

Reviewer: John Callaghan from UK

Hi Tom,

So, the table segment contains the "Current Relvar", and fbda contains the "Historical Relvar" to borrow C.J. Date's lingo. This is fine for a system which is keeping historical "transaction" versions only. It is the combination of transaction-time with valid-time that we are after here. (e.g I want to update Miss Jones to Mrs Smith - as of next week, when she is due to get married, but I want to issue the update statement NOW, seeing "Miss Jones" when I query the database "Now", and see "Mrs Smith" when the clock ticks over into next week.)

To achieve this, our design in predicated on only ever physically inserting rows, so there could never be any data in either the undo or fbda segments .... unless I 've misunderstood, of course.

"It [...] reads that undo and rolls back the change getting the version of the row before the 
<i><b>update/delete</b></i>. It then inserts that version into the archive. "

As we do not (physically) update/delete there is no insert to the archive.

The REDO log on the other hand does contain the data contained in every insert statement, so if flashback data archive had been based on this, it might have offered us a way to "leverage" fbda for our transaction-time dimension.

We do have demonstrations available which we can show you on the web - if you are in a position to spare the time - you might be able to point us to ways of making better use of existing database features once you've seen more fully what we're up to? If we could find a way to use fbda then we would see that as a potential advantage.

Thanks

John

Followup November 11, 2009 - 3pm Central time zone:

... to borrow C.J. Date's lingo. ...

you shouldn't do that - we should use lingo that is - well, understandable by everyone (self included, I don't really read his writings)

what you describe:

... (e.g I want to update Miss Jones to Mrs Smith - as of next week, when she is due to get married, but I want to issue the update statement NOW, seeing "Miss Jones" when I query the database "Now", and see "Mrs Smith" when the clock ticks over into next week.) ...

is available with workspace manager.

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11826/long_vt.htm#i1010463

restrictions on workspace manager with valid time support November 12, 2009 - 3am Central time zone

Bookmark | Bottom | Top

Reviewer: Sokrates

one should note that there are heavy (undocumented) restrictions on WM with valid time support.

for example:

CREATE TABLE d (id NUMBER PRIMARY KEY);
EXECUTE DBMS_WM.EnableVersioning (table_name=>'D', validTime=>TRUE, hist => 'NONE');
/
BEGIN DBMS_WM.Export(table_name => 'D',staging_table => 'D_STG', workspace => 'LIVE'); END;
/
*
ERROR at line 1:
ORA-20171: WM error: Export not supported on a table with valid time
ORA-06512: at "WMSYS.LT", line 13176
ORA-06512: at line 1

note: this is not documented !

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11826/long_ref.htm#insertedID24

doesn't loose one word about this

also, regarding exp/imp and expdp/impdp only full-export/import is supported for them !

Write a Review