3 Replies Latest reply: Apr 19, 2013 6:18 PM by Gary Graham-Oracle RSS

    3.1 EA3 - Describe of package body is mangled

    gclough
      If you issue a command such as "desc dbms_job", the output returned appears to be sorted... and that messes with the whole purpose of the output, which is to return the parameters for each of the functions/procedures inside the package:

      desc dbms_job
      PROCEDURE Argument Name Type IN/OUT Default
      ----------------------------- -------------- -------------- ------ -------
      BACKGROUND_PROCESS (FUNCTION) <return value> PL/SQL BOOLEAN OUT
      BROKEN JOB BINARY_INTEGER IN
      BROKEN BROKEN PL/SQL BOOLEAN IN
      BROKEN NEXT_DATE DATE IN DEFAULT
      CHANGE JOB BINARY_INTEGER IN
      CHANGE WHAT VARCHAR2 IN
      CHANGE NEXT_DATE DATE IN
      CHANGE INTERVAL VARCHAR2 IN
      CHANGE INSTANCE BINARY_INTEGER IN DEFAULT
      CHANGE FORCE PL/SQL BOOLEAN IN DEFAULT
      INSTANCE JOB BINARY_INTEGER IN
      INSTANCE INSTANCE BINARY_INTEGER IN
      INSTANCE FORCE PL/SQL BOOLEAN IN DEFAULT
      INTERVAL JOB BINARY_INTEGER IN
      INTERVAL INTERVAL VARCHAR2 IN
      ISUBMIT JOB BINARY_INTEGER IN
      ISUBMIT WHAT VARCHAR2 IN
      ISUBMIT NEXT_DATE DATE IN
      ISUBMIT INTERVAL VARCHAR2 IN DEFAULT
      ISUBMIT NO_PARSE PL/SQL BOOLEAN IN DEFAULT
      IS_JOBQ (FUNCTION) <return value> PL/SQL BOOLEAN OUT
      NEXT_DATE JOB BINARY_INTEGER IN
      NEXT_DATE NEXT_DATE DATE IN
      REMOVE JOB BINARY_INTEGER IN
      RUN JOB BINARY_INTEGER IN
      RUN FORCE PL/SQL BOOLEAN IN DEFAULT
      SUBMIT JOB BINARY_INTEGER OUT
      SUBMIT WHAT VARCHAR2 IN
      SUBMIT NEXT_DATE DATE IN DEFAULT
      SUBMIT INTERVAL VARCHAR2 IN DEFAULT
      SUBMIT NO_PARSE PL/SQL BOOLEAN IN DEFAULT
      SUBMIT INSTANCE BINARY_INTEGER IN DEFAULT
      SUBMIT FORCE PL/SQL BOOLEAN IN DEFAULT
      USER_EXPORT (1) JOB BINARY_INTEGER IN
      USER_EXPORT (1) JOB BINARY_INTEGER IN
      USER_EXPORT (1) MYCALL VARCHAR2 IN/OUT
      USER_EXPORT (1) MYCALL VARCHAR2 IN/OUT
      USER_EXPORT (2) JOB BINARY_INTEGER IN
      USER_EXPORT (2) JOB BINARY_INTEGER IN
      USER_EXPORT (2) MYCALL VARCHAR2 IN/OUT
      USER_EXPORT (2) MYCALL VARCHAR2 IN/OUT
      USER_EXPORT (2) MYINST VARCHAR2 IN/OUT
      WHAT JOB BINARY_INTEGER IN
      WHAT WHAT VARCHAR2 IN


      What I expected is something similar to what SQL*Plus returns, which is:

      SQL> desc dbms_job
      FUNCTION BACKGROUND_PROCESS RETURNS BOOLEAN
      PROCEDURE BROKEN
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER IN
      BROKEN BOOLEAN IN
      NEXT_DATE DATE IN DEFAULT
      PROCEDURE CHANGE
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER IN
      WHAT VARCHAR2 IN
      NEXT_DATE DATE IN
      INTERVAL VARCHAR2 IN
      INSTANCE BINARY_INTEGER IN DEFAULT
      FORCE BOOLEAN IN DEFAULT
      PROCEDURE INSTANCE
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER IN
      INSTANCE BINARY_INTEGER IN
      FORCE BOOLEAN IN DEFAULT
      PROCEDURE INTERVAL
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER IN
      INTERVAL VARCHAR2 IN
      PROCEDURE ISUBMIT
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER IN
      WHAT VARCHAR2 IN
      NEXT_DATE DATE IN
      INTERVAL VARCHAR2 IN DEFAULT
      NO_PARSE BOOLEAN IN DEFAULT
      FUNCTION IS_JOBQ RETURNS BOOLEAN
      PROCEDURE NEXT_DATE
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER IN
      NEXT_DATE DATE IN
      PROCEDURE REMOVE
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER IN
      PROCEDURE RUN
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER IN
      FORCE BOOLEAN IN DEFAULT
      PROCEDURE SUBMIT
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER OUT
      WHAT VARCHAR2 IN
      NEXT_DATE DATE IN DEFAULT
      INTERVAL VARCHAR2 IN DEFAULT
      NO_PARSE BOOLEAN IN DEFAULT
      INSTANCE BINARY_INTEGER IN DEFAULT
      FORCE BOOLEAN IN DEFAULT
      PROCEDURE USER_EXPORT
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER IN
      MYCALL VARCHAR2 IN/OUT
      PROCEDURE USER_EXPORT
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER IN
      MYCALL VARCHAR2 IN/OUT
      MYINST VARCHAR2 IN/OUT
      PROCEDURE WHAT
      Argument Name Type In/Out Default?
      ------------------------------ ----------------------- ------ --------
      JOB BINARY_INTEGER IN
      WHAT VARCHAR2 IN


      This has been the same behaviour in all versions of SQL*Developer since I can remember, but these tests were done with:

      SQL*Developer 3.1 EA3 (v3.1.06.82)
      MacOS v10.6.8
      Oracle Server Enterprise Edition v11.2.0.3.0

      I haven't seen an outstanding bug listed for this, so maybe it's just me... or possibly everyone else occasionally uses SQL*Plus to work around this problem.
        • 1. Re: 3.1 EA3 - Describe of package body is mangled
          gclough
          No answer, so I'll close this thread... still, it would be nice if SQL*Developer could have this functionality. :-(
          • 2. Re: 3.1 EA3 - Describe of package body is mangled
            gclough
            Trying again to close this...
            • 3. Re: 3.1 EA3 - Describe of package body is mangled
              Gary Graham-Oracle
              Hi Greg,

              There is no guarantee that SQL Developer worksheet processing matches every feature/behavior of SQL*Plus. So, in this case:
              1. SQL*Plus produces a parent -> child format report for each function/procedure and its parameters.
              2. SQL Developer produces a relational format report, where the parent is repeated in each report row.

              As for mangled, well, it seems (specifically for 3.2.20.09.87 and dbms_job on a 11.2 database) ...
              1. The parameters for a given function/procedure appear in the same order as SQL*Plus.
              2. If a given function/procedure name is overloaded (multiple parameter signatures), parameter names are incorrectly repeated, once per overload.
              3. Report rows for an overloaded function/procedure name will contain a (1) ... (n) to the right of the name. SQL*Plus format does not need this.
              4. In my environment I see a slight difference in the sort order of function/procedure names containing underscores as compared to SQL*Plus.

              To my eye, the only issue is the incorrect repetition of parameter names in overloaded methods. I will log a bug for that if one does not already exist.

              Regards,
              Gary
              SQL Developer Team

              Edited by: Gary Graham on Apr 19, 2013 4:17 PM
              Bug 16695887 - FORUM: DESC OF AN OVERLOADED METHOD REPEATS PARAMETER NAMES