Oracle PL/SQL/PL SQL/Utility Package

Debug package

 
SQL>
SQL> CREATE OR REPLACE PACKAGE Debug AS
  2    PROCEDURE Debug(p_Description IN VARCHAR2,
  3                    p_Value IN VARCHAR2);
  4
  5    PROCEDURE Reset;
  6  END Debug;
  7  /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Debug AS
  2    PROCEDURE Debug(p_Description IN VARCHAR2,
  3                    p_Value IN VARCHAR2) IS
  4    BEGIN
  5      DBMS_OUTPUT.PUT_LINE(p_Description || ": " || p_Value);
  6    END Debug;
  7
  8    PROCEDURE Reset IS
  9    BEGIN
 10      DBMS_OUTPUT.DISABLE;
 11      DBMS_OUTPUT.ENABLE(1000000);
 12    END Reset;
 13  BEGIN /* Package initialization code */
 14    Reset;
 15  END Debug;
 16  /
Package body created.
SQL>
SQL>
SQL>

Debug package based on UTL_FILE

 
CREATE OR REPLACE PACKAGE Debug AS
  v_DebugDir VARCHAR2(50);
  v_DebugFile VARCHAR2(20);
  PROCEDURE Debug(p_Description IN VARCHAR2,
                  p_Value IN VARCHAR2);
  PROCEDURE Reset(p_NewFile IN VARCHAR2 := v_DebugFile,
                  p_NewDir IN VARCHAR2 := v_DebugDir) ;
  PROCEDURE Close;
END Debug;
/
CREATE OR REPLACE PACKAGE BODY Debug AS
  v_DebugHandle UTL_FILE.FILE_TYPE;
  PROCEDURE Debug(p_Description IN VARCHAR2,p_Value IN VARCHAR2) IS
  BEGIN
    UTL_FILE.PUTF(v_DebugHandle, "%s: %s\n", p_Description, p_Value);
    UTL_FILE.FFLUSH(v_DebugHandle);
  EXCEPTION
    WHEN UTL_FILE.INVALID_OPERATION THEN
      RAISE_APPLICATION_ERROR(-20102, "Debug: Invalid Operation");
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      RAISE_APPLICATION_ERROR(-20103, "Debug: Invalid File Handle");
    WHEN UTL_FILE.WRITE_ERROR THEN
      RAISE_APPLICATION_ERROR(-20104, "Debug: Write Error");
  END Debug;
  PROCEDURE Reset(p_NewFile IN VARCHAR2 := v_DebugFile,
                  p_NewDir IN VARCHAR2 := v_DebugDir) IS
  BEGIN
    IF UTL_FILE.IS_OPEN(v_DebugHandle) THEN
      UTL_FILE.FCLOSE(v_DebugHandle);
    END IF;
    v_DebugHandle := UTL_FILE.FOPEN(p_NewDir, p_NewFile, "w");
    v_DebugFile := p_NewFile;
    v_DebugDir := p_NewDir;
  EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
      RAISE_APPLICATION_ERROR(-20100, "Reset: Invalid Path");
    WHEN UTL_FILE.INVALID_MODE THEN
      RAISE_APPLICATION_ERROR(-20101, "Reset: Invalid Mode");
    WHEN UTL_FILE.INVALID_OPERATION THEN
      RAISE_APPLICATION_ERROR(-20101, "Reset: Invalid Operation");
  END Reset;
  PROCEDURE Close IS
  BEGIN
    UTL_FILE.FCLOSE(v_DebugHandle);
  END Close;
BEGIN
  v_DebugDir := "/tmp";
  v_DebugFile := "debug.out";
  Reset;
END Debug;
/
show error

Debug package: inserts into a test table

 
SQL>
SQL> CREATE TABLE debug_table (
  2    linecount  NUMBER PRIMARY KEY,
  3    debug_str  VARCHAR2(200));

SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE Debug AS
  2    PROCEDURE Debug(p_Description IN VARCHAR2, p_Value IN VARCHAR2);
  3
  4    PROCEDURE Reset;
  5  END Debug;
  6  /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Debug AS
  2    v_LineCount NUMBER;
  3
  4    PROCEDURE Debug(p_Description IN VARCHAR2, p_Value IN VARCHAR2) IS
  5    BEGIN
  6      INSERT INTO debug_table (linecount, debug_str)
  7        VALUES (v_LineCount, p_Description || ": " || p_Value);
  8      COMMIT;
  9      v_LineCount := v_LineCount + 1;
 10    END Debug;
 11
 12    PROCEDURE Reset IS
 13    BEGIN
 14      v_LineCount := 1;
 15      DELETE FROM debug_table;
 16    END Reset;
 17
 18  BEGIN
 19    Reset;
 20  END Debug;
 21  /
Package body created.
SQL>
SQL> drop table debug_table;
Table dropped.
SQL>
SQL>

