 |
|
EnterpriseDB: Calling a Package
Oracle Tips by
Burleson
|
When a
packaged object is called, the package name must be prepended to the
object unless the object is being called from inside the package.
That means that when referencing a packaged object, you need to put
the package name followed by a period followed by the object name.
Below is
a sample script calling the package I created above. As usual, I will
address the script step by step.
DECLARE
--
Step 1
v_proc_sql VARCHAR2(200) := 'select * from emp where empno = :empno';
v_func_sql VARCHAR2(200) := 'select * from emp where empno = :empno';
v_proc_curs SYS_REFCURSOR;
v_func_curs SYS_REFCURSOR;
v_emp emp%ROWTYPE;
BEGIN
--
Step 2
DBMS_OUTPUT.PUT_LINE( 'Executing
PUBLIC_PROCEDURE');
DBMS_OUTPUT.PUT_LINE( ' ');
-- Step 2a
OPEN sample_pkg.v_ref FOR SELECT
empno FROM emp ORDER BY ename;
--
Step 2b
FETCH sample_pkg.v_ref INTO
sample_pkg.g_global_empno;
--
Step 2c
sample_pkg.public_proc( v_proc_sql, sample_pkg.g_global_empno,
v_proc_curs );
--
Step 2d
FETCH v_proc_curs INTO v_emp;
--
Step 2e
DBMS_OUTPUT.PUT_LINE( 'Proc Cursor
Loop, Name: ' || v_emp.ename ||
', empno: ' || sample_pkg.g_global_empno );
--
Step 2f
CLOSE v_proc_curs;
CLOSE sample_pkg.v_ref;
-- Step 3
DBMS_OUTPUT.PUT_LINE( ' ');
DBMS_OUTPUT.PUT_LINE( 'Executing PUBLIC_FUNCTION');
DBMS_OUTPUT.PUT_LINE( ' ');
-- Step 3a
OPEN sample_pkg.v_ref FOR SELECT
empno FROM emp ORDER BY mgr;
--
Step 3b
FETCH sample_pkg.v_ref INTO
sample_pkg.g_global_empno;
--
Step 3c
v_func_curs := sample_pkg.public_func( v_func_sql,
sample_pkg.g_global_empno );
--
Step 3d
FETCH v_func_curs INTO v_emp;
--
Step 3e
DBMS_OUTPUT.PUT_LINE( 'Func Cursor
Loop, Name: ' || v_emp.ename ||
', empno: ' || sample_pkg.g_global_empno );
--
Step 3f
CLOSE sample_pkg.v_ref;
CLOSE v_func_curs;
-- Step 4
DBMS_OUTPUT.PUT_LINE( ' ');
DBMS_OUTPUT.PUT_LINE( 'Executing DISPLAY_DATA');
DBMS_OUTPUT.PUT_LINE( ' ');
--
Step 4a
sample_pkg.display_data('Hello World!');
DBMS_OUTPUT.PUT_LINE( ' ');
-- Step 4b
sample_pkg.display_data(sysdate);
DBMS_OUTPUT.PUT_LINE( ' ');
-- Step 4c
sample_pkg.display_data(997.3);
DBMS_OUTPUT.PUT_LINE( ' ');
END;
*
edb_chap_3_call_pkg1.sql
DECLARE
--
Step 1
v_proc_sql VARCHAR2(200) := 'select
* from emp where empno = :empno';
v_func_sql VARCHAR2(200) := 'select * from emp where empno = :empno';
v_proc_curs SYS_REFCURSOR;
v_func_curs SYS_REFCURSOR;
v_emp emp%ROWTYPE;
BEGIN
-- Step
2
DBMS_OUTPUT.PUT_LINE( 'Executing PUBLIC_PROCEDURE');
DBMS_OUTPUT.PUT_LINE( ' ');
-- Step 2a
OPEN sample_pkg.v_ref FOR SELECT
empno FROM emp ORDER BY ename;
--
Step 2b
FETCH sample_pkg.v_ref INTO
sample_pkg.g_global_empno;
--
Step 2c
sample_pkg.public_proc( v_proc_sql, sample_pkg.g_global_empno,
v_proc_curs );
--
Step 2d
FETCH v_proc_curs INTO v_emp;
--
Step 2e
DBMS_OUTPUT.PUT_LINE( 'Proc Cursor
Loop, Name: ' || v_emp.ename ||
', empno: ' || sample_pkg.g_global_empno );
--
Step 2f
CLOSE v_proc_curs;
CLOSE sample_pkg.v_ref;
--
Step 3
DBMS_OUTPUT.PUT_LINE( ' ');
DBMS_OUTPUT.PUT_LINE( 'Executing PUBLIC_FUNCTION');
DBMS_OUTPUT.PUT_LINE( ' ');
-- Step 3a
OPEN sample_pkg.v_ref FOR SELECT
empno FROM emp ORDER BY mgr;
--
Step 3b
FETCH sample_pkg.v_ref INTO
sample_pkg.g_global_empno
--
Step 3c
v_func_curs := sample_pkg.public_func( v_func_sql,
sample_pkg.g_global_empno );
--
Step 3d
FETCH v_func_curs INTO v_emp;
--
Step 3e
DBMS_OUTPUT.PUT_LINE( 'Func Cursor Loop, Name: ' || v_emp.ename ||
', empno: ' || sample_pkg.g_global_empno );
--
Step 3f
CLOSE sample_pkg.v_ref;
CLOSE v_func_curs;
--
Step 4
DBMS_OUTPUT.PUT_LINE( ' ');
DBMS_OUTPUT.PUT_LINE( 'Executing DISPLAY_DATA');
DBMS_OUTPUT.PUT_LINE( ' ');
--
Step 4a
sample_pkg.display_data('Hello World!');
DBMS_OUTPUT.PUT_LINE( ' ');
--
Step 4b
sample_pkg.display_data(sysdate);
DBMS_OUTPUT.PUT_LINE( ' ');
--
Step 4c
sample_pkg.display_data(997.3);
DBMS_OUTPUT.PUT_LINE( ' ');
END;
Step 1
declares all of the variables we will be using.
*
v_proc_sql is the SQL that will be passed to the public_proc procedure
in the package
*
v_func_sql is the SQL that will be passed to the public_func function
in the package
*
v_proc_curs is the cursor that will be used in conjunction with the
public_proc procedure
*
v_func_curs is the cursor that will be used in conjunction with the
public_func function
* v_emp
is the record variable that will be used to store the results of the
procedure and function
Step 2
starts the call to the procedure portion of the package.
* Step
2a opens the public cursor declared in the package specification. The
syntax is normal CURSOR syntax except that the cursor name points to
the package (package_name.cursor_name).
* Step
2b fetched the empno column's value into the public g_global_empno
that is also declared in the package specification.
* Step
2c executes the packaged procedure. The procedure call also has the
package_name.procedure_name syntax.
* Step
2d fetches the values from the cursor that was opened in Step 2c. It
fetches the row into the v_emp record variable.
* Step
2e displays the output from this step and Step 2f closes both of the
cursors I opened.
This is
the output from this step in the script:
INFO:
Executing PUBLIC_PROCEDURE
INFO:
INFO:
Debug: v_table
is
empty, g_global_empno is: 7876, Cursor v_ref is open and has fetched 1
rows.
INFO:
Proc Cursor Loop, Name: ADAMS, empno: 7876
Step 3
starts the call to the function portion of the package. Steps 3b, 3d,
3e, and 3f are exactly the same as the steps in Step 2. The only
differences between Step 2 and Step 3 are in Step 3a where I ordered
the rows by mgr instead of empno and in Step 3c where we call the
stored function instead of the stored procedure.
This is
the output from this step in the script:
INFO:
INFO:
Executing PUBLIC_FUNCTION
INFO:
INFO:
Debug: v_table
is
empty, g_global_empno is: 7902, Cursor v_ref is open and has fetched 1
rows.
INFO:
Func Cursor Loop, Name: FORD, empno: 7902
The
final step is Step 4. Step 4 begins the display data portion of the
package.
* Step
4a calls display_data with a VARCHAR2 input.
* Step
4b calls display_data with a DATE input.
* Step
4c calls display_data with a NUMBER input.
This is
the output from the display_data portion of the package:
INFO:
INFO:
Executing DISPLAY_DATA
INFO:
INFO:
This is VARCHAR2 data: Hello World!
INFO:
Debug: v_table(1):Hello
World!, 12, g_global_empno is: 7902, Cursor v_ref is not open.
INFO:
INFO:
This is DATE data: 4 December , 2006
INFO:
Debug: v_table(1):Hello
World!, 12 v_table(2):4 December , 2006,
17, g_global_empno is: 7902, Cursor v_ref is not open.
INFO:
INFO:
This is NUMBER data: $997.30
INFO:
Debug: v_table(1):Hello
World!, 12 v_table(2):4 December , 2006, 17 v_table(3):$997.30, 7,
g_global_empno is: 7902, Cursor v_ref is not open.
INFO:
This
section isn't
enough
to make you a package guru but if you understand everything happening
in this package, you have a good start on becoming an SPL expert.
The last
major component in SPL that all developers and DBAs should understand
is Triggers.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
