Analyzing Laggards
Recently I had to write code to parse through simple test results to quickly find any differences. Given two set ID values I needed to find any values in the second set that differ from those in the first...
Read More...
|
Bi-Directional Cursors in PL/SQL
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...
Read More...
|
Calculating Amount of Time Between Two Dates
The best way to calculate the amount of time between two dates is to take advantage of the INTERVAL and TIMESTAMP datatypes...
Read More...
|
Design by Contract (DBC) and PL/SQL
Design by contract (DBC) is a method whose author is Bertrand Mayer, also the maker of the OOPL language Eiffel. Simplified, DBC is based on the principle that in each routine (procedure or function) with standard code, two additional parts – PRECONDITION and POSTCONDITION - need to be asserted...
Read More...
|
Dynamic Ref Cursor with Dynamic Fetch - An 11g Version
We've got a function that, based on dynamically generated query, returns a ref cursor variable. Now we want to use this ref cursor variable in our procedure, but we don't know the record structure. The problem is how to make a "FETCH l_ref_cur INTO record_variable" when we don't know the record variable structure...
Read More...
|
FORALL Workaround for an INSERT..SELECT..RETURNING Construct
This is a FORALL workaround to the frustrating lack of support for an INSERT..SELECT..RETURNING construct...
Read More...
|
Generate Database Documentation in HTML Format
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...
Read More...
|
Getting the SQL Plan for a Cursor with DBMS_XPLAN
DBMS_XPLAN has been extended in 10g to show the actual SQL plan for a cursor ( i.e. a particular execution of a SQL statement )...
Read More...
|
How to Get the Text Name of an Object's Type
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)?
Read More...
|
Index Collections by Strings
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...
Read More...
|
Integration of Java and PL/SQL in Oracle
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...
Read More...
|
Mixed Parameter Placement in Oracle 11g Functions
Since there are two parameters, you can either call the function by passing parameters as positional values or as named parameters. In Oracle Database 11g, you are free to use this notation...
Read More...
|
Name Those Exceptions!
When a SELECT INTO does not return at least one row, Oracle raises the NO_DATA_FOUND exception. Most PL/SQL developers probably think this exception is some sort of built-in, reserved word exception in the PL/SQL language. It is not. rather, it is simply one of a relatively small number of exceptions that are defined in the STANDARD package...
Read More...
|
Oddly In
I checked the documentation but could not find anything about automatically trimming blanks when you use the IN clause. So I experimented some more...
Read More...
|
OR(DBMS) or R(DBMS), That is the Question
Here is an example of how to use "object-relational" features "in the proper way" (in Date's sense) - to use object type as domain...
Read More...
|
PL/SQL Distance Calculator
The following function calculates the distance between 2 points using the Great Circle formula...
Read More...
|
Procedure to Convert Numbers to Text
The procedure below converts a value between 1 and a (Zillion -1) to its text equivalent...
Read More...
|
Record-level DML
PL/SQL is tightly integrated with SQL, no doubt about it. That doesn't mean, however, that it isn't possible to make that integration even tighter. Oracle took another step in this direction when it added native support for inserting and updating rows of data with PL/SQL records.
Read More...
|
Running Hierarchy Queries Against DUAL
The following are some findings regarding the use of hierarchical queries from DUAL...
Read More...
|
SELECT * FROM capability from within PL/SQL
The procedure in this download provides a "SELECT * FROM" capability from within PL/SQL. That is, you provide the name of the table, an optional where clause and column name filter, and the "in table" procedure displays the contents of the table matching your criteria.
Read More...
|
The Template Design Pattern in PL/SQL
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.
Read More...
|
Use BULK COLLECT Instead of Cursor FOR
When querying multiple rows of data from Oracle, don't use the cursor FOR loop. Instead, use the wonderful, amazing BULK COLLECT query, which improves query response time very dramatically.
Read More...
|
Use DBMS_UTILITY.FORMAT_ERROR_STACK instead of SQLERRM
SQLERRM is a function that returns the error message for a particular error code. If you do not pass an error code to SQLERRM, then it returns the error message associated with the value returned by SQLCODE...
Read More...
|
Using a Comma-separated List in an SQL Query
What the programmer is really looking for is a different type of variable that explicitly represents a set of values. Fortunately Oracle SQL supports exactly this, in the form of collections...
Read More...
|