Feed aggregator

Merge counter

Adrian Billington - Sun, 2004-02-22 02:00
A package to capture and report separate INSERT and UPDATE rowcounts from a MERGE statement. February 2004

Introduction to bulk pl/sql enhancements in 9i

Adrian Billington - Mon, 2003-09-22 03:00
Oracle extends its array processing capabilities in 9i. September 2003

User-defined aggregate functions in oracle 9i

Adrian Billington - Mon, 2003-09-22 03:00
We can create our own aggregate functions in 9i and use them like built-ins. September 2003

Call stack parser

Adrian Billington - Mon, 2003-09-22 03:00
A small package of functions for parsing the PL/SQL call stack. Includes an implementation of "WHOAMI" and "CALLER". September 2003

Decomposing sql%rowcount for merge

Adrian Billington - Fri, 2003-08-22 03:00
A method for getting insert and update counts from a MERGE operation. August 2003

Flashback query in oracle 9i

Adrian Billington - Fri, 2002-11-22 02:00
We can now query data from a previous point in time. November 2002

Multilevel collections in oracle 9i

Adrian Billington - Tue, 2002-10-22 03:00
Oracle 9i supports collections of collections of collections... October 2002

Multi-table inserts in oracle 9i

Adrian Billington - Tue, 2002-10-22 03:00
We can now insert into multiple tables from a single statement. October 2002

The merge statement in oracle 9i

Adrian Billington - Sun, 2002-09-22 03:00
Update or insert data from a single DML statement in 9i. September 2002

Case expressions and statements in oracle 9i

Adrian Billington - Wed, 2002-05-22 03:00
Extended CASE expressions and the new CASE statement in 9i. May 2002

Associative arrays in oracle 9i release 2

Adrian Billington - Wed, 2002-05-22 03:00
Oracle extends its PL/SQL array processing capabilities. May 2002

Put_line

Adrian Billington - Mon, 2002-04-22 03:00
A simple wrapper to DBMS_OUTPUT.PUT_LINE to workaround the 255 byte limit (not needed from 10.2 onwards). April 2002

Introduction to 8i analytic functions

Adrian Billington - Fri, 2002-02-22 02:00
Oracle 8.1.6 introduces a wide range of powerful new "windowing" functions. February 2002

Introduction to 8i bulk pl/sql processing

Adrian Billington - Sat, 2001-12-22 02:00
Oracle 8i now supports native array processing in PL/SQL. December 2001

Numeric sorting an alphanumeric column

Bar Solutions - Sun, 2001-12-02 18:00

.code, .code pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .code pre { margin: 0em; } .code .rem { color: #ff0000; } .code .kwrd { color: #008080; } .code .str { color: #0000ff; } .code .op { color: #0000c0; } .code .preproc { color: #cc6633; } .code .asp { background-color: #ffff00; } .code .html { color: #800000; } .code .attr { color: #ff0000; } .code .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .code .lnum { color: #606060; }

The other day a customer came up to me and said: I have this column that holds numeric data, usually. But when I sort it it gets all messed up, because is sorts it alphanumerically. That is 10 is listed before 2 etc.

My first suggestion was: well, sort by TO_NUMBER(column) then.

Well, he replied, that can’t be done. Sometimes the column contains alphanumeric data.

Oh, and I don’t want to use PL/SQL since I once learned that switching between the SQL engine and the PL/SQL engine costs a lot of performance.

Well, let’s start by making a simple statement that shows the problem:

WITH t AS
  (          SELECT '1' numval FROM dual
   UNION ALL SELECT to_char(9) FROM dual -- 9
   UNION ALL SELECT '#' FROM dual
   UNION ALL SELECT to_char(10) FROM dual -- 10
   UNION ALL SELECT 'G' FROM dual
   UNION ALL SELECT 'Green' FROM dual
   UNION ALL SELECT 'Yel2low' FROM dual
   UNION ALL SELECT 'Pink' FROM dual
   UNION ALL SELECT 'B' FROM dual
   UNION ALL SELECT '2' FROM dual
   UNION ALL SELECT '2912B' FROM dual
   UNION ALL SELECT 'B2912' FROM dual
  )
SELECT t.*
  FROM t
 ORDER BY t.numval

The output of this query, is as expected, sorted, but alphanumerically:

NUMVAL
-------
#
B
B2912
G
Green
Pink
Yel2low
1
10
2
2912B
9

12 rows selected.

Then I came to think: I want the ordering done numerically, but I cannot use to_number on the column, because there can be alphanumeric values and that will break the query. But what if I remove everything but the numbers for the column, when sorting. That is where Regular Expressions come in. I am definitely not an expert in the field of Regular Expressions, but I thought I would give it a try. I replace all the letters [:alpha:], punctuality elements [:punct:] and blanks [:blank:] with nothing.

WITH t AS
  (          SELECT '1' numval FROM dual
   UNION ALL SELECT to_char(9) FROM dual -- 9
   UNION ALL SELECT '#' FROM dual
   UNION ALL SELECT to_char(10) FROM dual -- 10
   UNION ALL SELECT 'G' FROM dual
   UNION ALL SELECT 'Green' FROM dual
   UNION ALL SELECT 'Yel2low' FROM dual
   UNION ALL SELECT 'Pink' FROM dual
   UNION ALL SELECT 'B' FROM dual
   UNION ALL SELECT '2' FROM dual
   UNION ALL SELECT '2912B' FROM dual
   UNION ALL SELECT 'B2912' FROM dual
  )
SELECT t.*
  FROM t
 ORDER BY to_number(regexp_replace(numval, '([[:alpha:]]|[[:punct:]]|[[:blank:]])')) NULLS LAST

The output is almost what I want:

NUMVAL
-------
1
Yel2low
2
9
10
B2912
2912B
#
B
G
Green
Pink

12 rows selected.

While reading about the character classes I came across the [:digit:] class and I knew there was a NOT operator. And that is actually what I want. Replace everything that is not a digit with nothing.

WITH t AS
  (          SELECT '1' numval FROM dual
   UNION ALL SELECT to_char(9) FROM dual -- 9
   UNION ALL SELECT '#' FROM dual
   UNION ALL SELECT to_char(10) FROM dual -- 10
   UNION ALL SELECT 'G' FROM dual
   UNION ALL SELECT 'Green' FROM dual
   UNION ALL SELECT 'Yel2low' FROM dual
   UNION ALL SELECT 'Pink' FROM dual
   UNION ALL SELECT 'B' FROM dual
   UNION ALL SELECT '2' FROM dual
   UNION ALL SELECT '2912B' FROM dual
   UNION ALL SELECT 'B2912' FROM dual
  )
SELECT t.*
  FROM t
 ORDER BY to_number(regexp_replace(numval, '([^[:digit:]])')) NULLS LAST

Same output, less typing (which is always good)

NUMVAL
-------
1
Yel2low
2
9
10
B2912
2912B
#
B
G
Green
Pink

12 rows selected.

But the combined values are not sorted properly yet. If there are other characters than digits in the column, then they should just be sorted alphabetically. The current query just remove all the non digits before sorting. What I want to happen is when I can remove all the non digits from the column and the length is still the same, then sort numerically, otherwise give it a NULL value putting them at the end of the resultset. My second ordering clause is just the column, resulting in all the non-numeric values being sorted ‘normal’

WITH t AS
  (          SELECT '1' numval FROM dual
   UNION ALL SELECT to_char(9) FROM dual -- 9
   UNION ALL SELECT '#' FROM dual
   UNION ALL SELECT to_char(10) FROM dual -- 10
   UNION ALL SELECT 'G' FROM dual
   UNION ALL SELECT 'Green' FROM dual
   UNION ALL SELECT 'Yel LOW' FROM dual
   UNION ALL SELECT 'Pink' FROM dual
   UNION ALL SELECT 'B' FROM dual
   UNION ALL SELECT '2' FROM dual
   UNION ALL SELECT '2912B' FROM dual
   UNION ALL SELECT 'B2912' FROM dual
  )
SELECT t.*
  FROM t
 ORDER BY CASE
            WHEN (length(numval) = length(regexp_replace(numval, '([^[:digit:]])'))) THEN
             to_number(regexp_replace(numval, '([^[:digit:]])'))
            ELSE
             NULL
          END NULLS LAST
         ,numval

Resulting in this output:

NUMVAL
-------
1
2
9
10
#
B
B2912
G
Green
Pink
Yel2low
2912B

12 rows selected.

This is a lot more like I think the customer wants his column to be sorted. Maybe not exactly what he want it, but it’s a good start, I think.

Is it unique?

Bar Solutions - Sun, 2001-12-02 18:00
.code, .code pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .code pre { margin: 0em; } .code .rem { color: #ff0000; } .code .kwrd { color: #008080; } .code .str { color: #0000ff; } .code .op { color: #0000c0; } .code .preproc { color: #cc6633; } .code .asp { background-color: #ffff00; } .code .html { color: #800000; } .code .attr { color: #ff0000; } .code .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .code .lnum { color: #606060; }

The other day one of my colleagues said something like: ‘Hey, this is strange. Oracle is not working correctly’. My first thought is then: ‘well, you probably didn’t specify you requirements correctly’. What was the problem? A unique index was created on a table to implement a business rule which can be rephrased into ‘One-Manager-Per-Department’. This includes multiple columns where any combination is allowed, but only one manager is allowed per department.

First, let’s make a simplified version of the well known EMP table.

CREATE TABLE emp
( empno  NUMBER(4)
, ename  VARCHAR2(10)
, job    VARCHAR2(9)
, deptno NUMBER(2)
)
/

Then we try creating a unique index on the job column:

CREATE UNIQUE INDEX ui_one_manager ON emp (job)
/

Insert some data:

INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7369, 'SMITH',  'CLERK',     20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7499, 'ALLEN',  'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7521, 'WARD',   'SALESMAN',  30);

And notice that you cannot enter two employees with the same job, which is correct since we said the JOB should be unique. But we just want the MANAGER to be unique. I remembered that NULL values will not be included in the index so let’s try this:

DROP INDEX ui_one_manager
/
CREATE UNIQUE INDEX ui_one_manager ON emp (CASE job
                                             WHEN 'MANAGER' THEN job
                                             ELSE NULL
                                           END)
/

Again insert some data:

INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7369, 'SMITH',  'CLERK',     20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7499, 'ALLEN',  'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7521, 'WARD',   'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7566, 'JONES',  'MANAGER',   20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7654, 'MARTIN', 'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7698, 'BLAKE',  'MANAGER',   30);

Doing a lot better, but we cannot enter more than one MANAGER, even if they are in different departments. Let’s include the deptno in the columns for the index:

DROP INDEX ui_one_manager
/
CREATE UNIQUE INDEX ui_one_manager ON emp (deptno
                                          , CASE job
                                             WHEN 'MANAGER' THEN job
                                             ELSE NULL
                                           END
                                          )
/

And try some data again:

INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7369, 'SMITH',  'CLERK',     20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7499, 'ALLEN',  'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7521, 'WARD',   'SALESMAN',  30);

Pretty much back where we started.

How about we apply the same case statement to the DEPTNO column like this:

DROP INDEX ui_one_manager
/
CREATE UNIQUE INDEX ui_one_manager ON emp (CASE job
                                             WHEN 'MANAGER' THEN deptno
                                             ELSE NULL
                                           END
                                          , CASE job
                                             WHEN 'MANAGER' THEN job
                                             ELSE NULL
                                           END
                                          )
/

And try some data again:

INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7369, 'SMITH',  'CLERK',     20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7499, 'ALLEN',  'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7521, 'WARD',   'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7566, 'JONES',  'MANAGER',   20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7654, 'MARTIN', 'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7698, 'BLAKE',  'MANAGER',   30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7782, 'CLARK',  'MANAGER',   10);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7788, 'SCOTT',  'ANALYST',   20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7839, 'KING',   'PRESIDENT', 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7844, 'TURNER', 'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7876, 'ADAMS',  'CLERK',     20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7900, 'JAMES',  'CLERK',     30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7902, 'FORD',   'ANALYST',   20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7934, 'MILLER', 'CLERK',     10);
And this time it worked.

To check if the unique index really works let’s try some more inserts:

INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (2912, 'BAREL', 'MANAGER', 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (2912, 'BAREL', 'MANAGER', 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (2912, 'BAREL', 'MANAGER', 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (2912, 'BAREL', 'MANAGER', 40);

Only the last one works. Which is what was expected.

Some things to share…

Bar Solutions - Sun, 2001-12-02 18:00

I have been awfully quiet on my blog lately. I think that is because I have been busy with other things, like my garden and stuff like that. There are some ‘techie’ thing I have done in the meantime, though,

Tech14-Ive_SubmittedI have submitted a couple of abstracts for Tech14. Hope at least one of them gets selected. I really like presenting and if it is in a different country, that is just a plus. That way I get to see something of the world while I am still able to do this, considering my illness.

ODTUG_Kscope14On a shorter timeframe, I will be presenting at KScope14 this year. I will be nice to see my friends in the US again. If you are in the neighborhood of Nieuwegein and you want to some of the sessions, but cannot make it to Seattle, Amis-logo-kleinmaybe you can attend the KScope14 preview at the AMIS office on June 12th.

Hope to see you at either one of these events.sftilt80

Oh, and if you have a moment to spare and you know your way around at SQL – PL/SQL – APEX or some other language/technologies, maybe you can sign up at the GivHub and help Steven Feuerstein.

PL/SQL vs SQL

Bar Solutions - Sun, 2001-12-02 18:00

plsql_logoThere is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only the EMP and DEPT tables to protect the suspects and maybe innocent.

The function defined is something like this:

.code, .code pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .code pre { margin: 0em; } .code .rem { color: #ff0000; } .code .kwrd { color: #008080; } .code .str { color: #0000ff; } .code .op { color: #0000c0; } .code .preproc { color: #cc6633; } .code .asp { background-color: #ffff00; } .code .html { color: #800000; } .code .attr { color: #ff0000; } .code .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .code .lnum { color: #606060; }

FUNCTION getenames(deptno_in IN emp.deptno%type) RETURN VARCHAR2
IS
  TYPE enames_aa IS TABLE OF emp.ename%type INDEX BY pls_integer;
  l_returnvalue VARCHAR2(32767) := '';
  l_enames enames_aa;
BEGIN
  SELECT e.ename
    BULK COLLECT INTO l_enames
    FROM emp e
   WHERE e.deptno = deptno_in
   ORDER BY e.ename ASC NULLS FIRST;
  IF l_enames.count > 0 THEN
    FOR indx IN 1 .. l_enames.count LOOP
      l_returnvalue := l_returnvalue || l_enames(indx) || ',';
    END LOOP;
  END IF;
  l_returnvalue := rtrim(l_returnvalue, ',');
  RETURN l_returnvalue;
END;

and the query executed is something like this:

SELECT d.dname, getenames(d.deptno) enames
  FROM dept d

The result of this query is:

DNAME          ENAMES
-------------- --------------------------------------------------------------------------------
ACCOUNTING     CLARK,KING,MILLER
RESEARCH       ADAMS,FORD,JONES,SCOTT,SMITH
SALES          ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
OPERATIONS     

The Oracle database performs a lot of (not with these demo tables, though) Context Switches between the SQL Engine and the PL/SQL Engine. And even worse, it performs Context Switches in the PL/SQL code back to the SQL Engine. The PL/SQL code has already been optimized using Bulk Processing, but still. The fastest way of doing something is by not doing it at all.

The SQL engine in Oracle 11G has been enhanced with functionality that does exactly what is now being done by the PL/SQL Function.

The query has to be rewritten a bit (well, actually a lot) but the result is just SQL so no more Context Switching:

SELECT d.dname, (SELECT listagg(e.ename, ',') WITHIN GROUP(ORDER BY e.ename)
                   FROM emp e
                  WHERE 1=1
                    AND e.deptno = d.deptno
                ) enames
  FROM dept d

If you run this statement you’ll get the exact same result as before, but with execution time. With these demo tables it is probably not so obvious that this approach is faster, but if you have real-world examples with real-world data then you will probably benefit from the time spent in the query.

Bottom line is, the Oracle SQL Engine gets more powerful every release and it may be so powerful it can take care of PL/SQL solutions you came up with in the past. And even though it is hard to say goodbye to working code, using SQL can dramatically speed up the execution of your code.

This post has been cross-posted to technology.amis.nl

Oracle PL/SQL Programming 6th edition by Steven Feuerstein

Bar Solutions - Sun, 2001-12-02 18:00

opp6_catI have reviewed the latest edition of Oracle PL/SQL Programming. This is not a book, at least for me, that I would read front to back. I use it mostly as a reference, but the great thing about this book is that you can also use this book to learn programming PL/SQL from scratch.

The book is nicely ordered into different parts, like Program structure and how to use SQL in PL/SQL (which by the way to should minimize as much as possible).  Besides all the information on how to use the PL/SQL language, there are also reference parts with for instance the Metacharacters for Regular Expressions or the number format models. Something you will probably not learn by heart if you don’t use it regularly.

Although Oracle release 12.1 doesn’t bring major changes to the PL/SQL language, there are some areas where it has been changed and improved. This doesn’t mean by the way this book shouldn’t be used when you are running an older version of Oracle. It is an invaluable reference when you are programming for Oracle version 11 or even 10. Even though Oracle doesn’t support the older versions anymore, you can still use some of the techniques in this book. Maybe you’ll learn about possibilities that you cannot use yet, because of the version you are on, but it may give you an idea on how to implement it anyway or information on why you should upgrade to a newer version. If you database supports Conditional Compilation (Page 1064) which is available since version 10G Release 2 you could already start implementing the new techniques and if your organization upgrades to a newer version you will use it right away. For instance the Function Result Cache (Pages 853 – 868) would be a great candidate for this.

This is a rather big book (one of the biggest I have on this subject), but it’s also one of the best. A good way to get your work done is know stuff is possible and find out the details when you want to use it. Even though I have been working with the language for more than 14 years, there a still some areas where I need to get the extra information and that’s where this book comes is really handy.

Pages

Subscribe to Oracle FAQ aggregator