Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: External table novelties

Re: External table novelties

From: Carel-Jan Engel <cjpengel.dbalert_at_xs4all.nl>
Date: Thu, 26 Aug 2004 14:02:18 +0200
Message-Id: <1093521737.20098.131.camel@dbalert199.dbalert.nl>


Daniel,
Data will be saved in the pipe, until you read it, and until the pipe blocks because its buufer is full. Then the writing process will stall.

The creative thing I can imagine is having several processes writing to one pipe, merging the logging of the processes. I have no idea about the order in which they can write, and whether write operations are atomic or not. This should be tested. Of course an ORDER BY <some timestamp> can resolve the ordering thing, but you won't see anything until the last process quit writing to the pipe causing and EOF. ORDER BY on a named pipe doesn't appear to be useful.

I've worked a lot with the DBMS_PIPE and DBMS_ALERT packages in the past. One example, set up as a prototype, but promoted to production because mgmt liked it but didn't want to pay extra for a final solution, connected two systems asynchrounously. We had an external process, residing on an Oracle-unaware host, sending messages to our database through a named pipe, which was made available over NFS. At the Oracle server a background process, written in ksh (this was 1993) processed the data read from the pipe, converting the lines into insert-statements. They were written to stdout, the ksh process was piped to SQL*Plus, performing/committing the inserts. A post-insert trigger fired an alert (DBMS_ALERT) to another process, an anonymous PL/SQL block running an infinite loop waiting for alerts and performing the post-processing in the database. This was our idea of AQ, before Oracle had it available.

Best regards,

Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok) ===

On Thu, 2004-08-26 at 13:34, Daniel W. Fink wrote:

> Mark,
>
> Very creative idea, IMHO. Here's one thought...An application loads
> and/or processes data. To track the progress, you could do queries
> against actual tables, or you could have the app write 'milestones' to
> the pipe, then you query that 'table'. If you aren't querying the pipe,
> will the output in the pipe be 'saved' in the pipe until you read it?
>
> Daniel
>
> Bobak, Mark wrote:
>
> >Jared,
> >
> >I have not thoroughly tested this, but the basic premise does work and =
> >is
> >valid. Create an external table that's defined on a named pipe, rather
> >than a file. I was half expecting an ORA-600 ora ORA-7445, but Oracle=20
> >(9.2.0.5 on Solaris 8) seems ok with it. I just did basic testing.
> >
> >select * from my_pipe;
> >will immediately return any data in the pipe, or block waiting on data.
> >If you send multiple lines of output to the pipe, all the lines will=20
> >be outputted in Oracle.
> >
> >Basically, external table works exactly like the named pipe does at the=20
> >O/S level.
> >
> >Now, the really creative part is figuring out what exactly you could do =
> >with
> >this.....I haven't given that a lot of thought just yet. Of course, you =
> >
> >could write a small JAVA proc that could execute any arbitrary O/S =
> >command=20
> >and pull the results into Oracle. Perhaps pull O/S in data about O/S=20
> >performance metrics, etc.....I don't know, I'm sure folks will come up
> >w/ creative ideas, but at the moment it's 3:11am, and I'm not feeling=20
> >creative.....;-)
> >
> >-Mark
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Aug 26 2004 - 06:56:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US