SQL & PL/SQL

Hi,

I am trying to bulk insert data from a data stream (inside ETL tool) into Oracle through a stored procedure, but under many restrictions. Hope you can help.

Database is Oracle 10g.

There are up to 1,000,000 records of around 100 bytes each, per delivery, for around 200,000,000 daily.

Target table:

CREATE TABLE TBL_STG
( "KEY" NUMBER(13,0),
"ATT_1" NUMBER(1,0),
"TIMESTAMP" NUMBER(14,0),
"ATT_2" NUMBER(14,0),
"ATT_3" NUMBER(14,0),
"ATT_4" NUMBER(2,0),
"ATT_5" NUMBER(1,0)
)

I can easily convert the input records stream into a collection (e.g, comma-separated list) with the ETL tool I use. I could then call the stored procedure with the record collection (comma delimited) as one of the parameters.

The problem is that I am using the ETL tool in a slightly modified setting (emulating continuous processing) which greatly limits my possibilities to load data into Oracle.

Basically I cannot use utilities (e.g., SQL*Loader);except when writing to disk first!!! nor the tool's provided api support.

I am now trying to call a stored procedure with a collection set however this gives the problem that following data types are NOT supported:

nested table/VARRAY/ANYTYPE: gives unknown column type 108 error
index-by table (old style): unknown col.tp. 152
Nclob/clob: unkn.col.tp 112
blob: 113

For the stored procedure all I have left is the following:

LONG RAW, RAW, LONG.

these data types are not recommended but at least still supported by 10g.

So hence my question, under these circumstances, could you provide me some help towards passing the RAW/... collection and interpreting it as a numeric table in Oracle. Your help is greatly appreciated.

Thanks