Getting Information on Functions and Procedures in a Package : user_procedures « System Tables Data Dictionary « Oracle PL/SQL Tutorial

Home
Oracle PL/SQL Tutorial
1.Introduction
2.Query Select
3.Set
4.Insert Update Delete
5.Sequences
6.Table
7.Table Joins
8.View
9.Index
10.SQL Data Types
11.Character String Functions
12.Aggregate Functions
13.Date Timestamp Functions
14.Numerical Math Functions
15.Conversion Functions
16.Analytical Functions
17.Miscellaneous Functions
18.Regular Expressions Functions
19.Statistical Functions
20.Linear Regression Functions
21.PL SQL Data Types
22.PL SQL Statements
23.PL SQL Operators
24.PL SQL Programming
25.Cursor
26.Collections
27.Function Procedure Packages
28.Trigger
29.SQL PLUS Session Environment
30.System Tables Data Dictionary
31.System Packages
32.Object Oriented
33.XML
34.Large Objects
35.Transaction
36.User Privilege
Oracle PL/SQL Tutorial » System Tables Data Dictionary » user_procedures 
30.94.3.Getting Information on Functions and Procedures in a Package

You can get information on your functions and procedures in a package from the user_procedures view.

SQL> desc user_procedures;
 Name               Null?    Type
 -------------------

 OBJECT_NAME        NOT NULL VARCHAR2(30)
 PROCEDURE_NAME              VARCHAR2(30)
 AGGREGATE                   VARCHAR2(3)
 PIPELINED                   VARCHAR2(3)
 IMPLTYPEOWNER               VARCHAR2(30)
 IMPLTYPENAME                VARCHAR2(30)
 PARALLEL                    VARCHAR2(3)
 INTERFACE                   VARCHAR2(3)
 DETERMINISTIC               VARCHAR2(3)
 AUTHID                      VARCHAR2(12)


SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56'Toronto',  'Programmer')
  3  /

row created.

SQL>
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78'Vancouver','Tester')
  3  /

row created.

SQL>
SQL>
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78'Vancouver','Tester')
  3  /

row created.

SQL>
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78'Vancouver','Manager')
  3  /

row created.

SQL>
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78'Vancouver','Tester')
  3  /

row created.

SQL>
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
  3  /

row created.

SQL>
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')
  3  /

row created.

SQL>
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
  3  /

row created.

SQL>
SQL>
SQL> -- display data in the table
SQL> select from Employee
  2  /

ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James                Smith                12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester

rows selected.

SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE employee_package AS
  2    TYPE t_ref_cursor IS REF CURSOR;
  3    FUNCTION get_employee_ref_cursor RETURN t_ref_cursor;
  4    PROCEDURE update_salary (p_id IN VARCHAR2,p_factor IN NUMBER);
  5  END employee_package;
  6  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY employee_package AS
  2    FUNCTION get_employee_ref_cursor
  3    RETURN t_ref_cursor IS
  4      employee_ref_cursor t_ref_cursor;
  5    BEGIN
  6      -- get the REF CURSOR
  7      OPEN employee_ref_cursor FOR
  8        SELECT id, first_name, salary
  9        FROM employee;
 10      -- return the REF CURSOR
 11      RETURN employee_ref_cursor;
 12    END get_employee_ref_cursor;
 13
 14    PROCEDURE update_salary (p_id IN VARCHAR2,
 15      p_factor IN NUMBER
 16    AS
 17      v_employee_count INTEGER;
 18    BEGIN
 19        UPDATE employee
 20        SET salary = salary * p_factor
 21        WHERE id = p_id;
 22        COMMIT;
 23    EXCEPTION
 24      WHEN OTHERS THEN
 25        -- perform a rollback when an exception occurs
 26        ROLLBACK;
 27    END update_salary;
 28  END employee_package;
 29  /

Package body created.

SQL>
SQL> SELECT object_name, procedure_name
  2  FROM user_procedures
  3  WHERE object_name = 'EMPLOYEE_PACKAGE';

OBJECT_NAME                    PROCEDURE_NAME
------------------------------ ------------------------------
EMPLOYEE_PACKAGE               GET_EMPLOYEE_REF_CURSOR
EMPLOYEE_PACKAGE               UPDATE_SALARY

SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
30.94.user_procedures
30.94.1.Getting Information on Procedures
30.94.2.Getting Information on Functions
30.94.3.Getting Information on Functions and Procedures in a Package
30.94.4.Join user_procedures and user_objects
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.