DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: What SQL is used by the PL/SQL "Desc" command? (for variables/constants in a package)

  1. #1
    Join Date
    Jun 2005
    Posts
    1

    What SQL is used by the PL/SQL "Desc" command? (for variables/constants in a package)

    I have a package called "test_1" generated using the PL/SQL:

    create or replace package test_1 as
    v_Var1 varchar2(255) := lpad('@', 255, '@');
    v_Var2 varchar2(10);

    c_Const1 constant pls_integer := 1;
    c_Const2 constant varchar2(1) := 'T';
    end;
    /

    When I type "DESC test_1" into PL/SQL I get:

    SQL> desc test_1
    Element Type
    -------- --------
    V_VAR1 VARIABLE
    V_VAR2 VARIABLE
    C_CONST1 CONSTANT
    C_CONST2 CONSTANT

    SQL>

    I am writing some SQL that is supposed to extract variable and constant package information, the DESC command does exactly what I want ... How do I do this in SQL? Ideally the output would be something like:

    Package Element Type
    ---------- -------- --------
    TEST_1 V_VAR1 VARIABLE
    TEST_1 V_VAR2 VARIABLE
    TEST_1 C_CONST1 CONSTANT
    TEST_1 C_CONST2 CONSTANT

    I'm sure the information is in the USER_* views, but which one is a mystery to me ...

    It is the description of the VARIABLES and CONSTANTS I'm after (not the functions/ procedures ... the user_arguments/ user_procedures views are available for that).

    Basically I'm developing a solution that will get all the objects in the database, compare them to all the other objects in the database and find out exactly who uses what (and there can be bits of dynamic SQL in here which is why I can't rely on the system views).

    I'm getting a bit desperate for a solution to this ... ;-)

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (I think you want DBA/ALL/USER_ARGUMENTS)

    OOPS - consider this deleted. It wasn't what you were asking.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    There was a similar post already. Have you looked at dbms_metadata ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width