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!

about packges

Discussion in 'SQL PL/SQL' started by madu, Oct 17, 2012.

  1. madu

    madu Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    hi,
    iam new child in oracle,
    i want to know how to hide one procedure/function in a package ?
    plz help me.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> CREATE OR REPLACE package tab_col AS
      2
      3     TYPE refcursor IS REF cursor;
      4
      5     PROCEDURE get_tab_from_col (p_col IN varchar2, p_result IN OUT refcursor);
      6
      7  END;
      8  /
     
    Package created.
     
    SQL>
    SQL> CREATE OR REPLACE package body tab_col AS
      2
      3     PROCEDURE get_tab_from_col (p_col IN varchar2, p_result IN OUT refcursor)
      4     AS
      5             v_query varchar2(100);
      6
      7     BEGIN
      8             v_query := 'select table_name, column_name from dba_tab_columns where column_name = '''||p_col||'''';
      9
     10             OPEN p_result FOR v_query;
     11
     12     END;
     13  END;
     14  /
     
    Package body created.
     
    SQL>
    SQL> variable t_result refcursor
    SQL>
    SQL> --
    SQL> -- Return one row
    SQL> --
    SQL> EXEC tab_col.get_tab_from_col('DUMMY', :t_result)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> print t_result
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------

    DUAL                           DUMMY
    SQL>
    SQL> --
    SQL> -- Return more than one row
    SQL> --
    SQL> EXEC tab_col.get_tab_from_col('COLUMN_NAME', :t_result)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> print t_result
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    CDC_SUBSCRIBED_COLUMNS$        COLUMN_NAME
    CDC_CHANGE_COLUMNS$            COLUMN_NAME
    APPLY$_CONF_HDLR_COLUMNS       COLUMN_NAME
    STREAMS$_DEST_OBJ_COLS         COLUMN_NAME
    DST$AFFECTED_TABLES            COLUMN_NAME
    DST$ERROR_TABLE                COLUMN_NAME
    V_$INDEXED_FIXED_COLUMN        COLUMN_NAME
    GV_$INDEXED_FIXED_COLUMN       COLUMN_NAME
    USER_CONS_COLUMNS              COLUMN_NAME
    ALL_CONS_COLUMNS               COLUMN_NAME
    DBA_CONS_COLUMNS               COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    USER_LOG_GROUP_COLUMNS         COLUMN_NAME
    ALL_LOG_GROUP_COLUMNS          COLUMN_NAME
    DBA_LOG_GROUP_COLUMNS          COLUMN_NAME
    COLUMN_PRIVILEGES              COLUMN_NAME
    USER_LOBS                      COLUMN_NAME
    ALL_LOBS                       COLUMN_NAME
    DBA_LOBS                       COLUMN_NAME
    USER_COL_COMMENTS              COLUMN_NAME
    ALL_COL_COMMENTS               COLUMN_NAME
    DBA_COL_COMMENTS               COLUMN_NAME
    USER_COL_PRIVS                 COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    ALL_COL_PRIVS                  COLUMN_NAME
    DBA_COL_PRIVS                  COLUMN_NAME
    USER_COL_PRIVS_MADE            COLUMN_NAME
    ALL_COL_PRIVS_MADE             COLUMN_NAME
    USER_COL_PRIVS_RECD            COLUMN_NAME
    ALL_COL_PRIVS_RECD             COLUMN_NAME
    DBA_ENCRYPTED_COLUMNS          COLUMN_NAME
    ALL_ENCRYPTED_COLUMNS          COLUMN_NAME
    USER_ENCRYPTED_COLUMNS         COLUMN_NAME
    USER_IND_COLUMNS               COLUMN_NAME
    ALL_IND_COLUMNS                COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    DBA_IND_COLUMNS                COLUMN_NAME
    USER_TAB_COLS                  COLUMN_NAME
    ALL_TAB_COLS                   COLUMN_NAME
    DBA_TAB_COLS                   COLUMN_NAME
    USER_TAB_COLUMNS               COLUMN_NAME
    ALL_TAB_COLUMNS                COLUMN_NAME
    DBA_TAB_COLUMNS                COLUMN_NAME
    USER_UPDATABLE_COLUMNS         COLUMN_NAME
    ALL_UPDATABLE_COLUMNS          COLUMN_NAME
    DBA_UPDATABLE_COLUMNS          COLUMN_NAME
    DICT_COLUMNS                   COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    ROLE_TAB_PRIVS                 COLUMN_NAME
    DBA_OLDIMAGE_COLUMNS           COLUMN_NAME
    USER_OLDIMAGE_COLUMNS          COLUMN_NAME
    USER_NESTED_TABLE_COLS         COLUMN_NAME
    ALL_NESTED_TABLE_COLS          COLUMN_NAME
    DBA_NESTED_TABLE_COLS          COLUMN_NAME
    USER_REFS                      COLUMN_NAME
    ALL_REFS                       COLUMN_NAME
    DBA_REFS                       COLUMN_NAME
    USER_OBJ_COLATTRS              COLUMN_NAME
    ALL_OBJ_COLATTRS               COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    DBA_OBJ_COLATTRS               COLUMN_NAME
    USER_CONS_OBJ_COLUMNS          COLUMN_NAME
    ALL_CONS_OBJ_COLUMNS           COLUMN_NAME
    DBA_CONS_OBJ_COLUMNS           COLUMN_NAME
    USER_PART_KEY_COLUMNS          COLUMN_NAME
    ALL_PART_KEY_COLUMNS           COLUMN_NAME
    DBA_PART_KEY_COLUMNS           COLUMN_NAME
    USER_SUBPART_KEY_COLUMNS       COLUMN_NAME
    ALL_SUBPART_KEY_COLUMNS        COLUMN_NAME
    DBA_SUBPART_KEY_COLUMNS        COLUMN_NAME
    USER_PART_LOBS                 COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    ALL_PART_LOBS                  COLUMN_NAME
    DBA_PART_LOBS                  COLUMN_NAME
    USER_LOB_PARTITIONS            COLUMN_NAME
    ALL_LOB_PARTITIONS             COLUMN_NAME
    DBA_LOB_PARTITIONS             COLUMN_NAME
    USER_LOB_SUBPARTITIONS         COLUMN_NAME
    ALL_LOB_SUBPARTITIONS          COLUMN_NAME
    DBA_LOB_SUBPARTITIONS          COLUMN_NAME
    DBA_PUBLISHED_COLUMNS          COLUMN_NAME
    USER_PUBLISHED_COLUMNS         COLUMN_NAME
    DBA_SUBSCRIBED_COLUMNS         COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    USER_SUBSCRIBED_COLUMNS        COLUMN_NAME
    LOADER_OID_INFO                COLUMN_NAME
    USER_TAB_COL_STATISTICS        COLUMN_NAME
    ALL_TAB_COL_STATISTICS         COLUMN_NAME
    DBA_TAB_COL_STATISTICS         COLUMN_NAME
    USER_TAB_HISTOGRAMS            COLUMN_NAME
    ALL_TAB_HISTOGRAMS             COLUMN_NAME
    DBA_TAB_HISTOGRAMS             COLUMN_NAME
    USER_PART_COL_STATISTICS       COLUMN_NAME
    ALL_PART_COL_STATISTICS        COLUMN_NAME
    DBA_PART_COL_STATISTICS        COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    USER_PART_HISTOGRAMS           COLUMN_NAME
    ALL_PART_HISTOGRAMS            COLUMN_NAME
    DBA_PART_HISTOGRAMS            COLUMN_NAME
    USER_SUBPART_COL_STATISTICS    COLUMN_NAME
    ALL_SUBPART_COL_STATISTICS     COLUMN_NAME
    DBA_SUBPART_COL_STATISTICS     COLUMN_NAME
    USER_SUBPART_HISTOGRAMS        COLUMN_NAME
    ALL_SUBPART_HISTOGRAMS         COLUMN_NAME
    DBA_SUBPART_HISTOGRAMS         COLUMN_NAME
    DBA_ASSOCIATIONS               COLUMN_NAME
    USER_ASSOCIATIONS              COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    ALL_ASSOCIATIONS               COLUMN_NAME
    DBA_USTATS                     COLUMN_NAME
    USER_USTATS                    COLUMN_NAME
    ALL_USTATS                     COLUMN_NAME
    ALL_COL_PENDING_STATS          COLUMN_NAME
    DBA_COL_PENDING_STATS          COLUMN_NAME
    USER_COL_PENDING_STATS         COLUMN_NAME
    ALL_TAB_HISTGRM_PENDING_STATS  COLUMN_NAME
    DBA_TAB_HISTGRM_PENDING_STATS  COLUMN_NAME
    USER_TAB_HISTGRM_PENDING_STATS COLUMN_NAME
    DBA_DIM_LEVEL_KEY              COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    ALL_DIM_LEVEL_KEY              COLUMN_NAME
    USER_DIM_LEVEL_KEY             COLUMN_NAME
    DBA_DIM_ATTRIBUTES             COLUMN_NAME
    ALL_DIM_ATTRIBUTES             COLUMN_NAME
    USER_DIM_ATTRIBUTES            COLUMN_NAME
    IMP_LOB_INFO                   COLUMN_NAME
    EXU9MVLCDCSC                   COLUMN_NAME
    EXU9MVLCDCCC                   COLUMN_NAME
    USER_TRIGGERS                  COLUMN_NAME
    ALL_TRIGGERS                   COLUMN_NAME
    DBA_TRIGGERS                   COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    USER_TRIGGER_COLS              COLUMN_NAME
    ALL_TRIGGER_COLS               COLUMN_NAME
    DBA_TRIGGER_COLS               COLUMN_NAME
    DBA_MVIEW_LOG_FILTER_COLS      COLUMN_NAME
    REPCAT$_GROUPED_COLUMN         COLUMN_NAME
    DBA_REPGROUPED_COLUMN          COLUMN_NAME
    ALL_REPGROUPED_COLUMN          COLUMN_NAME
    USER_REPGROUPED_COLUMN         COLUMN_NAME
    _ALL_REPGROUPED_COLUMN         COLUMN_NAME
    REPCAT$_AUDIT_COLUMN           COLUMN_NAME
    DBA_REPAUDIT_COLUMN            COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    ALL_REPAUDIT_COLUMN            COLUMN_NAME
    USER_REPAUDIT_COLUMN           COLUMN_NAME
    LOGMNR_TAB_COLS_SUPPORT        COLUMN_NAME
    LOGMNR_TAB_COLS_CAT_SUPPORT    COLUMN_NAME
    DBA_CUBE_VIEW_COLUMNS          COLUMN_NAME
    ALL_CUBE_VIEW_COLUMNS          COLUMN_NAME
    USER_CUBE_VIEW_COLUMNS         COLUMN_NAME
    DBA_CUBE_DIM_VIEW_COLUMNS      COLUMN_NAME
    ALL_CUBE_DIM_VIEW_COLUMNS      COLUMN_NAME
    USER_CUBE_DIM_VIEW_COLUMNS     COLUMN_NAME
    DBA_CUBE_HIER_VIEW_COLUMNS     COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    ALL_CUBE_HIER_VIEW_COLUMNS     COLUMN_NAME
    USER_CUBE_HIER_VIEW_COLUMNS    COLUMN_NAME
    LOGSTDBY_UNSUPPORT_TAB_11_2    COLUMN_NAME
    LOGSTDBY_UNSUPPORT_TAB_11_1    COLUMN_NAME
    LOGSTDBY_UNSUPPORT_TAB_10_2    COLUMN_NAME
    LOGSTDBY_UNSUPPORT_TAB_10_1    COLUMN_NAME
    DBA_LOGSTDBY_UNSUPPORTED       COLUMN_NAME
    DBA_ADVISOR_SQLA_COLVOL        COLUMN_NAME
    USER_ADVISOR_SQLA_COLVOL       COLUMN_NAME
    DBA_ADVISOR_SQLW_COLVOL        COLUMN_NAME
    USER_ADVISOR_SQLW_COLVOL       COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    _DBA_APPLY_CONSTRAINT_COLUMNS  COLUMN_NAME
    DBA_APPLY_KEY_COLUMNS          COLUMN_NAME
    ALL_APPLY_KEY_COLUMNS          COLUMN_NAME
    _DBA_APPLY_CONF_HDLR_COLUMNS   COLUMN_NAME
    DBA_APPLY_CONFLICT_COLUMNS     COLUMN_NAME
    ALL_APPLY_CONFLICT_COLUMNS     COLUMN_NAME
    _DBA_APPLY_TABLE_COLUMNS       COLUMN_NAME
    _DBA_APPLY_TABLE_COLUMNS_H     COLUMN_NAME
    DBA_APPLY_TABLE_COLUMNS        COLUMN_NAME
    ALL_APPLY_TABLE_COLUMNS        COLUMN_NAME
    STREAMS$_INTERNAL_TRANSFORM    COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    _DBA_STREAMS_TRANSFORMATIONS   COLUMN_NAME
    DBA_STREAMS_TRANSFORMATIONS    COLUMN_NAME
    DBA_STREAMS_DELETE_COLUMN      COLUMN_NAME
    DBA_STREAMS_KEEP_COLUMNS       COLUMN_NAME
    DBA_STREAMS_ADD_COLUMN         COLUMN_NAME
    DBA_COMPARISON_COLUMNS         COLUMN_NAME
    USER_COMPARISON_COLUMNS        COLUMN_NAME
    DBA_STREAMS_COLUMNS            COLUMN_NAME
    ALL_STREAMS_COLUMNS            COLUMN_NAME
    WM$NESTED_COLUMNS_TABLE        COLUMN_NAME
    USER_WM_IND_COLUMNS            COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    ALL_WM_IND_COLUMNS             COLUMN_NAME
    WM$CONS_COLUMNS                COLUMN_NAME
    USER_WM_CONS_COLUMNS           COLUMN_NAME
    ALL_WM_CONS_COLUMNS            COLUMN_NAME
    DBA_XML_TAB_COLS               COLUMN_NAME
    ALL_XML_TAB_COLS               COLUMN_NAME
    USER_XML_TAB_COLS              COLUMN_NAME
    DBA_XML_VIEW_COLS              COLUMN_NAME
    ALL_XML_VIEW_COLS              COLUMN_NAME
    USER_XML_VIEW_COLS             COLUMN_NAME
    USER_XML_COLUMN_NAMES          COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    DBA_XDS_ATTRIBUTE_SECS         COLUMN_NAME
    ALL_XDS_ATTRIBUTE_SECS         COLUMN_NAME
    USER_XDS_ATTRIBUTE_SECS        COLUMN_NAME
    USER_SDO_GEOM_METADATA         COLUMN_NAME
    ALL_SDO_GEOM_METADATA          COLUMN_NAME
    USER_SDO_INDEX_INFO            COLUMN_NAME
    ALL_SDO_INDEX_INFO             COLUMN_NAME
    USER_SDO_LRS_METADATA          COLUMN_NAME
    ALL_SDO_LRS_METADATA           COLUMN_NAME
    USER_SDO_TOPO_INFO             COLUMN_NAME
    ALL_SDO_TOPO_INFO              COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    USER_SDO_TOPO_METADATA         COLUMN_NAME
    ALL_SDO_TOPO_METADATA          COLUMN_NAME
    USER_ANNOTATION_TEXT_METADATA  COLUMN_NAME
    ALL_ANNOTATION_TEXT_METADATA   COLUMN_NAME
    WWV_FLOW_TRANSLATABLE_COLS$    COLUMN_NAME
    WWV_FLOW_HNT_COLUMN_INFO       COLUMN_NAME
    WWV_FLOW_MODEL_PAGE_COLS       COLUMN_NAME
    WWV_FLOW_WORKSHEET_HISTORY     COLUMN_NAME
    WWV_FLOW_WORKSHEET_CONDITIONS  COLUMN_NAME
    WWV_COLUMN_EXCEPTIONS          COLUMN_NAME
    APEX_DICTIONARY                COLUMN_NAME
     
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    APEX_UI_DEFAULTS_COLUMNS       COLUMN_NAME
    APEX_UI_DEFAULTS_LOV_DATA      COLUMN_NAME
    XSTREAM$_DML_CONFLICT_COLUMNS  COLUMN_NAME
    LOGSTDBY_UNSUPPORT_TAB_11_2B   COLUMN_NAME
    _DBA_APPLY_DML_CONF_COLUMNS    COLUMN_NAME
    DBA_APPLY_DML_CONF_COLUMNS     COLUMN_NAME
    ALL_APPLY_DML_CONF_COLUMNS     COLUMN_NAME
    ALL_STREAMS_TRANSFORMATIONS    COLUMN_NAME
    ALL_STREAMS_KEEP_COLUMNS       COLUMN_NAME
     
    218 ROWS selected.
     
    SQL>
    SQL>
     
     
    madu likes this.
  3. madu

    madu Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    hi david,
    thanks for the reply...
    i want to know that is any possible way to hide/ (disable) a procedure/function in a package specification...
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Only by:

    1) removing it from the package spec and package body
    2) commenting it out in the package spec and package body

    Access is granted to the package, not its individual contents; if a use has access to the package he/she has access to everything inside it. They may not have sufficient privilege to get the procedures/functions to run but they can still 'see' the entire contents.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Btw, Why do you want to hide procedures/functions in a package ?