| Please Note: Unless otherwise noted, all of the
PL/SQL
Pipeline's monthly tips are contributed by Steven Feuerstein.
|
Oracle PL/SQL Tips of the Month - 2002
| January | Script to Calculate Years, Months and Days Between Two Dates |
| February | Stored Procedure to Identify Tables
Having Foreign Keys on the Given Table's Primary Key |
| March | Stored Procedure to Disable/Enable Foreign Key Constraints Against a Specified Parent Table |
| April | Turning a SQL Statement into XML Using the C Version of XSU |
| May | Function for Determining the Constrained Length of a VARCHAR2 Parameter Sent to a Procedure |
| June | Simulating ROLLBACK TO SAVEPOINT Behavior in a Database Trigger |
| July | Speeding Up DBA% View Queries |
| August | Package to Generate XML Based on a SQL Query |
| September | A instr Function That Accepts Wildcards |
| October | Passing a Collection into a Cursor |
| November | Generating Sequence Numbers Without Gaps |
| December | Improved Script to Calculate Years, Months and Days Between Two Dates |
2001 Tips
2000 Tips
1999 Tips
1998 Tips
1997 Tips
January's Tip of the Month
Script to Calculate Years, Months and Days Between Two Dates
Compliments of Jason Bainbridge, (jason.bainbridge@mbox.com.au)
I often see
developers do the following to get the average years as a decimal:
(DateTo - DateFrom)/365.25
...and just as often I hear users complaining about age sensitive
applications not behaving correctly due to this kind of averaging, so I
wrote the script below. The script is pretty basic but it can be easily modified to suit whatever needs you may have.
Inputs to the script are just a date from and a date to, outputs are
the number of calendar years, months and days between the two dates,
which could easily be converted to a decimal if need be.
DECLARE
vdDateFrom DATE;
vnDayFrom NUMBER := 0;
vnMonthFrom NUMBER := 0;
vnYearFrom NUMBER := 0;
vdDateTo DATE;
vnDayTo NUMBER := 0;
vnMonthTo NUMBER := 0;
vnYearTo NUMBER := 0;
vnYearsDiff NUMBER := 0;
vnMonthsDiff NUMBER := 0;
vnDaysDiff NUMBER := 0;
E_STOP EXCEPTION;
BEGIN
vdDateFrom := '01/11/2001';
dbms_output.put_line('Date From: '||vdDateFrom);
vdDateTo := '17/11/2001';
dbms_output.put_line('Date To: '||vdDateTo);
IF vdDateTo < vdDateFrom THEN
RAISE E_STOP;
END IF;
vnDayFrom := to_char(vdDateFrom, 'dd');
vnMonthFrom := to_char(vdDateFrom, 'mm');
vnYearFrom := to_char(vdDateFrom, 'yyyy');
vnDayTo := to_char(vdDateTo, 'dd');
vnMonthTo := to_char(vdDateTo, 'mm');
vnYearTo := to_char(vdDateTo, 'yyyy');
IF vnMonthTo - vnMonthFrom < 0 THEN
vnYearsDiff := vnYearTo - vnYearFrom - 1;
ELSIF vnMonthTo - vnMonthFrom > 0 THEN
vnYearsDiff := vnYearTo - vnYearFrom;
ELSE
IF vnDayTo - vnDayFrom >= 0 THEN
vnYearsDiff := vnYearTo - vnYearFrom;
ELSIF
vnDayTo - vnDayFrom < 0 THEN
vnYearsDiff := vnYearTo - vnYearFrom - 1;
END IF;
END IF;
dbms_output.put_line('Years: '||vnYearsDiff);
IF vnMonthTo - vnMonthFrom < 0 THEN
IF vnDayTo - vnDayFrom >= 0 THEN
vnMonthsDiff := 12 + vnMonthTo - vnMonthFrom;
ELSIF
vnDayTo - vnDayFrom < 0 THEN
vnMonthsDiff := 12 + vnMonthTo - vnMonthFrom - 1;
END IF;
ELSIF vnMonthTo - vnMonthFrom = 0 THEN
IF vnDayTo - vnDayFrom >= 0 THEN
vnMonthsDiff := 0;
ELSIF
vnDayTo - vnDayFrom < 0 THEN
vnMonthsDiff := 11;
END IF;
ELSE
IF vnDayTo - vnDayFrom >= 0 THEN
vnMonthsDiff := vnMonthTo - vnMonthFrom;
ELSIF
vnDayTo - vnDayFrom < 0 THEN
vnMonthsDiff := vnMonthTo - vnMonthFrom - 1;
END IF;
END IF;
dbms_output.put_line('Months: '||vnMonthsDiff);
IF vnMonthFrom = vnMonthTo THEN
vnDaysDiff := vnDayTo - vnDayFrom;
IF vnDaysDiff < 0 THEN
vnDaysDiff := to_char(last_day(add_months(vdDateTo,-1)),'dd') - vnDayFrom;
IF vnDaysDiff < 0 THEN
vnDaysDiff := vnDayTo;
ELSIF vnDaysDiff >= 0 THEN
vnDaysDiff := vnDaysDiff + vnDayTo;
END IF;
END IF;
ELSE
vnDaysDiff := vnDayTo - vnDayFrom;
IF vnDaysDiff < 0 THEN
vnDaysDiff := to_char(last_day(add_months(vdDateTo,-1)),'dd') - vnDayFrom;
IF vnDaysDiff < 0 THEN
vnDaysDiff := vnDayTo;
ELSIF vnDaysDiff >= 0 THEN
vnDaysDiff := vnDaysDiff + vnDayTo;
END IF;
END IF;
END IF;
dbms_output.put_line('Days: '||vnDaysDiff);
EXCEPTION
WHEN E_STOP THEN
dbms_output.put_line('Error: Date To is less than Date From.');
END;
February's Tip of the Month
Stored Procedure to Identify Tables
Having Foreign Keys on the Given Table's Primary Key
Compliments of Dan Clamage, PL/SQL Pipeline SYSOP (danielj@clamage.com)
Frequently, you need to know what tables have foreign keys on a specified
table's primary key. This stored procedure displays them.
CREATE OR REPLACE
PROCEDURE show_fkeys(
p_table_name IN user_constraints.table_name%TYPE)
IS
CURSOR id_fkeys (
c_table_name user_constraints.table_name%TYPE)
IS
SELECT table_name, constraint_name fkey, r_constraint_name pkey,
status
FROM user_constraints
WHERE constraint_type='R'
AND r_constraint_name IN (
SELECT constraint_name
FROM user_constraints
WHERE table_name=c_table_name
AND constraint_type='P')
ORDER BY table_name, constraint_name;
rec_id_fkeys id_fkeys%ROWTYPE;
l_table_name user_constraints.table_name%TYPE;
l_pkey_name user_constraints.constraint_name%TYPE;
l_status NUMERIC;
BEGIN
l_table_name := UPPER(p_table_name);
SELECT constraint_name
INTO l_pkey_name
FROM user_constraints
WHERE table_name=l_table_name
AND constraint_type='P';
DBMS_OUTPUT.put_line(
'show_fkeys: foreign key constraints on table [' || l_table_name
|| ']');
DBMS_OUTPUT.put_line(
' whose primary key is [' || l_pkey_name || ']');
OPEN id_fkeys(l_table_name);
LOOP
FETCH id_fkeys INTO rec_id_fkeys;
EXIT WHEN id_fkeys%NOTFOUND;
DBMS_OUTPUT.put_line(RPAD('Table: [' || rec_id_fkeys.table_name
|| ']',40) ||
RPAD('FK Name: [' || rec_id_fkeys.fkey ||
']',42) ||
'Status: [' || rec_id_fkeys.status || ']');
END LOOP; -- display foreign keys
IF (id_fkeys%ROWCOUNT = 0) THEN
DBMS_OUTPUT.put_line(
'show_fkeys: No foreign keys found against table ' || l_table_name);
END IF; -- no rows found
CLOSE id_fkeys;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line(
'show_fkeys: no primary key exists for table ' || l_table_name);
WHEN OTHERS THEN
l_status := SQLCODE;
DBMS_OUTPUT.put_line('show_fkeys: ' || SQLERRM(l_status));
IF (id_fkeys%ISOPEN) THEN
CLOSE id_fkeys;
END IF;
END show_fkeys;
/
March's Tip of the Month
Stored Procedure to Disable/Enable Foreign Key Constraints Against a Specified Parent Table
Compliments of Dan Clamage, PL/SQL Pipeline SYSOP (danielj@clamage.com)
Sometimes you want to truncate a table which has foreign keys in
other tables pointing to the primary key of the table to be truncated.
But you can't truncate a table that has foreign keys pointing to
its primary key, even if the child tables are empty! You must first
disable the foreign key constraints. Then when you've finished reloading
data, you can enable the foreign key constraints. This stored procedure
simplifies the task of disabling/enabling foreign key constraints
against the specified parent table.
CREATE OR REPLACE
PROCEDURE control_fkeys(
p_table_name IN user_constraints.table_name%TYPE
,p_enable_flag IN NUMBER
,p_status IN OUT NUMERIC)
IS
k_enable user_constraints.status%TYPE := 'ENABLE';
k_disable user_constraints.status%TYPE := 'DISABLE';
CURSOR id_fkeys (
c_table_name user_constraints.table_name%TYPE
,c_status user_constraints.status%TYPE)
IS
SELECT table_name, constraint_name fkey, r_constraint_name pkey,
status
FROM user_constraints
WHERE constraint_type='R'
AND status!=c_status
AND r_constraint_name IN (
SELECT constraint_name
FROM user_constraints
WHERE table_name=c_table_name
AND constraint_type='P')
ORDER BY table_name, constraint_name;
rec_id_fkeys id_fkeys%ROWTYPE;
l_status user_constraints.status%TYPE;
l_table_name user_constraints.table_name%TYPE;
l_stmt VARCHAR2(255);
l_pkey_name user_constraints.constraint_name%TYPE;
BEGIN
p_status := 0;
l_table_name := UPPER(p_table_name);
IF (p_enable_flag = 1) THEN
l_status := k_enable;
ELSIF (p_enable_flag = 0) THEN
l_status := k_disable;
ELSE
DBMS_OUTPUT.put_line(
|| ']');
p_status := 1001;
END IF;
IF (p_status = 0) THEN
SELECT constraint_name
INTO l_pkey_name
FROM user_constraints
WHERE table_name=l_table_name
AND constraint_type='P';
DBMS_OUTPUT.put_line(
' ' || l_status ||
' foreign key constraints on table ' || l_table_name ||
' whose primary key is ' || l_pkey_name);
OPEN id_fkeys(l_table_name, l_status || 'D');
LOOP
FETCH id_fkeys INTO rec_id_fkeys;
EXIT WHEN id_fkeys%NOTFOUND;
l_stmt := 'ALTER TABLE ' || rec_id_fkeys.table_name || ' '
|| l_status ||
' CONSTRAINT ' || rec_id_fkeys.fkey;
DBMS_OUTPUT.put_line(l_stmt);
EXECUTE IMMEDIATE l_stmt;
END LOOP;
IF (id_fkeys%ROWCOUNT = 0) THEN -- no fkeys found that weren't
enabled/disabled
DBMS_OUTPUT.put_line(
'-- control_fkeys: No foreign keys found against table ' ||
l_table_name || ' to ' || l_status);
END IF;
CLOSE id_fkeys;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_status := 1002;
DBMS_OUTPUT.put_line(
'-- control_fkeys: no primary key exists for table ' || l_table_name);
WHEN OTHERS THEN
p_status := SQLCODE;
DBMS_OUTPUT.put_line('-- control_fkeys: ' || SQLERRM(p_status));
IF (id_fkeys%ISOPEN) THEN
CLOSE id_fkeys;
END IF;
END control_fkeys;
/
The status parameter can be used by a calling program to determine
the success or failure of the requested operation. You might use
this routine in a batch loading operation.
April's Tip of the Month
Turning a SQL Statement into XML Using the C Version of XSU
Compliments of the Oracle XML Portal
Oracle 9i uses a new C based XDK. The package interface to this is called DBMS_XMLGEN. This has caused
some confusion, because in Oracle 8i there was an older JAVA based package called XMLGEN. If you're
using Oracle 8i, use the DBMS_XMLQUERY and DBMS_XMLSAVE JAVA based packages. For Oracle 9i, use the C
based package DBMS_XMLGEN.
This example makes use of the new package DBMS_XMLGEN. This new package makes use of a C interface and
is much, much faster than DBMS_XMLQUERY or XMLGEN. However, the new package has limited functionality.
It does not allow you to specify tag case, for example. You also cannot use binding, however, the improved
speed makes up for it. Missing functionality such as this should be added in future releases.
PROCEDURE sp_getnegotiation (ngtn_id IN NUMBER,
cl_instance_party OUT CLOB
) IS
BEGIN
queryCtx := DBMS_XMLGEN.newContext('SELECT * FROM v_ngtn_party
WHERE id = ' || ngtn_id);
dbms_xmlgen.setRowTag(QueryCTX,'EMP_ROW');
dbms_xmlgen.setRowsetTag(QueryCTX,'EMP_RESULTS');
cl_instance_party := DBMS_XMLGEN.getXML(queryCtx);
DBMS_XMLGEN.closeContext(queryCtx);
END sp_getnegotiation;
May's Tip of the Month
Function for Determining the Constrained Length of a VARCHAR2 Parameter Sent to a Procedure
Compliments of Dan Clamage, PL/SQL Pipeline SYSOP danielj@clamage.com
One Pipeliner asked if there was a way for a procedure to find out the constrained
length of a VARCHAR2 parameter sent to it. So I developed the following
function. For example, you could use it in a SUBSTR call to make
sure you don't inadvertently overrun the parameter's length.
CREATE OR REPLACE
FUNCTION varlen(p_str IN OUT VARCHAR2)
RETURN NUMBER
IS
v_lo_len PLS_INTEGER := NVL(LENGTH(p_str),1);
varchar2
v_hi_len PLS_INTEGER := 32768;
v_mid_len PLS_INTEGER := 0;
v_lst_len PLS_INTEGER := -1;
v_save_str VARCHAR2(32767);
read
BEGIN
v_save_str := p_str;
WHILE (v_lst_len != v_mid_len) LOOP
v_lst_len := v_mid_len;
v_mid_len := FLOOR((v_hi_len + v_lo_len) / 2);
BEGIN
p_str := RPAD('A', v_mid_len, 'A');
v_lo_len := v_mid_len;
EXCEPTION
WHEN VALUE_ERROR THEN
v_hi_len := v_mid_len;
END;
END LOOP;
p_str := v_save_str;
RETURN(v_mid_len);
EXCEPTION
WHEN OTHERS THEN
p_str := v_save_str;
RETURN(TO_NUMBER(NULL));
END varlen;
June's Tip of the Month
Simulating ROLLBACK TO SAVEPOINT Behavior in a Database Trigger
Compliments of Zlatko Sirotic, (zlatko.sirotic@iii.hr)
Sometimes we need a transaction to be successful even if some parts of it fail.
In these cases we usually use SAVEPOINT / ROLLBACK TO SAVEPOINT statements.
However, attempting to use SAVEPOINT / ROLLBACK TO SAVEPOINT statements in a database trigger
results in errors like these:
ORA-04092: cannot SET SAVEPOINT in a trigger
ORA-04092: cannot ROLLBACK in a trigger
It is possible to simulate SAVEPOINT / ROLLBACK TO SAVEPOINT statements in the database trigger.
We'll demonstrate this using a simple example.
Suppose we need a three part transaction:
- One row of DEPT insertion
- Two rows of EMP insertion, with job = MANAGER and which go to the previously inserted DEPT row
- Two rows of EMP insertion, with job = PROGRAMER and which go to the previously inserted DEPT row
(Note that we write PROGRAMER (misspelled) instead of PROGRAMMER because that job column has only nine
characters).
Then suppose we need a successful transaction even if the third part fails,
but will cancel what the third part did (that is, only one EMP insertion).
Therefore the transaction is only correct when at the end of the transaction we have:
One row of DEPT, two rows of EMP = MANAGER, 2 rows of EMP = PROGRAMER
OR
One row of DEPT and two rows of EMP = MANAGER
Let's start by creating the following package:
CREATE OR REPLACE PACKAGE example_pkg IS
PROCEDURE insert_emps_for_dept (p_deptno dept.deptno%TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY example_pkg IS
PROCEDURE insert_managers (p_deptno dept.deptno%TYPE) IS
BEGIN
INSERT INTO emp (empno, ename, job, mgr, sal, deptno)
VALUES (1, 'EMP 1', 'MANAGER', NULL, 5000, p_deptno);
INSERT INTO emp (empno, ename, job, mgr, sal, deptno)
VALUES (2, 'EMP 2', 'MANAGER', 1, 4000, p_deptno);
END;
PROCEDURE insert_programmers (p_deptno dept.deptno%TYPE) IS
BEGIN
INSERT INTO emp (empno, ename, job, mgr, sal, deptno)
VALUES (3, 'EMP 3', 'PROGRAMER', 1, 1000, p_deptno);
RAISE_APPLICATION_ERROR
(-20001, 'SIMULATED ERROR IN MIDLE OF PART 3 OF A TRANSACTION');
INSERT INTO emp (empno, ename, job, mgr, sal, deptno)
VALUES (4, 'EMP 4', 'PROGRAMER', 1, 1000, p_deptno);
END;
PROCEDURE insert_emps_for_dept (p_deptno dept.deptno%TYPE) IS
BEGIN
insert_managers (p_deptno);
BEGIN
insert_programmers (p_deptno);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END;
END;
/
Now we call the procedure from an unnamed PL/SQL block (without a database trigger):
BEGIN
INSERT INTO dept (deptno, dname) VALUES (1, 'DEPT 1');
example_pkg.insert_emps_for_dept (1);
END;
/
Let's see what we got using a SELECT query:
SELECT emp.empno, emp.ename, dept.deptno, dept.dname
FROM emp, dept
WHERE empno BETWEEN 1 AND 4
AND emp.deptno = dept.deptno
ORDER BY empno
/
EMPNO ENAME DEPTNO DNAME
---------- --------------- ---------- --------------
1 EMP 1 1 DEPT 1
2 EMP 2 1 DEPT 1
3 EMP 3 1 DEPT 1
Of course, the transaction wasn't correct because EMP 3 remained inserted.
Now let's execute a ROLLBACK and change the insert_emps_for_dept procedure by adding
SAVEPOINT / ROLLBACK TO SAVEPOINT statements:
PROCEDURE insert_emps_for_dept (p_deptno dept.deptno%TYPE) IS
BEGIN
insert_managers (p_deptno);
BEGIN
SAVEPOINT before_insert_programmers;
insert_programmers (p_deptno);
EXCEPTION
WHEN OTHERS THEN ROLLBACK TO before_insert_programmers;
END;
END;
If the previous procedure is executed, we'll get correct results:
EMPNO ENAME DEPTNO DNAME
---------- --------------- ---------- --------------
1 EMP 1 1 DEPT 1
2 EMP 2 1 DEPT 1
Now let's execute a ROLLBACK again and create a database trig
ger:
CREATE OR REPLACE TRIGGER air_dept
AFTER INSERT ON dept
FOR EACH ROW
BEGIN
example_pkg.insert_emps_for_dept (:NEW.deptno);
END;
/
Then try to execute a command:
INSERT INTO dept (deptno, dname) VALUES (1, 'DEPT 1')
/
This produces the following error:
ERROR at line 1:
ORA-04092: cannot ROLLBACK in a trigger
ORA-06512: at "SCOTT.EXAMPLE_PKG", line 33
ORA-04092: cannot SET SAVEPOINT in a trigger
ORA-06512: at "SCOTT.AIR_DEPT", line 2
ORA-04088: error during execution of trigger 'SCOTT.AIR_DEPT'
Let's execute the ROLLBACK again, but this time we'll use a trick.
It is based on the fact that if we call a remote procedure (via a database link)
and if it results in an unhandled exception, its DML effects will be totally cancelled
(in contrast to a local procedure). We don't need a remote procedure, but we'll execute
a quasi-remote procedure using a local database link:
CREATE DATABASE LINK local_db_link
CONNECT TO scott IDENTIFIED BY tiger using 'local_alias'
/
Let's change insert_emps_for_dept procedure again so that it calls the insert_programmers
procedure via the database link. However, we have to change the package specification:
CREATE OR REPLACE PACKAGE example_pkg IS
PROCEDURE insert_emps_for_dept (p_deptno dept.deptno%TYPE);
-- must be in specification (database link)
PROCEDURE insert_programmers (p_deptno dept.deptno%TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY example_pkg IS
...
PROCEDURE insert_emps_for_dept (p_deptno dept.deptno%TYPE) IS
BEGIN
insert_managers (p_deptno);
BEGIN
example_pkg.insert_programmers@local_db_link (p_deptno);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END;
END;
/
If the INSERT command is executed again:
INSERT INTO dept (deptno, dname) VALUES (1, 'DEPT 1')
/
Using SELECT query we get the following:
EMPNO ENAME DEPTNO DNAME
---------- --------------- ---------- --------------
1 EMP 1 1 DEPT 1
2 EMP 2 1 DEPT 1
So, the result was as good as if we had used SAVEPOINT / ROLLBACK TO SAVEPOINT statements.
Of course, there are at least two different ways in which this could have been accomplished:
- One would be to create the insert_programmers procedure as an AUTONOMOUS_TRANSACTION.
The drawback to this is that the autonomous transaction
could be successful and that the main transaction could fail, leaving us with two rows of EMP = PROGRAMER
without one row of DEPT or two rows of EMP = MANAGER. (Of course this situation would be rare and would not happen if the EMP table had FK on
the deptno column).
- The other method would be to add an exception handler in the insert_programmers procedure to cancel the partial result:
PROCEDURE insert_programmers (p_deptno dept.deptno%TYPE) IS
BEGIN
INSERT INTO emp (empno, ename, job, mgr, sal, deptno)
VALUES (3, 'EMP 3', 'PROGRAMER', 1, 1000, p_deptno);
RAISE_APPLICATION_ERROR
(-20001, 'SIMULATED ERROR IN MIDLE OF 3.PART OF A TRANSACTION');
INSERT INTO emp (empno, ename, job, mgr, sal, deptno)
VALUES (4, 'EMP 4', 'PROGRAMER', 1, 1000, p_deptno);
EXCEPTION
WHEN OTHERS THEN
DELETE emp WHERE empno = 3;
END;
However it is possible, though unlikely, that this cancel event could fail and such a cancel could be
very complicated in a production scenario.
July's Tip of the Month
Speeding Up DBA% View Queries
Compliments of Darryl Hurley, Pipeline SYSOP(dhurley@mdsi.bc.ca)
Joining any DBA% view to any other DBA% view in a query is notoriously
slow. Coding as multiple queries will be much faster although it requires
more typing.
For example this query:
SELECT r.owner,
r.table_name,
decode (r.generated, 'USER NAME', 'constraint ' ||
r.constraint_name, '') c_name,
r.constraint_name,
r.delete_rule,
p.owner powner,
p.table_name ptable,
p.constraint_name pconstraint_name,
r.deferrable,
r.deferred
FROM dba_constraints r,
dba_constraints p
WHERE r.owner = 'DHURLEY'
AND r.constraint_type = 'R'
AND r.r_constraint_name = p.constraint_name;
Takes approximately 2 minutes to run on my account with a single foreign key
between two lonely tables.
Breaking the queries out like this:
DECLARE
CURSOR csrConstraints (pOwner dba_constraints.owner%type)
IS
SELECT constraint_name,
owner,
d.r_constraint_name,
d.r_owner
FROM dba_constraints d
WHERE owner = pOwner
AND constraint_type = 'R';
CURSOR csrRefConstraints
(pConstraint dba_constraints.constraint_name%type,
pOwner dba_constraints.owner%type)
IS
SELECT constraint_name,
table_name,
constraint_type
FROM dba_constraints
WHERE constraint_name = pConstraint
AND owner = pOwner;
vRefConstraint csrRefConstraints%rowtype;
BEGIN
FOR vRec in csrConstraints('DHURLEY')
LOOP
OPEN csrRefConstraints(vRec.r_constraint_name, vRec.r_owner);
FETCH csrRefConstraints into vRefConstraint;
dbms_output.put_line(rpad(vRec.owner, 30) || ' ' ||
rpad(vRec.constraint_name, 30) || ' ' ||
rpad(vRefConstraint.constraint_name, 30) || ' ' ||
rpad(vRefConstraint.table_name, 30));
CLOSE csrRefConstraints;
END LOOP;
END;
/
Reduces the execution time to 5 seconds.
The savings will be even greater on large systems.
August's Tip of the Month
Package to Generate XML Based on a SQL Query
Compliments of Nilesh Chindarkar (Nilesh.chindarker@lvl.co.uk)
The following package will take a SQL query as input and generate an XML document using the PL/SQL XML
API. It stores the data in a CLOB, the writes the data from the CLOB to an output file - output.xml. Uncomment
the dbms_output lines to view the output on screen. Note that the package requires Oracle 8i and Oracle XDK Java 9.2.0.2.0.
CREATE OR REPLACE PACKAGE pkg_plsql_xml
IS
PROCEDURE prc_print (p_clob IN CLOB);
PROCEDURE prc_gen_xml (
p_sql IN VARCHAR2,
p_rowset_tag IN VARCHAR2 DEFAULT NULL,
p_row_tag IN VARCHAR2 DEFAULT NULL,
p_rowid_attr_name IN VARCHAR2 DEFAULT NULL,
p_rowid_attr_value IN VARCHAR2 DEFAULT NULL,
p_collidattrname IN VARCHAR2 DEFAULT NULL,
p_usenullattributeindicator IN BOOLEAN DEFAULT NULL,
p_dateformat IN VARCHAR2 DEFAULT NULL,
p_maxrows IN NUMBER DEFAULT NULL,
p_skiprows IN NUMBER DEFAULT NULL
);
END pkg_plsql_xml;
/
CREATE OR REPLACE PACKAGE BODY pkg_plsql_xml
IS
PROCEDURE prc_print (p_clob IN CLOB)
IS
result CLOB;
cvl_tmp VARCHAR2 (32000);
nvl_amount NUMBER := 255; --32000;
nvl_pos NUMBER := 1;
nvl_clob_length NUMBER;
instr_pos NUMBER;
file_handle UTL_FILE.file_type;
v_file_location VARCHAR2 (30) := 'g:\batch\files';
v_filename VARCHAR2 (50) := 'Output.XML';
BEGIN
file_handle := UTL_FILE.fopen (v_file_location, v_filename, 'W');
result := p_clob;
---------------------------------------------------------
---------------------------------------------------------
nvl_clob_length := DBMS_LOB.getlength (result);
cvl_tmp := NULL;
nvl_amount := 255; --32000;
nvl_pos := 1;
LOOP
instr_pos :=
DBMS_LOB.INSTR (result, CHR (10), nvl_pos, 1)
- nvl_pos;
IF nvl_pos
+ instr_pos > nvl_clob_length
THEN
instr_pos := nvl_clob_length
- nvl_pos;
DBMS_LOB.READ (
lob_loc=> result,
amount=> instr_pos,
offset=> nvl_pos,
buffer=> cvl_tmp
);
EXIT;
END IF;
DBMS_LOB.READ (
lob_loc=> result,
amount=> instr_pos,
offset=> nvl_pos,
buffer=> cvl_tmp
);
UTL_FILE.put_line (file_handle, cvl_tmp);
nvl_pos := nvl_pos
+ instr_pos
+ 1;
IF nvl_pos > nvl_clob_length
THEN
EXIT;
END IF;
END LOOP;
UTL_FILE.fclose (file_handle);
END prc_print;
PROCEDURE prc_gen_xml (
p_sql IN VARCHAR2,
p_rowset_tag IN VARCHAR2 DEFAULT NULL,
p_row_tag IN VARCHAR2 DEFAULT NULL,
p_rowid_attr_name IN VARCHAR2 DEFAULT NULL,
p_rowid_attr_value IN VARCHAR2 DEFAULT NULL,
p_collidattrname IN VARCHAR2 DEFAULT NULL,
p_usenullattributeindicator IN BOOLEAN DEFAULT NULL,
p_dateformat IN VARCHAR2 DEFAULT NULL,
p_maxrows IN NUMBER DEFAULT NULL,
p_skiprows IN NUMBER DEFAULT NULL
)
IS
queryctx dbms_xmlquery.ctxtype;
result CLOB;
v_str VARCHAR2 (4000);
BEGIN
queryctx := dbms_xmlquery.newcontext (p_sql);
IF p_rowset_tag IS NOT NULL
THEN
dbms_xmlquery.setrowsettag (ctxhdl => queryctx, tag => p_rowset_tag);
END IF;
IF p_row_tag IS NOT NULL
THEN
dbms_xmlquery.setrowtag (ctxhdl => queryctx, tag => p_row_tag);
END IF;
IF p_rowid_attr_name IS NOT NULL
THEN
dbms_xmlquery.setrowidattrname (
ctxhdl=> queryctx,
attrname=> p_rowid_attr_name
);
END IF;
IF p_rowid_attr_value IS NOT NULL
THEN
dbms_xmlquery.setrowidattrvalue (
ctxhdl=> queryctx,
colname=> p_rowid_attr_value
);
END IF;
IF p_collidattrname IS NOT NULL
THEN
dbms_xmlquery.setcollidattrname (
ctxhdl=> queryctx,
attrname=> p_collidattrname
);
END IF;
IF p_usenullattributeindicator IS NOT NULL
THEN
dbms_xmlquery.usenullattributeindicator (
ctxhdl=> queryctx,
flag => p_usenullattributeindicator
);
END IF;
IF p_dateformat IS NOT NULL
THEN
dbms_xmlquery.setdateformat (
ctxhdl=> queryctx,
mask => p_dateformat
);
END IF;
IF p_maxrows IS NOT NULL
THEN
dbms_xmlquery.setmaxrows (ctxhdl => queryctx, ROWS => p_maxrows);
END IF;
IF p_skiprows IS NOT NULL
THEN
dbms_xmlquery.setskiprows (ctxhdl => queryctx, ROWS => p_skiprows);
END IF;
result := dbms_xmlquery.getxml (queryctx);
prc_print (p_clob => result);
dbms_xmlquery.closecontext (queryctx);
END prc_gen_xml;
END pkg_plsql_xml;
/
Usage Examples:
execute pkg_plsql_xml.prc_gen_xml('SELECT * FROM EMP');
execute pkg_plsql_xml.prc_gen_xml(P_SQL => 'SELECT * FROM
EMP',p_useNullAttributeIndicator=>True);
I.E:
PROCEDURE prc_gen_xml
(
p_sql IN VARCHAR2,
p_rowset_tag IN VARCHAR2 DEFAULT
NULL,
p_row_tag IN VARCHAR2
DEFAULT NULL,
p_rowid_attr_name IN VARCHAR2 DEFAULT
NULL,
p_rowid_attr_value IN VARCHAR2 DEFAULT
NULL,
p_collidattrname IN VARCHAR2 DEFAULT
NULL,
p_useNullAttributeIndicatorIN BOOLEAN DEFAULT NULL,
p_dateformat IN VARCHAR2 DEFAULT
NULL,
p_maxrows IN NUMBER
DEFAULT NULL,
p_skiprows IN NUMBER
DEFAULT NULL
);
September's Tip of the Month
A instr Function That Accepts Wildcards
Compliments of Patrick Barel (pbarel@vda.nl) and Michel Draaijer
with input from James Padfield, PL/SQL Pipeline SYSOP (jpadfiel@cellops.com).
The script below performs an instr function but accepts and processes wildcards (% and '_') in the
search argument. The script calls package vda$strings. Click Here to view
vda$strings.
CREATE OR REPLACE FUNCTION instrw (pi_text IN VARCHAR2, pi_search IN VARCHAR2,
pi_pos IN NUMBER DEFAULT 1,
pi_nth IN NUMBER DEFAULT 1)
RETURN NUMBER
IS
lv_returnvalue NUMBER;
BEGIN
lv_returnvalue := vda$strings.instrw(vp_wrd => pi_text, vp_exp =>
pi_search, vp_pos =>
pi_pos, vp_occ => pi_nth);
RETURN lv_returnvalue;
END instrw;
/
October's Tip of the Month
Passing a Collection into a Cursor
Compliments of Dan Clamage, PL/SQL Pipeline SYSOP (DClamage@MSA.com).
If you have a suitable schema-level nested table object type, you can pass a
variable based on the object type to a cursor as a parameter. You can use
this to simulate a dynamic IN clause in-list operator without using dynamic
SQL.
We'll use this schema-level nested table type in our experiment.
CREATE OR REPLACE TYPE obj_var_seq IS TABLE OF NUMBER;
In this example, we populate a variable that is based on the object type with
a BULK COLLECT, and pass it into a cursor on the parameter list. Note the
default column name provided when casting a nested table. Inside the cursor
it's used in a subquery in lieu of the in-list operator.
DECLARE
arr_var_seq obj_var_seq;
CURSOR cur_base(p_arr_var_seq obj_var_seq)
IS
SELECT var_seq, week_code, geo_seq, var_data
FROM fsb_base_data
WHERE var_seq IN (
SELECT column_value
FROM TABLE(CAST(p_arr_var_seq AS obj_var_seq)))
;
cnt PLS_INTEGER := 0;
BEGIN
SELECT var_seq
BULK COLLECT INTO arr_var_seq
FROM fsb_variables
WHERE var_active_flag IN ('A', 'P')
AND var_actual_type IN ('DF','DA','L')
;
FOR rec IN cur_base(arr_var_seq) LOOP
cnt := cnt + 1;
END LOOP;
DBMS_OUTPUT.put_line('cnt=[' || TO_CHAR(cnt) || ']');
END;
/
cnt=[147205]
We can also use the nested table inside an inline view, and join it to the
other table. This is probably more efficient than the previous example. We
aliased the default column name for readability.
DECLARE
arr_var_seq obj_var_seq;
CURSOR cur_base(p_arr_var_seq obj_var_seq)
IS
SELECT b.var_seq, b.week_code, b.geo_seq, b.var_data
FROM
fsb_base_data b
,(SELECT column_value var_seq
FROM TABLE(CAST(p_arr_var_seq AS obj_var_seq))) t
WHERE b.var_seq = t.var_seq
;
cnt PLS_INTEGER := 0;
BEGIN
SELECT var_seq
BULK COLLECT INTO arr_var_seq
FROM fsb_variables
WHERE var_active_flag IN ('A', 'P')
AND var_actual_type IN ('DF','DA','L')
;
FOR rec IN cur_base(arr_var_seq) LOOP
cnt := cnt + 1;
END LOOP;
DBMS_OUTPUT.put_line('cnt=[' || TO_CHAR(cnt) || ']');
END;
/
cnt=[147205]
Note that we don't have to initialize the nested table variable because that is
done by the BULK COLLECT. Even if the query returns no rows, the
array is initialized.
DECLARE
arr_var_seq obj_var_seq;
CURSOR cur_base(p_arr_var_seq obj_var_seq)
IS
SELECT b.var_seq, b.week_code, b.geo_seq, b.var_data
FROM
fsb_base_data b
,(SELECT column_value var_seq
FROM TABLE(CAST(p_arr_var_seq AS obj_var_seq))) t
WHERE b.var_seq = t.var_seq
;
cnt PLS_INTEGER := 0;
BEGIN
SELECT var_seq
BULK COLLECT INTO arr_var_seq
FROM fsb_variables
WHERE var_active_flag = 'Q'
;
DBMS_OUTPUT.put_line('bulk collected=[' || TO_CHAR(arr_var_seq.COUNT) ||
']');
IF (arr_var_seq IS NULL) THEN
DBMS_OUTPUT.put_line('array is atomically null');
ELSE
DBMS_OUTPUT.put_line('array is NOT atomically null');
END IF;
FOR rec IN cur_base(arr_var_seq) LOOP
cnt := cnt + 1;
END LOOP;
DBMS_OUTPUT.put_line('cnt=[' || TO_CHAR(cnt) || ']');
END;
/
bulk collected=[0]
array is NOT atomically null
cnt=[0]
November's Tip of the Month
Generating Sequence Numbers Without Gaps
Compliments of Zlatko Sirotic, (zlatko.sirotic@iii.hr).
What do you do if your management wants invoice numbers to be generated in sequence, without a
single number being skipped?
Unfortunately, Oracle sequences will never generate a gap free sequence of numbers.
If we realy must have IDs without gaps (in a multi-user environment),
we can write our own sequence generator.
For example:
CREATE TABLE sequence_generator (
table_name VARCHAR2 (30) PRIMARY KEY,
id NUMBER (32) NOT NULL)
/
CREATE OR REPLACE FUNCTION generated_id (p_table VARCHAR2)
RETURN NUMBER
IS
l_generated_id NUMBER;
l_table_name VARCHAR2 (30) := UPPER (p_table);
CURSOR c_seq_generator IS
SELECT id
FROM sequence_generator
WHERE table_name = l_table_name
FOR UPDATE; -- this locks current row
BEGIN
OPEN c_seq_generator;
FETCH c_seq_generator INTO l_generated_id;
IF c_seq_generator%FOUND THEN
l_generated_id := l_generated_id + 1;
UPDATE sequence_generator
SET id = l_generated_id
WHERE CURRENT OF c_seq_generator;
ELSE
l_generated_id := 1;
INSERT INTO sequence_generator (table_name, id)
VALUES (l_table_name, l_generated_id);
END IF;
CLOSE c_seq_generator;
RETURN l_generated_id;
EXCEPTION
WHEN OTHERS THEN
CLOSE c_seq_generator;
RAISE;
END;
/
We can use "generated_id" function above in a PRE-INSERT Forms trigger, but better yet would be
to use database trigger:
CREATE OR REPLACE TRIGGER bir_dept
BEFORE INSERT ON dept
FOR EACH ROW
BEGIN
:NEW.deptno := generated_id ('DEPT');
END;
/
And, we can use a POST-INSERT Forms trigger to refresh items corresponding to server-derived columns:
SELECT deptno INTO :dept.deptno -- server-derived column
FROM dept
WHERE ROWID = :dept.ROWID;
Of course, Forms item :dept.deptno must have the following properties:
- Query Only = Yes
- Insert / Update Allowed = No
This technique will guarantee that a new number is only allocated if the commit succeeds,
but at a price - only one person at a time can get a sequence number. So the number generation
is a bottleneck.
There is, however, a better solution.
The idea is to use two sequence generators:
- One for temporary IDs, based on the Oracle sequence;
We use this sequence in the BIR (Before Insert Row) trigger
- One for permanent IDs, based on the code table;
(Of course, we must ensure that the Oracle sequence value is greater than the value based on the code table);
We use this sequence in the BUR (Before Update Row) trigger, at the end of the transaction.
But how will the BUR trigger know that the end of the transaction has occurred? We must add a field
like "record_status" to the table and insure with the
database triggers that the row that has the value of "V" (Valid) has IDs without gaps.
Record_status will have three possible values: N(ew), V(alid) and C(hanged).
Valid transitions are: N to V, V to C, and C to V (as well as N to N, V to V, and C to C).
We add the record_status field to the DEPT table:
ALTER TABLE dept
ADD (record_status CHAR (1) DEFAULT 'N' NOT NULL,
CONSTRAINT dept_record_status_ck CHECK (record_status IN ('N', 'V', 'C')))
/
CREATE OR REPLACE TRIGGER bir_dept
BEFORE INSERT ON dept
FOR EACH ROW
BEGIN
:NEW.record_status := 'N';
SELECT test_seq.NEXTVAL INTO :NEW.deptno FROM DUAL; -- temporary id
END;
/
CREATE OR REPLACE TRIGGER bur_dept
BEFORE UPDATE ON dept
FOR EACH ROW
BEGIN
IF :NEW.record_status IS NULL THEN
RAISE_APPLICATION_ERROR (-20001, 'Record status can''t be NULL!');
END IF;
IF NOT
(
:OLD.record_status = 'N' AND :NEW.record_status = 'V'
OR
:OLD.record_status = 'V' AND :NEW.record_status = 'C'
OR
:OLD.record_status = 'C' AND :NEW.record_status = 'V'
OR
:OLD.record_status = :NEW.record_status
)
THEN
RAISE_APPLICATION_ERROR (-20002, 'Wrong transition!');
END IF;
IF :OLD.record_status = 'N' AND :NEW.record_status = 'V' THEN
:NEW.deptno := generated_id ('DEPT'); -- permanent id
END IF;
END;
/
If we use Forms development tools, then in the Forms trigger POST-FORMS-COMMIT,
(which fires immediately before the database COMMIT command), we must add code like this:
BEGIN
IF :SYSTEM.FORM_STATUS = 'QUERY' THEN
RETURN;
END IF;
UPDATE dept
SET record_status = 'V'
WHERE deptno = :dept.deptno
AND record_status = 'N';
SELECT deptno INTO :dept.deptno -- server-derived column
FROM dept
WHERE ROWID = :dept.ROWID;
END;
If Forms (or some other client tool) didn't give the UPDATE command before transaction COMMIT, the
database could have left DEPT table rows with a value "N" in record_status
and with temporary sequence values in deptno.
Using code tables to store the next value for a sequence is not recommended.
Use it only if you absolutely have to.
I must emphasize that in real life I would not write PL/SQL code directly
in the database triggers, but in packages, nor would I directly use RAISE_APPLICATION_ERROR.
It is written this way in this sample only for the purpose of code clarity.
December's Tip of the Month
Improved Script to Calculate Years, Months and Days Between Two Dates
As if to round out the 2002 PL/SQL Tips, December's tip is an improved version of the January tip compliments of Sean Gu, (xg_99@yahoo.com).
While the January 2002 tip was useful, the script had a couple of shortcomings.
First, the script did NOT take into account the time
between two dates that fall on the last days of the
month. For example, if you enter the from and to
dates as: 31-Jan-2002 and 28-Feb-2002, the script incorrectly
returns 0 year, 0 month and 28 days.
The correct answer should be 0 year, 1 month, 0 day.
Second, if one makes use of the Oracle built-in functions
ADD_MONTHS (the original script used it) and MONTHS_BETWEEN
(which many people are unaware of), then one can write
a much simpler script. In fact you can write the whole
thing in just one SQL select!
Here is the revised version of the script. It even
calculates dates backwards: DateFrom > DateTo
(NEGATIVE)!
create or replace
function get_date_diff(
DateFrom_in in date,
DateTo_in in date)
return varchar2
as
DateFrom date;
DateTo date;
nYears number;
nMonths number;
nDays number;
sign varchar2(1):='';
BEGIN
if DateFrom_in <= DateTo_in then
DateFrom:=DateFrom_in;
DateTo:=DateTo_in;
else
DateTo:=DateFrom_in;
DateFrom:=DateTo_in;
sign:='-';
end if;
nMonths:= trunc(MONTHS_BETWEEN(DateTo, DateFrom));
nDays := DateTo - add_months(DateFrom, nMonths);
if nDays <0 then
nMonths:= nMonths -1;
nDays := DateTo - add_months(DateFrom, nMonths);
end if;
nYears:= trunc(nMonths/12);
nMonths:= nMonths - nYears *12;
return sign ||'('|| nYears || ', '||
nMonths || ', '||
nDays || ')';
EXCEPTION
WHEN others THEN
return 'Error: '|| sqlerrm;
END;
Examples:
select get_date_diff('28-FEB-1994','31-MAR-1996') from dual;
Returns:
(2, 1, 0)
select get_date_diff('28-FEB-1994','28-MAR-1996') from dual;
Returns:
(2, 0, 28)
Reverse Examples:
select get_date_diff('31-MAR-1996', '28-FEB-1994') from dual;
Returns:
-(2, 1, 0)
select get_date_diff('28-MAR-1996', '28-FEB-1994') from dual;
Returns:
-(2, 0, 28)
For questions
or comments on this site: webmaster@quest-pipelines.com
All content Copyright
© Quest Software, Inc. All rights reserved.
|
