7 Replies Latest reply: Sep 27, 2013 8:08 PM by rp0428 RSS

    Stored Procedure display problem

    997928
      I just got the latest version of SQL Developer - 3.2.20.09, but I have been experiencing this problem with the previous version 3.2.10.09 as well, and since it wasn't fixed in the new version I thought I would ask about it.

      I'm using SQL Developer to connect to Oracle 11g.

      When I left-click on a stored procedure in the list of procedures in the Connections tab (the left pane), a tab appears in the larger space on the right that has the procedure's name on the tab and the subtabs Code, Grants, Dependencies, etc - in other words, the Procedure editing tab.

      However, the text that appears in the main editing window is a severely shorted version of the procedure - it simply contains 'create or replace' and nothing else.

      However, I can use both the 'Quick DDL' functionality in SQL Developer and the sqlplus command "select text from user_source where name = 'proc_name' " to verify that the procedure is, in fact, perfectly fine and I can execute it without any issues.

      It seems to me that this must be a bug in the way that SQL Developer is trying to populate the text in the Procedure edit windows, or a bug in some step along that chain. It's especially frustrating in the latest version because if I accidentally left-click the procedure name while trying to right-click it to 'Run' it, the tab with the incomplete text pops up and seems to be auto-compiling, meaning I can't run the program until I re-compile with the complete procedure. But even that is strange, because even though I can't run the program at that point, I can still use 'Quick DDL' to get at the original source.

      Any assistance with this problem would be greatly appreciated!

      Thanks for all your hard work.

      Edited by: 994925 on Mar 20, 2013 8:50 AM - changed tag
        • 1. Re: Stored Procedure display problem
          Gary Graham-Oracle
          Hi,

          If the stored procedure is in the schema pointed to by your connection, then that is a bit strange. If it is in a schema under the Other Users node, then it is a matter of which privileges you have. That is, if you have no privilege to edit another user's procedure, then maybe the code editor will not display the code even if Quick DDL will show it due to some general dictionary viewing privilege you may have. Reading the following may help:
          Can't see package body in SQL Developer version 2.1.1.64

          Regards,
          Gary
          SQL Developer Team
          • 2. Re: Stored Procedure display problem
            997928
            Gary,

            It is indeed in the schema pointed to by my connection.

            In fact, I've noticed something else even weirder. I use this procedure in two database instances, which have the same schema in both instances. I have this problem in one instance but not the other.

            Thanks,
            Nate
            • 3. Re: Stored Procedure display problem
              Gary Graham-Oracle
              Nate,

              OK, so it likely has nothing to do with privileges then. Of course, a user can create many basic types of database object in his own schema without granting any extra, specific privileges.

              Perhaps this could be something similar to the bug discussion (read from the top) in the following thread about an issue with Link Stored Procedures to Files -- not sure if that is still an outstanding bug:
              Re: Serious SQL Developer bugs

              Otherwise, all I can recommend is doing a retest on a clean SQL Developer install. By clean, I mean:
              1. Unzip into an empty directory
              2. Remove or rename the SQL Developer\system<3.n.n.n.n> directory with all your user preference settings.
              3. Do not migrate settings when prompted after starting up the newly installed version for the first time.

              All I can think of for the moment.

              Best Wishes,
              Gary
              • 4. Re: Stored Procedure display problem
                997928
                Gary,

                Thanks for the suggestions!

                Unfortunately, I am seeing this issue from multiple instances of SQL Developer. Several people are accessing/developing in the same schema and use this procedure, and we have all seen this odd behavior from different computers.

                If this problem is related to the bug you linked to, I don't know how, because I'm not doing anything with packages that I know of.

                Thanks again,
                Nate
                • 5. Re: Stored Procedure display problem
                  Gary Graham-Oracle
                  Curiouser and curiouser. If you see nothing in the Message log at that point, you might try seeing if anything comes out in a console window by following the initial steps in the How to collect debug info... instructions:
                  https://forums.oracle.com/forums/ann.jspa?annID=1812

                  Also, although I doubt anything gets locked if two or more users open a stored procedure in write mode at the same time, you could try ticking the
                  Tools -> Preferences -> Code Editor -> Start in Read Only mode
                  checkbox.

                  -Gary
                  • 6. Re: Stored Procedure display problem
                    user11986759

                    Hi, did you find a solution? I had the same issue. My sql developer version is 3.0 - so oracle suggested that the version is the issue. But i don't have issue with other databases. Please share your solution. Thanks

                    • 7. Re: Stored Procedure display problem
                      rp0428

                      Hi, did you find a solution? I had the same issue. My sql developer version is 3.0 - so oracle suggested that the version is the issue. But i don't have issue with other databases. Please share your solution. Thanks

                      If you have a question or issue you need help with then create your own thread and post ALL of the particulars of your use case including: full sql developer version, database name and full version and an EXACT set of steps that you use to reproduce the problem.