SQL today is the foundation for any successful application. Whether it is a SOA implementation, an ADF or APEX application, a Forms application or any other usage of the Oracle Database (or any other relational database), SQL is key for the success. AMIS has embraced the evolution of SQL through the subsequent releases of the Oracle Database - trying to stay abreast of the latest features and adopting new functionality whenever it made pragmatic sense. In 2004 we developed the 7Up training - that introduced Oracle developers who started their Oracle careers with Oracle7 to the wonders of Oracle 9i. Through the years, this training evolved into a 7-11 training and we kept on presenting, demonstrating and writing about new ways of using SQL - through gems such as inline views and subquery factoring, analytic functions, the model clause, aggregations, multi-table DML, Flashback, intervals and many more. We must be one of the few companies in the world able to demonstrate all of the above using the DEPT and EMP tables in the SCOTT schema.

In the month of April, we make another community contribution in the area of Oracle SQL: AMIS is the proud sponsor of the SQL Challenge - the world wide quiz for Oracle SQL developers that publishes weekly challenges, accessible via http://www.plsqlchallenge.com. Various SQL-smiths at AMIS gathered to produce five challenges to be published in five consecutive weeks. Challenges ranging from beginner level questions through the advanced level questions. Demonstrating the power of SQL and the fun of playing with it. Hopefully you find a mix of both easy and mind boggling questions. Maybe there are some things you had never thought of or even heard of and can be really useful in your day-to-day work.

AMIS provides prizes for contestants during AMIS April at the SQL Challenge. We will be selecting 2 winners from the people who have the highest score for all the quizzes. Besides that we will also select one random winner from all the people who played at least one of the five quizzes. Prizes include your choice of a SQL related eBook and for the first place winner also a SQL related book.

You can expect new quizzes every Saturday for the coming 5 weeks starting March 31st.

I will be presenting at the OGh Apex Event in Zeist (the Netherlands). I will be doing a presentation on how to build a plug-in for Apex, together with Alex Nuijten.

AMIS will be sponsoring the SQL Challenge in April. I have made a couple of questions for that. Lucas Jellema also made a couple of questions and we are now in the process of reviewing and approving them.

I will be doing two presentations at KScope12.

Should Invoker Rights Be Used?

When: Monday June 25, Session 3, 11:30 am - 12:30 pm

How can you ensure users use only their data and not someone else’s? How can you do this with minimal effort? How can you get rid of multiple codebases. How can you (partially) protect yourself against SQL Injection? Explore these questions and more in this informative session.

And

Can Collections Speed Up Your PL/SQL?

When: Wednesday June 27, Session 12, 8:30 am - 9:30 am

Collections (array-like structures in PL/SQL) are used in two of the most important performance features of PL/SQL: BULK COLLECT for data retrieval and FORALL for data modification. This session will cover these topics and more.

And, as a cherry on the cake, I received my ACE Award yesterday

Even though this is a pretty bad thing for the conference, since a lot of ACED speakers are now cancelling their sessions, it might also be a chance for you to have your ‘hour of fame’. It opens a couple of slots to be filled by other people.

Even though some of the best technologists in the world will not be appearing (you don’t get the ACED title easily) I hope (and think) this conference will be one of the best in the world.

This script was built for usage in demos and to check if different implementations differ in speed. Therefore the timer is just written to output using a procedure. I need a function to return the value of the timer so I can use the resulting value in a log message.

I decided to take this package as a starting point and adjust it to fit my needs. That’s why I added a function to retrieve the value which would otherwise be sent to the DBMS_OUTPUT buffer. It was actually just a copy of the show procedure with just a few adjustments.

If, like me, you want to achieve SPOD (Single Point Of Definition), then just copying bits of code just doesn’t feel right. Just looking at the code revealed that I declared the same type twice. This should be done in the declaration section of the package, so I could use it in multiple programs. I also noticed that retrieving the current timing value was done twice in exactly the same manner. This also calls for some refactoring. So I created a function that returns the timing values in a record. Hey, I can use the type I just declared globally to the package. Another great advantage of refactoring.

