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
Convert VarBinary Datatype to string
Moderators: chulett, rschirm, roy
Re: Convert VarBinary Datatype to string
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
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
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.
What I am suggesting is that you "fake" it and use a varchar datatype throughout the job instead of varbinary.
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
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.
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)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Convert VarBinary Datatype to string
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.
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.