Bar Solutions

Subscribe to Bar Solutions feed
The beginning of knowledge is the discovery of something we do not understand. [Frank Herbert]
Updated: 10 hours 32 min ago

Dot Qualify Everything?

Sun, 2016-07-24 00:35
.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; }

There is talk about Dot Qualifying Everything in your PL/SQL Code. But what are the pros and cons in this approach?

Let’s say we have a rather simple function which returns a string with alternatively upper and lower case characters. Normally I would write it as follows:

create or replace function wavey(string_in in varchar2) return varchar2 is
  l_returnvalue varchar2(30);
begin
  for indx in 1 .. length(string_in) loop
    l_returnvalue := l_returnvalue || case mod(indx, 2)
                       when 0 then
                        upper(substr(string_in, indx, 1))
                       else
                        lower(substr(string_in, indx, 1))
                     end;
  end loop;
  dbms_output.put_line(l_returnvalue);
  return l_returnvalue;
end;

The output for this function using the ENAME column of the EMP table is like this:

sMiTh
aLlEn
wArD
jOnEs
mArTiN
bLaKe
cLaRk
sCoTt
kInG
tUrNeR
aDaMs
jAmEs
fOrD
mIlLeR

But what if the was a malicious user that created a package names DBMS_OUTPUT which included all the programs in the original DBMS_OUTPUT package, but with some code added. Let’s create a simple package like this:

create or replace package dbms_output is
  procedure put_line(a in varchar2);
end;
create or replace package body dbms_output is
  procedure put_line(a in varchar2)
  is
  begin
    sys.dbms_output.put('changed :');
    sys.dbms_output.put_line(a);
  end;
end;

Notice I just included the PUT_LINE procedure in here and I am not really doing anything malicious here. The output of my function would now be:

changed :sMiTh
changed :aLlEn
changed :wArD
changed :jOnEs
changed :mArTiN
changed :bLaKe
changed :cLaRk
changed :sCoTt
changed :kInG
changed :tUrNeR
changed :aDaMs
changed :jAmEs
changed :fOrD
changed :mIlLeR

Not exactly the way I would want. How can I prevent this from happening? The answer is actually really simple. Qualify the call to DBMS_OUTPUT with the schema name where the packages resides:

create or replace function wavey(string_in in varchar2) return varchar2 is
  l_returnvalue varchar2(30);
begin
  for indx in 1 .. length(string_in) loop
    l_returnvalue := l_returnvalue || case mod(indx, 2)
                       when 0 then
                        upper(substr(string_in, indx, 1))
                       else
                        lower(substr(string_in, indx, 1))
                     end;
  end loop;
  sys.dbms_output.put_line(l_returnvalue);
  return l_returnvalue;
end;

The output is back to what we expected

sMiTh
aLlEn
wArD
jOnEs
mArTiN
bLaKe
cLaRk
sCoTt
kInG
tUrNeR
aDaMs
jAmEs
fOrD
mIlLeR

But should you Dot Qualify everything in your code? Like this?

create or replace function demo.wavey(string_in in sys.standard.varchar2) return sys.standard.varchar2 is
  l_returnvalue sys.standard.varchar2(30);
begin
  <<theloop>>
  for indx in 1 .. length(wavey.string_in) loop
    wavey.l_returnvalue := wavey.l_returnvalue || case mod(theloop.indx, 2)
                             when 0 then
                              sys.standard.upper(standard.substr(wavey.string_in, theloop.indx, 1))
                             else
                              sys.standard.lower(standard.substr(wavey.string_in, theloop.indx, 1))
                           end;
  end loop;
  sys.dbms_output.put_line(wavey.l_returnvalue);
  return wavey.l_returnvalue;
end;

I don’t this adds to the readability of the code, but it sure makes your code safer against malicious users that want to implement code that can be executed by your programs. Please add your thoughts on this subject.

Use the database to do the heavy lifting

Wed, 2016-06-15 03:25

Suppose you have data in your PL/SQL program stored in a collection. I am using the EMP table (14 records) but you should imagine you have many, many more records in your collection. If you want to order (sort) your collection in a different manner somewhere in your code you can of course write your own sorting routine but the Oracle database is doing this for ages and probably a lot smarter (after all these years) than you can ever do it.

If you are running an Oracle pre 12c database you can use the SQL-engine when you have your data in a Nested Table. I have created this little test script to try this. Again, the EMP table has only 14 records defined, but it is used to drive the point home.

CREATE OR REPLACE TYPE emp_t AS OBJECT
( empno number(4)
, ename varchar2(10)
, job varchar2(9)
, mgr number(4)
, hiredate DATE
, sal number(7,2)
, comm number(7,2)
, deptno number(2)
)
CREATE OR REPLACE TYPE emp_ntt AS TABLE OF emp_t
CREATE OR REPLACE PACKAGE emp_test IS
  FUNCTION get_emp RETURN emp_ntt;
  PROCEDURE emp_prc;
END;
CREATE OR REPLACE PACKAGE BODY emp_test IS
  PROCEDURE separator
  IS
  BEGIN
    dbms_output.put_line('-=-=-=-=-');
  END;
--
  FUNCTION get_emp RETURN emp_ntt
  IS
    l_returnvalue emp_ntt;
  CURSOR c_emp IS
  SELECT emp_t(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    FROM emp;
  BEGIN
    OPEN c_emp;
    FETCH c_emp BULK COLLECT INTO l_returnvalue;
    CLOSE c_emp;
    RETURN l_returnvalue;
  END;
--
  PROCEDURE emp_prc IS
    l_emps emp_ntt;
    l_emps_sorted emp_ntt;
    CURSOR c_emp_sort(emps_in emp_ntt)
    IS
    SELECT emp_t(empno, ename, job, mgr, hiredate, sal, comm, deptno)
      FROM table(emps_in)
     ORDER BY ename;
  BEGIN
    l_emps := get_emp;
    FOR rec IN (select ename FROM table(l_emps)) loop
      dbms_output.put_line(rec.ename);
    END loop;
--
    separator;
--
    OPEN c_emp_sort(l_emps);
    FETCH c_emp_sort BULK COLLECT INTO l_emps_sorted;
    CLOSE c_emp_sort;
    FOR indx IN l_emps_sorted.first .. l_emps_sorted.last loop
      dbms_output.put_line(l_emps_sorted(indx).ename);
    END loop;
  END;
END;

If I run this code

EXEC emp_test.emp_prc

the output looks like this:

SMITH
ALLEN 
WARD 
JONES 
MARTIN 
BLAKE 
CLARK 
SCOTT 
KING 
TURNER 
ADAMS 
JAMES 
FORD 
MILLER 
-=-=-=-=- 
ADAMS 
ALLEN 
BLAKE 
CLARK 
FORD 
JAMES 
JONES 
KING 
MARTIN 
MILLER 
SCOTT 
SMITH 
TURNER 
WARD

This is a great way of using the SQL engine to do the work you need done.

But what if you don’t have your data in a Nested Table, but in an Associative Array, which is PL/SQL only. If you are using Database 12c you’re in luck, because you can now use your Associative Array in de SQL statement.

I have created a similar script as the one before, except that I cannot define a TYPE in the SQL layer, it should be defined in a package specification.

PACKAGE emp_test IS
  TYPE emp_aat IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
  FUNCTION get_emp RETURN emp_aat;
  PROCEDURE emp_prc;
END;
PACKAGE BODY emp_test IS
  PROCEDURE separator
  IS
  BEGIN
    dbms_output.put_line('-=-=-=-=-');
  END;
--
  FUNCTION get_emp RETURN emp_aat
  IS
  l_returnvalue emp_aat;
  CURSOR c_emp IS
  SELECT *
    FROM emp;
  BEGIN
    OPEN c_emp;
    FETCH c_emp BULK COLLECT INTO l_returnvalue;
    CLOSE c_emp;
    RETURN l_returnvalue;
  END;
--
  PROCEDURE emp_prc IS
    l_emps emp_aat;
    l_emps_sorted emp_aat;
    CURSOR c_emp_sort(emps_in emp_aat)
    IS
    SELECT *
      FROM table(emps_in)
     ORDER BY ename;
  BEGIN
    l_emps := get_emp;
    FOR rec IN (select ename FROM TABLE(l_emps)) LOOP
      dbms_output.put_line(rec.ename);
    END LOOP;
--
    separator;
--
    OPEN c_emp_sort(l_emps);
    FETCH c_emp_sort BULK COLLECT INTO l_emps_sorted;
    CLOSE c_emp_sort;
    FOR indx IN l_emps_sorted.first .. l_emps_sorted.last LOOP
      dbms_output.put_line(l_emps_sorted(indx).ename);
    END LOOP;
  END;
END;

If I run this code on my 11Gr2 database it results in the following error:

No errors for PACKAGE DEMO.EMP_TEST 
Warning: Package body created with compilation errors 
Errors for PACKAGE BODY DEMO.EMP_TEST: 
LINE/COL ERROR 
-------- --------------------------------------------------------------------------- 
27/5 PL/SQL: SQL Statement ignored 
28/12 PL/SQL: ORA-22905: cannot access rows from a non-nested table item 
28/18 PLS-00382: expression is of wrong type 
33/17 PL/SQL: SQL Statement ignored 
33/35 PL/SQL: ORA-22905: cannot access rows from a non-nested table item 
33/41 PLS-00382: expression is of wrong type 
34/7 PL/SQL: Statement ignored 
34/28 PLS-00364: loop index variable 'REC' use is invalid

As mentioned before, you cannot access an Associative Array in the SQL layer. To use the SQL layer anyway you can of course create the necessary objects (record type and nested table type), copy all the data from the Associative Array to a Nested Table, run the SQL statement and copy the results back to an Associative Array. This means a lot of coding and the risk of errors. But if I run the same code on my 12Cr1 database, it completes successfully and displays the following result:

SMITH 
ALLEN 
WARD 
JONES 
MARTIN 
BLAKE 
CLARK 
SCOTT 
KING 
TURNER 
ADAMS 
JAMES 
FORD 
MILLER 
-=-=-=-=- 
ADAMS 
ALLEN 
BLAKE 
CLARK 
FORD 
JAMES 
JONES 
KING 
MARTIN 
MILLER 
SCOTT 
SMITH 
TURNER 
WARD

I think Oracle does a good job extending the possibilities of the PL/SQL language, especially integrating to SQL features into the PL/SQL layer. Although this might look like a small enhancement, but it can have a big impact on the performance of your program.

How an Oracle error can send you the wrong way…

Wed, 2016-01-06 08:53
.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; }

At my current assignment I needed to create an update statement to copy data from one table to another. Quite a simple task, I would say, but an error or actually a constraint violation sent me the wrong way in finding my solution.

Suppose I have two tables:

CREATE TABLE a
( mykey NUMBER
, thisvalue VARCHAR2(20) NOT NULL
)
/
CREATE TABLE b
( mykey NUMBER
, thatvalue VARCHAR2(20) NOT NULL
)
/

and some data in them:

INSERT INTO a (mykey,thisvalue) VALUES (1,'Larry');
INSERT INTO a (mykey,thisvalue) VALUES (2,'Bryn');
INSERT INTO a (mykey,thisvalue) VALUES (3,'Steven');
INSERT INTO a (mykey,thisvalue) VALUES (4,'Patrick');
INSERT INTO b (mykey,thatvalue) VALUES (1,'Larry Ellison');
INSERT INTO b (mykey,thatvalue) VALUES (2,'Bryn Llewellyn');
INSERT INTO b (mykey,thatvalue) VALUES (3,'Steven Feuerstein');
COMMIT
/ 

Now I want to update the values in table a with the values of table b. My first idea was to write a statement like this:

UPDATE a
   SET a.thisvalue = (select b.thatvalue
                        FROM b
                       WHERE b.mykey = a.mykey)
/

but this statement led to the following error:

ORA-01407: cannot update ("DEMO"."A"."THISVALUE") to NULL 


No problem, I thought, if the new value is somehow NULL, then just use the old value:

UPDATE a
   SET a.thisvalue = (select NVL(b.thatvalue, a.thisvalue)
                        FROM b
                       WHERE b.mykey = a.mykey)
/

but this still resulted in the same error:

ORA-01407: cannot update ("DEMO"."A"."THISVALUE") to NULL 

Then it dawned upon me. For mykey=4 there would be no match in table B, which resulted in no row returned, hence a NULL value.

The error given is absolutely correct, but it sent me the wrong way in finding a solution. If I would have gotten a NO DATA FOUND error, I would have known right away what was the problem.

The solution was pretty easy, just update the rows that have a matching row in table B:

UPDATE a
   SET a.thisvalue = (select b.thatvalue
                        FROM b
                       WHERE b.mykey = a.mykey)
 WHERE EXISTS (select b.thatvalue
                 FROM b
                WHERE b.mykey = a.mykey)
/

Another solution might be using the MERGE statement:

MERGE INTO a
USING (select b.mykey, b.thatvalue
         FROM b) b
ON (a.mykey = b.mykey)
WHEN MATCHED THEN
  UPDATE
     SET a.thisvalue = b.thatvalue
/

If the subselect results in more than one row you get an equivalent of the TOO_MANY_ROWS exception, but if the subselect results in no rows you don’t get the NO_DATA_FOUND (or equivalent) exception.

Update multiple columns

Wed, 2015-12-23 05:03

.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; }

This is something I knew somewhere in the back of my head, but had forgotten about until now.

When you want to update multiple columns in a single SQL statement based on a sub query you can of course duplicate this query for every column you want to update. But this violates the SPOD (Single Point Of Definition) ‘rule’.

As an example I have the following requirement:

Add two columns to the EMP table containing the name and job of the manager and fill these columns with the right values.

First of all I need to add the columns to the table, with is easy:

alter table emp add (mgr_name varchar2(10)
                    ,mgr_job varchar2(9)
                    )

Then comes the ‘tricky’ part. I can of course fill up these columns in separate statements, like this:

update emp e
   set e.mgr_name = (select m.ename
                       from emp m
                      where 1=1
                        and m.empno = e.mgr)
 where 1=1
   and e.mgr is not null
/
update emp e
   set e.mgr_job = (select m.job
                       from emp m
                      where 1=1
                        and m.empno = e.mgr)
 where 1=1
   and e.mgr is not null
/

But this implies two roundtrips from the client to the database. These statements can be combined into a single one:

update emp e
   set e.mgr_name = (select m.ename
                       from emp m
                      where 1=1
                        and m.empno = e.mgr)
     , e.mgr_job = (select m.job
                       from emp m
                      where 1=1
                        and m.empno = e.mgr)
 where 1=1
   and e.mgr is not null
/

But there is an easier, and more elegant way to do this:

update emp e
   set (e.mgr_name, e.mgr_job) = (select m.ename, m.job
                                   from emp m
                                  where 1=1
                                    and m.empno = e.mgr)
 where 1=1
   and e.mgr is not null
/

This is of course a pretty simple example, but you can imagine what would happen if you want to update more columns, create a complex sub query or worse, make modifications to the predicates. You are more than likely going to forget one or more sub queries giving you an undesired result.

update December 25th 2015: Find a demonstration script on LiveSQL

Autonomous transaction to the rescue

Tue, 2015-08-25 11:10
.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; }

Today, at my current project, I came across an issue where autonomous transactions came in handy.

The situation: I need to create a query to perform an export. A couple of the fields to be selected come from a global temporary table, nothing fancy so far except this global temporary table is filled by a (rather complex) procedure. Another problem is this table is emptied for every row, i.e. it will contain only one row at a time. ‘Just build a wrapper table function for this procedure and have that function call the procedure’ was my first idea.

I created a script that shows the situation

CREATE GLOBAL TEMPORARY TABLE empdate
(
  empno NUMBER(4)
, hiredate DATE
)
ON COMMIT DELETE ROWS
/
CREATE OR REPLACE PROCEDURE getthehiredate(empno_in IN NUMBER) IS
BEGIN
  DELETE FROM empdate;
  INSERT INTO empdate
    (empno
    ,hiredate)
    (SELECT empno
           ,hiredate
       FROM emp
      WHERE empno = empno_in);
END getthehiredate;
/

Then I set out to build a pipelined table function that accepts a cursor as one of its parameters. This function then loops all the values in the cursor, calls the procedure, reads the data from the global temporary table and pipes out the resulting record, nothing really fancy so far.

CREATE TYPE empdate_t AS OBJECT
(
  empno    NUMBER(4),
  hiredate DATE
)
/
CREATE TYPE empdate_tab IS TABLE OF empdate_t
/
CREATE OR REPLACE FUNCTION getallhiredates(empnos_in IN SYS_REFCURSOR) RETURN empdate_tab
  PIPELINED IS
  l_empno       NUMBER(4);
  l_returnvalue empdate_t;
BEGIN
  FETCH empnos_in
    INTO l_empno;
  WHILE empnos_in%FOUND LOOP
    getthehiredate(empno_in => l_empno);
    SELECT empdate_t(ed.empno, ed.hiredate)
      INTO l_returnvalue
      FROM empdate ed
     WHERE 1 = 1
       AND ed.empno = l_empno;
    PIPE ROW(l_returnvalue);
    FETCH empnos_in
      INTO l_empno;
  END LOOP;
  RETURN;
END getallhiredates;
/

But when I ran a query against this function:

