SQL>
SQL> CREATE OR REPLACE PACKAGE errpkg
2 IS
3 PROCEDURE record_and_stop;
4
5 END errpkg;
6 /
Package created.
SQL>
SQL> CREATE TABLE employee (
2 employee_id NUMBER(38,0)
3 ,deptno NUMBER(3,0) NOT NULL
4 ,first_name VARCHAR2(95) NOT NULL
5 ,last_name VARCHAR2(95) NOT NULL
6 ,salary NUMBER(11,2)
7 );
Table created.
SQL>
SQL> CREATE OR REPLACE PACKAGE employee_pkg
2 AS
3 SUBTYPE fullname_t IS VARCHAR2 (200);
4
5 FUNCTION fullname (l employee.last_name%TYPE,f employee.first_name%TYPE)
6 RETURN fullname_t;
7
8 FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)
9 RETURN fullname_t;
10 END employee_pkg;
11 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY employee_pkg
2 AS
3 FUNCTION fullname (l employee.last_name%TYPE,f employee.first_name%TYPE)
4 RETURN fullname_t
5 IS
6 BEGIN
7 RETURN l || ',' || f;
8 END;
9
10 FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)
11 RETURN fullname_t
12 IS
13 retval fullname_t;
14 BEGIN
15 SELECT fullname (last_name, first_name) INTO retval
16 FROM employee
17 WHERE employee_id = employee_id_in;
18
19 RETURN retval;
20 EXCEPTION
21 WHEN NO_DATA_FOUND THEN RETURN NULL;
22
23 WHEN TOO_MANY_ROWS THEN errpkg.record_and_stop;
24 END;
25 END employee_pkg;
26 /
SP2-0810: Package Body created with compilation warnings
SQL>
SQL> DECLARE
2 l_name employee_pkg.fullname_t;
3 employee_id_in CONSTANT PLS_INTEGER := 1;
4 BEGIN
5 l_name := employee_pkg.fullname (employee_id_in);
6
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>