Oracle PL/SQL Tutorial/Function Procedure Packages/Utility Procedure

A package to calculate your age

SQL>
SQL> CREATE OR REPLACE PACKAGE datecalc
  2  IS
  3     PROCEDURE showage (birthday_in IN DATE);
  4     PROCEDURE showage (birthday_in IN INTEGER);
  5     PROCEDURE showage (birthday_in IN VARCHAR2, mask_in IN VARCHAR2 := NULL);
  6  END datecalc;
  7  /
Package created.
SQL>
SQL> show error
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY datecalc
  2  IS
  3     FUNCTION datestring (date_in IN DATE) RETURN VARCHAR2 IS
  4     BEGIN
  5       RETURN "You are " || ROUND (TO_NUMBER (SYSDATE - date_in)) || " days old.";
  6     END datestring;
  7
  8     PROCEDURE showage (birthday_in IN DATE) IS
  9     BEGIN
 10        DBMS_OUTPUT.PUT_LINE (datestring (birthday_in));
 11     END showage;
 12
 13     PROCEDURE showage (birthday_in IN INTEGER) IS
 14     BEGIN
 15        showage (TO_DATE (birthday_in, "J"));
 16     END showage;
 17
 18     PROCEDURE showage (birthday_in IN VARCHAR2, mask_in IN VARCHAR2 := NULL) IS
 19     BEGIN
 20        IF mask_in IS NULL
 21        THEN
 22           showage (TO_DATE (birthday_in));
 23        ELSE
 24           showage (TO_DATE (birthday_in, mask_in));
 25        END IF;
 26     END showage;
 27  END datecalc;
 28  /
Package body created.
SQL> show error
No errors.
SQL>

Create procedure for displaying long text line by line

SQL>
SQL> create or replace procedure printLongText ( p_str in varchar2 )
  2  is
  3     l_str   long := p_str;
  4  begin
  5     loop
  6        exit when l_str is null;
  7        dbms_output.put_line( substr( l_str, 1, 250 ) );
  8        l_str := substr( l_str, 251 );
  9     end loop;
 10  end;
 11  /
Procedure created.
SQL>

Disable trigger

SQL>
SQL> CREATE OR REPLACE PROCEDURE settrig (tab IN VARCHAR2,action IN VARCHAR2) IS
  2     v_action VARCHAR2 (10) := UPPER (action);
  3     v_other_action VARCHAR2 (10) := "DISABLE";
  4  BEGIN
  5     IF v_action = "DISABLE"
  6     THEN
  7        v_other_action := "ENABLE";
  8     END IF;
  9     FOR rec IN (SELECT trigger_name
 10                   FROM user_triggers
 11                  WHERE table_owner = USER
 12                    AND table_name = UPPER (tab)
 13                    AND status = v_other_action)
 14     LOOP
 15        EXECUTE IMMEDIATE "ALTER TRIGGER " || rec.trigger_name || " " || v_action;
 16        DBMS_OUTPUT.put_line ("Set status of " || rec.trigger_name || " to " || v_action);
 17     END LOOP;
 18  END;
 19  /
Procedure created.
SQL>

Don"t display lines longer than 80 characters

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE println (val IN VARCHAR2)
  2  IS
  3  BEGIN
  4     IF LENGTH (val) > 80
  5     THEN
  6
  7        DBMS_OUTPUT.PUT_LINE (SUBSTR (val, 1, 80));
  8
  9        println (SUBSTR (val, 81));
 10
 11     ELSE
 12
 13        DBMS_OUTPUT.PUT_LINE (val);
 14
 15     END IF;
 16
 17  EXCEPTION
 18
 19     WHEN OTHERS
 20
 21     THEN
 22
 23        DBMS_OUTPUT.ENABLE (1000000);
 24
 25        println (val);
 26  END;
 27  /
Procedure created.
SQL>

Dynamically perform any DDL statements from within your normal PL/SQL processing.

SQL>
SQL> CREATE OR REPLACE PROCEDURE exec_ddl
  2     (p_statement_txt VARCHAR2) IS
  3     lv_exec_cursor_num    INTEGER := DBMS_SQL.OPEN_CURSOR;
  4     lv_rows_processed_num NUMBER := 0;
  5     lv_statement_txt      VARCHAR2(30000);
  6  BEGIN
  7     lv_statement_txt := p_statement_txt;
  8     DBMS_SQL.PARSE (lv_exec_cursor_num, lv_statement_txt,
  9        DBMS_SQL.NATIVE);
 10     lv_rows_processed_num := DBMS_SQL.EXECUTE (lv_exec_cursor_num);
 11     DBMS_SQL.CLOSE_CURSOR (lv_exec_cursor_num);
 12  EXCEPTION
 13     WHEN OTHERS THEN
 14        IF DBMS_SQL.IS_OPEN (lv_exec_cursor_num) THEN
 15           DBMS_SQL.CLOSE_CURSOR (lv_exec_cursor_num);
 16        END IF;
 17     RAISE;
 18  END exec_ddl;
 19  /
Procedure created.
SQL>

Execuate the same SQL in two ways: static way and dynamic way

SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> set echo on
SQL>
SQL> create or replace procedure DynEmpProc( p_job in varchar2 )
  2  as
  3      type refcursor is ref cursor;
  4      l_cursor   refcursor;
  5      l_ename    emp.ename%type;
  6  begin
  7      open l_cursor for "select ename from emp where job = :x" USING in p_job;
  8
  9      loop
 10          fetch l_cursor into l_ename;
 11          exit when l_cursor%notfound;
 12
 13          dbms_output.put_line( l_ename );
 14      end loop;
 15      close l_cursor;
 16  exception
 17      when others then
 18          if ( l_cursor%isopen )
 19          then
 20              close l_cursor;
 21          end if;
 22          RAISE;
 23  end;
 24  /
