Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VictorCP
Frequent Visitor

ORA-01858: a non-numeric character was found where a numeric was expected

Hi!

I'm trying to connect PowerBI desktop (PBI) to an Oracle table but I get the following error: ORA-01858: a non-numeric character was found where a numeric was expected

 

I have previously connected to other tables in that database without problem. The PBI user has permits to see the table. And I can connect to other tables in the same schema and load the data, but this one new does not load into PBI.

 

I have checked the table in Oracle, and the fields (there are 8 columns, not much) are defined as type number, var_char and date. And I can see the data with the credentials.

 

But when I try to pull the data into a PBI report (in desktop), I get the error.

If I try to pull the data from the main menu > Obtain data, with a simple query like 

select * from myView

 

I get the ORA-01858 error.

 

Some internet posts suggest that there could be a problem with the date type, but if I try to pull just one (numeric, with no null values) column and cast it as text...

Select to_char(myColumn1) from myView

I get the ORA-01858 error. (I have also tried Select myColumn1 from myView, with the same result)

 

If I try to go deeper and use the Power Query advance editor (PBI desktop main menu > edit queries > edit queries > (opens Power query window) > advanced editor ) with something (normal) like this, pulling just one numeric column...

 

let
Origin = Oracle.Database("myDatabase", [HierarchicalNavigation=true]),
step1 = Origin {[Schema="mySchema"]}[Data],
step2 = mySchema{[Name="myView"]}[Data],
step3 = Table.SelectColumns(myView, {"myColumn1"}),
step4 = Table.TransformColumnTypes(myView, {{"myColumn1", type text}})
in
step4 

 

I get the ORA-01858 error again. I have tried this query with other views in the same schema and it works out ok, so I assume it is well written and the problem is in other place.

 

I do not know what is happening. It is just one column with no null values. Is there something wrong with PBI or is it with the database? I think it is not the gateway, since I have other PBI reports working with other views in the same Oracle scheme.

 

Why I can not load data into PowerBI desktop? Where this error cames from?

 

Any help will be much appreciated,

thanks.

 

 

1 ACCEPTED SOLUTION
VictorCP
Frequent Visitor

Hi past me!

 

the solution for this was so simple as dumb.

 

I was not connecting to the data but to a table scheme (I knew that), and therefore PowerBI was reading all the instructions (selects, joins, filters...) to construct the view with the data. AND THERE WERE SOME HARDCODED DATES like  ...>= ‘01-feb-2010’...

and this is where the error comes from. PowerBI didn't liked it, and it showed the error ORA-01858: a non-numeric character was found where a numeric was expected.

The error was not with the data in PowerBI but with PowerBI trying to get the data.

 

Once the queries were changed, the problem was solved.

Thanks to all involved in this.

View solution in original post

4 REPLIES 4
VictorCP
Frequent Visitor

Hi past me!

 

the solution for this was so simple as dumb.

 

I was not connecting to the data but to a table scheme (I knew that), and therefore PowerBI was reading all the instructions (selects, joins, filters...) to construct the view with the data. AND THERE WERE SOME HARDCODED DATES like  ...>= ‘01-feb-2010’...

and this is where the error comes from. PowerBI didn't liked it, and it showed the error ORA-01858: a non-numeric character was found where a numeric was expected.

The error was not with the data in PowerBI but with PowerBI trying to get the data.

 

Once the queries were changed, the problem was solved.

Thanks to all involved in this.

VictorCP
Frequent Visitor

Ok, new idea (still not working when I get the data from the database).

 

I have checked in the database all the fields looking for strange characters and there are none except nulls. So everything looks ok.

Then I downloaded all the data from the database and import it as an Excel file into PowerBI desktop (it is not much, about 10k rows and 14 columns). It works, and recognizes all fields and columns. Zero errors.

 

I only get the error when I pull the data from the database. Is this a permit issue? I write correctly the credentials in PowerBI...

 

Any help?

Hi @VictorCP ,

 

Please refer to the links.

Solved: Error: ORA-01858 a non-numeric character was found... - Alteryx Community

Error running a Cognos BI extract against an Oracle source: ORA-01858 (ibm.com)

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lionel,

 

thanks for the ideas.

For the first one, I tried to cast my date column in the following ways, and I always get the ORA-01858 error:

select to_date(INITIAL_TIME,'YYYY-MM-DD HH24:MI:SS') from MYTABLE;
select to_date(INITIAL_TIME, 'DD/MM/YYYY HH24:MI:SS') from MYTABLE;
select to_timestamp(INITIAL_TIME, 'YYYY-MM-DD HH24:MI:SS') from MYTABLE;
select to_timestamp(INITIAL_TIME, 'DD/MM/YYYY HH24:MI:SS') from MYTABLE;
select CAST(INITIAL_TIME AS TIMESTAMP) from MYTABLE;
select to_char(CAST(INITIAL_TIME AS TIMESTAMP), 'DD/MM/YYYY HH24:MI:SS') from MYTABLE;
select to_timestamp(to_char(CAST(INITIAL_TIME AS TIMESTAMP), 'DD/MM/YYYY HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS') from MYTABLE;

 

 

For the second idea, I have no clue where to modify that. I ended up here https://docs.microsoft.com/bs-cyrl-ba/sql/odbc/microsoft/setting-the-date-format-on-connection?view=...  but I do not know where to modify it. 
Also, it says that is not supported https://community.powerbi.com/t5/Desktop/Oracle-Alter-Session-in-ODBC-query/m-p/839720

 

Does I need to download another version of the ODBC driver?

 

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.