3 Replies Latest reply on Feb 16, 2015 9:45 PM by rp0428

    DDL "omit schema" option should only ignore current schema

    B.Delmée
    B.Delmée Feb 15, 2015 11:04 AM

      otherwise you get misleading statements for synonyms, e.g:

      create synonym XYZ for XYZ;
-- instead of
create synonym target_schema.XYZ for source_schema.XYZ;

      note this seems to work correctly for views, though.

      Unchecking the "schema" box in the DDL options should only result in the omission of the logged-in (or better yet, current) schema for the connection.

      • 655 Views
      • Tags:
        • 1. Re: DDL "omit schema" option should only ignore current schema
          rp0428 Feb 15, 2015 4:26 PM (in response to B.Delmée)

          B.Delmée wrote:

           

          otherwise you get misleading statements for synonyms, e.g:

          note this seems to work correctly for views, though.

          Unchecking the "schema" box in the DDL options should only result in the omission of the logged-in (or better yet, current) schema for the connection.

          Why would you expect  to get 'target_schema.XYZ if you 'omit the schema'?

           

          The 'schema' that is 'omitted' is the schema that OWNS the object. It has NOTHING to do with whether the owning schema is the current schema or not. There is NO facility for arbitrarily seaching for other possible embedded schemas and modifying/omitting them the same way.

           

          Sql Developer uses Oracle's DBMS_METADATA package and that is just how the basic API of that package works. There is ONE schema that owns the object - do you want to 'emit' that schema or not? That is the choice..

          http://docs.oracle.com/cd/E11882_01/server.112/e22490/metadata_api.htm

           

          For example a user defined type can be nested to many levels and include attributes that are types owned by other schemas. So one object type could have 1, 2 or 20 embedded types that are owned by the current schema or by 1, 2 or 20 different owners..

           

          Either don't omit the schema for synonyms or use the REMAP facility discussed in that doc to do any additional remapping yourself. NOTE: - setting a remap filter REQUIRES you to know what schema you actually want to remap.

          1 person found this helpful
          • 2. Re: DDL "omit schema" option should only ignore current schema
            B.Delmée
            B.Delmée Feb 16, 2015 5:23 PM (in response to rp0428)

            Alright, thanks for the explanation, it worked for views just because the source schema is hard-coded in the view text.

            For synonyms (and, as per your explanantion, perhaps other objetc types) this option looks plain dangerous to me as it means you öqz be under the false impression you have captured valid/sufficient DDL to re-create objects , when in fact you may only realise the statements are incompletely qualified when you'll later try to run them.

            • 3. Re: DDL "omit schema" option should only ignore current schema
              rp0428 Feb 16, 2015 9:45 PM (in response to B.Delmée)

               

              Alright, thanks for the explanation, it worked for views just because the source schema is hard-coded in the view text.

              Sorry, no - it APPEARS to work for views because you likely have a very simple view.

               

              Views can be based on MANY tables (in different schemas) and other objects in different schemas including functions, procedures, packages, object types, sequences.

               

              Potentially a view could reference objects in TENS of schemas.

              For synonyms (and, as per your explanantion, perhaps other objetc types) this option looks plain dangerous to me as it means you öqz be under the false impression you have captured valid/sufficient DDL to re-create objects , when in fact you may only realise the statements are incompletely qualified when you'll later try to run them.

              Again, sorry to have to 'burst your bubble' but, in general, there IS NO WAY to extractt ALL of the relevant DDL needed to fully recreate, without error, an arbitrary complex object.

               

              You are generally on safe ground using the DDL extracted to recreate the object in the same schema but for any other schema than the original there are MANY other considerations:

               

              1. Privileges - you don't just need to know the owner and object name but you must have the proper privileges on that object. Also, depending on how/where the object is referenced those privileges may need to be granted DIRECTLY to the user and not thru a role.

               

              2. Synonyms (both PUBLIC and PRIVATE) - synonyms in a different schema might refer to entirely different objects than objects in the original schema.

               

              3. Other objects in other schemas can conflict with objects referenced.j Other users might have an EMP or DEPT table so if you reference a table without using a schema prefix you may not get the same table or even the same object.

               

              NONE OF THE ABOVE really has anything to do with Sql Developer. That tool primarily uses functionality that is part of Oracle