|
Calculating Amount of Time Between Two Dates
|
By Steven Feuerstein
Originally Posted November 2006
The best way to calculate the amount of time between two dates is to take advantage of the INTERVAL and TIMESTAMP datatypes, introduced in the Oracle9i Database. The following function takes advantage of these datatypes to accept two dates and return the interval of time between them:
CREATE OR REPLACE FUNCTION date_diff (
start_date_in IN DATE
, end_date_in IN DATE
)
RETURN INTERVAL DAY TO SECOND
IS
BEGIN
RETURN CAST ( end_date_in AS TIMESTAMP WITH TIME ZONE )
- CAST ( start_date_in AS TIMESTAMP WITH TIME ZONE );
END date_diff;
/
|
|
|
|
|
|
The Template Design Pattern in PL/SQL
|
By Zlatko Sirotic
Originally Posted October 2006
The Template Design Pattern is perhaps one of the most widely used and useful OO (Object Oriented) design patterns. It is used to set up the outline or skeleton of an algorithm, leaving the details to specific implementations later. This way, subclasses can override parts of the algorithm without changing its overall structure.
This is particularly useful for separating the variant and the invariant behaviour, minimizing the amount of code to be written. The invariant behaviour is placed in the abstract class (template) and then any subclasses that inherits it can override the abstract methods and implement the specifics needed in that context.
How do we implement the Template Design Pattern in PL/SQL, using PL/SQL 9i/10g object types? We must use MEMBER (non-STATIC) methods (procedures/functions):
CREATE OR REPLACE TYPE templ_method_abstract AS OBJECT (
dummy VARCHAR2(10),
MEMBER PROCEDURE template_method,
NOT INSTANTIABLE MEMBER PROCEDURE operation1,
NOT INSTANTIABLE MEMBER PROCEDURE operation2
)
NOT FINAL
NOT INSTANTIABLE
/
CREATE OR REPLACE TYPE BODY templ_method_abstract IS
MEMBER PROCEDURE template_method IS
BEGIN
operation1;
operation2;
END;
END;
/
CREATE OR REPLACE TYPE templ_method_concrete_a UNDER templ_method_abstract (
OVERRIDING MEMBER PROCEDURE operation1,
OVERRIDING MEMBER PROCEDURE operation2
)
/
CREATE OR REPLACE TYPE BODY templ_method_concrete_a IS
OVERRIDING MEMBER PROCEDURE operation1 IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Operation 1 in class CONCRETE A');
END;
OVERRIDING MEMBER PROCEDURE operation2 IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Operation 2 in class CONCRETE A');
END;
END;
/
CREATE OR REPLACE TYPE templ_method_concrete_b UNDER templ_method_abstract (
OVERRIDING MEMBER PROCEDURE operation1,
OVERRIDING MEMBER PROCEDURE operation2
)
/
CREATE OR REPLACE TYPE BODY templ_method_concrete_b IS
OVERRIDING MEMBER PROCEDURE operation1 IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Operation 1 in class CONCRETE B');
END;
OVERRIDING MEMBER PROCEDURE operation2 IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Operation 2 in class CONCRETE B');
END;
END;
/
DECLARE
l_templ_method_object templ_method_abstract;
BEGIN
l_templ_method_object := NEW templ_method_concrete_a ('DUMMY');
l_templ_method_object.template_method;
l_templ_method_object := NEW templ_method_concrete_b ('DUMMY');
l_templ_method_object.template_method;
END;
/
Why we can't use STATIC methods? First, in PL/SQL 9i/10g a STATIC method can't be abstract (NOT INSTANTIABLE):
CREATE OR REPLACE TYPE template_method_abstract_2 AS OBJECT (
dummy NUMBER(1),
STATIC PROCEDURE template_method,
NOT INSTANTIABLE STATIC PROCEDURE operation1,
NOT INSTANTIABLE STATIC PROCEDURE operation2
)
NOT FINAL
NOT INSTANTIABLE
/
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/38 PLS-00169: modifier 'STATIC' conflicts with prior 'NOT
INSTANTIABLE' specification
5/38 PLS-00169: modifier 'STATIC' conflicts with prior 'NOT
INSTANTIABLE' specification
Second, we can try to use non-abstract STATIC methods in (abstract) parent:
CREATE OR REPLACE TYPE template_method_abstract_3 AS OBJECT (
dummy NUMBER(1),
STATIC PROCEDURE template_method,
STATIC PROCEDURE operation1,
STATIC PROCEDURE operation2
)
NOT FINAL
NOT INSTANTIABLE
/
CREATE OR REPLACE TYPE BODY template_method_abstract_3 IS
STATIC PROCEDURE template_method IS
BEGIN
operation1;
operation2;
END;
STATIC PROCEDURE operation1 IS
BEGIN
NULL; -- simulates abstract method
END;
STATIC PROCEDURE operation2 IS
BEGIN
NULL; -- simulates abstract method
END;
END;
/
But, (in PL/SQL 9i/10g) we can't override the STATIC method:
CREATE OR REPLACE TYPE template_method_concrete_3 UNDER template_method_abstract_3 (
OVERRIDING STATIC PROCEDURE operation1,
OVERRIDING STATIC PROCEDURE operation2
)
/
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/32 PLS-00169: modifier 'STATIC' conflicts with prior 'OVERRIDING'
specification
3/32 PLS-00169: modifier 'STATIC' conflicts with prior 'OVERRIDING' |
|
|
|
|
|
FORALL Workaround for an INSERT..SELECT..RETURNING Construct
|
By Adrian Billington
Originally Posted September 2006
This is a FORALL workaround to the frustrating lack of support for an INSERT..SELECT..RETURNING construct. In many cases, this restriction doesn't cause too much pain, but recently I had to write some PL/SQL that screamed out for INSERT..SELECT..RETURNING to work. A collection of an object type was being sent from the mid-tier application for INSERT into a table minus the surrogate key values for the new records. After populating the table, the package had to send back the collection but include the new PK values. If INSERT..SELECT..RETURNING was supported, this would be a simple, one-step operation, but instead I was forced to code it as a two-step operation. Until now. This will work in 9i and 10g.
@set_prompt
set echo on
spool insert_select_returning_workaround.lst
CREATE TABLE t
( x INT
, y CHAR(1)
, z DATE );
CREATE TYPE ot AS OBJECT
( x INT
, y CHAR(1)
, z DATE );
/
CREATE TYPE ntt AS TABLE OF ot;
/
CREATE SEQUENCE s;
DECLARE
nt_passed_in ntt;
nt_to_return ntt;
FUNCTION pretend_parameter RETURN ntt IS
nt ntt;
BEGIN
SELECT ot(NULL, 'X', SYSDATE) BULK COLLECT INTO nt
FROM dual
CONNECT BY ROWNUM <= 5;
RETURN nt;
END pretend_parameter;
BEGIN
nt_passed_in := pretend_parameter();
FORALL i IN nt_passed_in.FIRST .. nt_passed_in.LAST
INSERT INTO t ( x, y, z )
VALUES
( s.NEXTVAL
, TREAT( nt_passed_in(i) AS ot ).y
, TREAT( nt_passed_in(i) AS ot ).z
)
RETURNING ot( x, y, z )
BULK COLLECT INTO nt_to_return;
FOR i IN nt_to_return.FIRST .. nt_to_return.LAST LOOP
DBMS_OUTPUT.PUT_LINE(
'Sequence value = [' || TO_CHAR( nt_to_return(i).x ) || ']'
);
END LOOP;
END;
/
spool off
DROP TABLE t PURGE;
DROP SEQUENCE s;
DROP TYPE ntt;
DROP TYPE ot;
The output is shown below:
102> CREATE TABLE t
2 ( x INT
3 , y CHAR(1)
4 , z DATE );
Table created.
102>
102> CREATE TYPE ot AS OBJECT
2 ( x INT
3 , y CHAR(1)
4 , z DATE );
5 /
Type created.
102>
102> CREATE TYPE ntt AS TABLE OF ot;
2 /
Type created.
102>
102> CREATE SEQUENCE s;
Sequence created.
102>
102>
102> --
102> -- Using the PLS-00436 workaround (see my 10g demo pages), we can now emulate
102> -- what we would expect INSERT..SELECT..RETURNING to do, but using
102> -- FORALL..INSERT..VALUES instead. The variable "nt_passed_in" represents the
102> -- collection parameter coming in from the middle-tier...
102> --
102>
102> DECLARE
2
3 nt_passed_in ntt;
4 nt_to_return ntt;
5
6 FUNCTION pretend_parameter RETURN ntt IS
7 nt ntt;
8 BEGIN
9 SELECT ot(NULL, 'X', SYSDATE) BULK COLLECT INTO nt
10 FROM dual
11 CONNECT BY ROWNUM <= 5;
12 RETURN nt;
13 END pretend_parameter;
14
15 BEGIN
16
17 nt_passed_in := pretend_parameter();
18
19 FORALL i IN nt_passed_in.FIRST .. nt_passed_in.LAST
20 INSERT INTO t ( x, y, z )
21 VALUES
22 ( s.NEXTVAL
23 , TREAT( nt_passed_in(i) AS ot ).y
24 , TREAT( nt_passed_in(i) AS ot ).z
25 )
26 RETURNING ot( x, y, z )
27 BULK COLLECT INTO nt_to_return;
28
29 FOR i IN nt_to_return.FIRST .. nt_to_return.LAST LOOP
30 DBMS_OUTPUT.PUT_LINE(
31 'Sequence value = [' || TO_CHAR( nt_to_return(i).x ) || ']'
32 );
33 END LOOP;
34
35 END;
36 /
Sequence value = [1]
Sequence value = [2]
Sequence value = [3]
Sequence value = [4]
Sequence value = [5]
PL/SQL procedure successfully completed.
102>
102> spool off |
|
|
|
|
|
Use BULK COLLECT Instead of Cursor FOR
|
By Steven Feuerstein
Originally Posted August 2006
When querying multiple rows of data from Oracle, don't use the cursor FOR loop. Instead, assuming you are running at least Oracle8i, start using the wonderful, amazing BULK COLLECT query, which improves query response time very dramatically. The following statement, for example, retrieves all the rows in the employee table and deposits them directly into a collection of records:
DECLARE
TYPE employee_aat IS TABLE OF employee%ROWTYPE
INDEX BY BINARY_INTEGER;
l_employees employee_aat;
BEGIN
SELECT *
BULK COLLECT INTO l_employees
FROM employee;
END;
Of course, if your table has 1,000,000 rows in it, the above block of code will consume enormous amounts of memory. In this case, you will want to take advantage of the LIMIT clause of BULK COLLECT as follows:
DECLARE
TYPE employee_aat IS TABLE OF employee%ROWTYPE
INDEX BY BINARY_INTEGER;
l_employees employee_aat;
CURSOR employees_cur IS SELECT * FROM employee;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_employees LIMIT 100;
EXIT WHEN l_employees.COUNT = 0;
END LOOP;
END;
Important! When you use BULK COLLECT, Oracle will not raise NO_DATA_FOUND even if no rows are found by the implicit query. Also, within the loop (using LIMIT), you cannot rely on cursor%FOUND to determine if the last fetch returned any rows. Instead, check the contents of the collection. If empty, then you are done.
For more complete coverage of this topic, check out my 21st Century PL/SQL seminar materials.
|
|
|
|
|
|
Script to Match Days of the Month with "Events" Table
|
By Joe Bednarz
May 2008
I used this script to give my .net developers a list of days of the month and then any corresponding events on that day. It handles multiple day items, as well as items that appear on the same day:
CREATE TABLE EVENTS
( begin_dt DATE, end_dt DATE, event_id NUMBER, event VARCHAR2(30 ));
INSERT INTO EVENTS
VALUES ('01-MAR-2008', '02-MAR-2008', 1, 'My first event');
INSERT INTO EVENTS
VALUES ('27-MAR-2008', '27-MAR-2008', 1, 'My second event');
INSERT INTO EVENTS
VALUES ('27-MAR-2008', '27-MAR-2008', 1, 'My third event');
SELECT all_dom.theday, event, event_id, begin_dt, end_dt
FROM (SELECT matchday, event, event_id, begin_dt, end_dt
FROM (SELECT LEVEL matchday
FROM DUAL
CONNECT BY LEVEL <= TO_CHAR (LAST_DAY (SYSDATE), 'DD')) dom,
EVENTS e
WHERE dom.matchday BETWEEN TO_CHAR (e.begin_dt, 'DD') AND TO_CHAR (e.end_dt, 'DD')
AND (TO_CHAR (SYSDATE, 'MM') = TO_CHAR (e.begin_dt, 'MM')
OR TO_CHAR (SYSDATE, 'MM') = TO_CHAR (e.end_dt, 'MM'))) ev,
(SELECT LEVEL theday
FROM DUAL
CONNECT BY LEVEL <= TO_CHAR (LAST_DAY (SYSDATE), 'DD')) all_dom
WHERE all_dom.theday = ev.matchday(+)
ORDER BY all_dom.theday;
THEDAY EVENT EVENT_ID BEGIN_DT END_DT
---------- ------------------------------ ---------- --------- ---------
1 My first event 1 01-MAR-08 02-MAR-08
2 My first event 1 01-MAR-08 02-MAR-08
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27 My second event 1 27-MAR-08 27-MAR-08
27 My third event 1 27-MAR-08 27-MAR-08
28
29
30
31
|
|
|
|
|
|