Bar Solutions

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

What is overloading and how and when do I use it

Thu, 2016-09-29 04:29

Dear Patrick,

Recently I heard someone talk about overloading in Java. What is it, is it possible in PL/SQL and if so, how would I use it?

Ramesh Cumar

Dear Ramesh,

Overloading is a technique of creating multiple programs with the same name that can be called with different sets of parameters. It is definitely possible to apply this technique in PL/SQL, in fact, Oracle does this a lot of times in their own built-in packages. If you take a look at the SYS.STANDARD package then you will find a lot of functions called TO_CHAR, but with different parameter sets. You probably never wondered how Oracle can use the same function name for completely different tasks. It’s just as easy to write
TO_CHAR(9) which will result in ‘9’ as it is to write TO_CHAR(SYSDATE) which will result in the current date in to format specified in the NLS_DATE_FORMAT parameter, for example 29-12-15 if the format is ‘DD-MM-RR’. If you would want to get this value in a different format you can just write TO_CHAR (SYSDATE, ‘Month, DDth YYYY’) to get ‘December, 29th 2015’. As you can see they are all calls to a function with the same name, but with completely different sets of parameters.

This behavior cannot be realized by making all the parameters option, like this:

FUNCTION TO_CHAR (num_in in number default null
, date_in in date default null
, datemask_in in varchar2) return varchar2;

Because if you would want to call this function without using named parameters this call
TO_CHAR (SYSDATE) would not work, since SYSDATE returns a DATE and the function expects a number as its first parameter. Maybe it might work, because of the implicit typecasts, but you get the idea.
The way this is implemented is there are multiple functions defined in a package with the same name but different sets of parameters.

One of the packages you can take a look at, because its implementation is readable, i.e. not wrapped, is the HTP package which you can use to generate HTML output for instance in an APEX application. If you take a look at for instance the PRINT procedure. In the package specification you can see there are three implementations available for this procedure:

procedure print (cbuf in varchar2 character set any_cs DEFAULT NULL);
procedure print (dbuf in date);
procedure print (nbuf in number);

The parameters of these function differ not only in name, but also in data type, which is a requirement for the use of overloading:
Data type and/or number and/or name of parameters must differ
The compiler will not complain if you don’t completely comply with this rule, but at runtime you will not be able to use either one of them.

Consider the following package with its implementation

[PATRICK]SQL>CREATE OR REPLACE PACKAGE ol IS
              PROCEDURE p (param_in IN VARCHAR2);
               PROCEDURE p (param_in IN CHAR);
             END ol;
             /
[PATRICK]SQL>CREATE OR REPLACE PACKAGE BODY ol IS
               PROCEDURE p (param_in IN VARCHAR2)
               IS
               BEGIN
                 dbms_output.put_line(param_in);
               END p;
               PROCEDURE p (param_in IN CHAR)
               IS
               BEGIN
                 dbms_output.put_line(param_in);
               END p;
             END ol;
             /

If you want to call the procedure there is no way Oracle can decide which one to use.

[PATRICK]SQL>BEGIN
               ol.p('Hello World');
             END;
             /
  ol.p('Hello World');
  *
ERROR at line 2:
ORA-06550: Line 2, column 3:
PLS-00307: too many declarations of 'P' match this call.
ORA-06550: Line 2, column 3:
PL/SQL: Statement ignored.

Even if you were using named parameters you would get the same error. What we have here is so called ‘ambiguous overloading’. You can read more about this subject at http://www.stevenfeuerstein.com/learn/building-code-analysis.
So, there is definitely a use for overloading but you have to be careful about the parameters, especially when parameters have default values. If you run into a situation of ambiguous overloading you now know why the compiler didn’t complain, but the runtime engine does.

Happy Oracle’ing,
Patrick Barel

If you have any comments on this subject or you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com. If I know the answer, or can find it for you, maybe I can help.

This question has been published in OTech Magazine of Summer 2015.

When would you use a normal table function?

Thu, 2016-09-22 04:22

Dear Patrick,

Last year I did a presentation on table functions at KScope. One of the questions I got was: ‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?

Erik van Roon

Dear Erik,

Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of data and which can be called in a normal SQL statement by using the TABLE() operator. Let’s create a simple function. Please note this is a demonstration only, not something you would normally solve using table functions (or PL/SQL as a whole).
First step is to create a record type in the database:

