Re: How can I get 'df - Pk' in a DIY$ view

> "Guerra, Abraham J" wrote:
>
> Hello group,
>
> Recently somebody submitted a link to a very interesting article
> http://www.oracle.com/oramag/webcolumns/2001/index.html?diy_dynamic.html on
> how to create Do-it-yourself dynamic views... in that article the
> author mentions he can get the results of 'df - Pk' in a view using an
> external procedure. Does anybody know how to do that? I could really
> use it in a tool I got.
>
> Thanks in advance.
>
> Abraham Guerra
> Database Administrator
> American Family Insurance
>

Abe,

  Having co-authored the article, I guess that I am in a good position to answer :-).
The idea behind the views is to offer a tabular, SQL-queryable view of PL/SQL tables held in your PGA. All the difficulty is of course in filling-up the tables with the data which is of interest to you. External procedures are a possibility, although not necessarily the best one.
The problem with external procedures is that, to simplify, static variables are bannished. It's a one go thing, you cannot repeatedly call an external procedure and hope to resume at each call exactly where you left at the previous call - it may work, but by chance. As usually the number of lines returned by a df -k is not enormous, what you can do in your function is execute a popen("df -k") (which returns a file pointer), and read from this file pointer until it returns NULL, *concatenating* the various lines (using some special character to mark line separation - '\n' springs to mind -, pclose() and return to Oracle a *single* big string; be careful with memory management though. Then it's a piece of cake to split the string and fill the PL/SQL tables as it should be.
Another solution is to use Java. I haven't yet (lack of opportunity), so I won't tell you much about it.
A third solution I have used for LDAP accesses is to write a daemon program which listens on a (DBMS) pipe. You push your requests down the pipe, it executes them (popen() again, and returns (line by line) the results down the pipe. Careful programming may even make the daemon able to service several requests at once.

HTH, Stephane Faroult
Oriole Software

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).