This discussion is archived
3 Replies Latest reply: Apr 19, 2013 4:18 PM by Gary Graham RSS

3.1 EA3 - Describe of package body is mangled

gclough Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Trying again to close this...
  • 3. Re: 3.1 EA3 - Describe of package body is mangled
    Gary Graham Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points