4 Replies Latest reply on Apr 4, 2016 10:37 AM by elmicha

    worksheet can't describe materialized views

    KarstenH-dk

      hi

      SQL*dev 4.1.3.20 can't describe materialized views

       

      mw.jpg

       

      but no problems when you query the materialized view.

       

      When searching for a column using the find function in the columns-tab of the table explorer - you have to be aware of it default is case-sensitive

      I think default case-insensitive would bee the most appropriate, as I do not expect anybody (well maybee 2 or 3) to use lowercase column-names.

       

      regards Karsten

        • 1. Re: worksheet can't describe materialized views
          Gary Graham-Oracle

          For the materialized view issue in 4.1.3, here are a couple of workarounds...

          1. info <object_name>

          2. ddl <object_name> materialized view


          In the current development build, the describe for a materialized view works fine, so no bug needs to be logged.

          • 2. Re: Re: worksheet can't describe materialized views
            elmicha

            What about materialized views on prebuilt table, is that the same issue?

             

            create table test_mv_on_prebuilt_table as

              select 1 as a_column from dual;

             

            create materialized view test_mv_on_prebuilt_table on prebuilt table as

              select 1 as a_column from dual;

             

            desc test_mv_on_prebuilt_table;

             

            doesn't work ("ERROR: object TEST_MV_ON_PREBUILT_TABLE does not exist"), and the SQL tab of the schema browser does not work correctly ("Unable to render MATERIALIZED VIEW DDL for object COCONUT.TEST_MV_ON_PREBUILT_TABLE with DBMS_METADATA attempting internal generator"). "desc test_mv_on_prebuilt_table" works fine in sqlplus.

             

            "info TEST_MV_ON_PREBUILT_TABLE" works, but "ddl TEST_MV_ON_PREBUILT_TABLE MATERIALIZED VIEW" shows nothing.

             

            (This is in 4.1.2.20.)

            • 3. Re: worksheet can't describe materialized views
              Gary Graham-Oracle

              What about materialized views on prebuilt table, is that the same issue?

              Basically the same issue.  But I must have been switching between too many versions of SQL Developer when I reviewed the original post, including the SQLcl command line.  It seems the DDL command worked for SQLcl but not the worksheet. Depending on the version, the worksheet's script output either shows nothing or

              Object table test_mv_on_prebuilt_table not found

              Object materialized view test_mv_on_prebuilt_table not found

              For the SQL tab issue in the object viewer or schema browser, try adding a comment to your materialized view, as in...

              COMMENT ON MATERIALIZED VIEW TEST_MV_ON_PREBUILT_TABLE  IS 'TestCommentOnly';

              Doing this on 4.1.3 permitted normal generation of the SQL via DBMS_METADATA.

              • 4. Re: Re: worksheet can't describe materialized views
                elmicha

                Thank you! With a comment it also works on 4.1.2.20.