SQL Developer Does not Generate DDL on SQL tab

User_X5645

Hi,

I recently upgraded my SQL Developer to Version 20.4.1.407.0006.

After the upgrade, SQL Developer does not generate SQL Script on Views.

Please let me know if I have to make any changes in the Preferences.

thatJeffSmith-Oracle

Well, you could upgrade again to 21.2.1

But also, what version of Oracle Database are you logged into, and are you logged in as the owner of AS_STUDENT_DATA view?

User_X5645

Hi thatJeffSmith-Oracle,

I am not the owner. However, I was able to view the SQL (DDL) in SQL developer Version 19.1.

Also, I can see the code in the Details Tab (TEXT) but not in SQL tab. I tried upgrading it to 21.2.1 with the same problem.

thatJeffSmith-Oracle

In older versions we would try to query the data dictionary to get the object DDL if you didn't have the privs for DBMS_METADATA package to generate DDL from the database itself. We no longer do this, if DBMS_METADATA doesn't work, you don't get any DDL for your object.

It's most likely this is causing your issue - ask your DBA to elevate your account privs.

The TEXT field there in the Details tab gives you the most important part of the DDL behind your view as a workaround.

User_U10LB

We are running into the same problem. It was working in 19.1 but stopped working after we upgraded to 21.4.1.349. Was there a resolution to show the DDL in SQL tab? Can you tell what grants need to be added

thatJeffSmith-Oracle

It may come down to catalog or dba - but really this is a core database issue and not a sql developer one.

ToolTimeTabor

Did you find an answer?

This is not your imagination. We had exactly the same behavior. We reverted to an instance of SQL Developer 18 on the same workstation, same credentials, etc. and it worked properly. So, it is definitely in the configuration or design of SQL Developer 20 to not display the DDL in the SQL tab or generate the DDL as scripts to a worksheet.

ToolTimeTabor

Don't agree on it not being a SQL Developer issue. See my previous comments.

https://community.oracle.com/tech/developers/discussion/comment/16821569#Comment_16821569

thatJeffSmith-Oracle

read what I wrote

In older versions we would try to query the data dictionary to get the object DDL if you didn't have the privs for DBMS_METADATA package to generate DDL from the database itself. We no longer do this, if DBMS_METADATA doesn't work, you don't get any DDL for your object.

ToolTimeTabor

Missed the first comments.

Thanks.

ToolTimeTabor

Jeff Smith,

Alright, we granted that permission to our developer's role.

SQL> grant execute on dbms_metadata to ODI_DEVELOPERS;

Grant succeeded.

Our test account, which is assigned the ODI_DEVELOPERS role, does not seem to be able to generate DDL in version 20 or 21 via the SQL tab, as shown here...

Equally, it does not work using the dbms_metadata.get_ddl in these versions.

Both techniques work in version 18.

Any thoughts?