Oracle PL/SQL Tips
As of January 2008, the Pipelines will no longer publish monthly tips. However, we welcome and will post
user-submitted tips on this page. Please send your tips to webmaster@quest-pipelines.com.
Our archive of monthly tips will be maintained, so feel free to browse them below.
Previous Tips of the Month
2007 Tips:
| January | %NOTFOUND Versus NO_DATA_FOUND |
| February | Concurrent Cursors and Batch Systems |
| March | Using the ANYDATA Object |
| May | Variable Binding in PL/SQL |
| June | Design by Contract (DBC) and PL/SQL |
| July | Concatenating Multiple Rows Into A Single String |
| August | Oracle 11g Sequence Enhancement |
| September | Dynamic Ref Cursor with Dynamic Fetch - An 11g Version |
| October | OR(DBMS) or R(DBMS), That is the Question |
| November | Best Practices to Improve SQL Performance |
| December | Mixed Parameter Placement in Oracle 11g Functions |
January's Tip of the Month
%NOTFOUND Versus NO_DATA_FOUND
By S. Inderjeet Singh, Reprinted from OracleBrains.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20701
Theoretically, %NOTFOUND is a cursor attribute that returns TRUE if the last FETCH statement
retrieved no record and FALSE if the last FETCH statement retrieved a record, while NO_DATA_FOUND
is a predefined exception (an error condition), which is raised when a SELECT … INTO … statement
fetches no record.
The difference between the two is that if a FETCH statement doesn’t retrieve any record,
NO_DATA_FOUND is not raised. In other words, when using cursors, the NO_DATA_FOUND exception
is not raised, rather the %NOTFOUND attribute value is set to TRUE. However, in the case of a
SELECT … INTO … statement, the NO_DATA_FOUND exception is raised and the %NOTFOUND attribute
of the implicit cursor is also set to TRUE.
Here is an example:
SQL> DECLARE l_ename VARCHAR2(100);
BEGIN
SELECT ename INTO l_ename FROM emp
WHERE empno = ‘1515′;
DBMS_OUTPUT.PUT_LINE(l_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF ( SQL%NOTFOUND ) THEN
DBMS_OUTPUT.PUT_LINE(’NOTFOUND’);
ELSE
DBMS_OUTPUT.PUT_LINE(’FOUND’);
END IF;
END;
/
NOTFOUND
SQL> DECLARE CURSOR c1 IS SELECT ename FROM emp WHERE empno = 1515;
l_ename VARCHAR2(100);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO l_ename;
IF ( c1%NOTFOUND ) THEN
DBMS_OUTPUT.PUT_LINE(’NOTFOUND’);
ELSE
DBMS_OUTPUT.PUT_LINE(l_ename);
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(’NO_DATA_FOUND’);
END;
/
NOTFOUND
Hence the two are different while sometimes taken to be the same.
February's Tip of the Month
Concurrent Cursors and Batch Systems
Reprinted from Oracle FAQ
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20702
Almost every data retrieval requirement for a batch process can be satisfied either by merging a lookup into the main cursor, or by using associative arrays. In a very small number of cases, there is a need to process rows from two or more very large tables which have N:M join-cardinality.
For example: consider a billing run where an invoice must be generated for each Customer, but in order to generate that invoice the program needs to process all of the Customer's Services, plus all of the Customer's Billing Instructions. If there was only one Billing Instruction per customer (say, the latest one), then the main cursor could join the Customer table to Customer Service (return a row per Service per Customer) and join in the latest Billing Instruction to each row. However, when multiple Billing Instructions are required, such a join would destroy the cardinality of the cursor. One Customer joined to 5 Services and 4 Billing Instructions would explode to 5*4=20 rows.
The most efficient solution to such a problem is to use Concurrent Cursors. For this example, use 3 explicit cursors: one that retrieves all of the customers ordered by the customer number, a second that retrieves all of the Customer Services ordered by customer number, and a third that retrieves all of the Customer Billing Instructions ordered by customer number. Open all of the cursors once only at the beginning of the program, and then fetch the rows customer by customer.
To demonstrate the concept more clearly, the sample code below does not use BULK COLLECT, although it could be adapted to do so.
CURSOR cust_cur IS
SELECT *
FROM customer
ORDER BY cust_num;
CURSOR custserv_cur IS
SELECT *
FROM customer_service
ORDER BY cust_num;
CURSOR custinst_cur IS
SELECT *
FROM customer_billing_instruction
ORDER BY cust_num
curr_cust_rec CUST_CUR%ROWTYPE;
curr_custserv_rec CUSTSERV_CUR%ROWTYPE;
curr_custinst_rec CUSTINST_CUR%ROWTYPE;
TYPE custserv_tab_type IS TABLE OF CUSTSERV_CUR%ROWTYPE;
TYPE custinst_tab_type IS TABLE OF CUSTINST_CUR%ROWTYPE;
custserv_tab CUSTSERV_TAB_TYPE;
custinst_tab CUSTINST_TAB_TYPE;
BEGIN
OPEN cust_cur;
OPEN custserv_cur;
OPEN custinst_cur;
FETCH cust_cur INTO curr_cust_rec;
FETCH custserv_cur INTO curr_custserv_rec;
FETCH custinst_cur INTO curr_custinst_rec;
WHILE cust_cur%FOUND LOOP
custserv_tab.DELETE;
WHILE custserv_cur%FOUND
AND curr_custserv_rec.cust_num <= curr_cust_rec.cust_num LOOP
IF curr_custserv_rec.cust_num = curr_cust_rec.cust_num THEN
custserv_tab.EXTEND;
custserv_rec.LAST := curr_custserv_rec;
END IF;
END LOOP;
custinst_tab.DELETE;
WHILE custinst_cur%FOUND
AND curr_custinst_rec.cust_num <= curr_cust_rec.cust_num LOOP
IF curr_custinst_rec.cust_num = curr_cust_rec.cust_num THEN
custinst_tab.EXTEND;
custinst_rec.LAST := curr_custinst_rec;
END IF;
END LOOP;
<... Process the invoice using services in the custserv_tab
collection and billing instructions in the custinst_tab
collection
...>
FETCH cust_cur INTO curr_cust_rec;
END LOOP;
END;
Even though the above example – not using BULK COLLECT – does not significantly reduce context switching to any significant degree, it still has a profound improvement in performance: usually several orders of magnitude. The reason for this is that the Customer Service and Customer Billing Instruction tables are processed in a single fast Full Table Scan, rather than the death-of-a-thousand-cuts method of reading via the index in a separate cursor per customer.
March's Tip of the Month
Using the ANYDATA Object
Reprinted from OracleBrains.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20703
First introduced in Oracle 9i, ANYDATA is a "self describing data instance type" which means it
not only holds the value, but it also holds its own data type within each instance of it. An
ANYDATA can be persistently stored in the database. It also contains many methods that allow us
to perform various functions with it.
Now how and where could we use this? Imagine a situation where we need to design a table with
a column which should store any type of data, and we only know what kind of data when the
application is actually run. For each row there can be any type of data type.
In the past, what we would normally do is declare it as VARCHAR2. While inserting we would
convert each value to VARCHAR2. But the problem with this approach is that we don't know what
kind of data type is stored and even if we know we need to write a conversion for it to convert it
into the original value. Now ANYDATA type comes to the rescue.
Such fields in Oracle 9i and above can be declared as SYS.ANYDATA. After that we can use
whatever type we want and it will stay that type. For example, if we use date, it will stays as
date and we will be able to perform any date operation on it without any need for conversion.
Examples:
SQL> CREATE TABLE tab1( col1 SYS.ANYDATA );
Table created.
SQL> INSERT INTO tab1 VALUES(SYS.ANYDATA.convertVarchar2(’Rajender Singh’));
SQL> INSERT INTO tab1 VALUES(SYS.ANYDATA.convertDate(SYSDATE));
SQL> INSERT INTO tab1 VALUES(SYS.ANYDATA.convertNumber(1972));
Now we know how to create a table with it and we know how to put in values.
The next question is how to get back a value. The bad news is that there is no straight-forward
method to do so. The good news is that the ANYDATA type provides many methods that help us perform
this and many others functions.
For example, by using the following methods exposed in the ANYDATA type we can get the desire
data in the original data type:
gettypeName()
getNumber()
getDate()
getVarchar2()
…
DECLARE
l_flag NUMBER;
l_v VARCHAR2(100);
l_n NUMBER;
l_d DATE;
l_error VARCHAR2(40);
BEGIN
CASE anydata_type_variable.gettypeName
WHEN ‘SYS.NUMBER’ THEN
l_flag :=anydata_type_variable.getNumber(l_n);
WHEN ‘SYS.DATE’ THEN
l_flag :=anydata_type_variable.getDate(l_d);
WHEN ‘SYS.VARCHAR2′ THEN
l_flag :=anydata_type_variable.getVarchar2(l_v);
ELSE
l_error := ‘** unknown **’;
END CASE;
….
…
May's Tip of the Month
Variable Binding in PL/SQL
By Rolland Sovarszki, Reprinted from http://www.dba-village.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20705
Binding standard selections has helped me a lot in improving PL/SQL procedure performance.
Say you have a SELECT statement (it could be a regullar select into or a FOR loop), and you
have a column A which can have either value 1 or value 0.
The selection would look like this:
SELECT *
FROM <my tab>
WHERE column_A = 1
<.....>
SELECT *
FROM <my tab>
WHERE column_A = 0
In this case the Optimizer would create 2 definitions for basically the same SQL statement.
Instead you could use binding variables:
DECLARE
val_1 CONSTANT PLS_INTEGER := 0;
val_2 CONSTANT PLS_INTEGER := 1;
BEGIN
SELECT *
FROM <my tab>
WHERE column_A = val_2;
<.....>
SELECT *
FROM <my tab>
WHERE column_A = val_1;
END;
In this case the Optimizer would create only 1 definition:
SELECT *
FROM <my tab>
WHERE column_A = :bind;
:bind then would take either value 1, or value 0, depending on the variable.
In this case you saved the Optimizer from doing 2 execution plans for the same selection.
June's Tip of the Month
Design by Contract (DBC) and PL/SQL
By Zlatko Sirotic, Istra Informaticki Inzenjering, Croatia
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20706
Design by contract (DBC) is a method whose author is Bertrand Mayer, also the maker of the OOPL
language Eiffel (Eiffel was designed in 1985, commercialy released in 1986, ISO-standardized in 2006).
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. An
additional assertion in this class is called INVARIANT. Contract is based on the routine's
putting up an obligation to the caller (some other routine)
to satisfy conditions of precondition and conditions of invariant, and the called routine's obligation
to satisfy conditions of postcondition and conditions of invariant.
The object oriented Eiffel programming language was created to implement DBC.
For now, other OO (object-oriented) languages don’t directly support the ideas behind DBC.
However, precondition and postcondition are applicable to many programming languages, both OO and not OO.
Invariants are applicable only in OOPL.
This is my attempt to use DBC methodology (including invariants) in Oracle PL/SQL.
This is an Eiffel class interface (not like a Java interface, but more like PL/SQL package
specification)
from Bertrand Meyer's book "Object Oriented Software Construction", second edition (OOSC2), 1997,
page 390-391:
class interface STACK [G]
creation make
feature
make (n: INTEGER) is
require
non_negative_capacity: n >= 0
ensure
capacity_set: capacity = n
end
feature
capacity: INTEGER
count: INTEGER
item: G is -– Top element
require
not_empty: not empty
end
feature
empty: BOOLEAN is
ensure
empty_definition: Result = (count = 0)
end
full: BOOLEAN is
ensure
full_definition: Result = (count = capacity)
end
feature
put (x: G) is
require
not_full: not full
ensure
not_empty: not empty
added_to_top: item = x
one_more_item: count = old count + 1
end
remove is -– Remove top element
require
not_empty: not empty
ensure
not_full: not full
one_fewer: count = old count - 1
end
invariant
count_non_negative: 0 <= count
count_bounded: count <= capacity
empty_if_no_elements: empty = (count = 0)
end -– class interface STACK
PL/SQL "equivalent":
CREATE OR REPLACE TYPE array_t AS TABLE OF INTEGER
/
CREATE OR REPLACE PACKAGE dbc AS
c_no_check CONSTANT INTEGER := 0;
c_check_preconditions CONSTANT INTEGER := 1;
c_check_pre_postconditions CONSTANT INTEGER := 2;
c_check_pre_post_invariants CONSTANT INTEGER := 3;
FUNCTION check_preconditions RETURN BOOLEAN;
FUNCTION check_pre_postconditions RETURN BOOLEAN;
FUNCTION check_pre_post_invariants RETURN BOOLEAN;
PROCEDURE set_level (p_level INTEGER);
PROCEDURE display_error (p_error VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY dbc AS
m_level INTEGER := c_no_check;
FUNCTION check_preconditions RETURN BOOLEAN IS
BEGIN
IF m_level >= c_check_preconditions THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
FUNCTION check_pre_postconditions RETURN BOOLEAN IS
BEGIN
IF m_level >= c_check_pre_postconditions THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
FUNCTION check_pre_post_invariants RETURN BOOLEAN IS
BEGIN
IF m_level >= c_check_pre_post_invariants THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
PROCEDURE set_level (p_level INTEGER) IS
BEGIN
IF p_level NOT IN
(c_no_check, c_check_preconditions, c_check_pre_postconditions,
c_check_pre_post_invariants)
THEN
RAISE_APPLICATION_ERROR (-20000, 'Wrong checking level');
END IF;
m_level := p_level;
END;
PROCEDURE display_error (p_error VARCHAR2) IS
BEGIN
RAISE_APPLICATION_ERROR (-20000, 'ERROR in method ' || p_error);
END;
END;
/
CREATE OR REPLACE TYPE stack AS OBJECT (
capacity INTEGER,
el_count INTEGER,
stack_implementation array_t,
CONSTRUCTOR FUNCTION stack (n INTEGER) RETURN SELF AS RESULT,
MEMBER FUNCTION item (SELF IN OUT stack) RETURN INTEGER,
MEMBER FUNCTION empty RETURN BOOLEAN,
MEMBER FUNCTION full RETURN BOOLEAN,
MEMBER PROCEDURE put (x INTEGER),
MEMBER PROCEDURE remove,
MEMBER FUNCTION count_non_negative RETURN BOOLEAN,
MEMBER FUNCTION count_bounded RETURN BOOLEAN,
MEMBER FUNCTION empty_if_no_elements RETURN BOOLEAN,
MEMBER PROCEDURE check_invariants
) NOT FINAL;
/
CREATE OR REPLACE TYPE BODY stack AS
CONSTRUCTOR FUNCTION stack (n INTEGER) RETURN SELF AS RESULT IS
BEGIN
IF dbc.check_preconditions AND n < 0 THEN
dbc.display_error ('stack - PRE');
END IF;
check_invariants;
capacity := n;
stack_implementation := array_t();
stack_implementation.EXTEND (n);
IF dbc.check_pre_postconditions AND capacity <> n THEN
dbc.display_error ('stack - POST');
END IF;
check_invariants;
END;
MEMBER FUNCTION item (SELF IN OUT stack) RETURN INTEGER IS
BEGIN
IF dbc.check_preconditions AND empty THEN
dbc.display_error ('item - PRE');
END IF;
check_invariants;
RETURN stack_implementation(el_count);
END;
MEMBER FUNCTION empty RETURN BOOLEAN IS
BEGIN
IF el_count = 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
MEMBER FUNCTION full RETURN BOOLEAN IS
BEGIN
IF el_count = capacity THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
MEMBER PROCEDURE put (x INTEGER) IS
BEGIN
IF dbc.check_preconditions AND full THEN
dbc.display_error ('put - PRE');
END IF;
check_invariants;
el_count := el_count + 1;
stack_implementation(el_count) := x;
IF dbc.check_pre_postconditions AND (empty OR item <> x) THEN
dbc.display_error ('put - POST');
END IF;
check_invariants;
END;
MEMBER PROCEDURE remove IS BEGIN
IF dbc.check_preconditions AND empty THEN
dbc.display_error ('remove - PRE');
END IF;
check_invariants;
el_count := el_count - 1;
IF dbc.check_pre_postconditions AND full THEN
dbc.display_error ('remove - POST');
END IF;
check_invariants;
END;
MEMBER FUNCTION count_non_negative RETURN BOOLEAN IS
BEGIN
IF el_count >= 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
MEMBER FUNCTION count_bounded RETURN BOOLEAN IS
BEGIN
IF el_count <= capacity THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
MEMBER FUNCTION empty_if_no_elements RETURN BOOLEAN IS
BEGIN
IF empty AND (el_count = 0)
OR
NOT empty AND (el_count <> 0)
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
MEMBER PROCEDURE check_invariants IS
BEGIN
IF NOT dbc.check_pre_post_invariants THEN
RETURN;
END IF;
IF NOT count_non_negative THEN
dbc.display_error ('INVARIANT count_non_negative');
END IF;
IF NOT count_bounded THEN
dbc.display_error ('INVARIANT count_bounded');
END IF;
IF NOT empty_if_no_elements THEN
dbc.display_error ('INVARIANT empty_if_no_elements');
END IF;
END;
END;
/
July's Tip of the Month
Concatenating Multiple Rows Into A Single String
By Steven Rea, Reprinted from Steven Rea's Oracle Tips, Tricks and Scripts
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20707
If you have multiple rows that you want to concatenate into a single string, such as creating a
string of e-mail addresses from a list of those addresses returned by a "select" statement, you can
use this tip that I found at asktom.oracle.com, which makes a novel use of the Oracle analytical
functions.
In the example below, we are getting a list of e-mail addresses for customers with a zip code
of 72204, and concatenating that list into a comma-separated string. To plug in your own SQL,
simply replace "select email_addr myvalues from customers where zip = 72204" with your own SQL
that returns a single column of values, and label that column "myvalues".
In the example below, "myvalues" is the label of the list of e-mail addresses returned by the
"select from customers" SQL. "row_number() over (order by myvalues)" produces a sequential number
for each record returned by the "select from customers" SQL (this column is labeled "rn"), where
the records are ordered by the e-mail address. "count(*) over ()" produces the total number of
e-mail addresses returned by the "select from customers" SQL (this column is labeled "cnt").
"ltrim(sys_connect_by_path(myvalues, ','),',')" concatenates the "path" of values created by the
"start with" and "connect by prior" chain, placing a comma between the values and, then, removing
the leftmost comma (this column is labeled "catvalues"). "rn = cnt" selects the row where the row
number equals the count of records, thus just returning the final row with the full path of values.
You can get an idea of what this is doing by removing the "where rn = cnt" line and seeing the
records that are returned.
with data
as
(
select myvalues, row_number() over (order by myvalues) rn, count(*) over () cnt
from
(
select email_addr myvalues from customers where zip = 72204
)
)
select ltrim(sys_connect_by_path(myvalues, ','),',') catvalues
from data
where rn = cnt
start with rn = 1
connect by prior rn = rn-1;
August's Tip of the Month
Oracle 11g Sequence Enhancement
By Rajender Singh, Reprinted from OracleBrains
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20708
In Oracle, the sequences object provides a sequential series of numbers to applications without
the overhead of disk I/O or transaction locking. These values are often used for primary and unique
keys.
In the previous release of Oracle, you could refer to the sequence values in SQL statements with the
following pseudocolumns:
- CURRVAL - returns the current value of a sequence.
- NEXTVAL - increments the sequence and returns the next value.
Example:
SELECT employeeid.NEXTVAL
INTO l_employeeid
FROM DUAL;
But one drawback was that these pseudocolumns cannot be used directly in PL/SQL.
Sometimes this can be quite irritating from a programmer's point of view.
In Oracle Database 11g, it is now possible to simply use the pseudocolumns CURRVAL and NEXTVAL
in a PL/SQL expression.
Example:
l_employeeid := employeeid.NEXTVAL;
September's Tip of the Month
Dynamic Ref Cursor with Dynamic Fetch - An 11g Version
By Zlatko Sirotic, Istra Informaticki Inzenjering, Croatia
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20709
Sometimes there is a problem as follows: 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.
This is a hard problem, because ref cursors do not (directly) support description! We are going
to use "a good old" DBMS_SQL package and its procedure DESCRIBE_COLUMNS in order to make an unknown
record variable. This is my Oracle Magazine tip from 2003:
"Dynamic Ref Cursor with Dynamic Fetch".
Prior to 11g, the calling function must return not only the ref cursor, but the original query also.
In 11g we can use new features DBMS_SQL.TO_CURSOR_NUMBER and DBMS_SQL.TO_REFCURSOR
(to convert a ref cursor to DBMS_SQL cursor and vice versa) - now the calling function can return
only the ref cursor.
1. "Generic" package
First we are going to make a "dyn_fetch" package. Procedure "describe_columns" creates
"g_record_def" dynamic record structure, with column names as col_1 (col_2 ...), because the SELECT
clause in the query can be without aliases (for example "SELECT deptno || dname FROM dept").
CREATE OR REPLACE PACKAGE dyn_fetch IS
g_count NUMBER;
g_record_def VARCHAR2 (32000);
PROCEDURE describe_columns (p_ref_cur IN OUT SYS_REFCURSOR);
PROCEDURE fetch_ref_cur (
p_ref_cur SYS_REFCURSOR,
p_process_def VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY dyn_fetch IS
varchar2_type CONSTANT PLS_INTEGER := 1;
number_type CONSTANT PLS_INTEGER := 2;
date_type CONSTANT PLS_INTEGER := 12;
rowid_type CONSTANT PLS_INTEGER := 11;
char_type CONSTANT PLS_INTEGER := 96;
long_type CONSTANT PLS_INTEGER := 8;
raw_type CONSTANT PLS_INTEGER := 23;
mlslabel_type CONSTANT PLS_INTEGER := 106;
clob_type CONSTANT PLS_INTEGER := 112;
blob_type CONSTANT PLS_INTEGER := 113;
bfile_type CONSTANT PLS_INTEGER := 114;
PROCEDURE describe_columns (p_ref_cur IN OUT SYS_REFCURSOR) IS
l_cur INTEGER;
l_desc_tab DBMS_SQL.DESC_TAB;
l_type VARCHAR2 (100);
l_col_type PLS_INTEGER;
l_col_max_len PLS_INTEGER;
l_col_precision PLS_INTEGER;
l_col_scale PLS_INTEGER;
BEGIN
l_cur := DBMS_SQL.TO_CURSOR_NUMBER (p_ref_cur); -- new feature in 11g
DBMS_SQL.DESCRIBE_COLUMNS (l_cur, g_count, l_desc_tab);
p_ref_cur := DBMS_SQL.TO_REFCURSOR (l_cur); -- new feature in 11g
g_record_def := NULL;
FOR i IN 1..g_count LOOP
l_col_type := l_desc_tab(i).col_type;
l_col_max_len := l_desc_tab(i).col_max_len;
l_col_precision := l_desc_tab(i).col_precision;
l_col_scale := l_desc_tab(i).col_scale;
IF l_col_type = varchar2_type THEN
l_type := 'VARCHAR2(' || l_col_max_len || ')';
ELSIF l_col_type = number_type THEN
l_type := 'NUMBER(' || l_col_precision || ',' || l_col_scale || ')';
ELSIF l_col_type = date_type THEN
l_type := 'DATE';
ELSIF l_col_type = rowid_type THEN
l_type := 'ROWID';
ELSIF l_col_type = char_type THEN
l_type := 'CHAR(' || l_col_max_len || ')';
-- ELSIF l_col_type = ...
-- long_type, raw_type ...
END IF;
g_record_def := g_record_def || ' col_' || i || ' ' || l_type || ',';
END LOOP;
g_record_def := RTRIM (g_record_def, ',');
END;
PROCEDURE fetch_ref_cur (
p_ref_cur SYS_REFCURSOR,
p_process_def VARCHAR2)
IS
l_statement VARCHAR2 (32000);
BEGIN
l_statement :=
' DECLARE
TYPE record_t IS RECORD (' || dyn_fetch.g_record_def || ');
l_record record_t;
BEGIN
LOOP
FETCH :p_ref_cur INTO l_record;
EXIT WHEN :p_ref_cur%NOTFOUND;' ||
p_process_def || '
END LOOP;
CLOSE :p_ref_cur;
END;
';
EXECUTE IMMEDIATE l_statement USING p_ref_cur; -- works in 10g
END;
END;
/
2. Function (or procedure) that returns ref cursor
CREATE OR REPLACE FUNCTION test_ref_cur RETURN SYS_REFCURSOR IS
l_query VARCHAR2 (32000);
l_ref_cur SYS_REFCURSOR;
BEGIN
l_query :=
' SELECT e.empno, e.ename,
e.deptno, d.dname
FROM emp e,
dept d
WHERE e.deptno = d.deptno
';
OPEN l_ref_cur FOR l_query;
RETURN l_ref_cur;
END;
/
3. Procedure that uses function that returns ref cursor
Procedure first calls "test_ref_cur" function that returns ref cursor. Then it calls the
"dyn_fetch.describe_columns" procedure in order to get the dynamically generated record structure,
and "dyn_fetch.fetch_ref_cur" procedure, sending two parameters: ref cursor and process definition through the
(internal) "process_def" function (in this case to show up rows with DBMS_SQL.PUT_LINE).
CREATE OR REPLACE PROCEDURE test_fetch_ref_cur IS
l_ref_cur SYS_REFCURSOR;
FUNCTION process_def RETURN VARCHAR2 IS
l_process_def VARCHAR2 (32000);
BEGIN
l_process_def := 'DBMS_OUTPUT.PUT_LINE (';
FOR i IN 1 .. dyn_fetch.g_count LOOP
l_process_def := l_process_def || ' l_record.col_' || i || ' || ''>>'' || ';
END LOOP;
l_process_def := RTRIM (l_process_def, ' || ''>>'' || ') || ');';
RETURN l_process_def;
END;
BEGIN
l_ref_cur := test_ref_cur;
dyn_fetch.describe_columns (l_ref_cur);
dyn_fetch.fetch_ref_cur (
p_ref_cur => l_ref_cur,
p_process_def => process_def);
END;
/
We can test this with:
SET SERVEROUTPUT ON;
EXECUTE test_fetch_ref_cur;
October's Tip of the Month
OR(DBMS) or R(DBMS), That is the Question
By Zlatko Sirotic, Istra Informaticki Inzenjering, Croatia
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20710
In the late 1990s, several vendors (including Oracle) released object-relational DBMS products
(ORDBMS).
But, C.J.Date in Database in Depth (2005, page 32) says:
"After all, the whole point of an "object/relational" system is precisely
that we can have attribute values in relations that are of arbitrary complexity.
Perhaps a better way to say it is this: a proper object/relational system is just a relational system
with proper type support - which just means it's a proper relational system, no more and no
less."
In An introduction to Database Systems (2004, page 885), Date says:
"... object/relational systems ... are, or should be, basically just relational systems that support
the relational domain concept (i.e., types) properly - in other words, true relational systems,
meaning in particular systems that allow users to define their own types."
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.
First create object type address_t:
CREATE OR REPLACE TYPE address_t AS OBJECT (
state VARCHAR2(30),
city VARCHAR2(30),
postal_code VARCHAR2(16)
) NOT FINAL
/
Next, create a relational table that has one column of object type address_t.
(Oracle 8.0 / 8i documentation uses the term "object-relational table" for this type of table;
but in Oracle 9i / 10g / 11g documentation this is simply "relational table".)
CREATE TABLE users_tab (
user_id NUMBER(30) PRIMARY KEY,
first_name VARCHAR2(30),
last_name VARCHAR2(30),
address address_t
)
/
INSERT INTO users_tab
VALUES (1, 'FN1', 'LN1', address_t ('S1', 'C1', 'PC1'));
This SELECT shows the physical structure of table users_tab:
SELECT segcol#, name, segcollength, type#
FROM sys.col$
WHERE obj# =
(SELECT object_id
FROM user_objects
WHERE object_name = 'USERS_TAB'
)
ORDER BY segcol#;
SEGCOL# NAME SEGCOLLENGTH TYPE#
---------- ------------------------------ ------------ ----------
1 USER_ID 22 2
2 FIRST_NAME 30 1
3 LAST_NAME 30 1
4 ADDRESS 1 121
5 SYS_NC00005$ 16 23
6 SYS_NC00006$ 30 1
7 SYS_NC00007$ 30 1
8 SYS_NC00008$ 16 1
Hidden columns SYS_NC00006$, SYS_NC00007$, SYS_NC00008$ corresponds to attributes state,
city, postal_code.
Now, create a subtype address_sub_t under (supertype) address_t:
CREATE OR REPLACE TYPE address_sub_t UNDER address_t (
street VARCHAR2(40)
)
/
INSERT INTO users_tab
VALUES (2, 'FN2', 'LN2', address_sub_t ('S2', 'C2', 'PC2', 'STREET 1'));
SELECT shows the changed physical structure of table users_tab:
SELECT segcol#, name, segcollength, type#
FROM sys.col$
WHERE obj# =
(SELECT object_id
FROM user_objects
WHERE object_name = 'USERS_TAB'
)
ORDER BY segcol#;
SEGCOL# NAME SEGCOLLENGTH TYPE#
---------- ------------------------------ ------------ ----------
1 USER_ID 22 2
2 FIRST_NAME 30 1
3 LAST_NAME 30 1
4 ADDRESS 1 121
5 SYS_NC00005$ 16 23
6 SYS_NC00006$ 30 1
7 SYS_NC00007$ 30 1
8 SYS_NC00008$ 16 1
9 SYS_NC00009$ 40 1
Oracle automatically adds a new hidden column, SYS_NC00009, that corresponds to attribute street!
Various SELECT statements:
SELECT user_id, first_name, last_name,
address
FROM users_tab
ORDER BY user_id;
USER_ID FIRST_NAME LAST_NAME ADDRESS(STATE, CITY, POSTAL_CODE)
---------- ------------- ----------- -----------------------------------------------------
1 FN1 LN1 ADDRESS_T('S1', 'C1', 'PC1')
2 FN2 LN2 ADDRESS_SUB_T('S2', 'C2', 'PC2', 'STREET 1')
SELECT user_id, first_name, last_name,
u.address.state
FROM users_tab u
ORDER BY user_id;
USER_ID FIRST_NAME LAST_NAME ADDRESS.STATE
---------- ------------------------------ ------------------------------ --------------
1 FN1 LN1 S1
2 FN2 LN2 S2
We can't (directly) select hidden columns that corresponds to attributes of subtype
(address_sub_t):
SELECT user_id, first_name, last_name,
u.address.street
FROM users_tab u
ORDER BY user_id;
u.address.street
*
ERROR at line 2:
ORA-00904: "U"."ADDRESS"."STREET": invalid identifier
But we can use TREAT ("object_column" AS "subtype"):
SELECT user_id, first_name, last_name,
TREAT (address AS address_sub_t).street address_street
FROM users_tab
ORDER BY user_id;
USER_ID FIRST_NAME LAST_NAME ADDRESS_STREET
---------- ------------------------------ ------------------------------ ----------------
1 FN1 LN1
2 FN2 LN2 STREET 1
Show rows that belongs to a selected subtype:
SELECT user_id, first_name, last_name,
TREAT (address AS address_sub_t).street address_street
FROM users_tab
WHERE address IS OF (address_sub_t)
ORDER BY user_id;
USER_ID FIRST_NAME LAST_NAME ADDRESS_STREET
---------- ------------------------------ ------------------------------ ---------------
2 FN2 LN2 STREET 1
Show rows that belongs to a selected (super)type and all its subtypes:
SELECT user_id, first_name, last_name,
TREAT (address AS address_sub_t).street address_street
FROM users_tab
WHERE address IS OF (address_t)
ORDER BY user_id;
USER_ID FIRST_NAME LAST_NAME ADDRESS_STREET
---------- ------------------------------ ------------------------------ ----------------
1 FN1 LN1
2 FN2 LN2 STREET 1
Show rows that belongs to selected (super)type only:
SELECT user_id, first_name, last_name,
TREAT (address AS address_sub_t).street address_street
FROM users_tab
WHERE address IS OF (ONLY address_t)
ORDER BY user_id;
USER_ID FIRST_NAME LAST_NAME ADDRESS_STREET
---------- ------------------------------ ------------------------------ ---------------
1 FN1 LN1
We can update the whole object column:
UPDATE users_tab
SET address = address_sub_t ('S2', 'C2', 'PC2', 'STREET 2')
WHERE user_id = 2;
We can update hidden columns that correspond to attributes of the root type (address_t):
UPDATE users_tab u
SET u.address.state = 'STATE 2'
WHERE user_id = 2;
But we can't (directly) update hidden columns that correspond to attributes of a subtype
(address_sub_t):
UPDATE users_tab u
SET u.address.street = 'STREET 2'
WHERE user_id = 2;
SET u.address.street = 'STREET 2'
*
ERROR at line 2:
ORA-00904: "U"."ADDRESS"."STREET": invalid identifier
or
UPDATE users_tab
SET TREAT (addres AS address_sub_t).street = 'STREET 2'
WHERE user_id = 2;
SET TREAT (addres AS address_sub_t).street = 'STREET 2'
*
ERROR at line 2:
ORA-00927: missing equal sign
This works - UPDATE inline wiew:
UPDATE (SELECT TREAT (address AS address_sub_t).street address_street
FROM users_tab
WHERE user_id = 2
)
SET address_street = 'STREET 2';
or
UPDATE (SELECT user_id,
TREAT (address AS address_sub_t).street address_street
FROM users_tab
)
SET address_street = 'STREET 2'
WHERE user_id = 2;
Novemer's Tip of the Month
Best Practices to Improve SQL Performance
By P. V. Neir, Reprinted from Articles.Freemegazone
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20711
Being developers we often see clients complaining about database performance. Performance
problems arise when we ignore good practices. In this article I briefly list some of the best
practices one
should follow while working with SQL.
- The database design should be simple and clear. It should be user friendly. It should have flexible structure so that depending on the future requirements the database can easily be expanded.
- Store relevant and necessary information in the database.
- Use normalized tables in the database. It is better to use small multiple tables instead of using one large table.
- Use denormalization as the last option for your database.
- Use database look up tables. It will help you maintain data integrity of your database.
- Use numeric or small width primary key for your database tables. Processing small data takes less time.
- Do not store images in the database. Store URLs of images instead.
- Do not display SQL errors to users. Most of the attackers get benefit of error messages.
- Make an ERROR table in the database. Storing errors in the ERROR table will help you keep track of bugs.
- Assign proper data types to the fields in the database. It is better to use TINYINT for age rather than VARCHAR (45).
- Use database field names in SELECT, INSERT, UPDATE commands. Avoid using SELECT * or INSERT INTO TABLE VALUES (value1, value2...).
- Use LIKE clause properly. Improper usage may cause performance problems in your database.
- Use database server's timestamp instead of web server's timestamp.
- Use alias in the SQL statements which require multiple tables.
- Write SQL commands in capital letters. It helps in readability.
- Use nested queries as minimum as possible. Use JOIN statement instead.
- Avoid query running in the loop.
- Use stored procedures. They are fast and help in maintainability and security of the database.
- Avoid CURSOR, GOTO and IF statements. Prefer SWITCH CASES.
- Use comments.
- Do proper indexing. It will improve the speed of operations in the database tables. Improper or too much indexing affects performance of the database.
- Use inet_aton() function to store IPs as integers in the database. The inet_aton() function converts the string, in the Internet standard dot notation, to a network address, and stores the address in the structure provided.
- Retrieve the IPs with inet_ntoa() function. The inet_ntoa() function converts the specified Internet host address to a string in the Internet standard dot notation.
- Always encrypt the sensitive data.
- Do not rely on server configurations. Always write the most portable code possible.
- Do proper documentation of your application.
- Always do testing of your database application. Make cases based on requirements and maintain a checklist of the tests.
Decemer's Tip of the Month
Mixed Parameter Placement in Oracle 11g Functions
By Arup Nanda, for Knowledge Xpert for PL/SQL
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20712
Here is a simple function to illustrate the concept:
CREATE OR REPLACE FUNCTION do_something (p_in_1 NUMBER, p_in_2 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_in_1 + p_in_2;
END;
/
Since there are two parameters, you can either call the function by passing parameters as positional values as shown below:
BEGIN
do_something (1, 2);
END;
Or, as named parameters:
BEGIN
do_something ( p_in_1 => 1, p_in_2 => 2);
END;
However, prior to Oracle Database 11g, the latter had issues if used in select statements. In Oracle Database 10g and below, if you issue the following statement:
SELECT do_something (p_in_1 => 1, p_in_2 => 1)
FROM DUAL;
You will get the error:
select do_something (p_in_1=>1,p_in_2=>1) from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis
In Oracle Database 11g, you are free to use this notation.
SQL> select do_something (p_in_1=>1,p_in_2=>1) from dual;
DO_SOMETHING(P_IN_1=>1,P_IN_2=>1)
-------------------------------
2
1 row selected.
This works perfectly. You can specify the named notation towards the end; the first ones must be positional. For instance, the following will be valid, where the parameter p_in_1 is set to 1:
SELECT do_something (1, p_in_2 => 2)
FROM DUAL
But this one will not be (the positional parameter is at the end):
SQL> SELECT do_something (p_in_1=>1, 2)
FROM DUAL;
select do_something (p_in_1=>1, 2) from dual
*
ERROR at line 1:
ORA-06553: PLS-312: a positional parameter association may not follow a named association
For questions
or comments on this site: webmaster@quest-pipelines.com
All content Copyright
© Quest Software, Inc. All rights reserved.
|
