Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Trigger referencing LONG in 8i

Re: Trigger referencing LONG in 8i

From: Nicolai Tufar <>
Date: Wed, 9 Feb 2005 01:48:30 +0200
Message-ID: <>

Paul Drake wrote:
> I would suggest that you post that to and to the
> newsgroup . Perhaps someone there has
> been in the same situation.

I doubt that there is something more informative than this list. But asking Tom Kyte worth a try. From << Status: Sorry I have a large backlog right now, please ask a question later>>. I will ask anyways. Thanks.

Powell, Mark D wrote:
> Don't blame Oracle for bad application design,
> bad code, a bad vendor, and failure of the powers that
> be to upgrade the application in a timely manner.
Upgrading in a timely manner costs a lot of $$. Still, your argument is a valid one. Unfortunately I am not in charge of that system, nor do I have authority to suggest a change :(

Knight, Jon wrote:
> One other suggestion:
> You mention that it's a "small system" and you can do SELECTs. Could you
> dump the table _very_ often to a staging table? As long as the dump is more
> frequent than the purges, you'll get all the records. Then write a quick
> process to de-dup and move the staging data into your new system.

Stephen Booth wrote:
> I take it that no table has two or more columns wider than 256 bytes then.

Yes, there is only one offending table and it has only one LONG column. The rest of tables are fine.

Stephen Booth wrote:
> Does oracle not let you create a trigger at all on a table with a long
> column? That seems wierd. I've never tried it on any version but I
> struggle to think why that would be so. I could possibly understand
> not letting you manipulate a long column in a trigger but stopping you
> from creating a trigger at all on a table because one column happens
> to be a long seems wierd.

It is exactly what happens... or not.. oh, wait. If I create the target table with VARCHAR2(almost infinite) then I would probably be able to insert into it. Thank you very much for your help. I am at home now, and have no oracle database handy. I will try it the first thing when I come to work tomorrow morning.

> There's a library in the 9i XDK (XML Development Kit), 9.0.1.x version
> as I recall, that lets you extract data from a 7 or above database (it
> does a normal SQL*Net or Net8 connection so all the database sees is a
> connection from a client) and present it as an XML file. You've said
> that the app deletes data after a time but you can't predict when.
> Can you be sure that a row will not be deleted if it is less than a
> certain period old? For example whilst during period of 10 minutes
> zero or more rows will be deleted, no row less than 10 minutes old
> will be deleted. If so you could extract the data to an XML
> file/stream more frequently than that (to be on the safe side) then
> strip out the data you already have (maybe based on rowid, possibly
> based on an XSLT that basically does a diff along the lines of "return
> all records in file b that don't exist in file a, discard all records
> that are in both files or just file a" or have a pair of tables and do
> "select * from b minus select * from a;" then drop a, recreate it as a
> copy of b and truncate b) and import it into your target DB. I'm
> thinking XML rarther than plain text as that gives you more ability to
> manpulate the data outside the databases (or, indeed, inside) and less
> headaches about field/record separators.

It will be the second thing I try, should the first one fail.         

Thanks a lot,
Nicolai Tufar

Received on Tue Feb 08 2005 - 18:51:13 CST

Original text of this message