Debug package: uses DBMS_OUTPUT.

 
SQL>
SQL> CREATE TABLE debug_table (
  2    linecount  NUMBER PRIMARY KEY,
  3    debug_str  VARCHAR2(200));
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE Debug AS
  2    PROCEDURE Debug(p_Description IN VARCHAR2, p_Value IN VARCHAR2);
  3
  4    PROCEDURE Reset;
  5  END Debug;
  6  /
Package created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Debug AS
  2    PROCEDURE Debug(p_Description IN VARCHAR2,
  3                    p_Value IN VARCHAR2) IS
  4    BEGIN
  5      DBMS_OUTPUT.PUT_LINE(p_Description || ": " || p_Value);
  6    END Debug;
  7
  8    PROCEDURE Reset IS
  9    BEGIN
 10      DBMS_OUTPUT.DISABLE;
 11      DBMS_OUTPUT.ENABLE(1000000);
 12    END Reset;
 13
 14  BEGIN
 15    Reset;
 16  END Debug;
 17  /
Package body created.
SQL>
SQL>

stop_watch

 
SQL>
SQL> CREATE OR REPLACE PACKAGE stop_watch AS
  2     pv_start_time_num     PLS_INTEGER;
  3     pv_stop_time_num      PLS_INTEGER;
  4     pv_last_stop_time_num PLS_INTEGER;
  5  PROCEDURE start_timer;
  6  PROCEDURE stop_timer;
  7  END stop_watch;
  8  /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY stop_watch AS
  2  PROCEDURE start_timer AS
  3  BEGIN
  4     pv_start_time_num     := DBMS_UTILITY.GET_TIME;
  5     pv_last_stop_time_num := pv_start_time_num;
  6  END start_timer;
  7
  8  PROCEDURE stop_timer AS
  9  BEGIN
 10     pv_stop_time_num := DBMS_UTILITY.GET_TIME;
 11     DBMS_OUTPUT.PUT_LINE("Total Time Elapsed: " ||
 12        TO_CHAR((pv_stop_time_num - pv_start_time_num)/100,
 13        "999,999.99") || " sec   Interval Time: " ||
 14        TO_CHAR((pv_stop_time_num - pv_last_stop_time_num)/100,
 15        "99,999.99") || " sec");
 16     pv_last_stop_time_num := pv_stop_time_num;
 17  END stop_timer;
 18  END;
 19  /
Package body created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE test_if (p_condition_num NUMBER) AS
  2     lv_temp_num         NUMBER := 0;
  3     lv_temp_cond_num    NUMBER := p_condition_num;
  4  BEGIN
  5     stop_watch.start_timer;
  6     FOR lv_count_num IN 1..1000 LOOP
  7        IF lv_temp_cond_num = 1 THEN
  8           lv_temp_num := lv_temp_num + 1;
  9        ELSIF lv_temp_cond_num = 2 THEN
 10           lv_temp_num := lv_temp_num + 1;
 11        ELSIF lv_temp_cond_num = 3 THEN
 12           lv_temp_num := lv_temp_num + 1;
 13        ELSIF lv_temp_cond_num = 4 THEN
 14           lv_temp_num := lv_temp_num + 1;
 15        ELSIF lv_temp_cond_num = 5 THEN
 16           lv_temp_num := lv_temp_num + 1;
 17        ELSIF lv_temp_cond_num = 6 THEN
 18           lv_temp_num := lv_temp_num + 1;
 19        ELSIF lv_temp_cond_num = 7 THEN
 20           lv_temp_num := lv_temp_num + 1;
 21        ELSE
 22           lv_temp_num := lv_temp_num + 1;
 23        END IF;
 24     END LOOP;
 25     stop_watch.stop_timer;
 26  END;
 27  /
Procedure created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>

This object type represents a point on a Cartesian grid.

 
SQL>
SQL> CREATE OR REPLACE TYPE Point AS OBJECT (
  2    x NUMBER,
  3    y NUMBER,
  4
  5    MEMBER FUNCTION ToString RETURN VARCHAR2,
  6    PRAGMA RESTRICT_REFERENCES(ToString, RNDS, WNDS, RNPS, WNPS),
  7
  8    MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0))
  9      RETURN NUMBER,
 10    PRAGMA RESTRICT_REFERENCES(Distance, RNDS, WNDS, RNPS, WNPS),
 11
 12    MEMBER FUNCTION Plus(p IN Point) RETURN Point,
 13    PRAGMA RESTRICT_REFERENCES(Plus, RNDS, WNDS, RNPS, WNPS),
 14
 15    MEMBER FUNCTION Times(n IN NUMBER) RETURN Point,
 16    PRAGMA RESTRICT_REFERENCES(Times, RNDS, WNDS, RNPS, WNPS)
 17  );
 18  /
