Oracle PL/SQL Tutorial/Function Procedure Packages/EXEC

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

   <source lang="sql">

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></source>

Use EXEC to call a procedure in a package

   <source lang="sql">

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></source>