String truncate while calling stored procedure from DS

singhald · Post by **singhald** » Tue Nov 03, 2009 6:27 am

Hi

I am using stored procedure stage to call oracle stored procedure. This stage reads a string parameter "Error Message" and updates message into oracle table. The message is truncating while calling stored procedure from DataStage job And it writes only first char of the message in database table.

When I call the same stored procedure from oracle toad, the input message is not truncating. It write entire message into table.

BEGIN DBSchema.Stp('Message'); END;

could any one please explain why it is happening.

Thanks in Advance

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Nov 03, 2009 7:07 am

What is the datatype for the message column ?

What happens if you hardcode the value ?

singhald · Post by **singhald** » Tue Nov 03, 2009 7:18 am

Sainath.Srinivasan wrote:What is the datatype for the message column ?

What happens if you hardcode the value ?

datatype is Varchar. If i hardcode the value I am getting below error message

APT_CombinedOperatorController(1),0: Fatal Error: Fatal: Inserted value too large for column, row rejected.

Please let me know if you need any more details

Thanks

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Nov 03, 2009 7:22 am

Set APT_DISABLE_COMBINATION to true and re-run the job.

It appears to be a length issue.

singhald · Post by **singhald** » Tue Nov 03, 2009 7:41 am

Sainath.Srinivasan wrote:Set APT_DISABLE_COMBINATION to true and re-run the job.

It appears to be a length issue.

After Setting APT_DISABLE_COMBINATION to true . I am getting the same error message in stored procedue stage only

Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Nov 03, 2009 2:06 pm

Now you know the source of the error and may more closely focus your diagnostic efforts. Check whether there is a metadata mismatch; the stored procedure stage does not report these.

singhald · Post by **singhald** » Wed Nov 04, 2009 2:52 am

ray.wurlod wrote:Now you know the source of the error and may more closely focus your diagnostic efforts. Check whether there is a metadata mismatch; the stored procedure stage does not report these. ...

Hi Ray,

I dont have any datatype mismatch. in addition to this, i dont have any defined length for this varcher field in datastage and database stored procedure.

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Nov 04, 2009 2:57 am

The database has a defined length, and may be objecting to metadata suggesting that the string may have a potentially larger (that is, unlimited) size.

singhald · Post by **singhald** » Wed Nov 04, 2009 3:10 am

ray.wurlod wrote:The database has a defined length, and may be objecting to metadata suggesting that the string may have a potentially larger (that is, unlimited) size. ...

In this situation, what length should i define in DataStage table definition?

currently i am using varchar2 in both datastage and database without specifying any length. but it is giving me only first char of message in target table.

Thanks

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Nov 04, 2009 3:14 am

Try the following
1.) Make the length as 10 and re-run
2.) Change the stored procedure to take 2 parameters.
Set one to be hardcoded and next from the link variable.

singhald · Post by **singhald** » Wed Nov 04, 2009 3:30 am

Sainath.Srinivasan wrote:Try the following
1.) Make the length as 10 and re-run
2.) Change the stored procedure to take 2 parameters.
Set one to be hardcoded and next from the link variable. ...

when i am specifying the length 10 for message field. I am getting below ewarning message followed by error message.

warning messages:

STp_Stage,0: Error: Output link column definitions failed validation. Refer to previous messages for details.
The SQL precision (10) of input link column P_ERROR_MESSAGE must be equal to the SQL precision (0) of output link column P_ERROR_MESSAGE.
The SQL precision (10) of input link column P_PROCESS_MESSAGE must be equal to the SQL precision (0) of output link column P_PROCESS_MESSAGE.

Error Message:

STp_Stage,0: Error occurred in call to ORPHCallActivePluginInitialize().

This error is coming for any change in length other than 0.

Thanks

singhald · Post by **singhald** » Wed Nov 04, 2009 4:59 am

Hi

I have done some workaround to resolve this issue. Now I am directly putting job parameters in side the stored procedure stage in Procedure call Syntax box instead of passing from transformer. I am passing only one field from transformer stage, and rest of the fields from job parameters.

For example:

BEGIN #DB_SCHEMA#.PKG_Name.ProcedureName(:1,'#PROCESS_MSG#', '#ERROR_MSG#'); END;

It is working for me without any issue.

Thanks for all your replies.