Category: SQL

February 29th, 2016 by Mathias

Reports of this blogs death have been greatly exaggerated. It has been very quiet here though while I worked on getting the Swedish part of Miracle started. It is now rocking the Stockholm market so it’s time to get back into more geeky stuff.

Talking of which. I have encountered Liquibase for database versioning time after time and always come to the conclusion that it is not what a DBA want to use. I recently took it out for a spin to prove once and for all that it is not capable of what real DBAs need.

Well, lets just say that it did not end like I expected. The phrase, an old dog learns a new trick comes to mind. Once I got over the idea that it is the DDL I have to protect and realised that it is rather control over how changes occur I need. In fact I get all the control I need and a lot of problems with being able to back out changes and to adopt for different kinds of environments and/or database types are easily handled.

Do take it out for a spin, you’ll like it.

If you read Swedish, I wrote a document with a full demo of lots of the features. You find it here. It is a long document, partly because it has a long appendix with a changelog and partly because I step through each change (of 37) and explain them.

I also show how APEX applications can be installed with Liquibase. This is often said to not work, you have to do it by hand with APEX. Well, not only is it possible – it is easy.

I’d translate the demo and the document to English if that would be useful to many people. Let me know if this sounds like a document you’d like to see in English.

Posted in DBA, SQL, Versioning

March 15th, 2013 by Mathias

In this post I’ll finish up the CRUD implementation using records, procedures and views. This series of blog posts started with this post which was followed by this.

At this point we have a working report that links to a form. The report is based on a view and the form is based on a procedure. At this point the form is only loading the record in using a procedure that uses a record in its signature. In this post we’ll complete the functionality by using the same form for insert, update, and delete functionality.

Let’s start with adding a mode page item to the form. We will use this to know if the form is invoked in insert or in update/delete mode.

  • Right-click on “Form on Stored Procedure” on the page where your form is (page 4 in my example)
  • Select “Create Page Item”
  • Select Hidden
  • Give the page item a name, like P4_MODE.
  • Click “Next”
  • Click “Next” (again)
  • Select “Create Item”

You now have a new page item which is not displayed, but whose value you can reference on this page.

To make this example easy, we’ll start with just implementing update functionality. But first we’ll go to the report and change this link column to pass in a “U” for the mode page item when it is clicked.

In the page with the report, perform the following steps.

  • Right-click “List of Employees”
  • Click “Edit Report Attributes”
  • Scroll down to “Link Column”
  • For Item 2, enter the name and value we want the newly created page item to be set to. P4_MODE and U in my case.
  • Apply Changes

Now with that done, let’s head back to the page with the form. But before we make any changes there, we need to change the package to add a procedure for updating a row in the emp table.

Here is the code for the procedure we’ll use.

create or replace package tb_access as
  type t_emp_rec is record (empno emp.empno%TYPE
                           ,ename emp.ename%TYPE);

  procedure read_emp(p_in_empno     in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec);

  procedure write_emp(p_in_empno    in emp.empno%type
                     ,p_in_emp_rec  in t_emp_rec);
end tb_access;
create or replace package body tb_access as
  procedure read_emp(p_in_empno in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec) is
      select empno
        into p_out_emp_rec.empno
        from emp
       where empno = p_in_empno;
  end read_emp;
  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_emp_rec in t_emp_rec) is
      update emp
         set ename = p_in_emp_rec.ename
       where empno = p_in_empno;
  end write_emp;
end tb_access;

This code adds the procedure write_emp wich also takes empno and the record used for read_emp, only this time they are both in parameters as we’ll not return any data from the procedure that writes to the table. The actual code in the procedure is a very simple update that sets the name to the ename in the record for the row that has the empno passed in.

With this in place we’re ready to change the form to call this procedure upon submit. We do this by taking the code used for reading in the record (The process in page rendering) and put it into the “Run Stored Procedure” process we commented out in page processing.

The code we copy looks like this:

  in_emp_rec tb_access.t_emp_rec;

  tb_access.read_emp(p_in_empno    => :p4_empno
                    ,p_out_emp_rec => in_emp_rec);

  :p4_ename := in_emp_rec.ename;

