Tutorials
Oracle 9i
Tutorial 15: Oracle 9i : Oracle Packages
Tutorial 15: Oracle 9i : Oracle Packages - Page 2
Oracle Packages
A package is a group of procedures, functions, variables and SQL statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification or spec or package header and Package Body.
Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code.
Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification or spec.
Advantages:
Package Syntax:
The package specification

The package body

If the specification of the package declares only types, constants, variables, exceptions, and call specs the package body is not required there. This type of packages only contains global variables that will be used by subprograms or cursors.
Example for a bodiless package

For accessing the types, subprograms etc from a package we write
Package_name.type_name
Package_name.subprogram_name


Executing Procedure and function from above package

The following package spec and package body declare and define 2 procedures. First the procedure is used to insert new records into the table DEPT, where the second procedure deletes a record from it.




Next Page: Tutorial 15: Oracle 9i : Oracle Packages - Page 2
| Can we call cursor as an anonymous block |
| pl/sql packeges concepts are easy to understand |
|
thanx a billion , where million is too little :) for this clear , helpful and specific explanation . it really helped me and made that stuff crystal clear to me .. thanx again . |
| You will provide somemore Examples |
| It is very simple and example also easy to understand.It is realy helpful to me |
| it is very good but you should provide some more examples |
| it is good but more examples ,some complex ones, should be provided. |
|
can any body explain me how package provides information hiding facility in oracle explain with examples pls help me... |
|
can anybody answer this query: i need to check if a package of the name 'x' exists in a particulas db. what will be the sql command for that? select x from dual; doesnt work. |
|
you should query the user_source table as such: SELECT text from user_source WHERE name = 'x' AND type = 'PACKAGE'; or something like that |
|
you should query the user_source table as such: SELECT text from user_source WHERE name = 'x' AND type = 'PACKAGE'; |
|
I have about 250 procedures and each 25 is a group. These procedures are created dynamically by using definition tables. What I want to do is to group each 25 procedure in a package but I can not execute these procedures in a package since the length of the string is to big to execute. I use execute immediate or dbms_sql.parse to execute the string. What should I do ? Is there a method of adding procedures to a package ? Thanks in advance. |