Oracle Table not visible from Power Designer

[Vipul09]

Hello,

I am facing issue to view Oracle objects from SAP Power Designer even after successful connection. I have created a data source pointing to Oracle Database and tested successfully. I can see certain objects like  User, Role and abstract data by one of the user that I am using but no other objects are visible like table, view, procedure etc.

Canyou please guide me if their is any configuration missing due to which certain objects are not visible on my Power Designer.

SAP Power Designer Version(R) 16.6 SP06 (64 bit) 

Version 16.6.6.0 (5645)

Oracle client - 12c

SAP PowerDesigner 

[Ondrej_Divis · Accepted solution]

Hi,

there are two potential reasons that come into my mind:

• Insufficient DB rights for the DB user you are using to connect PD to DB
• Problem with your PD or Oracle 12c definition file

To find out, where the problem might be, you can do several things:

• Try the exactly same procedure on different computer (and different PD installation)
• Try different DB user on your computer (and your PD installation)

That should tell you whether the problem is on the DB or the PD side.

Another thing you can do is to take the query responsible for filling the list of tables and run it manually in Oracle (using the same DB user of course). You can find that query if you go to menu Tools - Resources... - DBMS... - then doubleclick on the Oracle12c definition file to open it up in its editor and navigate to Script\Objects\Table\SqlListQuery. You just need to slightly adjust the query by replacing inner variables enclosed in % characters or remove some parts of the WHERE clause, but it is quite simple. The data returned should tell you whether the problem is in your DB user or somewhere in PD. Let us know how it goes. I wonder what caused it.

Regards,

Ondrej Divis

[Vipul09 · Accepted solution]

Hi @Ondrej_Divis ,

I tried to ran the query from  Script\Objects\Table\SqlListQuery as suggested by you from Sql Develper with same user but it doesn't give any output.

The query is hitting the table sys.all_all_tables but not returning any output.

I changed the table name sys.all_tables but still no output.

But when I removed the sys and passed only all_tables I am getting the table list as output from the query.

Can you conclude anything here and explain the root cause of the issue ?

Thanks,

Vipul

SAP PowerDesigner 

[Ondrej_Divis · Accepted solution]

First of all, all_tables and all_all_tables are not tables, but views. And obviously, you don`t have problem with PD, but with DB permissions of your DB user. Get read permission for the sys schema. Then try reverse engineering again.

HTH,

Ondrej Divis

[Vipul09 · Accepted solution]

Hi @Ondrej_Divis,

We have given the necessary select permission to the user and we are able to see the tables from PD now.

Thanks for your help.

Vipul