Replace the code in “Run Stored Procedure” with the above and change it to look like this:

  in_emp_rec tb_access.t_emp_rec;

  in_emp_rec.ename := :p4_ename;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_out_emp_rec => in_emp_rec);

In the “Run Stored Procedure” change the condition to “- Select condition type -” to not have a condition for when the process is executed. It is currently conditioned on running only when the save button is clicked, leave that condition on for now.

With that in place, it is time to test the functionality of the forms new functionality. Run the report, click on the link and update the name of an employee. Assuming you followed the above and got the changes made in the right places, the form will now update the name of the employee.

The next step in this will be to add insert functionality to the form. First off is to set up the package to support inserting an employee. As we’re now going to have both insert and update through the same procedure, we need to include the mode in the signature of the procedure.

Let’s first update the package specification so the write_emp looks like this:

  procedure write_emp(p_in_empno    in emp.empno%type
                     ,p_in_mode     in varchar2
                     ,p_in_emp_rec  in t_emp_rec);

The procedure in the package needs to be updated to look like this:

  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_mode    in varchar2
                     ,p_in_emp_rec in t_emp_rec) is
      case p_in_mode
        when 'U' then
          update emp
             set ename = p_in_emp_rec.ename
           where empno = p_in_empno;
        when 'I' then
          insert into emp
           values((select max(empno) + 1 from emp) 
      end case;
  end write_emp;

Before the form is functional again, we need to change the call in “Run Stored Procedure” to match the new signature of write_emp.

  in_emp_rec tb_access.t_emp_rec;

  in_emp_rec.ename := :p4_ename;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_in_mode     => :p4_mode
                     ,p_in_emp_rec  => in_emp_rec);

With that change in place the form now works and the mode we set on the link column is now passed to the procedure to make sure an update is performed and not an insert.

To introduce insert functionality, we want to add a new button on the page with the report that invokes the form in insert mode. To add the button follow these steps on the page with the report.

  • Right-click on “List of Employees”
  • Select “Create Region Button”
  • Set name and label to “Add”
  • Click Next
  • Set position to “Right of Interactive Report Search Bar”
  • Set Alignment to “Left”
  • Click Next.
  • Select action “Redirect to Page in this Application”
  • Set the page to your form-page (4 in my example)
  • Set Request to INSERT
  • Set “Clear Cache” to 4
  • Set “Set these items” to P4_MODE
  • Set “With these values” to I.
  • Click Next.
  • Click “Create Button”

If you test the page, you’ll see that it ends up immediately to the right of the search bar. Do not click on the button just yet.

Now go to the page with the form and open the process in rendering (Read Emp).

  • Set “Condition Type” to “Value of item / column in expression 1 = expression 2”.
  • Set Expression 1 to P4_MODE.
  • Set Expression 2 to U
  • Apply Changes

This ensures that we’re only reading a row from emp when updating data. When inserting, there is no data to be read and displayed.

You may also want to set the same condition on the page item for empno to hide it when inserting as there is no empno to display then.

Test the function now by running the report page and click on the “Add” button. It adds a row with the name you enter on the form. The rest of the columns will have null as their value. Adding more columns in the procedures and on the form is an exercise left to the reader.

With the function now supporting insert and update, it is time to add the delete function so we can get rid of some of the test employees we’ve created. This function will be implemented only on the form and by adding supporting code in the package. Thus, the delete will be performed by clicking on the link and then clicking on a delete button on the form.

To implement this we’ll update the implementation of the write_emp procedure once more.

  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_mode    in varchar2
                     ,p_in_emp_rec in t_emp_rec) is
      case p_in_mode
        when 'I' then
          insert into emp
           values((select max(empno) + 1 from emp) 
        when 'U' then
          update emp
             set ename = p_in_emp_rec.ename
           where empno = p_in_empno;
        when 'D' then
          delete from emp
           where empno = p_in_empno;
      end case;
  end write_emp;

With the package updated, our next step is to change the code in the “Run Stored Procedure” process on the form page.

  in_emp_rec tb_access.t_emp_rec;
  v_mode     varchar2(1);
  in_emp_rec.ename := :p4_ename;
  v_mode           := :p4_mode;

  if :REQUEST = 'DELETE' then
    v_mode := 'D';
  end if;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_in_mode     => v_mode
                     ,p_in_emp_rec  => in_emp_rec);

The code is now changed such that a click on the delete button (not implemented yet) will set the mode to D. This is done by checking the REQUEST which we will set to DELETE for the button we’ll create now. REQUEST is a standard attribute available on buttons and branches in APEX. By setting it to a unique value, we can check for different invocation methods in code. This allows us to deal with different scenarios with just one block of code.

To add the button, follow these steps.

  • On the form-page, right-click on “Form on Stored Procedure”.
  • Select “Create Region Button”
  • Set “Button Name” and “Label” to “Delete”.
  • Click Next
  • Set “Position” to “Top of Region”
  • Set Alignment to “Left”
  • Click Next
  • Click Next (again)
  • Click Create Button

The value of “Button Name” is used to set REQUEST when the page is submitted. With this in place our form should now support deleting rows also. Update the branch on the page to not have a condition on the button pressed. Take it out for a spin.

There is one thing left to do, hide the delete button when the form is invoked to add a row.

  • Right-click the delete-button under rendering.
  • Select “Edit”.
  • Scroll down to conditions.
  • Set “Condition Type” to “Value of item / column in expression 1 = expression 2”.
  • Set Expression 1 to P4_MODE.
  • Set Expression 2 to U
  • Apply Changes

Now you’ve got a CRUD solution based on an interactive report with a form that supports insert, Update, and Delete. The total amount of code required is very small and you still have isolated the APEX-application from changes to the table structure by using a view, even the record can have fields added to it without impacting the functionality of your application. Developing using the demonstrated methods here allows you to create these applications almost as fast as with making them table based, but you have much more control and can make a lot of changes without touching the application,

Posted in APEX, Oracle, PL/SQL, SQL

April 13th, 2011 by Mathias

Quite a while ago I wrote about how to seup the alert log as an external table. Since then 11g has been introduced and is now widely used. It of couse changes the location and makes the alert log an xml file.

While it is possible to select from it using xml functions like Laurent Schneider does here, it is still a bit cumbersome.

Tanel Poder (@TanelPoder) found a nicer way by using X$DBGALERTEXT which does a really nice job of parsing the xml-file into a lot of different columns. A friend at work, Daniel Ekberg, let me know about it a while back. I just never got around to looking into it until today.

It is very nice but there is one slight issue with it. It is X$ and thereby only available to SYSDBA. That can of course be solved with a view and proper grants and synonyms. However, views on X$ can cause some issues during upgrades so such views should probably be dropped before upgrading.

While not documented, this is a neat way to gain access to a lot of information from the xml.log (alert-log).

Posted in Alert Log, DBA, Oracle, SQL, XML

October 3rd, 2010 by Mathias

Yes, this post is a little out of order as it clearly didn't take place after the Thursday afternoon sessions. I missed it during the conference so I had to catch up on it later on the on demand site. I thought it was interesting enough to write up a few notes.

It was held by Tom Kyte and the subject was "What's new in database development". It'd be more correct to say Oracle development than database development as he covered just about any area that Oracle focuses on.

He started with APEX and spent a lot of time on it. The coverage APEX gets in general sessions and the number of really good APEX sessions speaks volumes about how strong APEX is becoming within the Oracle development technologies.

Tom shared a little background of how APEX was built by Mike Hitchwa when Mike set next to Tom and wrote the initial code for APEX. Anyone that has been impressed with the way bind variables are handled in APEX got their explanation for why. Tom has relentlessly been preaching about how to to it and the issues caused when not done well. So it all makes sense when you hear that it was Tom who wrote the initial code for bind variable handling in APEX.

Tom went on to say that they are seeing a lot of momentum behind APEX right now. The catch phrase he used to explain why it grows fast in popularity was "Easy architecture, easy to deploy, and easy to run".

Tom then spent some time talking about performance of APEX as a lot of people still thinks it is not able to handle enterprise level system demands.

The site that is used to run all the applications people create on Oracles hosted apex "test and demo" site has a surprisingly small computer running it. The specifications for it is:

  • Poweredge 1950
  • 2 Dual core Xeon 2.33 Ghz
  • 32 GB Ram
  • Cost : $4,300 (in 2007)
  • Runs 11G database

