1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

pocedure validity issue reg

Discussion in 'SQL PL/SQL' started by laxman, Mar 6, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear all,
    I have created a package and successfully compiled it but i am unable see the status of all the procedure belongs to this pacakage.

    Code (SQL):
    sql>CREATE OR REPLACE PACKAGE close_dup
    IS



    PROCEDURE close_dup_req (
    IN_FilePath varchar2,
    IN_FileName varchar2,
    IN_GenReqID BOOLEAN
    );

    PROCEDURE Genrepclosereq (
    IN_FilePath varchar2,
    IN_FileName varchar2,
    IN_Status NUMBER,
    IN_ReqCount  NUMBER,
    IN_ReqIDList    varchar2
    );
    END;

    package created

    // Also i have compiled the Package body successfully.

    SQL> SELECT object_name,STATUS FROM all_objects WHERE object_name='CLOSE_DUP';

    OBJECT_NAME  STATUS
    ------------------------------ -------
    CLOSE_DUP    VALID
    CLOSE_DUP    VALID
    but when i am trying to get the status of one of the procedure as below:
    Code (SQL):

    SQL> SELECT object_name,STATUS FROM all_objects WHERE object_name='CLOSE_DUP_REQ';

    no ROWS selected
     
    What could be the problem?

    Thanks n regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The 'problem' is your misconception that a packaged procedure has visibilkity outside of the package; it does not. Since the package is valid all procedures and functions inside that package are valid. Describe the package and you'll see the procedures:

    Code (SQL):
    DESC close_dup
    They are NOT available outside of the package and will not be listed as stand-alone objects.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Now i understood clearly sir,thanks a lot but in case i want to retrive the name of the procedure belong to this package then in which way i can fire a query to all_objects or user_objects to get the information .

    Kindly aplozise if my points are not clear to you.

    Regards
    Laxman
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot. You CAN describe the package as I have told you before, but there is no information in all_objects or dba_objects to report on procedures located in packages. You could also look in ALL_SOURCE or DBA_SOURCE to see the code which generated the package. Your best option is to describe the package in question.
     
  5. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    fine sir, i should follow this approach. thanks a lot once again

    Regards
    Laxman