3 Replies Latest reply: Mar 20, 2014 11:11 AM by rp0428 RSS

    How can I create the same structured type for a different user ?

    user1577093

      I am using DataModeler Version 4.0.1.836

      We have a generic structured type that is defined in the Data Types Model section with its attribute and methods.

      I also have one instance of this type that is defined in the Physical Model for a specific schema.

      Both of these got created during a previous reverse engineer of the real database.

      There is a requirement to have the same type defined in 2 other schemas.

      When I right click on the "Structred Types" entry in the Physical Model, I had hoped to see a option to create a new subtype, but nothing appears.

      I cannot find any place where I can create additional instances of Structured Types where for specific schemas.

      Anyone know how to do this ?

        • 1. Re: How can I create the same structured type for a different user ?
          David Last-Oracle

          Hi,

           

          To do this you would need to create two more Structured Types in your Data Types Model (which would need to have different names).

          This will result in two more Structured Types in your Physical Model.

           

          You can then update their Name and User properties in the Physical model so that they all have the same name but different Users/Schemas.

           

          However the DDL generated for references to these Structured Types from Tables or other objects would include the relevant schema name (assuming the "Include Schema in DDL" preference was set), so you would need to make sure your Table or Column (or other object) referred to the relevant instance of the (Data Model) Structured Type.

           

           

          An alternative approach might be to generate the DDL for the 3 different schemas separately.  For example you could set the Structured Type's User to SCHEMA1, and generate DDL for SCHEMA1 (and any other Users that use the SCHEMA1 version of the Structured Type), and then do the same for SCHEMA2 and SCHEMA3.

           

           

          Note that there is now a forum specifically for Data Modeler issues: SQL Developer Data Modeler

           

          David

          • 2. Re: How can I create the same structured type for a different user ?
            user1577093

            Thanks a lot for your prompt reply, David !

            In our case, the type is used in a functions that exist in 3 different schemas.
            The code that was written for the functions use the same type name in all 3 schemas, which kinda makes sense since it basically is 3 times the same type, just in different schemas.

            Generating 3 times  in 3 different ddl script is not ideal, so I guess we'll have to do something along the lines of your first option. I'll look into it.

            Thanks again

            • 3. Re: How can I create the same structured type for a different user ?
              rp0428
              Generating 3 times  in 3 different ddl script is not ideal, so I guess we'll have to do something along the lines of your first option.

              No - that is NOT ideal.

               

              Just create that common object in ONE schema and grant privileges to the other schemas to use it. You could also create a PUBLIC synonym for that common object.

               

              That way all references will reference the common object unless you intentionally create an object of the same name in an individual schema.