Not a very big computer to serve a site that provides support to anone who wants to try APEX.

Tom shared som of the numbers for what is used for.

  • They had 3.5 million views (pages loaded and processed) last week.
  • Distinct users accessing during the week: 3028
  • Total workspaces :9062
  • Applications: 32776

I'd think most "enterprise" systems would have less demand than that.

Some of the biggest things on are:

  • SQL Developer updates 800K hits /week
  • Pro MED (Prodution system) 770K hits / week
  • Asktom 250K-1000K hits / week
  • APEX Appbuilder 238K hits / week
  • JDeveloper updates 175K hits / week

Impressive to serve all of that from a small(ish) computer.

Tom then shared some key features from the 4.0 release.


Designed for the enduser and designed for webbased content sharing. A solution that allows endusers to stop mailing exceldocuments around would be a good thing…

Dynamic Actions

Declarative client-side behavior. It is integrated in the framework. Uses JQuery.

This allows using common javascript function for handling advanced user interface improvements. From validations to drag and drop and more.

Team Development

Allows you to manage the application development process within APEX. This makes APEX able to be used by large development projects. It has built in end-user feedback. When a user has a problem they can tell you directly in the application and it feeds into the team development features.

Oracle APEX Listener

Built in Java.

It is a good alternative to mod_plsql and it is certified against WLS and Glassfish.

After that long piece on APEX, Tom went through som improvements for other technologies.

SQL Developer

The datamodeller is now free. It will now come with the product and will be supported with the database (just like SQL Developer itself).

It has real-time SQL monitoring. It will allow you to see what part of the execution plan Oracle is currently spending time in for a SQL. This exists in Enterprise Manager, but this gives the insight to many more as EM often is just used by the DBAs.

It can now find the SQL trace file and display the contents graphically in SQL Developer. This ought to make trace files much more accessible to developers.

It has support for AWR and ASH reports and has added developer specefic things that are not available in the other AWR/ASH aware tools.

A hierarchical profiler is now also included to allow you to see what functions calls which in a run of PL*SQL code.

SQL Developer now used PL/SCOPE data to show information about where variables are defined and used, This is something that has been added to the database and can alays be accessed with SQL, but it is more convenient to have it hooked into the development tool.

They have also added a DBA Navigator that allows DBAs to access most things they deal with in the database. Most objects (such as extents and tablespaces) can be seen and modified.

An interface for DBMS_SCHEDULER has been added. It is supposedly a much nicer way to configure jobs than to do it by have, which can be both tedious and complicated.

Another neat feature is that auto tracing has been improved to not only allow tracing of a statement, you can now trace two statements and see a comparison report of the tracing so you can compare them. Since this usually has been done with Toms "Runstats" package, I'd assume this feature has Toms fingers all over it.


It is free and provides native access to the database. It has statement caching (on the client side) and will support TimesTen in

I'm not a .Net person at all , but it sounds as if this provides a lot of benefits both in use and in performance. If you work with .Net, then you will want to take a look at all the things Oracle provides in this area.


There is a Universal Connection Pool for all technologies using connection pools to use. This is supposed to reduce the overhead caused by having several connection pools.

For Secure Files there has apparently been a performance issue dealing with very large files, my understanding from Toms presentation is that it resulted in double buffering. Now there is Zero Copy which sounded as if it would remove all buffering and thereby improve the speed significantly.

Database Improvements

Tom focused on the optimizer and on developer related topics as this was a Develop keynote.

Tom showed how the optimizer could detect a three-way join that was not needed. It was a fairly complicated situation, but the optimizer can apparently completely skip accessing tables now if they are not needed for the end result.


Edition based redefinition is available in a editions of 11G. Tom thinks this is the killer feature of 11G, and says that it may be the first time the killer feature is available in all editions and with no additional licensing requirements.

It allows live changing of code (PL and views). Live changing of data can already be done with online redefinition and online actions on indexes.

The process now is:

  • Create new edition
  • Test new edition of the code while users still use the old.
  • Two options for rolling out
    • Let new sessions use the new one and allow old to keep using the old edition.
    • Switch everyone over to the new when the system is "idle". Takes just seconds.

