Category: Oracle

September 21st, 2010 by mathias

This will be a APEX only day. The day starts with two APEX 4.0 presentations.

First up was a presentations about dynamic actions in APEX held by Anthony Raynor.

Dynamic acttions is a declarative way (in 4.0) to use javascript to enhance clientside behavior of an APEX application. It is used for things such as disabling some fields on the page in some situations.

Using it is done by defining four things:

  • When
  • Condition
  • Action
  • Affected elements

For example it coould be used on pageload to disable fields it a checkbox is nog checked and then it could be applied to any number on items.

The presentations wrapped up with showing some nice demos on how the dynamic actions can be used from display to setting items to values in sosme conditions. One neat way to use it was to use it to delete rows in a alist without having to do a pagerefresh for everey delete.

Anthony has a nice sample application that I think are available on OTN that shows how to use dynamic actions.

The next session was on tabular forms in apex.

Tabular forms are used to make updates to multiple rows shown in a list/grid. The presentations was a fairly basic review of what can be done with tabular forms.

Some new features were shown. One of the nicer is that errors on multiple rows are now highlighted and an error per row is dispslayed on top of the page.

Other improvements are that radio and checkboxes are better supported. FMAP arrays have been introduced to make it easay to map a columnname in a form with the name eof the array containing the values in that columnname. It is alsos possible to make a call in pl_sql to find the array holding the data for a columnname (such as EMPNO).

That was the end of the mornings presentations, more apex fun to look forward to after lunch.

Posted in APEX, OOW, Oracle

June 10th, 2010 by mathias

I like when a blog I follow links to a blogpost I have already seen and that I thought was a gem. I used to happen quite frequently when Cathy Sierras blog was active, but it still happens. The reason it is nice is that I like it is that if the first post was really good, you can almost guarantee that I will like the next writer I follow that discusses the same thing.

This week it was Seth Godin who wrote about deadlines and why they work even if you set them for yourself. They have to be set publicly, but they do work. I have recently had a couple of discussions at work around it and this blogpost summarizes it and adds a few more points. I enjoyed the fact that someone else has found the same effect on having to set deadlines for themselves.

The post that referred to it was Tom Kyte's where he talks about finishing up his new book by starting to give Seth's blog praise and talk about finding that he is not the only one deadlines work for. One post that both discusses Seth's blog and gives us the news that Toms new book will indeed find it's way to the bookshelves one day is a blog post to celebrate.

Deadlines do work and no you are not special, they work the same way for everyone. If you do not set one and tell someone else about it, you are likely to not be done until after the original deadline you set.

Go set yourself a deadline today for something you really want to achieve/complete.

Posted in Books, Oracle

June 6th, 2010 by mathias

In case you haven't noticed already, David Peake's blog just announced that APEX 4.0 moves one step closer to become available for download. It is now the release that powers

This is good news as it shows that the release is getting close to be completed. Upgrading is usually one of the last public steps before a release becomes available to download.

Posted in APEX, Oracle

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

January 9th, 2010 by mathias

Interested Transaction List is the method through which Oracle keeps track of which transaction has updated which row in a block. I wrote an article about it a while back where I show how the ITL works and how it impacts performance in certain situations. The basis of the article is a page locking scenario I encountered. Sure, page locking is not possible in Oracle, but an ITL wait is very similar to a page lock. 

You find the article here.

Please post comments and questions here.

Posted in ITL, Oracle, Performance

January 9th, 2010 by mathias

I have just uploaded an article I wrote almost five years ago to the day. It was my first attempt and I wrote about Oracle AQ which is Oracle version of a messaging product.

AQ is included with the database and has a lot of nice features including full support for XML payloads. Another important benefit is that your messages are synchronized with your DML as both are committed in the same transaction.

You can find the article here. Please post comments and questions here.

I will post a couple more articles that I've written but somehow never got posted to this site. I will write a short blog entry about each of them to get a permanent place for comments about each article.

Posted in AQ, Oracle, XML

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 31st, 2007 by mathias

I'll give myself a quota for no more than one rant a month. However, one of my pet peeves is how Oracle licenses the database. I'l probably return to why I dislike the general model in a future rant, but this on e will be specific to partitioning.

