Convert VarBinary Datatype to string

sanjay · Post by **sanjay** » Tue Jun 07, 2005 12:02 am

Hi All

I need to convert varBinary datatype to string . i tried to just change output file to varchar but job is aborting . i need to convert Varbinary dataype to use fuction like substring .

Regard
Sanjay

ArndW · Post by **ArndW** » Tue Jun 07, 2005 12:12 am

Declare in the input as VarChar - unless you have NLS enabled.

sanjay · Post by **sanjay** » Tue Jun 07, 2005 12:17 am

Hi Arnd
Thanks Arnd but the problem is i am using sequential file stage .reject of sequential file is stored in VarBinary format by default we can't change so tried to put a transformer or copy stage to convert into varchar but job is aborting

Sanjay

The pro

sanjay wrote:Hi All

I need to convert varBinary datatype to string . i tried to just change output file to varchar but job is aborting . i need to convert Varbinary dataype to use fuction like substring .

Regard
Sanjay

ArndW · Post by **ArndW** » Tue Jun 07, 2005 12:32 am

I should have elaborated, the VarBinary datatype is used to store information in a variable length format that is not to be interpreted as belonging to any given character set. This causes problems, as you have noted, in Px when it tries to do a normal datatype conversion. If you are reading from a database you can declare this input column as VarChar and treat it the same way, if you have NLS enabled then the column must have it deactivated so that DS doesn't try to apply any character conversion to the data.

What I am suggesting is that you "fake" it and use a varchar datatype throughout the job instead of varbinary.

sanjay · Post by **sanjay** » Tue Jun 07, 2005 10:43 am

Hi
i can't avoid varbinary because it creates by default in reject option in sequential file stage . it does n't allow to change the datatype of coumn

Sanjay

ArndW wrote:I should have elaborated, the VarBinary datatype is used to store information in a variable length format that is not to be interpreted as belonging to any given character set. This causes problems, as you have noted, in Px when it tries to do a normal datatype conversion. If you are reading from a database you can declare this input column as VarChar and treat it the same way, if you have NLS enabled then the column must have it deactivated so that DS doesn't try to apply any character conversion to the data.

What I am suggesting is that you "fake" it and use a varchar datatype throughout the job instead of varbinary.

ArndW · Post by **ArndW** » Tue Jun 07, 2005 10:50 am

Sanjay - don't use VarBinary, declare each and every occurrence as VarChar. So even if your table uses VarBinary, declare it as VarChar in your Oracle OCI input. That way you avoid the problems you are describing. As far as DataStage goes, this is transparent (with the proviso of NLS as mentioned earlier)

ray.wurlod · Post by **ray.wurlod** » Wed Jun 08, 2005 2:16 am

You have no control over the metadata in the rejects link of a Sequential File stage. You get one column; the raw data record (which does not match the metadata on the input link) and its type is VarBinary. If you want to change this you need to feed it into a downstream stage in which you can change the data type, for example a Modify or Transformer stage.

thebird · Post by **thebird** » Mon Jun 20, 2005 11:32 am

Sanjay,

You can use a Column importer and a transformer for this purpose. The reject link from the seq file would lead to the column importer where yopu can split the incoming rejected VarBinary row to multiple columns as in the source, but with the datatype as Varchar. And then in the transformer downstream, convert the fields into the required datatypes.

This should work, as we have tried this out in one of our jobs.

Regards,

The Bird.

DSXchange