Type created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE TYPE BODY Point AS
  2    MEMBER FUNCTION ToString RETURN VARCHAR2 IS
  3      myResult VARCHAR2(20);
  4      v_xString VARCHAR2(8) := SUBSTR(TO_CHAR(x), 1, 8);
  5      v_yString VARCHAR2(8) := SUBSTR(TO_CHAR(y), 1, 8);
  6    BEGIN
  7      myResult := "(" || v_xString || ", ";
  8      myResult := myResult || v_yString || ")";
  9      RETURN myResult;
 10    END ToString;
 11
 12    MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0))
 13      RETURN NUMBER IS
 14    BEGIN
 15      RETURN SQRT(POWER(x - p.x, 2) + POWER(y - p.y, 2));
 16    END Distance;
 17
 18    MEMBER FUNCTION Plus(p IN Point) RETURN Point IS
 19      myResult Point;
 20    BEGIN
 21      myResult := Point(x + p.x, y + p.y);
 22      RETURN myResult;
 23    END Plus;
 24
 25    MEMBER FUNCTION Times(n IN NUMBER) RETURN Point IS
 26      myResult Point;
 27    BEGIN
 28      myResult := Point(x * n, y * n);
 29      RETURN myResult;
 30    END Times;
 31  END;
 32  /
Type body created.
SQL> show errors
No errors.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    v_Point1 Point := Point(1, 2);
  3    v_Point2 Point;
  4    v_Point3 Point;
  5  BEGIN
  6    v_Point2 := v_Point1.Times(4);
  7    v_Point3 := v_Point1.Plus(v_Point2);
  8    DBMS_OUTPUT.PUT_LINE("Point 2: " || v_Point2.ToString);
  9    DBMS_OUTPUT.PUT_LINE("Point 3: " || v_Point3.ToString);
 10    DBMS_OUTPUT.PUT_LINE("Distance between origin and point 1: " ||
 11      v_Point1.Distance);
 12    DBMS_OUTPUT.PUT_LINE("Distance between point 1 and point 2: " ||
 13      v_Point1.Distance(v_Point2));
 14  END;
 15  /
Point 2: (4, 8)
Point 3: (5, 10)
Distance between origin and point 1: 2.23
Distance between point 1 and point 2: 6.70
PL/SQL procedure successfully completed.
SQL>

time the performance enhancements of native dynamic SQL.

 
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PACKAGE Timing AS
  2    PROCEDURE StartTiming;
  3    PROCEDURE StopTiming;
  4    PROCEDURE PrintElapsed(p_Message IN VARCHAR2);
  5  END Timing;
  6  /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Timing AS
  2    v_StartTime NUMBER;
  3    v_EndTime NUMBER;
  4
  5    PROCEDURE StartTiming IS
  6    BEGIN
  7      v_StartTime := DBMS_UTILITY.GET_TIME;
  8    END StartTiming;
  9
 10    PROCEDURE StopTiming IS
 11    BEGIN
 12      v_EndTime := DBMS_UTILITY.GET_TIME;
 13    END StopTiming;
 14
 15    PROCEDURE PrintElapsed(p_Message IN VARCHAR2) IS
 16      v_Elapsed NUMBER := (v_EndTime - v_StartTime) / 100;
 17    BEGIN
 18      DBMS_OUTPUT.PUT_LINE(
 19        "Elapsed Time for " || p_Message || " is " ||
 20        v_Elapsed  || " seconds.");
 21    END PrintElapsed;
 22  END Timing;
 23  /
Package body created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL>
SQL> TRUNCATE TABLE MyTable;
Table truncated.
SQL>
SQL> DECLARE
  2    v_CursorID INTEGER;
  3    v_Dummy NUMBER;
  4    v_SQLStatement VARCHAR2(100) := "INSERT INTO MyTable (num_col) VALUES (:num)";
  5
  6    c_NumRows CONSTANT NUMBER := 5000;
  7  BEGIN
  8    DELETE FROM MyTable;
  9    COMMIT;
 10
 11    Timing.StartTiming;
 12    v_CursorID := DBMS_SQL.OPEN_CURSOR;
 13
 14    FOR v_Count IN 1..c_NumRows LOOP
 15      DBMS_SQL.PARSE(v_CursorID, v_SQLStatement, DBMS_SQL.NATIVE);
 16      DBMS_SQL.BIND_VARIABLE(v_CursorID, ":num", v_Count);
 17      v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
 18    END LOOP;
 19
 20    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
 21
 22    Timing.StopTiming;
 23    Timing.PrintElapsed("DBMS_SQL");
 24
 25    DELETE FROM MyTable;
 26    COMMIT;
 27
 28    Timing.StartTiming;
 29    FOR v_Count IN 1..c_NumRows LOOP
 30      EXECUTE IMMEDIATE v_SQLStatement USING v_Count;
 31    END LOOP;
 32    Timing.StopTiming;
 33    Timing.PrintElapsed("Native Dynamic SQL");
 34  END;
 35  /
Elapsed Time for DBMS_SQL is 1.04 seconds.
Elapsed Time for Native Dynamic SQL is .52 seconds.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>