Oracle PL/SQL Tips of the Month - 2006
| January | Integration of Java and PL/SQL in Oracle |
| February | Index Collections by Strings |
| March | Generate Database Documentation in HTML Format |
| April | Bi-Directional Cursors in PL/SQL |
| May | Altering Types |
| June | Oddly In |
| July | How to Get the Text Name of an Object's Type |
| August | Use BULK COLLECT Instead of Cursor FOR |
| September | FORALL Workaround for an INSERT..SELECT..RETURNING Construct |
| October | The Template Design Pattern in PL/SQL |
| November | Calculating Amount of Time Between Two Dates |
| December | SQL and Collections |
January's Tip of the Month
Integration of Java and PL/SQL in Oracle
Compliments of Keshav Chennakeshav
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20601
This is a way to create a Java class in an Oracle database using JDBC and calling the class
through a function created in PL/SQL. This java class, "dbTableRowCount", uses a default or
currently open connection to the database. To connect to the database from an external
application, the connection part of the code has to be enhanced providing a username and password.
This code can be implemented on an Oracle database version 8i and higher. The classes can be
created to perform DDL and DML tasks as needed per application requirements.
1. Creation of java class
CREATE or REPLACE JAVA SOURCE NAMED "dbTableRowCount"
AS
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
public class dbTableRowCount {
public static String rowCount (String tabName) throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
String sql0 = "SELECT table_name FROM all_tables
WHERE table_name = UPPER('" + tabName + "')";
String sql1 = "SELECT SYSDATE, COUNT(*) FROM " + tabName;
int rows = 0;
String today = "";
String op = "Table " + tabName;
try {
Statement stmt = conn.createStatement();
ResultSet rschk = stmt.executeQuery(sql0);
if ( !rschk.next() ) {
op += ! " - Does Not Exist in the Database";
} else {
ResultSet rset = stmt.executeQuery(sql1);
while (rset.next()) {
today = rset.getString(1);
rows = rset.getInt(2);
}
rset.close();
op += " Row Count = " + rows + " On " + today.toString();
}
rschk.close();
stmt.close();
} catch (SQLException e) {System.err.println(e.getMessage());}
return (op);
}
}
;
/
2. Creation of PL/SQL function
Once the class "dbTableRowCount" is successfully created, create a function in PL/SQL as
shown below.
CREATE OR REPLACE FUNCTION dbtabrowcount(tab_name VARCHAR2)
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'dbTableRowCount.rowCount(java.lang.String) return java.lang.String';
/
3. Code test
The code is tested as any Oracle database function.
SELECT dbtabrowcount('any_table')
FROM DUAL;
SELECT dbtabrowcount('xxxxx')
FROM DUAL;
February's Tip of the Month
Index Collections by Strings
Compliments of Steven Feuerstein, from the November issue of OPP/News
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20602
If you are running Oracle9i Database Release 2 or above, you can advantage of
a very, very cool feature: associative arrays that are indexed by strings,
rather than integer. I have found this technique handy in a number of
situations, most recently when I wanted to cache the contents of a relational
table in a collection and access rows by a GUID (globally unique identifier)
primary key value. These GUIDs are strings, not integers, so the typical INDEX
BY BINARY_INTEGER won't help me out much. String-based indexing is also very
helpful when you need to index information by more than one value, as is the
case with a concatenated index.
In the package definition below, I am loading the contents of my books table
(code to define this table is available in this newsletter's
download) into a series of associative arrays that emulate the primary key
and unique indexes on the table. The unique index arrays rely on string-based
indexes. By caching the table contents in collections, I can greatly speed up
the performance of querying against this static copy of the table. Very handy
when running batch processes against large tables!
For some additional background on this technique, check out my Brave New World
seminar.
CREATE OR REPLACE PACKAGE summer_reading
IS
SUBTYPE author_title_t IS VARCHAR2 (32767);
FUNCTION author_title (
author_in books.author%TYPE
,title_in books.title%TYPE
,delim_in IN VARCHAR2 := '^'
)
RETURN author_title_t;
FUNCTION onebook (
book_id_in IN books.book_id%TYPE
) RETURN books%ROWTYPE;
FUNCTION onebook (
isbn_in IN books.isbn%TYPE
)
RETURN books%ROWTYPE;
FUNCTION onebook (
author_in books.author%TYPE
,title_in books.title%TYPE
)
RETURN books%ROWTYPE;
PROCEDURE load_arrays;
PROCEDURE set_reload_interval (
interval_in IN NUMBER
);
PROCEDURE set_reload_interval (
interval_in IN INTERVAL DAY TO SECOND
);
END summer_reading;
/
CREATE OR REPLACE PACKAGE BODY summer_reading
IS
g_last_load DATE;
g_reload_interval INTERVAL DAY TO SECOND := NULL;
TYPE book_id_aat IS TABLE OF books%ROWTYPE
INDEX BY PLS_INTEGER;
TYPE isbn_aat IS TABLE OF books.book_id%TYPE
INDEX BY books.isbn%TYPE;
TYPE author_title_aat IS TABLE OF books.book_id%TYPE
INDEX BY author_title_t;
books_aa book_id_aat;
by_isbn_aa isbn_aat;
by_author_title_aa author_title_aat;
FUNCTION author_title (
author_in books.author%TYPE
,title_in books.title%TYPE
,delim_in IN VARCHAR2 := '^'
) RETURN author_title_t
IS
BEGIN
RETURN UPPER (author_in) || delim_in || UPPER (title_in);
END;
PROCEDURE load_arrays
IS
BEGIN
DBMS_OUTPUT.put_line ( 'Reloading books arrays at '
|| TO_CHAR (SYSDATE, 'HH24:MI:SS')
);
FOR rec IN (SELECT *
FROM books)
LOOP
books_aa (rec.book_id) := rec;
by_isbn_aa (rec.isbn) := rec.book_id;
by_author_title_aa (
author_title (rec.author, rec.title)) :=
rec.book_id;
END LOOP;
g_last_load := SYSDATE;
END load_arrays;
PROCEDURE set_reload_interval (
interval_in IN INTERVAL DAY TO SECOND
)
IS
BEGIN
g_reload_interval := interval_in;
END;
PROCEDURE set_reload_interval (
interval_in IN NUMBER
)
IS
BEGIN
g_reload_interval :=
NUMTODSINTERVAL (interval_in, 'SECOND');
END;
FUNCTION reload_needed
RETURN BOOLEAN
IS
retval BOOLEAN := g_reload_interval IS NOT NULL;
l_date DATE := SYSDATE;
BEGIN
IF retval
THEN
retval :=
NUMTODSINTERVAL (l_date - g_last_load, 'DAY') >
g_reload_interval;
END IF;
RETURN retval;
END;
FUNCTION onebook (
book_id_in IN books.book_id%TYPE
)
RETURN books%ROWTYPE
IS
BEGIN
IF reload_needed
THEN
load_arrays;
END IF;
RETURN books_aa (book_id_in);
END;
FUNCTION onebook (
isbn_in IN books.isbn%TYPE
)
RETURN books%ROWTYPE
IS
l_book_id books.book_id%TYPE :=
by_isbn_aa (isbn_in);
BEGIN
RETURN onebook (l_book_id);
END;
FUNCTION onebook (
author_in books.author%TYPE
,title_in books.title%TYPE
)
RETURN books%ROWTYPE
IS
BEGIN
RETURN onebook (
by_author_title_aa (
author_title (author_in, title_in)));
END;
BEGIN
load_arrays;
END summer_reading;
/
March's Tip of the Month
Generate Database Documentation in HTML Format
Compliments of Oleg Savkin
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20603
This is a small package to generate database documentation in HTML format. It sends an email
with 3 attachments (HTML files) containing a nicely formatted database object description.
Here is brief description of this package and its usage:
Supported objects:
- Package and Package body
- Function
- Procedure
- Trigger
- User Type
- Table
- View
To call the package:
Example 1:
documentator.generateDocInfo( IN_OBJECT, IN_FILE_NAME, IN_EMAIL)
Where IN_OBJECT in the object name, IN_FILE_NAME is the output file name that will be
used as the base for output file names and IN_EMAIL is the email recipient address.
The following will prepare a document for the object EMP in the current schema.
documentator.generateDocInfo('EMP', 'emp.htm', 'email@your_domain.com');
Example 2:
documentator.generateDocInfoByType(IN_OBJECT_TYPE, IN_FILE_NAME, IN_EMAIL)
Where IN_OBJECT_TYPE is the object type (PACKAGE, etc..). The package will generate
documentation for all objects for the given type. IN_FILE_NAME is the output file name that will be
used as the base for output file names and IN_EMAIL is the email recipient address.
The following will prepare document for all tables in the current schema.
documentator.generateDocInfoByType('TABLE','table.htm', 'email@your_domain.com');
Example 3:
documentator.generateDocInfoByTypes(<TYPES>, IN_FILE_NAME, IN_EMAIL)
Where IN_OBJECT_TYPE specifies objects types (PACKAGE, PROCEDURE, etc..) separated by commas,
IN_FILE_NAME is the output file name that will be
used as the base for output file names and IN_EMAIL is the email recipient address.
The following will prepare a document for all passed object types in the current schema.
documentator.generateDocInfoByTypes('FUNCTION, PROCEDURE, PACKAGE, TRIGGER,
TYPE, TABLE, VIEW', 'objects.htm',
'email@your_domain.com');
To view the documentation, save attachments in any folder and open file with name passed as
parameter IN_FILE_NAME. This package is easy enough to extend for all other database objects not
included in it.
Note: Even if this specific functionality is of no use to you, the code is worth looking at as it does contain
some interesting components including emailing from within PL/SQL, string manipulation, etc.
Click here to download the package.
April's Tip of the Month
Bi-Directional Cursors in PL/SQL
Compliments of Steven Feuerstein, from the February issue of OPP/News
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20604
Sadly, Oracle does not yet support bi-directional access to cursor result sets (a.k.a,
"scrollable cursors") through a PL/SQL interface. You might well find, however, that you can
achieve the desired effect with a combination of the following:
- Multiple queries (each with different ORDER BY clauses that correspond to the different ways
you need to traverse the result set)
- Analytic functions: As the SQL Reference states, "Analytic functions compute an aggregate
value based on a group of rows. They differ from aggregate functions in that they return multiple
rows for each group. The group of rows is called a window and is defined by the analytic_clause.
For each row, a sliding window of rows is defined. The window determines the range of rows used
to perform the calculations for the current row..."
For tables with relatively small numbers of rows, the use of multiple queries might yield a
satisfactory implementation. If, however, your result set is very large, you might run into some
performance issues. In addition, you may still not be able to reference arbitrary rows within the
result set as desired.
Fortunately, you can achieve the desired effect of a bi-directional cursor rather easily by
caching the result in a PL/SQL collection. Once the data has been moved into the cache, you can
move back and forth through the result set, compare rows, etc., with complete freedom and a high
degree of efficiency.
May's Tip of the Month
Altering Types
Reprinted from OracleDBA.co.uk
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20605
Recent versions of Oracle allow you to alter a type on the fly without having to
recreate it. Here's a simple example of how to do it, because as you can see from the first demo
below, it's not quite as intuitive as CREATE OR REPLACE.
create type T1 as object ( x number, y number ) not final;
/
Type created.
create type T2 as table of T1;
/
Type created.
create or replace type T1 as object ( x number, y number, z number );
/
create or replace type T1 as object ( x number, y number, z number );
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
What you need is the ALTER TYPE clause:
alter type T1 add attribute ( z number );
alter type T1 add attribute ( z number )
*
ERROR at line 1:
ORA-22312: must specify either CASCADE or INVALIDATE option
alter type T1 add attribute ( z number ) cascade;
Type altered.
June's Tip of the Month
Oddly In
Compliments of Steven Feuerstein, from the March issue of OPP/News
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20606
I recently received an email from a puzzled developer. So...what's wrong with the following
scenario?
SQL> SELECT 'SQL IN trimmed trailing blanks!'
2 FROM DUAL
3 WHERE 'CA ' IN ( 'CA', 'US' )
4 /'TRIMMEDTRAILINGBLANKS!'
------------------------
SQL IN trimmed trailing blanks!
Isn't that odd? I asked if the string "CA " (CA followed by three spaces) was in the list
of 'CA' and 'US' -- and Oracle said yes!
I checked the documentation but could not find anything about automatically trimming blanks
when you use the IN clause. So I experimented some more:
1. Trims tabs, too:
SQL> SELECT 'SQL IN trimmed trailing tab!'
2 FROM DUAL
3 WHERE 'CA ' IN ( 'CA', 'US' )
4 /'SQLINTRIMMEDTRAILINGTAB!'
----------------------------
SQL IN trimmed trailing tab!
2. Does not trim leading spaces:
SQL> SELECT 'SQL IN does not trim leading blanks!'
2 FROM DUAL
3 WHERE ' CA' IN ( 'CA', 'US' )
4
/
no rows selected
3. And most important of all: does not trim anything at all when you execute the same query
inside a PL/SQL block or simply use the native PL/SQL IN clause:
DECLARE
PROCEDURE exec_in ( message_in IN VARCHAR2, value_in IN VARCHAR2 )
IS
l_dummy VARCHAR2 ( 32767 );
BEGIN
BEGIN
SELECT message_in
INTO l_dummy
FROM DUAL
WHERE value_in IN ( 'CA', 'US' );
DBMS_OUTPUT.put_line ( message_in );
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( message_in || ' did not happen.' );
END;
IF value_in IN ( 'CA', 'US' )
THEN
DBMS_OUTPUT.put_line ( '"PL/SQL IN ' || SUBSTR ( message_in, 9 ));
ELSE
DBMS_OUTPUT.put_line ( '"PL/SQL IN '
|| SUBSTR ( message_in, 9 )
|| ' did not happen.'
);
END IF;
END;
BEGIN
exec_in ( '"SQL IN trimmed trailing blanks."', 'CA ' );
exec_in ( '"SQL IN trimmed leading blanks."', ' CA' );
exec_in ( '"SQL IN trimmed trailing tab."', 'CA ' );
END;
/
"SQL IN trimmed trailing blanks." did not happen.
"PL/SQL IN trimmed trailing blanks." did not happen.
"SQL IN trimmed leading blanks." did not happen.
"PL/SQL IN trimmed leading blanks." did not happen.
"SQL IN trimmed trailing tab." did not happen.
"PL/SQL IN trimmed trailing tab." did not happen.
The lesson we learn once again: write everything in PL/SQL!
Ah, just kidding. You should continue to choose the best tool for the challenge at hand.
But in this case, avoid funkiness with IN by executing your query within a PL/SQL block!
July's Tip of the Month
How to Get the Text Name of an Object's Type
Compliments of Zlatko Sirotic (zlatko.sirotic@iii.hr)
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20607
The SYS_TYPEID function (in PL/SQL 9i/10g) can be used in a query to return the typeid
of the most specific type of the object instance passed to the function as an argument.
But, is there any way to get the text name of an object's type
(or must I create my own get_type_name function and implement it in every type)?
One (not perfect) solution is to use ANYDATA:
CREATE TYPE Person_typ AS OBJECT (
ssn NUMBER,
name VARCHAR2(30),
address VARCHAR2(100)
) NOT FINAL
/
CREATE TYPE Student_typ UNDER Person_typ (
deptid NUMBER,
major VARCHAR2(30)
) NOT FINAL
/
CREATE TYPE PartTimeStudent_typ UNDER Student_typ (
numhours NUMBER
)
/
CREATE TABLE persons OF Person_typ
/
INSERT INTO persons
VALUES (Person_typ (1243, 'Bob', '121 Front St'))
/
INSERT INTO persons
VALUES (Student_typ (3456, 'Joe', '34 View', 12, 'HISTORY'))
/
INSERT INTO persons
VALUES (PartTimeStudent_typ (5678, 'Tim', 'xx', 13, 'PHYSICS', 20))
/
SELECT name, SYS_TYPEID (VALUE(p)) typeid
FROM persons p
/
NAME TYPEID
---- ------
Bob 01
Joe 02
Tim 03
First variant - parameter is of type Person_type:
CREATE OR REPLACE FUNCTION person_typ_name (p_person Person_typ) RETURN VARCHAR2 IS
l_person ANYDATA;
BEGIN
l_person := ANYDATA.ConvertObject (p_person);
RETURN l_person.GetTypeName;
END;
/
or without local variable l_person:
CREATE OR REPLACE FUNCTION person_typ_name (p_person Person_typ) RETURN VARCHAR2 IS
BEGIN
RETURN ANYDATA.ConvertObject (p_person).GetTypeName;
END;
/
SELECT name, person_typ_name (VALUE(p)) person_type_name
FROM persons p
/
NAME PERSON_TYPE_NAME
---- ---------------------------------
Bob SCOTT.PERSON_TYP
Joe SCOTT.STUDENT_TYP
Tim SCOTT.PARTTIMESTUDENT_TYP
Second variant - parameter is of (generic) type ANYDATA:
CREATE OR REPLACE FUNCTION any_typ_name (p_object ANYDATA) RETURN VARCHAR2 IS
BEGIN
RETURN p_object.GetTypeName;
END;
/
and SELECT statement must be:
SELECT name, any_typ_name (ANYDATA.ConvertObject (VALUE (p))) person_type_name
FROM persons p
/
NAME PERSON_TYPE_NAME
---- ---------------------------------
Bob SCOTT.PERSON_TYP
Joe SCOTT.STUDENT_TYP
Tim SCOTT.PARTTIMESTUDENT_TYP
August's Tip of the Month
Use BULK COLLECT Instead of Cursor FOR
Compliments of Steven Feuerstein, from the June issue of OPP/News
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20608
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.
September's Tip of the Month
FORALL Workaround for an INSERT..SELECT..RETURNING Construct
Compliments of Adrian Billington
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20609
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
October's Tip of the Month
The Template Design Pattern in PL/SQL
Compliments of Zlatko Sirotic
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20610
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'
November's Tip of the Month
Calculating Amount of Time Between Two Dates
Compliments of Steven Feuerstein, from the February issue of OPP/News
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20611
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;
/
December's Tip of the Month
SQL and Collections
Reprinted from OracleDBA.co.uk
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20612
One of the most common reasons people appear to be using dynamic SQL is to handle the case when the user can specify one, two or any amount of values to be searched on. For example, the user may select (say) two names from a LOV on people, and so the where-clause part of the query would look like:
where SURNAME in ( 'SMITH', 'WILLIAMS')
but of course if they had selected FOUR names from the LOV, then the where-clause part of the query would look like:
where SURNAME in ( 'SMITH', 'WILLIAMS','DIO','MALMSTEEN')
So throughout forms applications, the way people tend to handle this is with dynamic piece of SQL along the lines of:
l_sql := 'select * from persons where surname in ('|| lv_list_of_names ||')';
Which we then process with EXEC_SQL or similar. As we all now know, its a bad thing to do on performance grounds (no bind variables = no performance). But its also a pain to code correctly - we have to carefully build up the list of names into a comma separated list, we have to check for surnames that contains quotes, etc etc. And its a big security hole as well.
You might be thinking that surely it can't be possible to process an arbritrary number of in-list items with the same single SQL ? Well, yes it can. All it takes a little knowledge of the object-relational options within Oracle.
Firstly, we created a nested table type (you'd only need to do this once for the whole database) to hold a list of strings that have been (say) picked from a LOV
SQL> create or replace type varchar2_list as table of varchar2 (255);
2 /
Type created.
Then we can create a simple function which takes a comma-separated list of predicate values and convert them to the varchar2_list type.
SQL> create or replace
2 function in_list( p_string in varchar2 ) return varchar2_list as
3 l_string long default p_string || ',';
4 l_data varchar2_list := varchar2_list();
5 n number;
6 begin
7 loop
8 exit when l_string is null;
9 n := instr( l_string, ',' );
10 l_data.extend;
11 l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 )) );
12 l_string := substr( l_string, n+1 );
13 end loop;
14 return l_data;
15 end;
16 /
Function created.
And here is where things get very sweet...Oracle can re-cast the results of such a function into a relational structure on the fly. For example, we can take the string 'SMITH,JONES,BROWN' and automatically treat it as if they were rows in a table.
SQL> select *
2 from table(cast(in_list('SMITH,JONES,BROWN') as varchar2_list));
COLUMN_VALUE
--------------------------
SMITH
JONES
BROWN
"Big deal" I hear you say. But once you can convert a string to a table, then you can of course, use it within normal SQL. For example, lets build a string of people's surnames (in the variable "list_of_people"). We can then query the NOMINAL table for just those people using static SQL.
SQL> declare
2 list_of_people varchar2(100) := 'SMITH,WILLIAMS,DIO,MALMSTEEN';
3 begin
4 for i in ( select id, date_created
5 from person
6 where upper(surname) in (
7 select *
8 from table(cast(in_list(list_of_people) as varchar2_list))) ) loop
9 dbms_output.put_line('ID = '||i.id);
10 dbms_output.put_line('DATE_CREATED = '||i.date_created);
11 end loop;
12 end;
13 /
ID = 9000001
DATE_CREATED = 01/OCT/03
ID = 9000002
DATE_CREATED = 01/OCT/03
ID = 9000003
DATE_CREATED = 01/OCT/03
ID = 9000004
DATE_CREATED = 01/OCT/03
ID = 9000010
DATE_CREATED = 01/OCT/03
ID = 9000023
DATE_CREATED = 02/OCT/03
ID = 9000026
DATE_CREATED = 02/OCT/03
PL/SQL procedure successfully completed.
Notice that the SQL in red above does not contain any literals - it doesn't matter what the list of people being sought is, we can re-use the same SQL over and over. No dynamic SQL needed, no literal strings needed.
In fact, anything that you can convert into the nested table type varchar2_list, can be used. So (for example), its trivial to change our demo so that rather than passing a string list of names, you could pass any SQL statement to generate the list of surnames that we are interested in. All we need do it alter our IN_LIST function to loop through a ref cursor as opposed to a string.
SQL> create or replace
2 function in_list( p_string in varchar2 ) return varchar2_list as
3 type rc is ref cursor;
4 l_cursor rc;
5 l_tmp long;
6 l_data varchar2_list := varchar2_list();
7 begin
8 open l_cursor for p_string;
9 loop
10 fetch l_cursor into l_tmp;
11 exit when l_cursor%notfound;
12 l_data.extend;
13 l_data(l_data.count) := l_tmp;
14 end loop;
15 close l_cursor;
16 return l_data;
17 end;
18 /
Function created.
Now my "list_of_people" variable can be a SQL statement, and my loop still works just fine.
SQL> declare
2 list_of_people varchar2(100) := 'select surname from nominal '||
3 ' where surname like ''S%''';
4 begin
5 for i in ( select id, date_created
6 from person
7 where upper(surname) in (
8 select *
9 from table(cast(in_list(list_of_people) as varchar2_list))) ) loop
10 dbms_output.put_line('ID = '||i.id);
11 dbms_output.put_line('DATE_CREATED = '||i.date_created);
12 end loop;
13 end;
14 /
ID = 9000001
DATE_CREATED = 01/OCT/03
ID = 9000010
DATE_CREATED = 01/OCT/03
ID = 9000021
DATE_CREATED = 02/OCT/03
ID = 9000022
DATE_CREATED = 02/OCT/03
ID = 9000023
DATE_CREATED = 02/OCT/03
ID = 9000024
DATE_CREATED = 02/OCT/03
ID = 9000025
DATE_CREATED = 02/OCT/03
ID = 9000026
DATE_CREATED = 02/OCT/03
ID = 9000027
DATE_CREATED = 02/OCT/03
ID = 9000074
DATE_CREATED = 07/OCT/03
PL/SQL procedure successfully completed.
There are a myriad of uses for collections and object types in PL/SQL. You'll find all the details on using object types within PL/SQL within the PL/SQL reference and the Application Developer - Object Relational reference.
For questions
or comments on this site: webmaster@quest-pipelines.com
All content Copyright
© Quest Software, Inc. All rights reserved.
|