SELECT *
FROM TABLE(getallhiredates(CURSOR (SELECT empno
FROM emp)))
/

I ran into an error:

ORA-14551: cannot perform a DML operation inside a query 

So, all the work I done so far had been for nothing? Time wasted? I don’t think so. If there is anything I learned over the years it is that Oracle tries to stop you doing certain things but at the same time supplies you the tools to create a work-around.

There is something like an autonomous transaction, that might help me in this case so I changed the code for the function a bit:

CREATE OR REPLACE FUNCTION getallhiredates(empnos_in IN SYS_REFCURSOR) RETURN empdate_tab
  PIPELINED IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_empno       NUMBER(4);
  l_returnvalue empdate_t;
BEGIN
  FETCH empnos_in
    INTO l_empno;
  WHILE empnos_in%FOUND LOOP
    getthehiredate(empno_in => l_empno);
    SELECT empdate_t(ed.empno, ed.hiredate)
      INTO l_returnvalue
      FROM empdate ed
     WHERE 1 = 1
       AND ed.empno = l_empno;
    PIPE ROW(l_returnvalue);
    FETCH empnos_in
      INTO l_empno;
  END LOOP;
  COMMIT;
  RETURN;
END getallhiredates;
/

But when I ran the query:

SELECT *
FROM TABLE(getallhiredates(CURSOR (SELECT empno
FROM emp)))
/

I ran into a different error:

ORA-06519: active autonomous transaction detected and rolled back

So this doesn’t work or does it? Pipelined table functions have ‘exit’ the function multiple times. Whenever a row is piped out. So, I tried to put the COMMIT just before the PIPE ROW command:

CREATE OR REPLACE FUNCTION getallhiredates(empnos_in IN SYS_REFCURSOR) RETURN empdate_tab
  PIPELINED IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_empno       NUMBER(4);
  l_returnvalue empdate_t;
BEGIN
  FETCH empnos_in
    INTO l_empno;
  WHILE empnos_in%FOUND LOOP
    getthehiredate(empno_in => l_empno);
    SELECT empdate_t(ed.empno, ed.hiredate)
      INTO l_returnvalue
      FROM empdate ed
     WHERE 1 = 1
       AND ed.empno = l_empno;
    COMMIT;
    PIPE ROW(l_returnvalue);
    FETCH empnos_in
      INTO l_empno;
  END LOOP;
  RETURN;
END getallhiredates;
/

And when I ran my statement again:

SELECT *
FROM TABLE(getallhiredates(CURSOR (SELECT empno
FROM emp)))
/

It worked as I hoped for.

As you can see I have tried to mimic the situation using the EMP and DEPT tables. I think this is a nice little trick, but it should be used with caution. It is not for no reason that Oracle prevents you from running DML inside a query, but in this case I can bypass this restriction.


What’s this ‘WHERE 1=1’?

Sun, 2015-03-22 02:30

Since some time I have been adding WHERE 1=1 to all my queries.
I get queries like this:

SELECT *
  FROM emp e
 WHERE 1=1
   AND e.ename LIKE 'A%'
   AND e.deptno = 20

Lots of people ask me what’s the use of this WHERE 1=1.

You know I like to type as little as possible but here I am typing a lot of extra characters. And yet, it makes my development life a lot easier.

If my query has a lot of predicates and I want to see what happens then I usually comment those predicates out by using — (two dashes). I use my own CommentLine plug-in for this. This is easy for the second and higher predicates. But if I want to comment out the first predicate, then it get a bit harder. Well, not harder, but more work.

If I didn’t use the WHERE 1=1 and I wanted to comment out the ename predicate then I would have to do something like this:

SELECT *
  FROM emp e
 WHERE /*e.ename LIKE 'A%'
   AND */e.deptno = 20

I agree, it’s not hard to do, but I think it’s a lot more work than just adding — (two dashes) in front of a line:

SELECT *
  FROM emp e
 WHERE 1 = 1
--   AND e.ename LIKE 'A%'
   AND e.deptno = 20

And, as I don’t like typing or at least, I want to make it as easy for me as possible, I am using another one of my plug-ins, Template, where I defined a template w1 which results in

WHERE 1 = 1
   AND 


making it easy for me to write the queries.

I think adding this extra predicate has no (or hardly any) influence on the execution time of the query. I think the optimizer ignores this predicate completely.

I hope this explains a bit why I write my queries like this.

Numeric sorting an alphanumeric column

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?

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…

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

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

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.