That was the end of Toms presentation. He covered a lot of technologies, even some that are not mentioned above (like Ruby). WHat I think is interesting is what wasn't mentioned at all.

The fist one that comes to mind is JDeveloper. It has not become very popular with Java purists and it is not mentioned at all. There are some rumors of it being de-emphasized by Oracle as how you work with it seems to be very far from how most Java projects want to organize their builds and deployments.

Next interesting omission is the technology mostly integrated with JDeveloper. No mention at all on the Oracle Develop for their key Java development framework. ADF did not get any love at all and that is not true just for this presentations, there were very few mentions of it in any of the presentations I attended. True, I did not seek out ADF sessions, but virtually every single presentation on non APEX technologies found a way to talk about APEX, the oposite was true for ADF. Even when you felt that someone was building up to a plug for ADF, they never went there.

The lack of love for ADF and JDeveloper may mean that they are not considered critical to Oracles success anymore. I assume ADF was used to build the fusion applications which would mean that the tool and product is probably not going away, but could it mean that they are primarily being focused for large product development and not something that is good for custom development projects?

Personally it feels as if ADF is now essentially becoming a tool used when integrating with Oracle (fusion) Apps or Oracle SOA Suite. I'm not sure a custom development project would end up using ADF. Of course there will be exceptions to that, but they will probably be fewer and fewer.

It could also be that JDeveloper and ADF starts becoming less and less of something for the customers and more and more of an internal tool at Oracle. I'm sure they have very specialized versions and techniques used in development to help the development of the fusion applications.

On the other hand, it could just be that this wasn't the time for releasing improvements to JDeveloper or ADF. I have trouble convincing myself of that though. The reason is that Oracle now owns Java and if there ever would have nee a reason to show JDeveloper and ADF in its full glory, now would have been the time. By not releasing improvements to what is considered problems with JDeveloper and ADF, they have probably given more momentum to the competing tools. They of course ha another tool in this space now, maybe NetBeans will be the future migration path of JDeveloper and when ADF finds a new home.

Posted in APEX, DBA, OOW, Oracle, Performance, SQL

September 29th, 2010 by Mathias

The day starts with a presentation b6 Tom Kyte about "What else can you do with system and session data".

Tom starts with reviewing the history of tuning an Oracle database.

The prehistoric era (v5) required writeing debug code as that was the only way to get any information about what the code did.

The dark ages followed (v6) and now Oracle introduces:

  • Counters/ratios
  • bstat/estat
  • SQL Trace
  • The first few (7?) v$-views are introduced

In the rainessance era (v7) introduced a few more things:

  • The wait event instrumentation was introduced
  • Move from counters to timers
  • Statspack

The modern era (v10) introduced the tools we prefer today

  • DB-Time tuning
  • Multiple scoping levels (AWR)
  • Always on, non-intrusive
  • Bult into the infrastructure – instrumentation, ASH, AWR, ADDM, EM

DBA_HIST_* views is where the historic data is located.

Tom contunued with discussing the metrics and the timemodel and how to join that to get data about them. However, all his presentations are available for everyone and you're better off reading them on his site than getting a summary of it here.

As usual, Tom places his presentations and other things under files on asktom.

The next presentation was Apex debugging and tracing with Doug Gault from Sumneva.

Debug mode is truned off on the app level by default. It neds to be enabled there before it can be used.

Debug data is now written to a table, and not dumped on the webpage as before. It is viewed by clicking view debug in the developer bar in the APEX interface.

Use conditional display to output things only in debugmode (using v('DEBUG') ).

Things that happens on a page after it has been rendered will not be captured. This is things like javascript running on this client.

APEX_DEBUG_MESSAGE is currently undocumented, but the code shows how to use it.

Debugging can be turned on/off on the page programmatically.

Logging from you own code allows you to see what parts of a package call it is that takes the time.

Doug then shows a few different ways to collect and report on the debugdata for a report.

I recommend looking at the presentation for ideas on how to use it and what can be done. Unfortunately this presentation does not seem to be available on the sumneva website at this point. If you are interested in it, send an email to them asking if they are willing to share it on their presenation page. You'd of course want to check their site first to make sure it isn't uploaded between the time I write this and the time you read it.


Posted in APEX, OOW, Oracle, Performance, SQL