[PATRICK]SQL>CREATE TYPE emp_rt AS OBJECT
             ( empno NUMBER(4)
             , ename VARCHAR2(10)
             , mgr   NUMBER(4)
             )
             /

Then you need to create a table type in the database:

[PATRICK]SQL>CREATE TYPE emp_tt AS TABLE OF emp_rt
             /

Then it is time for the simple function. The DBMS_LOCK.SLEEP call is in there to show the difference between Table Functions and Pipelined Table Functions.

[PATRICK]SQL>CREATE OR REPLACE FUNCTION tf_emp RETURN emp_tt
             AS
               l_returnvalue emp_tt;
             BEGIN
               SELECT emp_rt(e.empno, e.ename, e.mgr)
                 BULK COLLECT INTO l_returnvalue
                 FROM emp e
                ORDER BY e.deptno;
               FOR indx IN l_returnvalue.first ..
                           l_returnvalue.last LOOP
                 l_returnvalue(indx).ename :=
                   INITCAP(l_returnvalue(indx).ename);
                 dbms_lock.sleep(.25); -- for demo purposes only
               END LOOP;
               RETURN l_returnvalue;
             END;
             /

Now you can call the function in the FROM clause of your SQL statement as if it were a relational table:

[PATRICK]SQL>SELECT *
               FROM TABLE(tf_emp)
             /

Notice that the result is displayed after all records have been processed, i.e. after 3.5 seconds (due to the DBMS_LOCK.SLEEP statement).
Now let’s create a PIPELINED table function, which produces the same result but in a different manner:

[PATRICK]SQL>CREATE OR REPLACE FUNCTION ptf_emp RETURN emp_tt
                               PIPELINED
             AS
               l_emps emp_tt;
               l_returnvalue emp_rt;
             BEGIN
               SELECT emp_rt(e.empno, e.ename, e.mgr)
                 BULK COLLECT INTO l_emps
                 FROM emp e
                ORDER BY e.deptno;
               FOR indx IN l_emps.first ..
                           l_emps.last LOOP
                 l_returnvalue :=
                   emp_rt(empno => l_emps(indx).empno
                         ,ename => INITCAP(l_emps(indx).ename)
                         ,mgr => l_emps(indx).mgr);
                 PIPE ROW (l_returnvalue);
                 dbms_lock.sleep(.25); -- for demo purposes only
               END LOOP;
               RETURN;
             END;
             /

If you set the arraysize of your SQL*Plus session (or your command window in PL/SQL Developer) you can see how the results are being returned as they are produced, i.e. 0.25 seconds apart.

[PATRICK]SQL>SET ARRAYSIZE 1
[PATRICK]SQL>SELECT *
               FROM TABLE(ptf_emp)
             /

Now you can see the difference between a Table Function and a Pipelined Table Function. Pipelined Table Functions are best used when you are executing different stages of transformation of your data, for example reading from an OLTP system and writing to a DataWareHouse system. If you PARALLEL_ENABLE your functions AND your source table can be read parallel then you could really see some performance benefits.
But all of this doesn’t explain why you should NOT use pipelining in a table function. The ONLY reason I can think of is when you want to be able to call the function from plain PL/SQL. PL/SQL does one call to a function and expects one result from it. Not a result spread out over many ‘callback’s.
If you create a function like this:

[PATRICK]SQL>CREATE OR REPLACE PROCEDURE call_tf AS
               l_emps emp_tt;
             BEGIN
               l_emps := tf_emp;
               FOR indx IN l_emps.first .. l_emps.last LOOP
                 dbms_output.put_line(l_emps(indx).empno ||
                                     ' ' || l_emps(indx).ename ||
                                     ' ' || l_emps(indx).mgr);
               END LOOP;
             END;
             /

And call this function, then everything works ok. It takes about 3.5 seconds for the function to complete, due to the DBMS_LOCK.SLEEP call in the tf_emp function.
If you call the pipelined table function you are stopped at compile time. The call to a pipelined table function is not allowed.

[PATRICK]SQL>CREATE OR REPLACE PROCEDURE call_ptf AS
               l_emps emp_tt;
             BEGIN
               l_emps := ptf_emp;
               FOR indx IN l_emps.first .. l_emps.last LOOP
                 dbms_output.put_line(l_emps(indx).empno ||
                                     ' ' || l_emps(indx).ename ||
                                     ' ' || l_emps(indx).mgr);
               END LOOP;
             END;
             /
