Packages
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.
|
1 2 3 4 5 6 7 8 |
CREATE OR REPLACE PACKAGE date_time is -- 'as' could also be used in this statement function convert_date return varchar2; function convert_time return varchar2; procedure print_date_time ; -- the procedure doesn't have a return type end ; CREATE OR REPLACE PACKAGE BODY datetime is function convert_date return varchar2 is date varchar2(20); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
begin select to_char(sysdate, 'DD/MM/YYYY') into date from dual; return date ; end; function get_time return varchar2 is time varchar2(20); begin select to_char(sysdate, 'HH24:MI:SS') into time from dual; return time; end; procedure print_time_date is begin dbms_output.put_line(sysdate); end; end; |
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.
Package
|
1 |
select DATETIME.GET_TIME_DATE from dual ; |
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.
Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE OR REPLACE PACKAGE dept_ADMINISTRATION is procedure add_dept(p_deptno number, p_deptname varchar2); procedure remove_dept(p_deptno number); --procedure printout_dept; --function get_dept_name (p_deptno number) return varchar2; --function get_dept_count return number; end; CREATE OR REPLACE PACKAGE BODY dept_dept_ADMINISTRATION is procedure add_dept(p_deptno number, p_deptname varchar2) is begin insert into dept(department_id, department_name) values (p_deptno, p_deptname); end; procedure remove_dept(p_deptno number) is begin delete from dept where department_id = p_deptno; if (SQL%NOTFOUND) then raise_application_error(-20100, 'No such department'); end if; exception when others then raise_application_error(-20100, SQLERRM); end; end; |
UTL_FILE
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;
alternatively
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:
|
1 2 3 4 5 6 7 8 9 |
fopen ; Opens the file is_open : Is the file open or closed put : to write something new_line : to move to the next line put_line : to write to the next line get_line : to read to write puts and to read gets fseek : to find a file fclose : to close a file |
The directory names must be written in upper case letters.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
declare textfile utl_file.file_type; cursor c_employees is select employee_id, first_name, last_name from employees ; row varchar2(500); begin textfile := utl_file.fopen('REPORTS','employees_records.txt','w',32767); for r_employees in c_employees loop row := r_employees.employee_id||','||r_employees.first_name||','||r_employees.last_name; utl_file.PUT_LINE(textfile,row); end loop;utl_file.fclose(textfile); end; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
declare row varchar2(500); -- A maximum of 4000 textfile utl_file.file_type; begin textfile := utl_file.FOPEN('REPORTS','employees_records.txt','r',32767); if not utl_file.IS_OPEN(textfile) then dbms_output.put_line('Unable to open file'); else loop utl_file.GET_LINE(textfile,row); dbms_output.put_line(row); end loop; end if ; utl_file.fclose(textfile); exception when no_data_found then utl_file.fclose(textfile); when others then dbms_output.put_line(SQLERRM); utl_file.FCLOSE_ALL; end ; |
Using RMAN for...
12 March 2019