January 10th, 2010 by Mathias

I've just uploaded my latest article. This time it is a look at the SQL keyword CASE. It's power is often not fully appreciated. It is much more flexible and powerful than you may expect from just taking a quick glance in the official documentation.

The article is located here.

Please post comments and questions here.

Posted in Oracle, SQL

June 19th, 2007 by Mathias

As Pawel showed in his post some details about how the row scn technology works in specific situations isn't as well documented as you'd wish.

I asked Oracle development to clarify the questions I had and the answer back was both interesting and useful. Every kind of update and lock on a row will make the rowscn for the row/block NULL until the updating/locking transaction completes. That is, any kind of lock and update can be identified when retrieving data.

More interesting is that this can be used to implement "skip locked" which is a feature oracle AQ uses, but it is not supported for use by end users. For a table where rowdependencies is defined, this can now be implemented with "where ora_rowscn is NOT NULL". I don't know exactly how and when I'll use it, but it is good to have this tool when the situation arises. I'm sure it'll come in handy soon enough as discussions about the skip locked feature seems to occur almost on a monthly basis.

I'm on a long vacation so I cannot test this until I come home again. I had some time over this mornings, so I figured it would be a good time to show that the blog isn't completely dead, it's just not too active when I have to choose between talking with friends and family I haven't seen in a couple of years and researching Oracle technologies.

Posted in Oracle, SQL

May 18th, 2007 by Mathias

Both Gary and Philip postd working versions. Gary's didn't work for me when I had the same letter more than one time in the string. Philips did work and the need to deal with each letter as if it is unique can of course be questioned. The intent was for a solution that could handle that and as Philip showed adjusting that is as easy as to just add a distinct.

My version is based on not using analytical functions of which I thought connect by was a part. Looking at it in another way, I wanted something that could be adopted to other databases.

My starting version of the solution would be:

   with v    as (select 'ABC' v from dual)
,base as (select substr(v.v,1,1) nv, 1 nn from v
union all
select substr(v.v,2,1) nv, 2 nn  from v
union all
select substr(v.v,3,1) nv, 3 nn  from v)
select a.nv||b.nv||c.nv
from base a, base b, base c
where a.nn not in(b.nn, c.nn)
and b.nn not in (c.nn)

What's not to like? I get to use one with construct that takes the output from another with construct as it's input. :-)
The "base" with gives us a result table that has one letter from the input string on each row. I'm then joining that with itself three times (as I have three letter in the string) to get a cartesian product that holds all cominations. It has the valid combinations (where all three letters are used) and invalid ones (where some letters are used more than one time). The last thing is to remove the invalid combinations. I do this by checking if the number I assign to each value (nn) occurs more than one time in the result. For AAA we would have nn for table alias A, B, and C be 1. I only have to check if each value occurs later on in the column list as if it was equal to an earlier one, the test for that earlier one would detect that the same was used.

So it works… Interesting and satisfying at the same time. However, the union is not too lovable. How can we avoid it? Well, the easy way would of course be to use the connect by trick, but as I intended to not use it I'll have to cheat and use anoter unrelated table (view).

   with v    as (select 'ABC' v from dual)
,base as (select substr( v.v,rownum,1) nv
,rownum nn from v, all_objects
where rownum <= length(v.v))

select a.nv||b.nv||c.nv
from base a
,base b
,base c
where a.nn not in (b.nn, c.nn)
and b.nn not in (c.nn)

I'm usig all_objects which should have more rows than the length of a string you want all combinations. It works, but I still prefer the connect by version if I were to use it. This way it does satisfy the limitation I put up for this.

So while this works, it is not as dynamic as one could want it to be. You need a different SQL if you have a four letter string. It ieasy to change this for that, but it is still annoying to have to have many different ones. An alternative would be to write PL*SQL to generate the SQL and then execute it. While that may be nicer, I wanted a solution that was a single statement.

I did find a way to do it. I'm sure it can be done easier and nicer. Maybe analytic functions would make the SQL faster and easier to understand. However, This way combines XML, with clauses, and dynamic in-line queries. For fancy use of the Oracle database, it's almost optimal… How practical it would be to use this very often and with long strings would have to be tested before getting it into a production scenario.

   with v as (select 'ABC' v from dual)
