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

Oracle BI Administration Tool - OCI problems

Received Response
42
Views
17
Comments
2»

Answers

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    If the user is not the owner, then as Gianni mentioned, have you checked the fully qualified table name in connection pool setting? Or if not, are you using public synonyms?

    pastedImage_0.png

  • Gianni Ceresa
    Gianni Ceresa Mod

    I never talked about privileges, but fully qualified name which is: schema.tablename vs tablename (without the schema).

    An option in the connection pool which by default isn't checked (it's a checkbox) and you must enable to make it works.

    That's a simple SQL thing: you can't expect the database scans all the schemas you have some privileges to find out if by mistake there is an object with a matching name, also because ... what happen if same name in 2 schemas?

    That's why there is that checkbox which, IMHO, must always be enabled because it doesn't cost anything but prevent so many issues ....

  • Miguel Ferrufino
    Miguel Ferrufino Rank 3 - Community Apprentice

    Gianni Ceresa

    Thank you for your help again.

    I already checked the checkbox "checkbox to use fully qualified names" and I still have the same error, BUT as I said befeore, the user that I am using is not the owner of the table so, if I connect to source through sqlplus with this user I have to use schema.table_name  to be able to see the data, so I created a synonym for a table to validate if it works and it does. BUT I think that it is not the best solution. Do I have to create synosyms for every table when I use another user who is not owner of table?? 

  • Ebin Cherian
    Ebin Cherian Rank 5 - Community Champion

    Hi,

    Can you try giving the "SELECT" privilege to user used in connection pool ?

    GRANT SELECT ON owner.table TO user;

  • Gianni Ceresa
    Gianni Ceresa Mod 
    3057520 wrote:so I created a synonym for a table to validate if it works and it does. BUT I think that it is not the best solution. Do I have to create synosyms for every table when I use another user who is not owner of table??

    Why did you create a synonym?

    It's useless, when checking the "fully qualified name" checkbox OBIEE uses the schema.table_name syntax in all the queries, so not a problem if your connection pool uses a different user, it will just works.

    Get rid of the synonym, do not start having weird things like that in your database or you will end up with a messy DB you will not manage to keep clean and with errors because using the wrong object at some point.

    Just to know: what is your DB? Oracle but which version?

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    Silly question, did you confirm that the db you are connecting to, using sqlplus and the one in the admin tool connection pool are the same?

  • Miguel Ferrufino
    Miguel Ferrufino Rank 3 - Community Apprentice

    Thank you Gianni Ceresa.

    I created a new RPD and checked the "fully qualified name" checkbox and finally it works perfectly without synonyms.

    FYI, my db source is Oracle 11g (11.2.0.4).

    THANK YOU!! USEFUL SUPPORT.

    Best regards,