There could be a high number of functions and procedures within a project. It is helpful to summarize the functions used in the production machine.

What functions and procedures are used for will be answered in this section.

A package collects the procedures and functions together. It consists of 2 parts:

Spec: Where the definitions are placed

Body: Contains the code.

All definitions in the spec must have corresponding code in the body. Code that isnt going to be run elsewhere can be placed in the package’s spec section.

It isn’t possible to close functions and procedures from outside access, but this can be done in packages.


This procedure can’t be run as it is, it needs to between a begin – end or with an exec.

Anything called from the package loads the entire contents of the package into memory. There shouldn’t be any standalone functions or packages within projects, they should all be contained within packages. When working on professional projects, it’s recommended preparing and placing all code within packages.



UTL_FILEs are used by PL/SQL programs to read and write physical (operating system) files on Oracle servers.

(CSV etc.)

utl_mail – to send mail

utl_file – to read and write files

Example: To read employee records and write them to a text file utl_file cannot write to the root of “c:\” but can write to another child directory.

create directory Reports, as ‘/u02/UTL_Directory’ – gives permission to users to create a directory.

grant create any directory to user_name;


Create a directory as a system user and give read/write permissions to a user.

grant read,write on directory Reports to user_name;

Users aren’t allowed to own directories, who ever makes a directory they appear as belonging to the sys.

They can be queried from “dba_directories”.

UTL_FILE parameters:

The directory names must be written in upper case letters.