stored procedure error

India2000 · Post by **India2000** » Wed Mar 20, 2013 3:26 pm

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

prasannakumarkk · Post by **prasannakumarkk** » Wed Mar 20, 2013 7:59 pm

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.

India2000 · Post by **India2000** » Wed Mar 20, 2013 8:22 pm

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

prasannakumarkk · Post by **prasannakumarkk** » Wed Mar 20, 2013 8:56 pm

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

prasannakumarkk · Post by **prasannakumarkk** » Wed Mar 20, 2013 9:08 pm

One more question. Do you have Oracle native installed?

India2000 · Post by **India2000** » Thu Mar 21, 2013 5:38 am

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

chulett · Post by **chulett** » Thu Mar 21, 2013 7:22 am

prasannakumarkk wrote:I have successfully implemented with varchar.

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

chulett · Post by **chulett** » Thu Mar 21, 2013 7:35 am

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

prasannakumarkk · Post by **prasannakumarkk** » Thu Mar 21, 2013 7:45 am

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

chulett · Post by **chulett** » Thu Mar 21, 2013 7:49 am

Because theirs isn't.

prasannakumarkk · Post by **prasannakumarkk** » Thu Mar 21, 2013 7:58 am

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?

chulett · Post by **chulett** » Thu Mar 21, 2013 9:13 am

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.

prasannakumarkk · Post by **prasannakumarkk** » Thu Mar 21, 2013 10:44 am

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?

prasannakumarkk · Post by **prasannakumarkk** » Wed Mar 27, 2013 5:22 am

Am curious about the solution that you used.