SQL & PL/SQL

Hi there,

I would like to start a new thread about an interesting suggestion that was made to me in [url http://forums.oracle.com/forums/thread.jspa?threadID=1101897&tstart=0&messageID=4441737#4441737]another discussion. Basically I would like to try and implement a Java-based pipelined table function with Oracle 11.1.0.7 and I'd need some help to get started.

The current situation is as follows: I have an existing piece of Java code, embedded into an external ETL job, that does the following:
<li> Accept a file path as input;
<li> Open the file using the FileInputStream library;
<li> Pipe it through an appropriate decompressor according to extension (gunzip, unzip, uncompress, unrar...); this is done using GZIPInputStream, ZIPInputStream, UncompressInputStream, etc...
<li> Loop start: Read the piped output byte by byte and extract data fields from it; the fields are big-endian integers or ASCII strings, all of known lengths except the last one in each record which is a string of variable length; the length of this last field is encoded in one of the previous integer fields.
<li> Fill up a "row" object with the decoded field values;
<li> Output the resulting row;
<li> Loop until EOF.

Currently the output row is appended to a CSV file which is afterwards loaded into the database using an external table, then processed using views, insert into statements, etc. I would like to bypass the CSV phase by directly doing the file extract into the database itself. This means adapting and wrapping the existing Java code into a pipelined table function that would accept a file name as input (in an existing directory object) and output the corresponding rows of records. Now I've found some documentation about how to do that ([url http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/pipelined_example.htm#CHDHDHEE]Pipelined Table Functions: Interface Approach Example). However I still have no idea about how to open a file for streaming, pipe it through a filter, browse through it byte by byte, etc. Would somebody be kind (and patient) enough to briefly sketch the skeleton of a solution, or possibly pointers to existing code snippets, etc? This would be greatly appreciated.

Thanks to all for your help,
Chris