,base as (select substr(v.v,rownum,1) nv
,rownum nn
from v
where rownum <= length(v.v))       ,sel  as (select 'select ' ||                        substr(max(sys_connect_by_path('a'||
nn||'.nv', '||')),3)||' data' a
from base
connect by prior nn = nn - 1
start with nn = 1)
,frm  as (select 'from ' ||
'base a'||nn, ',')),2) a
from base
connect by prior nn = nn - 1
start with nn = 1)
,whr2  as (select 'a'||(nn-1)||'.nn not in('||
'a'||nn||'.nn', ', ')),2) ||
')' cond
,max(rownum) rn
from base
where nn > 1
connect by prior nn = nn + 1
start with nn = length( v.v)
group by nn
order by nn)
,whr  as (select 'where ' || substr(max(
sys_connect_by_path(cond, ' and '))
,5) a
from whr2
where not rn = length(v.v)
connect by prior rn = rn + 1
start with rn = length( v.v) - 1)
select extractvalue(t.column_value,'/DATA')
from sel a
,frm b
,whr c
'for $root in $vals
return $root/ROWSET/ROW/DATA'
passing xmltype(dbms_xmlgen.getxml(
'with v    as (select ''ABC'' v
from dual)
,base as (select substr(v.v,rownum,1) nv
,rownum nn
from v
where rownum <= length(v.v))'                              ||a.a||' '                              ||b.a||' '                              ||c.a)) as "vals") t; 

The formatting is a bit forced here as I need it to fit in the line size google allows on this blog. I will not explain in detail what I have tried to achieve in this SQL. If you would want me to talk about what I'm doing in a post, leave a comment and I'll try to write it up in a post soon.

In short, I'm generating different pieces of the SQL statement needed in the with clauses and them I'm using that to create the XML document with all these cominations ina single XML document. I'm then using some XML function and XMLTable with an XML Query to get it back out form one row (one XML doc) to multiple rows to get it back to relational data.

The threaad that started me thinking about this was this thread. There are some permutations things there that solves this with pure analytics. But why do this the easy way when you can make it more complicated. Laurent's solution is probably the only one that scales well with longer strings.

I hope you enjoyed this and hopefully even learned something. I know I did, both that Cartesian products can be used in ways I hadn't really thought of and that I need to learn more about analytical functions.

I also learned more about XML function and how to move data in and out of these dynamic in-line queries.

Posted in Challenge, Oracle, SQL, XML

May 14th, 2007 by Mathias

I don't know of a better name for this feature. I think this name descibes the function well, even if it may sound overly ambitious. This whole post will be based on a post by Laurent Schneider where he uses XML features to do a select count(*) from different tables and return the result in one result table. It is like a auto generated number of selects that are unioned together.

You can find Laurent's post here. It's a wonderful piece of SQL to do something most of us would probably not have thought of using a single SQL statement for.

The version Laurent uses is:

   select table_name
'select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from user_tables;

Let's first review what happens in this query. The inner most function is a call to dbms.getxml with a query that will return one row and one value. This sql stateement is created on the fly based on the table name the outer select retrieves from user_tables. The function will of course turn the query result into XML text. The xmltype that wraps the function will convert the XML text into an XML object so XPATH functions can be used. The extractvalue function pulls out the node ROWSET/ROW/C where the two first are given as that is how getxml will return the data. C is the name of the column or in this case the alias assigned in the select statement as the resulting "column" from the count(*) function. The last thing that happens is that the extracted value is converted to a number and it is then returned to the outer select as the result from this function.

The query returns a result set like:

TABLE_NAME                      COUNT

Posted in Oracle, SQL, XML

May 13th, 2007 by Mathias

Instead of having to look at your different alert logs for ORA-00600 errors frequently, why not have them come to you? This post will, as promised, show the Perl code needed to publish a feed with ORA-00600 errors. This is building on the previous two posts that shows how to create an external table for the alert log and how to pull out all information for an ORA-00600 so each fetch retrieves one the data you're interested in.

The feed I'm using is a RSS 2.0 format. All feeds are fairly closely related so you can change it or provide different ones if you want to. You'll have to look up the different formats and then make the changes you want to this code.

The script I have here is a simplified version that removes all the usual dynamic nature and flexibility with parameters I usually have. This is done for brevity and to focus the script on the task at hand – delivering the ORA-00600 errors to your newsreader soon after they occur.

The script is not pretty and it could be written much nicer with use of modules such as XML::RSS. Unfortunately that module has a chain of dependencies that I didn't want to satisfy, so I'm creating the tags the old fashioned hard coded way. If you are to do a lot feeds with Perl, you will want to get that module installed.

require v5.6;
use strict;
use CGI qw(:standard :html3 :netscape);
use CGI::Carp;
use DBI;
use DBD::Oracle qw(:ora_session_modes);


if ( $ENV{TWO_TASK} ) { undef $ENV{TWO_TASK}; }

my $statusRec;
print 'Content-Type: text/xml' . "\n\n";
print '<?xml version="1.0"?>' . "\n";
print '<rss version="2.0">' . "\n";
print ' <channel>' . "\n";
print ' <title>Oracle ORA-00600</title>' . "\n";
print ' <description>ORA 600 errors</description>' . "\n";
print ' <language>en-us</language>' . "\n";
print ' <copyright>Copyright 2007</copyright>' . "\n";
print ' <managingEditor>email@domain</managingEditor>' . "\n";
print ' <webMaster>email@domain</webMaster>' . "\n";
print ' <generator>script name</generator>';

# Create db connection
$DBH = DBI->connect("DBI:Oracle:host=zzz;SID=zzz;port=nnnn", "alertlog", "alertlog",
{ RaiseError => 1, AutoCommit => 0 })
|| die "Could not connect\n\tError: $DBI::errstr";

my $STH = $DBH->prepare("with alert as
(select rownum rn
from alert_log)
select a.text_line date_msg
,b.text_line file_msg
,c.text_line err_msg
from alert a inner join alert b
on a.rn = b.rn - 1
inner join alert c
on b.rn = c.rn - 1
where c.text_line like '%ORA-00600%'");

$STH->execute() || die "Couldn't execute statement: $STH->errstr";

while($statusRec = $STH->fetchrow_hashref())
print " <item>\n";
print " <title>ORA-600 Occured</title>\n";
print " <description>An ORA-00600 has been detected.\n";
print "<p>\n";
print " \n";
print "The following information was logged about the event:\n</p><p>";
print $statusRec->{"DATE_MSG"} . "</p>\n<p>";
print $statusRec->{"FILE_MSG"} . "</p>\n<p>";
print $statusRec->{"ERR_MSG"} . "</p>\n" . "</description>\n";
print " <author>your name</author>\n";
print " </item>\n";


print " </channel>\n";
print "</rss>";

That is all it takes. This is obviously a CGI script that would run in your web server. If you have one with CGI capabilities configured, you'd place the script there and call it with an url like "http://your.domain/cgi-bin/script_name.cgi". Ask your web admin if you don' know where and how to place this script. If you have your own web server and don't know how to do this, leave a comment and I'll try to give you a few quick hints about how to get it working.

I'm afraid the code above will be truncated by google's new blog interface. The code will still be there so just cut and paste it into your favorite code editor and you'll have all the needed code.

You will probably want to change these lines to something different:

$DBH = DBI->connect("DBI:Oracle:host=...snip...,
...and the following nodes...

The first one should point to where you have Oracle installed and the second will be your connection string. If you don't know the port ask your DBA or try the default of 1521. It is the port you have your listener configured to listen on.

There is no guarantee that the code works as is. Every environment is different and the code is clearly not environment proof. If you have problems with it, check your error log for the web server and leave a message if you want my input on what the problem might be.

Enough alert log stuff? Probably. Sorry if you feel I've overdone it and got stuck on this subject for too long. Next post will not even mention the alert log.

If you have APEX or want to wait for 11G where it will be a supported option, you will want to use APEX and pure SQL for this rather than Perl. A good intro to how to do it can be found on It is written by Sean Dillon and looks well written and easy to follow, it can be found here. I've not tried it myself, but it sounds straight forward and anything from the people behind asktom and APEX at Oracle is usually both easy to understand and easy to implement (relative to the subject's complexity).

Posted in Alert Log, Oracle, Perl, RSS, SQL