After having created the function to retrieve the timing value I started using it. While using it, I found out that I actually wanted to run multiple timers at the same time. The current package only provides a single timer so I decided to add support for multiple timers. Doing so seems easy enough by using an Associative Array. But index it by a numeric value doesn’t seem like a good idea. Since Oracle 9i Release 2 we can also index by VARCHAR2 which is probably a better idea than using a numeric index. Chances of using the same name for concurrently running timers are smaller than the chance of using the same number twice.

I don’t think the code is completely done and there are certainly some pieces that can use improvement, but it works for me. If you come up with an improved version, don’t hesitate to let me (and Adrian Billington) know.

The code for the (modified) timer package can be found here.

The original code is as follows:

   1:  CREATE OR REPLACE FUNCTION is_numeric(parameter_ IN VARCHAR2) RETURN NUMBER IS

   2:   

   3:    char_         VARCHAR2(1);

   4:    char_counter_ INTEGER;

   5:    is_numeric_   NUMBER;

   6:   

   7:  BEGIN

   8:   

   9:    is_numeric_   := 1;

  10:    char_counter_ := 0;

  11:    WHILE (char_counter_ <= length(parameter_)) LOOP

  12:      char_counter_ := char_counter_ + 1;

  13:      char_         := substr(parameter_, char_counter_, 1);

  14:      IF (instr('0123456789', char_, 1) = 0) THEN

  15:        is_numeric_ := 0;

  16:        EXIT;

  17:      END IF;

  18:    END LOOP;

  19:   

  20:    RETURN(is_numeric_);

  21:  END is_numeric;

What this code does is check every character to see if it’s a numeric character. What happens if you send in a negative number? Or if you prefix a positive number with a plus sign (+)? The current code will flag this as NOT numeric while it actually is.

I figured I should use what is there and let Oracle do the heavy lifting. Rely on what the guys in the PL/SQL team have come up with.

My code looks like this:

   1:  CREATE OR REPLACE FUNCTION is_numeric_pba(parameter_in IN VARCHAR2) RETURN NUMBER IS

   2:    l_is_numeric NUMBER;

   3:    l_number     NUMBER;

   4:  BEGIN

   5:    BEGIN

   6:      -- try to convert the parameter to a number

   7:      l_number := to_number(parameter_in);

   8:      -- if it works then it's a number

   9:      l_is_numeric := 1;

  10:    EXCEPTION

  11:      WHEN OTHERS THEN

  12:        -- if it fails then it's not a number

  13:        l_is_numeric := 0;

  14:    END;

  15:    RETURN(l_is_numeric);

  16:  END is_numeric_pba;

This code lets Oracle try to convert the characters into a number. If it succeeds in doing so, it’s a number. If it fails, then an exception will be thrown so it is not a number. This code also works for negative numbers and signed numbers. Heck, it even works for calculations.

It is even faster than the original.

The test code:

   1:  clear screen

   2:  set serveroutput on size 9000000

   3:  set timing on

   4:  declare

   5:    l number;

   6:  begin

   7:    for i in 1 .. 100000 loop

   8:      l := (is_numeric(i));

   9:    end loop;

  10:  end;

  11:  /

  12:  declare

  13:    l number;

  14:  begin

  15:    for i in 1 .. 100000 loop

  16:      l := (is_numeric_pba(i));

  17:    end loop;

  18:  end;

  19:  /

  20:   

Some results:

   1:  Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

   2:  Connected

   3:   

   4:  PL/SQL procedure successfully completed

   5:   

   6:  Executed in 0.375 seconds

   7:   

   8:  PL/SQL procedure successfully completed

   9:   

  10:  Executed in 0.094 seconds

(I had to run it a lot of times to see any difference ;-))

Seems that Oracle internally does a better and faster job than you can with your own code :-).

Subquery on multiple columns

I needed to query a table but the records needed to be available in a subquery. Normally I would write a statement like this:

SELECT a.*
  FROM a
 WHERE a.field in (SELECT b.field
                     FROM b
                    WHERE ...)

But in this case I needed to check 2 columns for equality. I thought, hey, no problem, I’ll just rewrite the query to use an EXISTS instead of an IN:

SELECT a.*
  FROM a
 WHERE EXISTS (SELECT 1
                 FROM b
                WHERE b.field1 = a.field1
                  AND b.field2 = a.field2)

That was a nice idea but when I ran this query, it didn’t return any results before I decided to kill the query (after 15 minutes).

There must be a way to query a subset and compare it to multiple columns. After a quick search on the internet I learned that is was possible to use the IN set operator on multiple columns.

SELECT a.*
  FROM a
 WHERE (a.field1, a.field2) IN (SELECT b.field1, b.field2
                                  FROM b)

This query returned results within seconds.

CASE as parameter

I have a table in which I need to replace numeric values with their successor. The field is filled like this: xxxTry where it should become xxxTryy incrementing y by 1. Ok, this may look complicated, maybe an example will explain it:

010Tr7 must become 010Tr08

010Tr8 must become 010Tr09

010Tr9 must become 010Tr10.

First I thought to nest a lot of replace statements like this:

SELECT DISTINCT REPLACE(
                  REPLACE(
                    REPLACE(
                      REPLACE(
                        REPLACE(
                          REPLACE(
                            REPLACE(
                              t.column
                            ,'Tr9','Tr10')
                          ,'Tr8','Tr09')
                        ,'Tr7','Tr08')
                      ,'Tr6','Tr07')
                    ,'Tr5','Tr06')
                  ,'Tr4','Tr05')
                ,'Tr3','Tr04')
                ...

This looked ugly (although it does exactly what was needed). So I tried to use the CASE EXPRESSION on the query like this:

REPLACE(t.column
       ,CASE
          WHEN t.column LIKE '%Tr9' THEN 'Tr9'
          WHEN t.column LIKE '%Tr8' THEN 'Tr8'
          WHEN t.column LIKE '%Tr7' THEN 'Tr7'
          WHEN t.column LIKE '%Tr6' THEN 'Tr6'
          WHEN t.column LIKE '%Tr5' THEN 'Tr5'
          WHEN t.column LIKE '%Tr4' THEN 'Tr4'
          WHEN t.column LIKE '%Tr3' THEN 'Tr3'
          WHEN t.column LIKE '%Tr2' THEN 'Tr2'
          WHEN t.column LIKE '%Tr1' THEN 'Tr1'
        END
       ,CASE
          WHEN t.column LIKE '%Tr9' THEN 'Tr10'
          WHEN t.column LIKE '%Tr8' THEN 'Tr09'
          WHEN t.column LIKE '%Tr7' THEN 'Tr08'
          WHEN t.column LIKE '%Tr6' THEN 'Tr07'
          WHEN t.column LIKE '%Tr5' THEN 'Tr06'
          WHEN t.column LIKE '%Tr4' THEN 'Tr05'
          WHEN t.column LIKE '%Tr3' THEN 'Tr04'
          WHEN t.column LIKE '%Tr2' THEN 'Tr03'
          WHEN t.column LIKE '%Tr1' THEN 'Tr02'
        END)

This is much more verbose and much more work to type it all in, but I think it’s nice to see that CASE EXPRESSIONS can be used as parameters in other functions. Of course, you wouldn’t expect differently, but I nice to see it works.

By the way, the shortest query for this issue I came up with is a totally different approach:

substr(t.naam,1,5) || trim(both from to_char(to_number(substr(t.naam,6))+1,'09'))

(Note the trim function needed in the query. Check out Alex Nuijtens blog on why this is).

Just a couple of things I learned lately.

Table functions are functions that produce a collection or rows (either a nested table or a varray) that can be queried like a physical database table. You use a table function like the name of a database table, in the FROM clause of a query. The difference with a physical database table in the FROM clause of the query is that you need to use the TABLE() operator to tell the SQL engine that it should treat whatever comes out of the function as if it were a normal table.

Table function concepts

There a couple of steps to take when you are working with table functions. Like when you are working with normal tables you have to describe the way the records of the collection are set up.

Define Record Type

First of all you define a record type. This is pretty much the same records are defined in a database table, only difference is that you are now defining an object type instead of a table.

