ORA-00900: Error with opening a session in a SQL statement for Oracle database

[boeschr]

Hi all

Maybe someone can help me. 

We have a case where we want to access data from an Oracle DB where we have to open a session before we can execute our query. While this works in other tools, Power BI seems to have a problem (see screenshot)

Our first idea was to create a new query with just the part to open the session and a second one to get the data from the db. This only worked from time to time, when the query to open the session was executed before the second one. Unfortunately, we where not able to solve this problem.

Then we tried to create functions and call them in the right sequence order (see code below). But this dind't work as well.

let
    openSession = () => Oracle.Database("XYZ", [HierarchicalNavigation=true, Query="begin e.env_session_intf#.open_session; end;"]),
    Source = ()  => Oracle.Database("Database", [HierarchicalNavigation=true, Query= "select * from XYZ"]),
    Load = Function.InvokeAfter(openSession, #duration(0,0,0,1)),
    Delay = Function.InvokeAfter(Source, #duration(0,0,0,59)),
    #"Renamed Columns" = Table.RenameColumns(Delay,{{"NAME", "Name"}, {"COMPONENT", "Component"}, {"RELEASE", "Release"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Name", type text}, {"Component", type text}, {"Release", type text}, {"Change ID", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Name",  "Component", "Release", "Change ID", "ID"})
in
    #"Reordered Columns"

Any suggestions how this could work? Maybe someone already has experience with this problem.

Thanks a lot!

[Anonymous]

Hi @boeschr ,

The error message "ORA-00900" indicates that there is a syntax error in the SQL statement. One possible cause of this error is that the Oracle client either isn't installed or isn't configured properly. If it's installed, verify that the tnsnames.ora file is properly configured and you're using the proper net_service_name. You also need to make sure that the net_service_name is the same between the machine that uses Power BI Desktop and the machine that runs the gateway. It is advised that for Oracle configuration errors, the customer should work with their oracle support to understand and configure correctly.

Best Regards,

Stephen Tao

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

[NeilChapdelain]

Thanks for the advice, I will check it.

Spoiler

Thanks for the advice, I will check it. If you're in need of thesis writing help and came across the website https://studyclerk.com/thesis-writing-help while searching on Google, it's essential to read evaluations and reviews before using their services. Reviews can offer valuable insights into the website's credibility, reliability, and quality of its writing assistance.