Oracle PL/SQL Tutorial/Function Procedure Packages/EXEC

Demmonstrate the creation and execution of a simple PL/SQL procedure.

SQL>
SQL> create or replace procedure add_up
  2   (p1  in  number DEFAULT 0,
  3    p2  in  number DEFAULT 0,
  4    p3  out number)
  5  as
  6  begin
  7    p3 := p1 + p2;
  8  end;
  9  /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- execute from SQL*Plus:
SQL>
SQL> var x number
SQL> exec add_up(10,10,:x)
PL/SQL procedure successfully completed.
SQL> print x
         X
----------
        20
SQL>
SQL>

Use EXEC to call a procedure in a package

SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE calc_pkg
  2  IS
  3     PROCEDURE calculate_totals;
  4  END calc_pkg;
  5  /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY calc_pkg
  2  IS
  3     PROCEDURE calculate_totals
  4     IS
  5     BEGIN
  6        RAISE VALUE_ERROR;
  7     EXCEPTION
  8        WHEN OTHERS
  9        THEN
 10           DBMS_OUTPUT.put_line("Current line number "|| $$plsql_line);
 11           DBMS_OUTPUT.put_line("Failed in program "|| $$plsql_unit);
 12     END calculate_totals;
 13  END calc_pkg;
 14  /
Package body created.
SQL>
SQL> EXEC calc_pkg.calculate_totals;
PL/SQL procedure successfully completed.
SQL>