To use partitioning, you have to pay for the Enterprise Edition of the database at $40,000 per CPU and then pay an additional $10,000 for the partitioning option. That is a lot of money if all I really need is SE One (at $5,000 per CPU) with Partitioning. Unfortunately, that is not an option.

I know partitioning used to be a fairly exotic feature. That was however over 10 years ago, when few datbases were really large and few SQL statements would benefit from parallel execution. As the average database has grown to be really large and most database servers now are multi CPU (and core) servers, this should now be treated as core database functionality.

In many cases it doesn't matter as large companies has site wide use of Oracle DB technology. It does, however, matter for smaller companies and startup companies. If you were to build a new solution today and market it initially to small companies or even run it as your own ASP, you would not be able to design with partitioning. Later on, your solution will be created and the cost of rearchitecting for partitioning is unacceptable.

Even worse is when you're taking an existing application and trying to move it downstream where SE One has to be used to meet the budgets those implementations have. Why spend a lot of time of a lot of good resources to implement "manual" partitioning?

I think Oracle would benefit from making partitioning included in all licenses. One thing it would do would tie customers closer to Oracle as the cost of converting partitioning from one DB to another is high due to the lack of standard even for the DDL used. I think it would also remove another reason for customers to consider other databases. I think licensing should be such that customers can scale with the database and get started on what they need. The situation today is such that building small and needing partitioning always requires a discussion of "can we replace Oracle in our technology stack".

By having partitioning as an included feature, companies could design for it when they begin designing new applications and get the use of it as they grow. That helps Oracle attract small companies that hopefully grow into large customers over time. It also helps in not getting people convinced that Oracle doesn't scale as using partitioning was too expensive when they started and now it's too costly (or politically impossible) to change it.

Designing manual partitioning schemes is really a waste of my and other Oracle professionals time. So… Please Oracle, it is time to revisit how licensing partitioning is done. It is a feature we need to put in the core toolbox and get people to make better use of the database with it. I hope 11G comes with a new licensing strategy for this. I believe Oracle would make a lot of money on making this an included feature in all editions of the database.

That's the partitioning rant. I know others have similar ideas on this as I've read some recent blogs on partitioning. I love Oracle's partitioning, but I hate having to use it only for the largest customers. I really dislike having to think of and implement manual partitioning, I know it will n ot scale as well as database driven partitioning and I still have to waste my time on it for financial reasons. Even worse is having to discuss if we should avoid Oracle altogether as we cannot use partitioning and then maybe a cheaper database will work just as well for us. I don't want to have to avoid the best database over financial details.

If one database is the best for the job, then I want a licensing model that allows me to start small and pay more as my need grows (meaning using SE One to start with and grow to EE while I design for and with partitioning the whole time).

Posted in DBA, Licensing, Oracle, Partitioning

May 21st, 2007 by mathias

Reading this post about why the average Oracle IT worker doesn't read blogs got me thinking. Does the average IT worker read blogs? I'm not sure. In what I have observed, it is something a segment does. I'd contend that the average blog reader is more advanced and more thirsty for more knowledge that the average person in the same field. I think it is also something the more internet aware do. Everyone knows about the internet, but how many non blog readers can explain the technologies involved? I have a feeling that most that can are on the blogosphere as readers today.

This is not to say that there aren't really good Oracle Specialists (or IT Specialists for that matter) that doesn't read blogs at all. Some has never looked at newsreaders and stay away because they don't want the hassle of learning one more product, other feel it is a waste of time as most blogs are useless to them, yet others feel that there are good blogs – but they are too hard to find.

I think most will improve and learn interesting things if they start reading blogs, I know I have learned many things I'd never have thought of or heard about if it weren't for blogs. Participating on and reading the best blogs is like an informal study group where you can opt in to learn about the subjects you are interested in.

I think it is that some like reading and learning small segments, while others are happy just doing what they know and hopefully hitting the Oracle docs from time to time when they get stuck. It may be similar to how it is natural for some to look up concepts and syntax in the documentation while others try to avoid the documentation like the plague. Maybe most blog readers are among those who like to read the documentation to learn more. There is a difference between looking things up because you have to and enjoying it because may always learn something new.

What do you think? Is this an awareness issue or is this just one of those things where some people will never learn to like the blog format for their reading and learning?

Posted in Blogging, Oracle

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