10 Replies Latest reply on Sep 20, 2014 6:40 PM by htmlDB

    Schema Name option for PL/SQL objects in 4.0.3.16

    htmlDB

      Just downloaded SQL Dev 4.0.3.16 and I am looking for the option (if it exists) to include the schema name when editing PL/SQL objects. High priority for me.

       

       

      Thanks

        • 1. Re: Schema Name option for PL/SQL objects in 4.0.3.16
          thatJeffSmith-Oracle

          No such option.

           

          But if you export your pl/sql source, you have the option of including the schema name in the generated code.

          • 2. Re: Schema Name option for PL/SQL objects in 4.0.3.16

            What do you mean by 'include the schema name when editing'?


            • 3. Re: Schema Name option for PL/SQL objects in 4.0.3.16
              htmlDB

              in many of the databases I work on I have multiple schemas that I work in all day long.

               

              so lets say I have the following schemas

               

                DEV

                AR

                AP

                GL

                WIP

               

              open SQL Developer log into the DEV schema

               

              open the Other Users node.

               

              open the AR node

               

              open the Packages node

               

              click on one of the packages

               

              click the open body icon

               

              make a quick edit and then compile...

               

              WHOOPS. that got compiled in the DEV schema!!!! 

               

               

              Get it??

              • 4. Re: Schema Name option for PL/SQL objects in 4.0.3.16
                htmlDB

                or a more realistic scenario.

                 

                identify that a change needs to be made in CRM_INTERFACE_PKG.

                 

                Paste CRM_INTERFACE_PKG into a SQL worksheet.

                 

                Click <SHIFT>+F4

                 

                The object PL/SQL opens in an editor.

                 

                Make changes and compile.....

                 

                test to see if the changes were effective...

                 

                no change!! look again and realize that the object got compiled in DEV instead of.... HMM what schema DID that come from???

                • 5. Re: Schema Name option for PL/SQL objects in 4.0.3.16
                  thatJeffSmith-Oracle

                  No, I don't get that.

                   

                  Compiling objects from under the 'other users' node in the connection tree respects/honors said schema - it won't compile to the local/connection schema.

                  • 6. Re: Schema Name option for PL/SQL objects in 4.0.3.16
                    thatJeffSmith-Oracle

                    if you paste the code into a sql worksheet - you're on your own, you'll need to make sure you're compiling the code where you want it to, just like in SQL*Plus.

                    • 7. Re: Schema Name option for PL/SQL objects in 4.0.3.16
                      htmlDB

                      pasting the object name into the sql worksheet and clicking <shift>+F4 is the most efficient method of opening an object for editing. Why would you refuse to add an option to append the schema name to the object?! I don't understand your reluctance to do it.

                       

                      As I stated before, I will continue to use TOAD as my development tool until this feature is added, even though I would prefer to use SQL Dev, this is seriously the only reason I do not.

                       

                      Thanks,

                       

                      Dan

                      • 8. Re: Schema Name option for PL/SQL objects in 4.0.3.16
                        identify that a change needs to be made in CRM_INTERFACE_PKG.

                         

                        Paste CRM_INTERFACE_PKG into a SQL worksheet.

                         

                        Click <SHIFT>+F4

                         

                        The object PL/SQL opens in an editor.

                         

                        Ok - I can't reproduce that in sql developer 4.0.2.15.21 for the SCOTT schem

                         

                        1. Scott does NOT own a procedure named 'CREATETAB'.

                        2. HR DOES own a procedure with that name

                        3. Scott has CREATE ANY PROCEDURE privilege

                         

                        If I paste 'CREATETAB' into a sql worksheet for a SCOTT connection I get a 'No object found' dialog - it does NOT find an object of that name in the SCOTT schema so it does NOT open it in an editor. So there is NOTHING to change or compile.

                         

                        If I do that in an HR sql worksheet it opens the code in an editor. Changing the procedure and compiling saves the changes to the HR procedure but does NOT add the procedure to the SCOTT schema.

                         

                        One difference:

                         

                        1. In Sql Developer the code does NOT have the schema name appended to the procedure name

                        2. In Toad the code DOES have the schema name appended to the procedure name

                         

                        A couple of points re the metadata for code

                         

                        1. Oracle (e.g. ALL_SOURCE) does NOT store the schema name as part of the object name. It also does NOT store the words CREATE or the words CREATE OR REPLACE.

                         

                        2. Using DBMS_METADATA to extract the DDL, by default, adds CREATE OR REPLACE and adds the schema name to the procedure name

                         

                        3. Sql developer does add CREATE OR REPLACE but does NOT add the schema name to the procedure name.

                         

                        4. Toad adds both CREATE OR REPLACE and the schema name to the procedure name

                         

                        It appears that sql developer is setting the SCHEMA filter to FALSE before calling DBMS_METADATA. It does this even though I have the 'show schema' flag set in the export options.

                         

                        Summary -

                         

                        I can't reproduce populating another schema's code into a SCOTT schema by using shift F4.

                         

                        But I agree that there seems to be no good reason why the schema name should not be prepended to all code extracted unless a user setting for 'schema' is set otherwise. There is such a setting when exporting code so those settings could likely be easily used for the sql editors.

                         

                        That is a call the sql development team needs to make and I can't reproduce any scenario where there is any issue to resolve. As Jeff said if you paste code then you are presumed to know where you are pasting it. If extracted code (e. g. a sql editor) alwalys included the schema prefix you would then have issues if you wanted to paste that code into a sql editor for another schema since it would then have the wrong schema name. Then the complaint would be that you have to manually remove the schema name.

                         

                        All in all I think it is working in the most general way possible.

                        • 9. Re: Schema Name option for PL/SQL objects in 4.0.3.16
                          thatJeffSmith-Oracle

                          if you see us compiling to the wrong schema in the procedure editor, please let us know. That would be a serious bug.

                           

                          if you want the code out to worksheet to code manually, use the export feature - it will grab the schema on demand. Or add it yourself and YOU take control of exactly what gets executed, when, and where.

                          • 10. Re: Schema Name option for PL/SQL objects in 4.0.3.16
                            htmlDB

                            PLEASE PLEASE PLEASE.... Pretty please add an option to append the schema name to PL/SQL objects. I don't want to argue about it, just a feature that would be very helpful to me (and to others I would think)

                             

                            Thank you for your consideration in this.

                             

                             

                            Dan