Procedure created.
SQL>
SQL> create or replace procedure StaticEmpProc( p_job in varchar2 )
  2  as
  3  begin
  4      for x in ( select ename from emp where job = p_job )
  5      loop
  6          dbms_output.put_line( x.ename );
  7      end loop;
  8  end;
  9  /
Procedure created.
SQL>
SQL> set serveroutput on size 1000000
SQL> exec DynEmpProc( "CLERK" )
SMITH
ADAMS
JAMES
MILLER
PL/SQL procedure successfully completed.
SQL> exec StaticEmpProc( "CLERK" )
SMITH
ADAMS
JAMES
MILLER
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.

Procedure create_order

SQL>
SQL> CREATE TABLE error_log(
  2     order_id    NUMBER(10)      NOT NULL,
  3     error_code  NUMBER NOT      NULL,
  4     error_text  VARCHAR2(1000)  NOT NULL,
  5     logged_user VARCHAR2(30)    NOT NULL,
  6     logged_date DATE            NOT NULL
  7  );
Table created.
SQL>
SQL> CREATE TABLE order_tab(
  2     order_id     NUMBER(10) PRIMARY KEY,
  3     order_date   DATE NOT NULL,
  4     total_qty    NUMBER,
  5     total_price  NUMBER(15,2),
  6     supp_id      NUMBER(6)
  7  );
Table created.
SQL>
SQL> INSERT INTO order_tab VALUES (101,sysdate,100,750,1001);
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE log_error
  2                  (p_order_id NUMBER,
  3                   p_error_code NUMBER,
  4                   p_error_text VARCHAR2)
  5  IS
  6    PRAGMA AUTONOMOUS_TRANSACTION;
  7  BEGIN
  8    INSERT INTO error_log VALUES (p_order_id,p_error_code,p_error_text,USER,SYSDATE);
  9    COMMIT;
 10  END;
 11  /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE create_order(p_order_id NUMBER)
  2  IS
  3  BEGIN
  4    INSERT INTO order_tab VALUES(p_order_id,SYSDATE,NULL,NULL,1001);
  5    COMMIT;
  6  EXCEPTION WHEN OTHERS THEN
  7    log_error(p_order_id,SQLCODE,SQLERRM);
  8    ROLLBACK;
  9  END;
 10  /
Procedure created.
SQL>
SQL> DECLARE
  2    PRAGMA AUTONOMOUS_TRANSACTION;
  3    FUNCTION dummy_log_error RETURN NUMBER;
  4    v_num NUMBER(1):=0;
  5    returnCode NUMBER :=dummy_log_error;
  6    FUNCTION dummy_log_error RETURN NUMBER
  7    IS
  8    BEGIN
  9      INSERT INTO error_log VALUES (-99,-99,"Dummy Error!",USER,SYSDATE);
 10      RETURN (-99);
 11    END;
 12  BEGIN
 13    INSERT INTO error_log VALUES (v_num,v_num,"No Error!",USER,SYSDATE);
 14    COMMIT;
 15  END;
 16  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table order_tab;
Table dropped.
SQL>
SQL> drop table error_log;
Table dropped.

Returns the total from an order number being passed in.

SQL>
SQL> CREATE TABLE ord
  2  (order_id      NUMBER(7),
  3   customer_id   NUMBER(7),
  4   date_ordered  DATE,
  5   date_shipped  DATE,
  6   sales_rep_id  NUMBER(7),
  7   total         NUMBER(11, 2),
  8   payment_type  VARCHAR2(6),
  9   order_filled  VARCHAR2(1)
 10  );
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO ord VALUES (100, 204, "31-AUG-92", "10-SEP-92", 11, 601100, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (101, 205, "31-AUG-92", "15-SEP-92", 14, 8056.6, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (102, 206, "01-SEP-92", "08-SEP-92", 15, 8335, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (103, 208, "02-SEP-92", "22-SEP-92", 15, 377, "CASH", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (104, 208, "03-SEP-92", "23-SEP-92", 15, 32430, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (105, 209, "04-SEP-92", "18-SEP-92", 11, 2722.24, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (106, 210, "07-SEP-92", "15-SEP-92", 12, 15634, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (107, 211, "07-SEP-92", "21-SEP-92", 15, 142171, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (108, 212, "07-SEP-92", "10-SEP-92", 13, 149570, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (109, 213, "08-SEP-92", "28-SEP-92", 11, 1020935, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (110, 214, "09-SEP-92", "21-SEP-92", 11, 1539.13, "CASH", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (111, 204, "09-SEP-92", "21-SEP-92", 11, 2770, "CASH", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (112, 210, "31-AUG-92", "10-SEP-92", 12, 550, "CREDIT", "Y");
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE order_process(p_ordering_num NUMBER) IS
  2     CURSOR cur_get_order (p_ord_num ord.order_id%TYPE) IS
  3        SELECT *
  4        FROM   ord
  5        WHERE  order_id = p_ord_num;
  6     lv_order_rec cur_get_order%ROWTYPE;
  7  BEGIN
  8     OPEN cur_get_order (p_ordering_num);
  9     FETCH cur_get_order INTO lv_order_rec;
 10     DBMS_OUTPUT.PUT_LINE("Order Total: " ||
 11        TO_CHAR(lv_order_rec.total));
 12     CLOSE cur_get_order;
 13     EXCEPTION
 14        WHEN OTHERS THEN
 15           RAISE_APPLICATION_ERROR(-20100, "Order Problem.", FALSE);
 16  END;
 17  /
Procedure created.
SQL>
SQL> show error
No errors.
SQL>
SQL>
SQL> drop table ord;
Table dropped.
SQL>