Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter