SQL developer version 21.2 view definition not displaying.view sql don't display definition

Question

I am using sql developer version 21.( Recently installed) I can't access the view sqls/ definition from the view tab. I can accessor see the view text from " details" tab but not from the "Sql" tab. I don't have admin privilege. The same user can view view sqls from sqldeveloper version 18...

Answer 1

In older versions of SQL Developer we had a 'try to generate DDL' method for when DBMS_METADATA.GET_DLL() wasn't available.

This wasn't a maintainable position. The 'internal generator' has multiple issues, and we decided to deprecate it.

In order to see the DDL for an object, you need for the DBMS_METADATA package to be available to your user, for said object.

What SQL Developer runs to get you the DDL for a VIEW, is approx:

SELECT
    dbms_metadata.get_ddl(
        'VIEW',
        :name,
        :owner
    )
FROM
    dual
UNION ALL
SELECT
    dbms_metadata.get_ddl(
        'TRIGGER',
        trigger_name,
        owner
    )
FROM
    dba_triggers
WHERE
        table_owner = :owner
    AND table_name = :name
UNION ALL
SELECT
    dbms_metadata.get_dependent_ddl(
        'COMMENT',
        table_name,
        owner
    )
FROM
    (
        SELECT
            table_name,
            owner
        FROM
            dba_col_comments
        WHERE
                owner = :owner
            AND table_name = :name
            AND comments IS NOT NULL
        UNION
        SELECT
            table_name,
            owner
        FROM
            sys.dba_tab_comments
        WHERE
                owner = :owner
            AND table_name = :name
            AND comments IS NOT NULL
    )

In a development environment, a developer should have full access to their application, and I would extend that to the data dictionary. It's another reason I advocate developers have their own private database (Docker/VirtualBox/Cloud/whatever).

If that fails, consult your data model.

If you don't have a data model, that's another problem.

If that fails, you do have workaround of checking the Details panel for a view to get the underlying SQL.

Answer 2

Just FYI, I searched for an answer to this problem and found no actual solutions. thatjeffsmith was correct that earlier versions of SQLDeveloper do not have this issue or requirement of higher privs to view the SQL tab. However, the link he provided was version 20.4 and it sill did not display the SQL tab correctly. I reverted back to 3.1.07 (which I happened to be using prior to upgrading my laptop) and using the same login to the same instance it does display the SQL for views, full definition, without issue. This is against a 12c Oracle database.