Warning: Procedure created with compilation errors.
[PATRICK]SQL>sho err
Errors for PROCEDURE CALL_PTF:
LINE/COL ERROR
-------- ---------------------------------------------------------------------
1/10     PLS-00653: aggregate/table functions are not allowed in PL/SQL scope

You can of course wrap the call to the pipelined table function in a cursor like this:

[PATRICK]SQL>CREATE OR REPLACE PROCEDURE call_ptf2 AS
               CURSOR c_emps IS
                 SELECT emp_rt(t.empno, t.ename, t.mgr)
                   FROM TABLE(ptf_emp) t;
               l_emps emp_tt;
             BEGIN
               OPEN c_emps;
               FETCH c_emps BULK COLLECT
                 INTO l_emps;
               FOR indx IN l_emps.first .. l_emps.last LOOP
                 dbms_output.put_line(l_emps(indx).empno ||
                                     ' ' || l_emps(indx).ename ||
                                     ' ' || l_emps(indx).mgr);
               END LOOP;
             END;
             /

Procedure created.

But when you call this function you will see that it takes about 3.5 seconds to fetch all the records, effectively using the pipelined table function as a normal table function. This might be your escape to use pipelined table functions in a SQL only environment and still use the same function in a PL/SQL environment.
‘But you said pipelined table functions are best used then executing different stages of transformation. That includes multiple PL/SQL functions.’ you might wonder. That is correct. A pipelined table function may call another pipelined table function. It can use for instance a collection as its input like this

[PATRICK]SQL>CREATE OR REPLACE FUNCTION ptf_emp3
                                        (emps_in IN emp_tt)
                                 RETURN emp_tt PIPELINED
             AS
               l_emp emp_rt;
             BEGIN
               FOR indx IN emps_in.first .. emps_in.last LOOP
                 l_emp := emps_in(indx);
                 l_emp.ename := UPPER(l_emp.ename);
                 PIPE ROW (l_emp);
                 dbms_lock.sleep(.25); -- for demo purposes only
               END LOOP;
               RETURN;
             END;
             /

When you call this function using this:

[PATRICK]SQL>SET ARRAYSIZE 1
[PATRICK]SQL>SELECT *
               FROM TABLE(ptf_emp3(CAST(MULTISET (SELECT *
                                             FROM TABLE(ptf_emp))
                                                     AS emp_tt)))
             /

You will notice the output will not start displaying until after about 3.5 seconds which tells me the call to ptf_emp must be completed before pft_emp3 can start doing its work.
If you change the parameter to accept a cursor (and of course change the processing as well) like this:

[PATRICK]SQL>CREATE OR REPLACE FUNCTION ptf_emp4
                               (cursor_in IN SYS_REFCURSOR)
                                 RETURN emp_tt PIPELINED
             AS
               l_emp emp_rt;
             BEGIN
               LOOP
                 FETCH cursor_in
                   INTO l_emp;
                 EXIT WHEN cursor_in%NOTFOUND;
                 l_emp.ename := upper(l_emp.ename);
                 PIPE ROW(l_emp);
                 dbms_lock.sleep(.25); -- for demo purposes only
               END LOOP;
               RETURN;
             END;
             /

And call this function using this statement:

[PATRICK]SQL>SET arraysize 1
[PATRICK]SQL>SELECT *
               FROM TABLE(ptf_emp4(CURSOR (SELECT emp_rt(empno
                                                        ,ename
                                                        ,mgr)
                                       FROM TABLE(ptf_emp))))
             /

You will notice the output starting to display after about half a second. The total time needed for this function to complete is roughly the same as the previous, but this function starts processing sooner, which might be exactly what you need.

I hope this sheds a bit of light on the subject. I think bottom line is to always use pipelined table functions instead of normal table functions, except when the function is only meant for PL/SQL or client consumption. You can always work around implementation restrictions if you need to use the same codebase in both SQL and PL/SQL.

Happy Oracle’ing,
Patrick Barel

If you have any comments on this subject or you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com. If I know the answer, or can find it for you, maybe I can help.

This question has been published in OTech Magazine of Spring 2015.

What’s the difference between SEMI-JOIN and ANTI-JOIN?

Thu, 2016-09-15 04:15

Dear Patrick,

What is an ANTI-JOIN? And what is the difference between the SEMI-JOIN and the ANTI-JOIN?

Lillian Sturdey

Dear Lillian,

First of all, both SEMI-JOIN and ANTI-JOIN are not in the SQL syntax but they are more a pattern. You might expect to be able to write something like:

[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc
               FROM dept d
               SEMI JOIN emp e ON (e.deptno = d.deptno)
             /

to get all the departments that have at least one employee.
Or:

[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc
               FROM dept d
               ANTI JOIN emp e ON (e.deptno = d.deptno)
             /

to get the departments with no employees. But all you get is an error saying your command is not properly ended, which can be read as a syntax error.

ERROR at line 3:
ORA-00933: ORA-00933 SQL command not properly ended.

Maybe your first idea would be to use a normal join to get all the departments with at least one employee:

[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc
               FROM dept d
               JOIN emp e ON (e.deptno = d.deptno)
             /

But this results in a record for every row in the EMP table. And we only wanted every unique department.

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        10 ACCOUNTING     NEW YORK
        30 SALES          CHICAGO
        20 RESEARCH       DALLAS

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        20 RESEARCH       DALLAS
        10 ACCOUNTING     NEW YORK

14 rows selected.

Well, that’s easy enough, you think, just add a DISTINCT to the statement:

[PATRICK]SQL>SELECT DISTINCT d.deptno, d.dname, d.loc
               FROM dept d
               JOIN emp e ON (e.deptno = d.deptno) 
             /

Exactly the result we are looking for:

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
        10 ACCOUNTING     NEW YORK
        30 SALES          CHICAGO

But what if the EMP table contains hundreds, thousands or maybe millions of rows. That would mean the database has to do a lot of work to filter out the distinct values.
A different, and probably better, approach would be to use the SEMI-JOIN pattern. You can use the IN operator like this:

[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc
              FROM dept d
              WHERE d.deptno IN (SELECT e.deptno
                                   FROM emp e)
             /
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        10 ACCOUNTING     NEW YORK

This is exactly what we want to see but for big tables this is not the correct way to go. For every record in the dept table all the records in the EMP table are checked. Again, if we have a lot of employees, this means a lot of work for the database.
A better SEMI-JOIN to use is the EXISTS operator:

[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc
               FROM dept d
              WHERE EXISTS (SELECT 1
                              FROM emp e
                             WHERE e.deptno = d.deptno)
             /

Please note that with the current optimizer in the database Oracle will rewrite your query to use the best approach for the task. If the inner table (in our example EMP) is rather small, then the IN approach might be the best, in other cases it might be better to use the EXISTS approach. Where in earlier versions you had to think about which way to go (IN is better for small tables, EXISTS is better for big ones), you can now rely on the optimizer to make the correct choice.
If you would want to see exactly the opposite of this query, i.e. all departments with no employees, you use an ANTI-JOIN pattern, which is pretty much the same but in this case you use NOT IN or NOT EXISTS. A different approach, which I think is pretty nice is to use an OUTER JOIN and check for the non-existence of values in column for the OUTER JOINED table.

[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc
               FROM dept d
               LEFT OUTER JOIN emp e ON (e.deptno = d.deptno)
              WHERE e.empno IS NULL
             /
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

Hope this gives you a bit more insight in this subject and gives you a better understanding of the wonders of the SQL language. Notice there are many ways to reach the same result, but one approach might be more economical than the other.

Happy Oracle’ing,
Patrick Barel

Hope this answers your question.
Happy Oracle’ing,
Patrick Barel

If you have any comments on this subject or you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com. If I know the answer, or can find it for you, maybe I can help.

This question has been published in OTech Magazine of Winter 2014.

What is the difference between NVL and COALESCE?

Thu, 2016-09-08 04:08

Dear Patrick,

Could you tell me what the difference is between NVL and COALESCE?

Kindest regards,
Paul McCurdey

Dear Paul,

NVL returns the value of the first argument if it doesn’t evaluate to NULL, otherwise it will return the value of the second argument.
COALESCE returns the first argument that doesn’t evaluate to NULL. That can be any one of the arguments.
So they are definitely similar – but there are significant differences.
First of all, COALESCE is a function that’s part of the ANSI-92 standard whereas NVL was made in the 80′s when there were no standards. Since COALESCE is the newer function of the two (since 9i), it is better equipped for multiple values and it does less work, therefore it’s the greener option (doing less work means using less resources, like power, and therefore it is greener).
How is doing less work better, you might ask? Well, would you want to do the work for which you know the result is never going to be used? I know I wouldn’t. That is one thing COALESCE does for you. It does not evaluate an argument if its result is not needed.
A big advantage of using the COALESCE function is the short-circuit evaluation. Where NVL evaluates both arguments, whether the second argument should be used or not, COALESCE only evaluates the arguments if they are needed.
For example:
If you run the following statement:

[PATRICK]SQL>SELECT NVL(1, 1/0) FROM dual
             /

you will see the

ORA-01476: division by zero

error.
In this statement the first argument (1) is NOT NULL so the second argument (1/0) should not be returned, but since with NVL PL/SQL evaluates the expression, this statement results in the exception.
The COALESCE function only evaluates the arguments if they are needed.
If you run the following statement:

[PATRICK]SQL>SELECT COALESCE(1, 1/0) FROM dual
          2  /

you will not get an error, since the second argument is not evaluated. In other words it evaluates exprN only if expr(N-1) evaluates to NULL.

A simple test shows the difference again:
First we create simple package which holds a variable and a function to increase that variable:

[PATRICK]SQL>CREATE OR REPLACE PACKAGE nvl_test
             IS
               g_value NUMBER := 0;
               FUNCTION increase_value RETURN NUMBER;
             END nvl_test;
             /
[PATRICK]SQL>CREATE OR REPLACE PACKAGE BODY nvl_test
             IS
               FUNCTION increase_value RETURN NUMBER
               IS
                l_returnvalue NUMBER;
               BEGIN
                 dbms_output.put_line('nvl_test.increase_value');
                 nvl_test.g_value := nvl_test.g_value + 1;
                 l_returnvalue := nvl_test.g_value;
                 RETURN l_returnvalue;
               END increase_value;
             END nvl_test;
             /

Then a script to demonstrate what happens. First display the value of the variable. Then call the NVL function where the first value is NULL. As you can see, the function in the package is called, hence the variable is increased. Then another call to the NVL function, this time with a non NULL value. The function in the package is still called even though its value is not being used.
Then we reset the value of the variable and run the same tests, but this time using the COALESCE function. As you can see, the function is only being called if the previous argument(s) evaluate to NULL.

[PATRICK]SQL>DECLARE
               l_dummy NUMBER;
               l_foo NUMBER;
             BEGIN
               dbms_output.put_line('====reset package====');
               nvl_test.g_value := 0;
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
               l_foo := NVL(NULL,nvl_test.increase_value);
               dbms_output.put_line(l_foo);
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
               l_foo := NVL(2912,nvl_test.increase_value);
               dbms_output.put_line(l_foo);
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
               dbms_output.put_line('====reset package====');
               nvl_test.g_value := 0;
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
               l_foo := coalesce(NULL,nvl_test.increase_value);
               dbms_output.put_line(l_foo);
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
               l_foo := coalesce(2912,nvl_test.increase_value);
               dbms_output.put_line(l_foo);
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
             END;
             /

====reset package====
0
nvl_test.increase_value
1
1
nvl_test.increase_value
2912
2
====reset package====
0
nvl_test.increase_value
1
1
2912
1

If you run the anonymous block in an IDE where you can step through the code, you can see when the code is executed and when it is bypassed.
So, if you don’t need or want the code executed when the value of a variable or result of a function is not NULL, then you should use COALESCE to prevent this from happening. But there might be a use case in which you always want a piece of code executed whether the first argument is NULL or not. Then you should use (or stick to) NVL.
I think the rule should be: Use COALESCE unless…

Hope this answers your question.
Happy Oracle’ing,
Patrick Barel

If you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com. If I know the answer, or can find it for you, maybe I can help.

This question has been published in OTech Magazine of Fall 2014

How do I get my query results paginated?

Thu, 2016-09-01 04:01

Dear Patrick,

I have got a website with a search form. I want to display a limited number of results to the user and have him/her navigate through different pages. Is this possible using plain SQL?

Kindest regards,

Mitchell Ian

Dear Mitchell,

Of course this is possible. It might take some thinking, but that has never hurt anyone (yet). First we need a table with some randomly sorted data in it. In this example I am just using 20 records, but you can use this approach on bigger tables of course.

[PATRICK]SQL>CREATE TABLE t AS 
             SELECT LEVEL val#, to_char(LEVEL, '9999') value_in_text 
               FROM dual 
             CONNECT BY LEVEL < 21
              ORDER BY dbms_random.random 
             /  

Table created.

The order by dbms_random.random is to ensure the data is inserted in random order. I you just select from this new table then you data will be unordered.

Now we select the first ‘page’ from this table. Our page size is 5 records. So the query will be:

[PATRICK]SQL>SELECT *
              FROM t
             WHERE ROWNUM <= 5
            /
VAL#       VALUE
---------- -----
10         10
20         20
16         16
1          1
17         17

This results in the first 5 rows from the table. If we want to get the next 5, rownums 6 through 10 then you might want to try something like this.

[PATRICK]SQL>SELECT *
               FROM t
              WHERE ROWNUM > 5 AND ROWNUM <= 10
             /

no rows selected

Unfortunately this doesn’t work. I appears this query will never have any resulting row with a number between 6 and 10. The solution to this issue is the use of a subquery:

[PATRICK]SQL>SELECT val#, value_in_text
               FROM (SELECT t.val#, t.value_in_text, ROWNUM rn
                       FROM t)
              WHERE rn > 5 AND rn <= 10
             /

VAL#       VALUE
---------- -----
13         13
4          4
5          5
3          3
14         14

In this query we first select all the rows we might need for the pages and using this resultset we just select the rows we are interested in for our page.

If your table is rather big you may want to include the maximum rownum in the inline view.

[PATRICK]SQL>SELECT val#, value_in_text
               FROM (SELECT t.val#, t.value_in_text, ROWNUM rn
                       FROM t
                      WHERE ROWNUM <= 10)
              WHERE rn > 5 AND rn <= 10
             /

VAL#       VALUE
---------- -----
13         13
4          4
5          5
3          3
14         14

As you are probably aware of the is no guarantee on how the rows are being returned unless you specify an order by clause. But what happens if you were to just include this order by in your query. Let’s see what happens when you include it in the first query for the first page:

[PATRICK]SQL>SELECT *
               FROM t
              WHERE ROWNUM <= 5
             ORDER BY t.val#
             /

VAL#       VALUE
---------- -----
12         12
13         13
15         15
17         17
19         19

The rows returned are in order, but they are definitely not the first 5 values currently in the table. That is how the sql engine works. It first gets the first 5 rows to honor the predicate in the query and then it sorts the result before returning it to the caller.

What we should do to get the correct behavior of our query is use a subquery to get the results in order and apply the rownum clause to that result.

[PATRICK]SQL>SELECT *
               FROM (SELECT *
                       FROM t
                     ORDER BY t.val#)
              WHERE ROWNUM &lt;= 5
             /

VAL#       VALUE
---------- -----
1          1
2          2
3          3
4          4
5          5

We can now use this to build a query to get the next page of results:

[PATRICK]SQL>SELECT val#, value_in_text
               FROM (SELECT val#, value_in_text, ROWNUM rn
                       FROM (SELECT *
                               FROM t
                             ORDER BY t.val#)
                     ORDER BY rn)
              WHERE rn > 5 AND rn <= 10
             /

VAL#       VALUE
---------- -----
6          6
7          7
8          8
9          9
10         10

When you have access to an Oracle 12c database, it is a lot easier, to get the first page of the ordered results, you can issue this statement:

[PATRICK]SQL>SELECT *
               FROM t
            ORDER BY t.val#
            FETCH FIRST 5 ROWS ONLY
            /

To get another page you can provide query with an offset of how many rows to skip:

[PATRICK]SQL>SELECT *
               FROM t
             ORDER BY t.val#
             OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY
             /

Under the covers Oracle still issues similar queries as the ones we built earlier, but it is a lot easier to write these.

Hope this sheds a bit of light on your issue.

Happy Oracle’ing,

Patrick Barel

If you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com. If I know the answer, or can find it for you, maybe I can help.

This question has been published in OTech Magazine of Summer 2014

Virtual Private Database…

Sun, 2016-07-31 01:39

Some time ago I was asked to assist in fixing or at least finding the cause of a performance problem. The application ran fine until the Virtual Private Database (VPD) policy was applied. Oracle claims there should be near zero impact on your application when you implement VPD, then how is this possible?

First of all, the policy applied was a rather complex one. A complex query should be executed to determine if the current user has access to the record. Let’s say this query takes up a second, then I would expect my query to run about a second slower, maybe two. But the query took several minutes to complete when the VPD policy was applied. This didn’t make sense to me, so I decided to find out what was really happening.
To do this, I opened up my sandbox database to try and recreate this situation.
First I need to create two new users

create user vpd1 identified by vpd1
/
grant connect, resource to vpd1
/
create user vpd2 identified by vpd2
/
grant connect, resource to vpd2
/

Then I created a simple table to hold the data that should be protected by the VPD policy:

drop table emp purge
/
create table emp
       (empno number(4) not null, 
        ename varchar2(10), 
        job varchar2(9), 
        mgr number(4), 
        hiredate date, 
        sal number(7, 2), 
        comm number(7, 2), 
        deptno number(2))
;
insert into emp values (7369, 'SMITH',  'CLERK',     7902, to_date('17-12-1980', 'DD-MM-YYYY'),  800, null, 20);
insert into emp values (7499, 'ALLEN',  'SALESMAN',  7698, to_date('20-02-1981', 'DD-MM-YYYY'), 1600,  300, 30);
insert into emp values (7521, 'WARD',   'SALESMAN',  7698, to_date('22-02-1981', 'DD-MM-YYYY'), 1250,  500, 30);
insert into emp values (7566, 'JONES',  'MANAGER',   7839, to_date('02-04-1981', 'DD-MM-YYYY'),  2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN',  7698, to_date('28-09-1981', 'DD-MM-YYYY'), 1250, 1400, 30);
insert into emp values (7698, 'BLAKE',  'MANAGER',   7839, to_date('01-05-1981', 'DD-MM-YYYY'),  2850, null, 30);
insert into emp values (7782, 'CLARK',  'MANAGER',   7839, to_date('09-06-1981', 'DD-MM-YYYY'),  2450, null, 10);
insert into emp values (7788, 'SCOTT',  'ANALYST',   7566, to_date('09-12-1982', 'DD-MM-YYYY'), 3000, null, 20);
insert into emp values (7839, 'KING',   'PRESIDENT', null, to_date('17-11-1981', 'DD-MM-YYYY'), 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN',  7698, to_date('08-09-1981', 'DD-MM-YYYY'),  1500,    0, 30);
insert into emp values (7876, 'ADAMS',  'CLERK',     7788, to_date('12-01-1983', 'DD-MM-YYYY'), 1100, null, 20);
insert into emp values (7900, 'JAMES',  'CLERK',     7698, to_date('03-12-1981', 'DD-MM-YYYY'),   950, null, 30);
insert into emp values (7902, 'FORD',   'ANALYST',   7566, to_date('03-12-1981', 'DD-MM-YYYY'),  3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK',     7782, to_date('23-01-1982', 'DD-MM-YYYY'), 1300, null, 10);
commit
/
drop table emp_vpd purge
/
create table emp_vpd as select * from emp
/
commit
/

And of course I need to grant access to this table to the newly created users:

grant all on emp_vpd to vpd1
/
grant all on emp_vpd to vpd2
/

On the table I need to create a policy function so I create a package (which mimics the customers package, just simpler) to do this:

create or replace package emp_vpd_policy as
  function first_policy(owner_in   in varchar2
                       ,objname_in in varchar2) return varchar2;
  function allowed(empno_in  in number
                  ,deptno_in in number) return number;
end emp_vpd_policy;
/
sho err
create or replace package body emp_vpd_policy as
  function first_policy(owner_in   in varchar2
                       ,objname_in in varchar2) return varchar2 is
  begin
    dbms_output.put_line('first policy');
    if (user = 'VPD1') then
      return 'emp_vpd_policy.allowed(emp_vpd.empno, emp_vpd.deptno)=10';
    elsif user = 'VPD2' then
      return 'emp_vpd_policy.allowed(emp_vpd.empno, emp_vpd.deptno)=20';
    else
      return '1=1';
    end if;
  end first_policy;
  function allowed(empno_in  in number
                  ,deptno_in in number) return number is
  begin
    dbms_output.put_line('emp_vpd_policy.allowed(' || empno_in || ',' || deptno_in || ')');
    return deptno_in;
  end allowed;
end emp_vpd_policy;
/
sho err

and then protect the EMP_VPD table using a policy:

begin
  sys.dbms_rls.add_policy(object_schema => 'DEMO'
                         ,object_name => 'EMP_VPD'
                         ,policy_name => 'EMP_VPD_SEL'
                         ,function_schema => '&myuser'
                         ,policy_function => 'EMP_VPD_POLICY.FIRST_POLICY'
                         ,statement_types => 'SELECT');
end;
/

The package will show what will happen when I perform a select on the table:

conn vpd1/vpd1
set serveroutput on size unlimited
select * from demo.emp_vpd
/
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7782 CLARK      MANAGER    7839 6/9/1981      2450.00               10
 7839 KING       PRESIDENT       11/17/1981    5000.00               10
 7934 MILLER     CLERK      7782 1/23/1982     1300.00               10
first policy
first policy
emp_vpd_policy.allowed(7369,20)
emp_vpd_policy.allowed(7499,30)
emp_vpd_policy.allowed(7521,30)
emp_vpd_policy.allowed(7566,20)
emp_vpd_policy.allowed(7654,30)
emp_vpd_policy.allowed(7698,30)
emp_vpd_policy.allowed(7782,10)
emp_vpd_policy.allowed(7788,20)
emp_vpd_policy.allowed(7839,10)
emp_vpd_policy.allowed(7844,30)
emp_vpd_policy.allowed(7876,20)
emp_vpd_policy.allowed(7900,30)
emp_vpd_policy.allowed(7902,20)
emp_vpd_policy.allowed(7934,10)

In my case this is done rather quickly, there’s almost no difference in timing for the query with or without the policy applied. But as you can see, the policy is executed for each and every record that is being checked. Well, not really the policy itself, but the function that is defined in the policy. So if this function takes a lot of time and your table has a lot of records then the query will run for a very long time. There has got to be a better way to do this.
Let’s analyze what happens, the actual policy is executed twice. What if we use this architecture to our benefit. In the first pass we can setup some in memory data structure to hold whatever we need, this might take some time and then in the second pass we can use this data to be used in the actual check.
First we drop the policy so we can create a new one:

begin
  sys.dbms_rls.drop_policy(object_schema => '&myuser'
                          ,object_name => 'EMP_VPD'
                          ,policy_name => 'EMP_VPD_SEL');
end;
/

For our implementation we need a Nested Table type to be created in the database:

create or replace type empnos_tt is table of number(4) 
/

Then we create a new package to hold the policy function.

create or replace package emp_vpd_pp as
  function sel( owner_in   in varchar2
              , objname_in in varchar2
              ) return varchar2;
  function read_g_empnos return empnos_tt;
end emp_vpd_pp;
/ 
sho err

The function SEL will be used in the policy. The function READ_G_EMPNOS is needed to retrieve the data in the package variable. Then the actual implementation of the package:

create or replace package body emp_vpd_pp as
  g_empnos empnos_tt;
  beenhere boolean := false;
  function sel( owner_in   in varchar2
              , objname_in in varchar2
              ) return varchar2 is
  begin
    if not(beenhere) then
      if user = 'VPD1' then
        begin
          select emp.empno
            bulk collect into g_empnos
            from emp
           where emp.deptno = 10;
         exception
           when others then
           dbms_output.put_line(sqlerrm);
         end;
      elsif user = 'VPD2' then
        begin
          select emp.empno
            bulk collect into g_empnos
            from emp
           where emp.deptno = 20;
         exception
           when others then
           dbms_output.put_line(sqlerrm);
         end;
      end if;
    end if;
    beenhere := not(beenhere);
    if ((user = 'VPD1') or (user = 'VPD2')) then
      return 'emp_vpd.empno in (select column_value
                                  from table(emp_vpd_pp.read_g_empnos))';
    else
      return '1=1';
    end if;
  end sel;
  function read_g_empnos return empnos_tt
    is
    begin
      return (g_empnos);
    end;
begin
  beenhere := false; 
end emp_vpd_pp;
/
sho err

In the initialization section of the package we initialize the Boolean variable. Then, when the policy function is executed for the first time (per query) we select the column values we need and save that into the package variable. The second time we execute the policy function we use the values saved in the predicate that is being added.

begin
  sys.dbms_rls.add_policy(object_schema => 'DEMO'
                         ,object_name => 'EMP_VPD'
                         ,policy_name => 'EMP_VPD_SEL'
                         ,function_schema => 'DEMO'
                         ,policy_function => 'EMP_VPD_PP.SEL'
                         ,statement_types => 'SELECT');
end;
/

Notice the predicate with the use of the Nested Table is executed always, but the Nested Table is only filled up in the first execution of the policy function. Using this technique the database only has to execute the expensive query once and its result can be used multiple times at almost no cost.
Using this policy function has exactly the same result, but the execution improved dramatically. Using this technique the database only has to execute the expensive query once per query instead of for every row.

This post is also available at the AMIS blog

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.