 |
|
Administration of OPERATOR Objects
Oracle Database Tips by Donald Burleson |
Oracle8i introduced the concept of an OPERATOR
object, which is user-defined and can be used as an operator, much the
same as +, -, *, and the like. This provides extensibility to the set
of standard SQL operators. Operators are used in index types, DML, and
SQL statements.
Operators can reference functions, packages,
types, and other user-defined objects. To create an operator, you must
have the CREATE OPERATOR or CREATE ANY OPERATOR privilege, as well as
EXECUTE permission on any underlying objects. New operators are
usually defined by data cartridge developers, but may have many uses
in database administration and management.
Creation of an OPERATOR Object
OPERATOR objects are created through the
CREATE OR REPLACE OPERATOR command. This command is of sufficient
complexity that I refer you to the SQL Reference Manual on the
documentation website for a detailed listing. But here we?ll examine
an example of the creation of an OPERATOR object.
First, the underlying package and function on
which the operator depends must be built. Assuming we?re novice DBAs,
we don't realize that there is a concatenation operator (||) already
defined for Oracle, so we decide to build one using the nifty new
CREATE OPERATOR command. First, we must create a package that contains
our procedures, which will be used to implement the concatenation
operator.
CREATE OR REPLACE
PACKAGE con AS
FUNCTION concat_string (var1 IN VARCHAR2, var2 IN VARCHAR2)
RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE con AS
FUNCTION concat_string (var1 IN VARCHAR2, var2 IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
var3:=var1+var2;
RETURN var3;
END concat_string;
END;
/
Now that we have our package, we can create an
operator that utilizes the package:
CREATE OR
REPLACE OPERATOR concat
BINDING (VARCHAR2,VARCHAR2) RETURN VARCHAR2
USING con.concat_string;
Here is a demonstration of the preceding
examples in use:
SQL> CREATE
OR REPLACE PACKAGE con AS
2 FUNCTION concat_string (var1 IN VARCHAR2,
var2 IN VARCHAR2)
3 RETURN VARCHAR2;
4 END;
5 /
Package
created.
SQL> CREATE
OR REPLACE PACKAGE BODY con AS
2 FUNCTION concat_string (var1 IN VARCHAR2,
var2 IN VARCHAR2)
3 RETURN VARCHAR2 AS
4 var3 VARCHAR2(4000);
5
BEGIN
6 select var1||var2 into var3 from dual;
7 RETURN var3;
8 END concat_string;
9 END;
10 /
Package body
created.
SQL> CREATE
OR REPLACE OPERATOR concat
2 BINDING (VARCHAR2,VARCHAR2) RETURN VARCHAR2
3 USING con.concat_string;
Operator
created.
SQL> select
'test' testit
2 from dual where concat('test','test')='testtest';
TESTIT
???
test
SQL>
What all this does is allow you to use
nonpurified functions. Notice what happens if we call the function
directly:
SQL> select
'Test' testit from dual
2 where con.concat_string('it ','works')='it works'
where con.concat_string('it ','works')='it works'
*
ERROR at line 2:
ORA-06571: Function CONCAT_STRING does not
guarantee not to update database
Even if we add the line PRAGMA
RESTRICT_REFERENCES(concat-string, ?WNDS?); to the package
specification we, still get:
SQL> select
'Test' testit from dual
2 where
con.concat_string('it ','works')='it works';
where con.concat_string('it ','works')='it works'
*
ERROR at line 2:
ORA-06573: Function CONCAT_STRING modifies
package state, cannot be used here
One quick note about using the CREATE OR
REPLACE OPERATOR command: In version 8.1.3 I was able to use it about
three times, then it started returning ORA-06553 errors unless the
operator was dropped first.
Altering OPERATOR Objects
Operators can only be dropped and re-created;
there is no ALTER OPERATOR command.
Dropping OPERATOR Objects
Operators are dropped using the DROP OPERATOR
command; the command syntax is:
DROP
OPERATOR [schema.]operator_name [FORCE];
The FORCE clause forces the drop of the
OPERATOR even if it is currently being accessed by one or more schema
objects. Any dependent objects are marked as INVALID.
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
|
and include the URL for the page.
