Dynamic Switching of Schema/Database based on user logged in — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Dynamic Switching of Schema/Database based on user logged in

Received Response
2
Views
10
Comments
1362797
1362797 Rank 2 - Community Beginner
edited Aug 13, 2024 7:09PM in Oracle Analytics Cloud and Server

Hi All,

With the help of Session variables i was able to achieve the dynamic switching of the schemas to which the OBIEE Connects. It is working fine as expected.

However i am getting an error while running an ibot.

View Display Error

Odbc driver returned an error (SQLExecDirectW).

Error Details

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 1017, message: ORA-01017: invalid username/password; logon denied at OCI call OCISessionBegin. [nQSError: 17014] Could not connect to Oracle database. (HY000)

What can be the probable issue?

Regards,

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    What can be the probable issue?

    ^-- The user running the agent isn't a user known to / authorized on the database schema you're dynamically connecting to.

  • 1362797
    1362797 Rank 2 - Community Beginner

    Hi Christian,

    Thanks for the reply. The same user when creating a report in adhoc is working fine as expected but when he is running an agent. It throws the error.

  • 1362797
    1362797 Rank 2 - Community Beginner

    and using the same report in agent which he has created in adhoc

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Have you verified this in detail in the agent configuration, its XML and most importantly the log files of the agent execution  as well as the detailed logging in the NQServer and NQQuery logs?

  • 1362797
    1362797 Rank 2 - Community Beginner

    Hi Christian,


    • When i hardcode one schema in connection pool (not using switching of the schemas) and try to run an ibot it is working fine. No Errors.
    • There are no logs mentioned as when i try to run the ibot it gives me error in case of dynamic switching.


  • 1362797
    1362797 Rank 2 - Community Beginner

    So just wanted to know how ibot/agents uses the connection pool information because in adhoc the report is working fine for the user whereas an agent is not working

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    What are the values of username and password in your connection pool?

    It's maybe :USER and :PASSWORD ?

  • 1362797
    1362797 Rank 2 - Community Beginner

    User Name: VALUEOF(NQ_SESSION.USER )

    Password:  VALUEOF(NQ_SESSION.PASSWORD)

  • Gianni Ceresa
    Gianni Ceresa Mod

    Have a look at this : Agents not working when :USER :PASSWORD used in connection pool

    Same situation, same issue ...

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    So increase the logging. Normal agent operations always produce the logs necessary.

    You must find out what exact values are being used for your variables whne running an agent and through that which schema should be accessed.