CREATE TYPE script_line AS OBJECT
  (line NUMBER
  ,text VARCHAR2(32767))
/

This creates a type that can hold the contents of a single record. Compare this to a record in a physical table.

Define Nested Table type

After the creation of the record type you define a Nested Table type using the previously created Record Type. You can also create a Varray type if you are absolutely certain that you will not exceed the boundary of the varray.

CREATE or replace TYPE script_lines AS
 TABLE OF script_line;
/

Define function returning Nested Table type

After creating the types needed for our function we can define and implement the function in which we will do what we want it to do.

CREATE OR REPLACE FUNCTION createinsertscriptfor(
  tablename_in IN VARCHAR2
) RETURN script_lines

The complete implementation of the function can be found here.

This function can encapsulate our knowledge of the datadictionary and the way an insert statement should be constructed. All the things we tend to forget all the time, like the quotes that are needed when working with varchar2 fields. Or doubling the quotes when they are present in the data.

The function will return a nested table which is created and initialized in the declaration section of the function. For every record in the table there will be a row in the nested table.

Query function using the TABLE() operator

After having defined the types and the function we can query the function in SQL as if it were a physical table. Only thing we have to tell the SQL engine is to treat the result of the function as a fysical table, using the TABLE() operator, hence the name TABLE FUNCTION.

Nesting functions

Function can take the result of a query as input for (one of) their parameters. If we build multiple functions where one function can use the output of the other we can work with the results of the previous function without having to copy the entire collection from one function to another. When the functions are pipelined as well then there is a possibility to parallellize the execution of the functions.

Pipelined table functions

Functions can be pipelined. This means the results will become available when they are produced. As soon as a result is available instead of adding it to the nested table it will be piped out of the function.

CREATE OR REPLACE FUNCTION createinsertscriptfor(
  tablename_in IN VARCHAR2
) RETURN script_lines PIPELINED

The keyword PIPELINED is added to the function. Instead of extending the nested table and adding the result to it, it will be piped out of the function using PIPE ROW. When we are done with the code in the function control can be returned using the RETURN keyword. There are some strange things here. The function is said to return a NESTED TABLE but in fact in returns single records, one at a time. If you consider the output of the entire function then you can see that a table of records is being returned. Therefore the function should be defined as if it returns a NESTED TABLE. When the function is at its end there is nothing left to return but control. That is why we have a RETURN without any value.

If you query the function in SQL*Plus you will not see any difference in behavior. That is because SQL*Plus will retrieve the results and start displaying them when it has something like 15 results.

SQL> show arraysize

arraysize 15

When the arraysize is set to 1 then the results will be displayed one at a time as soon as they become available.

SQL> set arraysize 1

Parallel table functions

If your database permits it, that is when the database is setup to support it, then code can be executed in parallel. If the table is enabled for PARALLEL processing and the function is also parallel enabled then the function can be executed in parallel.

Normal execution is like this:

The data travels from function to function all being processed in memory. If the functions and the table were parallel enabled then execution would be something like this:

In both approaches every row of data will be kept in memory until the function is done with it. Enabling parallel execution can speed up processing even on a laptop computer with a single processor but with multiple coress. If we add the pipelined option to the function then the stress on memory would be less, since the next function can start when the previous is done with a row. In theory there will not be more rows kept in memory than the ones being processed at the moment.

To enable a function for parallel execution all you have to do is add the PARALLEL_ENABLE clause to the functions signature:

{[ORDER | CLUSTER] BY column_list}
PARALLEL_ENABLE ({PARTITION p BY
[ANY | (HASH | RANGE)
  column_list]} )

If you want to control how the data is parallelized then you should define one of the parameters of the function as a strongly typed ref cursor and use this parameter in the PARTITION p BY clause along with the type of partitioning (HASH or RANGE) and the columns you want the partitioning to be done by. If you are partitioning by ANY then you can also use a weak ref cursor. Partitioning is dividing the data being processed. If you don’t tell Oracle how the data should be partitioned, then it divides it evenly over all the parallel processes available. If you provide the HASH or RANGE parameter you have more control over what rows will be processed by which parallel process.

Build a table function to export data

