Bar Solutions

Subscribe to Bar Solutions feed
The beginning of knowledge is the discovery of something we do not understand. [Frank Herbert]
Updated: 1 week 2 days ago

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.

Pages