DSXchange

Hi,

I have a scenario where I need to call an transform stored procedure(oracle) with In and Out parameters.These parameters are CLOB type. But Datastage 8.7 has the longvarchar equivalent to CLOB. If I use lonavarchar it gives an error datatype not supported.. If I use varchar It gives me value/numeric error in the value..Can any one please give me a direction if DataStage can handle this scenario.

Thanks

CLOB can hold up tp 4gb but can you judge what will be the length of input column, Varchar should hold good. Check whether the error is during call of procedure or within procedure

Try with Unknown datatype.

I get the following errosr fr varchar in datastage

Stored_Procedure_0,0: Fatal Error: Fatal: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "Store_proc",

If I use unknown datatype:

Stored_Procedure_0,0: Error: Output link column definitions failed validation. Refer to previous messages for details.
DSTAGE-STP-0133`:`The SQL type (VarChar) of input link column Request must match the SQL type (Char) of output link column Request.

Store_proc is already defined with IN and OUT parameters as CLOB. I cannot change the store proc as its the existing one..

Since there is a priviege to change the datae type for IN and OUT.. I tried with varchar

if run the store proc directly with varchar in and out.. it gives the error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Can you check code.at line mentioned in error 167.
I have successfully implemented with varchar.

One more question. Do you have Oracle native installed?

I have oracle connector and not EE stage.DS version is 8.7

prasannakumarkk wrote:I have successfully implemented with varchar.

Only because your CLOB value was small, i.e. 4K or less.

In your shoes I'd be contacting your official support provider, see if the stage even supports CLOBs.

Yes craig it was less than 4k. But the error shown is reverse of what is happening.

Because theirs isn't.

My first question was that what is the length, but didnt hear anything from OP.
But why the error is thrown at some other line?

Some other line than what? It's clear they have a true "Large OBject" they are dealing with, hence the errors when it tries to shove it into a VARCHAR. And hence my suggestion to check to see of the stage even supports those objects.

Ok let me ask like this
CLOB parameter is IN OUT,
what is the value that is actually being passed (IN)
what is the derived Output value (OUT)
or it is both same?

Am curious about the solution that you used.