Using the knowledge we have about the datadictionary and our knowledge about table functions makes it possible to write code that creates a script to copy data from one scheme to another, for instance to copy settings from the development environment to the testing environment. Of course this script is something we could write during development, but experience teaches that we will always forget at least one row of data. If the table has a limited number of columns it’s really easy to create a SQL statement that would give us the script we want.

Let’s use the allknown DEPT table as an example. Suppose we want to create a script like this:

insert into DEPT( LOC, DNAME, DEPTNO)
          values ( 'NEW YORK', 'ACCOUNTING', 10);
insert into DEPT( LOC, DNAME, DEPTNO)
          values ( 'DALLAS', 'RESEARCH', 20);
insert into DEPT( LOC, DNAME, DEPTNO)
          values ( 'CHICAGO', 'SALES', 30);
insert into DEPT( LOC, DNAME, DEPTNO)
         values ( 'BOSTON', 'OPERATIONS', 40);

It is rather simple to create a SQL script that creates this script for us that will deliver us with exactly this script.

SELECT 'insert into DEPT( LOC, DNAME, DEPTNO) values ( '''||
                          LOC||''', '''||
                          DNAME||''', '||
                          DEPTNO||');' line
  FROM DEPT;

This is really easy for simple tables with a few columns although you still have to remember to double the quotes in your query and what happens when a varchar2 field has a quote in it.

It would be so much easier to hide all this complexity behind a PL/SQL interface. And with table functtions we can.

I have created a function that does just this. It is by far not complete and it can probably be improved a lot but it’s a nice thing to play with tablefunctions. You can download the code from this location[download link] but I will highlight some pieces of the code

As described earlier I create a record type to hold a line for the insert script and a nested table to hold all the lines. The signature of the function is also given earlier.

The code first gets all the columns for the table from the datadictionary. With this information the first part of the statement is built. Then all the rows in the table are being processed. Depending on their datatype they will be handled differently. A varchar column for instance will have some extra processing to make sure quotes are put in the script correctly. When a row is processed, an SQL statement is ready and can be added to the nested table. When every row is processed the entire table is returned.

Available tools

Ofcourse there are many tools available that can do pretty much the same thing. But what if you want to create a script which you can include in your deploy script so the DBA can run the install from SQL*Plus. There is for instance Data Compare for Oracle by Red Gate. To create a script that can be included in your deployment script it is easiest to create a dummy schema with the same tables you want to create a script for, but with no data in them. Then let the tool do it’s magic and check the script which can be saved and included in the overall script.

The code generated by this tool is like this:

DECLARE
 null_value CHAR(1) := NULL;
 statement1 CHAR(46);
BEGIN
 statement1 := 'INSERT INTO "EMPTY"."DEPT" VALUES (:0, :1, :2)';
 EXECUTE IMMEDIATE statement1 USING 10, 'ACCOUNTING', 'NEW YORK';
 EXECUTE IMMEDIATE statement1 USING 20, 'RESEARCH', 'DALLAS';
 EXECUTE IMMEDIATE statement1 USING 30, 'SALES', 'CHICAGO';
 EXECUTE IMMEDIATE statement1 USING 40, 'OPERATIONS', 'BOSTON';
END;
/
COMMIT;

This is a completely different approach to the scipt using tablefunctions, but in the end the result is the same. Creating the script ourselves using a PL/SQL function is in my opinion more fun, but creating the script using the tool is probably faster. You can download a fully functional trial of Data Compare for Oracle from the Red Gate website so you may as well give it a go and a generate some scripts for free this way to see for yourself.

Of course, we are all (or almost all) acquainted with DBMS_JOB to schedule jobs in the Oracle database it had been replaced in Oracle 10.2 with the Oracle Scheduler. You can use the scheduler to schedule jobs (hey, that’s pretty much what the name says), but you can do much more than that. The easiest way to schedule a job is create it and have it run at certain intervals, lets say every day or every week.

When you want to run you job every first day of the month, that’s harder and if you want to run it every last day of the month, that’s even harder. Using the Oracle Scheduler you can create rather complex schemes to run the job. The calendering syntax results in a set of timestamps when the job should be run. Eddie had a lot of examples in his  presentation ranging from rather simple ones like:

Run every hour

 FREQ=HOURLY;INTERVAL=1;

or

Run every 5 minutes:

 FREQ=MINUTELY;INTERVAL=5;

to rather complex ones like:

Run at 10 pm daily from Monday to Friday

 FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=22; BYMINUTE=0; BYSECOND=0;

to very complex ones like:

Run on the 60th, 120th and 180th day of the year

FREQ=YEARLY; BYYEARDAY=60,120,180

Run on the last workday of every month, assuming that workday are Monday through Friday

 FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1

Building a schedule using these intervals is very powerful, but if you want multiple jobs to use the same schedule it can be a very tedious job to write the intervals every time. And what happens when the schedule changes? You need to change a lot of jobs. Not very Single Point Of Definition.

Luckily Oracle provides us with the possibility to create schedules which can then in turn be used in the scheduled jobs. That way all the jobs that use the same schedule will be changed by changing the schedule once instead of changing all the jobs individually.

Another very powerful tool in the scheduler is the resource manager. Using this tool you can assign resources to certain jobs based on a schedule you created. For instance, you want an update job to run every hour, but during office hours you want to have it run with the lowest priority. Outside office hours I can consume as many resources as it needs.

This would involve creating a schedule in which the office hours are set. Then we would create a resource plan where we can limit the resources for a certain job class. Then, after this, we can create jobs, associated with job classes and have then use less resources during office hours, i.e. when the resource plan is active. Note: resources will only be limited when usage reaches 100%.

Another powerful feature is the possibility to implement a file watcher using the scheduler. This way you can start jobs when a file arrives on a system. This is a feature which is introduced in the version 11g Release 2. This is an example of an event based job. Other events include for instance:

Using these event you can for instance run a job to send out an email or a text message to a DBA that something is wrong and have him/her take appropriate action.

You can view Eddies entire presentation at:

http://www.scribd.com/doc/60199706/Five-Features-You-Ought-to-Know-About-the-Oracle-Scheduler-PPT

You can also read the article he wrote to accompany the presentation at:

http://www.scribd.com/doc/59265286/Five-Features-You-Ought-to-Know-About-the-Oracle-Scheduler

The book starts with a high-level overview of jQuery Mobile. By following the examples in the first chapter, you will have built your first jQuery Mobile application when you are done with it. Luckily the example code can be downloaded so you don’t have to type all that ;-). Then paging and navigation in jQuery Mobile is covered and after that the UI elements (toolbars, buttons, form elements etc.) are covered. By the time you are done reading chapter 3, which is pretty fast, you can create you own mobile application.

The next part of the book covers the new theme framework. How to use it and of course how to customize it. I have learned that application look and feel requires a whole different level of expertise and most of the time I just stick with the themes provided, but if you feel comfortable enough, don’t hesitate to have a go at customizing your mobile app. After this there is a lot of info on the new events jQuery Mobile creates (Tap, pinch zoom, swiping etc.) , what methods it exposes and how you can customize jQuery Mobile for your own applications.

The final chapter shows how to build an actual mobile application. A tweeting client, built from scratch.

The book assumes you are already familiar with the jQuery Javascript library. If you are not, then I suggest reading "jQuery Pocket Reference" by David Flanagan, also available from O’Reilly. It also assumes familiarity with HTML markup (”Head First HTML with CSS & XHTML” by Elisabeth Robson, Eric T Freeman and CSS 3 (”The Book of CSS3” by Peter Gasston).

Some basic familiarity with mobile web browsers is also assumed. As it says in the book: Though jQuery Mobile aims to provide a cross-platform API, it is still necessary for a mobile web developer to understand mobile browsers and their capabilities.

APEX 4.1 was originally supposed to include the jQuery Mobile framework, but since it is still in the beta phase when APEX is supposed to be released they choose not to include it. Ofcourse you can include the beta code yourself in your current APEX templates, but you should be aware of the fact that you can run into some issues and you shouldn’t build production applications with it. But still, you can learn a lot by playing around with the framework. And this book can definately help you gain more insight on how this framework works and how it can be used.