topic Re: Oracle Datatype Error in Dataflux in SAS Data Management

Oracle Datatype Error in Dataflux

Shakti_Sourav — Wed, 02 Mar 2022 09:54:56 GMT

Hy Team,

I am using SAS Data Management studio env newly, and I am getting datatype error. we are using Oracle database.

I have attached screenshot, Please find it and kindly suggest me how to resolve it ?

Re: Oracle Datatype Error in Dataflux

audrey — Wed, 02 Mar 2022 15:02:26 GMT

Hi,

The error message "Unsupported data type" is coming from the node called "Data Source 1" in your job, so this seems to be while reading data coming from Oracle.

Can you check the said node to see what table it is reading, and what data types the fields have?

Also, from the screenshot, I see you're running this job from Data Management Server. Can you confirm if the same job worked fine when running from Data Management Studio?

Thanks,

Audrey

Re: Oracle Datatype Error in Dataflux

Shakti_Sourav — Thu, 03 Mar 2022 04:53:49 GMT

Hi Audrey,

Good Morning

I have attached Screenshot, please find it

Yes, It's running fine in Data Management Studio, I have attached Screenshot below

Thanks

Shakti

Re: Oracle Datatype Error in Dataflux

audrey — Thu, 03 Mar 2022 08:00:53 GMT

Hello,

I see you have maked my previous message as a solution. Does this mean your problem is fixed? How?

If not, can you send a DESC from Oracle of the input table?

Does the Data Management Server run on Unix? If so, can you look at the odbc.ini file and make sure you have

EnableNcharSupport=1? If it's set to 0, change it, save the file, restart Data Management Server and try again please.

Thanks,
Audrey

Re: Oracle Datatype Error in Dataflux

Shakti_Sourav — Fri, 04 Mar 2022 06:00:56 GMT

Hello,

Good Morning

Clicked by mistake as solution.

I have attached Screenshot from Oracle of the input table, Please find it

Yes, data Management server run in UNIX

Let me check the odbc.ini file.

Thank You

Shakti

Re: Oracle Datatype Error in Dataflux

audrey — Fri, 04 Mar 2022 08:22:31 GMT

In your screenshot, I don't see nchar or nvarchar fields, so this might not be the problem.

You can still try to put EnableNCharSupport=1 and restart Data Management Server, but not sure it'll fix it.

If not, can you send the odbc.ini?

Thanks,

Audrey

Re: Oracle Datatype Error in Dataflux

Shakti_Sourav — Mon, 07 Mar 2022 06:16:18 GMT

Hello Audrey,

I have attached odbc.ini screenshot. Please find it and let me know if any required further details.

Thank You

Shakti

Re: Oracle Datatype Error in Dataflux

audrey — Mon, 07 Mar 2022 09:44:59 GMT

There should be an odbc.ini file in the bin or etc folder of your Data Management Server.

This is the one I would like to see.

Thanks,

Audrey

Re: Oracle Datatype Error in Dataflux

Shakti_Sourav — Tue, 08 Mar 2022 05:59:42 GMT

HI,

The Screen which is shared with you that is from DMS Server under /etc/ directory as you can see in screen shoot.

if any please suggest the path which file you need to review.

Re: Oracle Datatype Error in Dataflux

audrey — Tue, 08 Mar 2022 15:10:00 GMT

Hello,

This is odbc.ini from /etc/
I would like to see odbc.ini from the Data Management Server. This should be under something like opt/SASHome/DataFluxDataManagementServer/<version>/bin/

Thanks,
Audrey

Re: Oracle Datatype Error in Dataflux

Shakti_Sourav — Wed, 09 Mar 2022 10:17:16 GMT

Hi Audrey,

I have attached one screenshot of bin directory and also attached

one text file, which is out put of odbc.ini file which is under /etc/ directory Please find it path mentioned in txt file.

Re: Oracle Datatype Error in Dataflux

RacheLGomez123 — Thu, 03 Nov 2022 08:49:58 GMT

ataFlux software and SAS data sets support different data types. Accordingly, automatic data-type conversions will take place when Data Management Studio software reads or writes SAS data sets. Also, nulls and missing values will be converted to other values. These changes can impact features that depend on particular data types. For example, when a profile reads a SAS data set, SAS fields with a format that applies to datetime values will be reported as datetime. SAS fields with a format that applies to time values will be reported as a time and SAS fields with a format that applies to date values will be reported as a date. As a result, the profile will not calculate some metrics such as Blank Count or Maximum Length for those fields.

The following data-type conversions are made automatically when DataFlux software, such as a data job or a profile, reads SAS data.

For jobs: SAS numeric columns with a format that applies to date, time or datetime values will be converted to a DataFlux field of type date.
For profiles: SAS fields with a format that applies to datetime values will be reported as datetime. SAS fields with a format that applies to time values will be reported as a time and SAS fields with a format that applies to date values will be reported as a date. Other SAS numeric columns will be converted to a DataFlux field of type real.
SAS character columns will be converted to a DataFlux field of type string with the same length as the SAS character column.
Nulls and missing values will be converted to other values, as follows.

SAS missing values will be converted to DataFlux null values. SAS special numeric missing values, whether they are specified with the MISSING statement in a SAS DATA step or with a dot followed by a letter or underscore, are also converted to null values.
DataFlux null values will be converted to SAS missing values.
A DataFlux field of type string that contains a blank will be converted to a SAS character field containing a blank. This blank will be interpreted by SAS as a missing value.

Regards,

Rachel Gomez