Page 1 of 1
Oracle Error - OCI_INVALID_HANDLE
Posted: Tue Dec 07, 2004 12:59 pm
by sathyanveshi
Friends,
While trying to run a job to load data from a source table to a staging table, I encountered the following error:
"LOAD_PS_RBT_ACCOUNT..Transform_PS_RBT_ACCOUNT: Oracle Error - OCI_INVALID_HANDLE"
LOAD_PS_RBT_ACCOUNT is the job name. Transform_PS_RBT_ACCOUNT is the transformation name.
I could understand that this is a Oracle related error. But I wanted to know the reason for this error and any mitigations.
Cheers,
Mohan
Posted: Tue Dec 07, 2004 3:01 pm
by ray.wurlod
It's a connectivity error. The "handle" (a variable used in the OCI to manage the connection or the statement) is invalid. Possible causes include improperly specifying the connection string or database name, or a connection that has been lost since first being connected - that is, a network failure.
Posted: Tue Dec 07, 2004 3:38 pm
by ririr
try viewing data from Source Stage using "VIEW DATA". That would help to troubleshoot if the error is because of the incorrect login, SID(database name) or the SQL itself.
Posted: Fri Apr 22, 2005 5:20 am
by hkotze
Hi Guys,
I get the exact same error and when we test it in toad with the same logon name and password, sid and tnsnames file we get a result back. The source data do contain a clob field that I specified as longvarchar and the length I made 2 000 000.
Any answer on this.
DS version 7.5A
OS HPUX 11i
Posted: Fri Apr 22, 2005 6:56 am
by chulett
CLOB fields are not supported, especially not ones that big.
I would guess that is the heart of your problem.
What happens if you take this field out of the metadata and don't try to process it? Does it work fine then?
Posted: Fri Apr 22, 2005 7:54 am
by hkotze
CLOB fields are supported up to 2 Gig as per Oracle OCI 9i pdf doc.
I changed the type to a varchar and it is working only truncating some input data. It seems the plugin have some problems with CLOB's
Posted: Fri Apr 22, 2005 8:19 am
by kcbland
hkotze wrote:CLOB fields are supported up to 2 Gig as per Oracle OCI
Yes but this is a terrible idea to move that much data on a row using DataStage. Numbers and letters are what ETL tools are meant to handle. Think a moment about moving a gigabyte of data as a single row. Your tool is written to allow you maximum flexibility to parse and manipulate strings of data. Even if the tool can do it (DS can't), you're talking about 1 row an hour of performance. Processing is a factor of characters per second, not rows.