Oracle Error - OCI_INVALID_HANDLE

sathyanveshi · Post by **sathyanveshi** » Tue Dec 07, 2004 12:59 pm

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

ray.wurlod · Post by **ray.wurlod** » Tue Dec 07, 2004 3:01 pm

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.

ririr · Post by **ririr** » Tue Dec 07, 2004 3:38 pm

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.

hkotze · Post by **hkotze** » Fri Apr 22, 2005 5:20 am

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

chulett · Post by **chulett** » Fri Apr 22, 2005 6:56 am

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?

hkotze · Post by **hkotze** » Fri Apr 22, 2005 7:54 am

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

kcbland · Post by **kcbland** » Fri Apr 22, 2005 8:19 am

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.