Feed aggregator
Replace by position
Have you ever wanted to replace a character in a string just by position? I needed this the other day and IMHO there is no way in Oracle PL/SQL to do this, so I decided to write this my self.
It is really simple code and it probably needs improvement but it works for now and I think it is rather simple. Any improvement suggestions are of course very welcome in the comments.
CREATE OR REPLACE FUNCTION replacepos
( source_in IN VARCHAR2
, replacechar_in IN VARCHAR2
, position_in IN NUMBER) RETURN VARCHAR2 IS
l_returnvalue VARCHAR2(32767);
BEGIN
— copy from the source string up to, but not including,
— the character position
— to be replaced
l_returnvalue := substr( str1 => source_in
, pos => 1
, len => position_in - 1);
— add the replacement character
— just a single character, but more can be sent in,
— so substring the parameter
l_returnvalue := l_returnvalue ||
substr( str1 => replacechar_in
, pos => 1
, len => 1);
— copy the rest of the source string
l_returnvalue := l_returnvalue ||
substr( str1 => source_in
, pos => position_in + 1);
RETURN l_returnvalue;
END replacepos;
[Book Review] jQuery Pocket Reference by David Flanagan
jQuery is the “write less, do more” JavaScript library. Its powerful features and ease of use have made it the most popular client-side JavaScript framework for the Web. This book is jQuery’s trusty companion: the definitive “read less, learn more” guide to the library. jQuery Pocket Reference explains everything you need to know about jQuery, completely and comprehensively.
I decided to review the JQuery pocket reference. As the title already shows this should be treated as a reference rather than a book you should read from start to finish. I read the first couple of chapters and it learned me a lot about the way JQuery works. I think it is a very powerful way to interact with webpages and it should come in handy while doing APEX development.Especially the way you can respond to user actions, making your page more interactive and thus more attractive to the user.
I think the rest of the book should be treated the way I think it is supposed to be treated: as a reference rather than a book to read from A through Z. Knowledge from this should come in handy while developing APEX applications, especially when implementing Dynamic Actions.
Part of the book consists of an extensive description of every available function, at least, the ones available at the time of writing. Also all the available selectors and selector functions are described.
There is a full chapter on creating plugins. It is probably good to know how to create a plugin in JQuery to increase your knowledge about how this can be used when building an APEX plugin around it.
I think when creating specials in APEX, like dynamic actions or plugins, it is good to know at least a bit of how stuff works and JQuery becomes of ever increasing importance when building web interfaces. Whether you build your applications completely by hand, using plain HTML and JavaScript, use PHP or APEX or whatever interface you are using, almost every one of them uses or can use JQuery.
This books helps in understanding the language and design a bit better. Of course you can use the internet to find all the information you need, but if you are a bit like me, then you tend to get lost in all the information you find out there and often it is really hard to understand what is being written and what it is meant to do.
Analytic vs Hierarchical
While playing around with Analytic functions with a Hierarchical function in my query I stubled upon some strange behaviour I cannot explain (yet). The documentation provided by Oracle states the following:
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause.
Right, that would mean I can apply something analytic and something hierarchical in one query. But when I tried it, it didn’t seem to work the way I expected.
First, I started off with a simple hierarchical query:
SELECT LEVEL elevel ,sys_connect_by_path(e.ename, '/') epath ,e.* FROM emp e CONNECT BY PRIOR e.empno = e.mgr START WITH e.mgr IS NULL;
Now I want to apply an Analytic function. I want to know what the next level is.
SELECT LEVEL elevel ,sys_connect_by_path(e.ename, '/') epath ,lead(LEVEL) over(PARTITION BY 1 ORDER BY 1) ellevel ,e.* FROM emp e CONNECT BY PRIOR e.empno = e.mgr START WITH e.mgr IS NULL;
Hey, where did my path value go. It seems that analytic functions screw up the hierarchical function that has been used. I do not understand how this happens, but I figured out a workaround:
WITH emp_hier AS (SELECT LEVEL elevel ,sys_connect_by_path(e.ename, '/') epath ,e.* FROM emp e CONNECT BY PRIOR e.empno = e.mgr START WITH e.mgr IS NULL) SELECT lead(elevel) over(PARTITION BY 1 ORDER BY 1) ellevel ,e2.* FROM emp_hier e2;
As it turns out, I can force the SQL engine to apply an analytical function last if I make sure the resultset is created using subquery factoring.
XOR in SQL
If you have ever tried to implement a requirement that implies that either one field is filled, or the other one but not both and not both not then you might have thought about using an XOR function. Unfortunately this is not implemented in Oracle SQL.
First of all, let’s review some of the truth tables that are implemented in SQL.
The way XOR should be implemented is:
XOR left right result false false false false true true true false true true true falseIn PL/SQL there is a function available that implements this truthtable. It’s in the standard package and looks like this:
desc sys.standard.XOR; Parameter Type Mode Default? --------- ------- ---- -------- (RESULT) BOOLEAN LEFT BOOLEAN IN RIGHT BOOLEAN IN
Let’s create a simple table and fill it up for testing:
create table t
(left number
,right number);
insert into t (left, right) values (0,0);
insert into t (left, right) values (0,1);
insert into t (left, right) values (1,0);
insert into t (left, right) values (1,1);
commit;
An idea would be to just call this function in a query like this:
select * from t
where sys.standard.xor(((left <> 0)), ((right <> 0)))
But, unfortunately the boolean datatype has not been implemented in SQL, so this doesn’t work.
How to solve this issue then? We can of course write down all the rules in a where clause and make sure it return the correct results:
select * from t
where (
( ((left is not null) and (left <> 0))
and ((right is null) or (right = 0))
)
or
( ((left is null) or (left = 0))
and ((right is not null) and (right <> 0))
)
)
That is one ugly query. How can we rewrite the XOR function using just AND and OR function. The OR function gives almost everything we need, but it gives one result to many. And that is exactly the outcome of applying the AND function to our dataset. So:
XOR = a OR b minus (a AND b)
select * from t
where ( ((left is not null) and (left <> 0))
or ((right is not null) and (right <> 0))
)
minus
select * from t
where ( ((left is not null) and (left <> 0))
and ((right is not null) and (right <> 0))
)
or
XOR = a OR b and NOT (a AND b)
select * from t
where ( ((left is not null) and (left <> 0))
or ((right is not null) and (right <> 0))
)
and not
( ((left is not null) and (left <> 0))
and ((right is not null) and (right <> 0))
)
I think these last two queries are more descriptive to what is being done. Which query to choose depends on how much data is in your table. In my example it doesn’t make any difference in time. I tested with a table with over 29500 rows and it appears the minus operation is a bit faster than the version with the extra predicate.
Rather simple code, but I hope it will help you a bit (it certainly helped me). I wrote this to make sure I don’t forget it 
Dynamic ShowHide in APEX
When you want to show and hide certain regions in APEX based on the selection of the user then there is of course the possibility to use the Region Display Selector as a region on your page. Using this region all the other regions in the page that have Region Display Selector in the attributes section of the region set to YES. I don’t really like the way this region is built, the way it looks when you run the page that is.
I wanted to have more control about the look and feel of my selector region.
First of all, I don’t want the ‘Show All’ option and I cannot find a way to turn this button off.
Next, I want to display the option below each other, instead of next to each other.
Then, I want to be able to add different images to the options.
But doing so means I will have to do everything myself, which is not a bad thing, just takes some time to do it.
Enter Dynamic Actions. When I click an item, I want the appropriate region to be displayed and the other ones should be hidden. First iteration of my test page included 5 dynamic actions and in each dynamic action I showed the appropriate region and hid the other ones. So every dynamic action included 5 True Actions. 1 to show the region and 4 to hide others. But since I am a developer I am in essence lazy and I want to achieve SPOD (Single Point Of Definition). Thinking about what actually needs to be done is not show selected region and hide others, but actually hide all regions and show the selected region. That would include one ‘overall’ dynamic action the will hide all the regions and one dynamic action per item. Luckily you can have a single dynamic action attached to multiple items, so I created a single dynamic action on the click event of multiple items.
Too bad there is no multi select or shuttle option on the selection of items as there is for the affected item selection, but we can type the different item names separated by commas. When the user clicks on one of the items defined all regions will be hidden. Then, after this single dynamic action, I need to create separate dynamic actions on every item to display the right region.
Make sure the regions are hidden first and then the right region is shown. If you get this the other way around, then nothing will be displayed at all because the region will be displayed and right after displaying it, it will be hidden again because the other dynamic action says so.
When this was working I wanted to give some more feedback to the user when he/she hovers over one of the triggering items. This takes another two dynamic actions. One to underline the text when the user enters the item (with the mouse) and one to set the text to normal when the user leaves the item. I can use pretty much the same approach as I did with the hiding of the regions, but I don’t want the true action to affect all the items when pointing to a single one. Luckily APEX provides an option for the Affected Elements called Triggering Element.
This way the chosen Setting will only be applied to the element that triggered this dynamic action.
You can view a demo for these dynamic actions at: http://apex.oracle.com/pls/apex/f?p=41376 or download the source from http://bar-solutions.com/weblog/wp-content/show-hide.zip and install it in your own workspace.
[Book Review] SQL Pocket Guide By Jonathan Gennick
I started reading this book by Jonathan Gennick about a week ago. After a couple of
chapters I realized this book is more a reference than a book you
should read from A through Z. I am really an Oracle guy. I think I
know a lot about the PL/SQL and I think I know a lot about SQL as
well. It’s fun to see how much of this knowledge can be applied to
other databases as well. This reference is a nice book to get ideas on
how other databases help us developers and maybe even create some of
the functions in the Oracle environment.
This book shows most of the functions available in all databases and
how they differ from environment to environment. It describes just
about all the functions you will ever need in your day to day work.
When you need to do something that is not described in this book, then
you are probably better off by diving into the specific documentation
for your preferred database.
As said, this book is a good reference for your basic needs. If you
need to make application database independent (which IMHO is a really
bad idea, but that’s something else) you can use this reference to see
what is supported in all the different RDBMS’s.
Pipelined Table Functions
I have had trouble with a certain view I have to create at the customer site I am currently working at. The view involves 3 SQL queries combined by using a UNION ALL, since the separate queries are mutually exclusive. Using the UNION ALL makes it a bit faster since Oracle doesn’t have to do all the work of sorting and checking for duplicate rows.
Retrieving data from this view resulted in memory errors since there is too much data for the server to come up with and to return to me. After checking all kinds of initialization parameters I decided to try and build a couple of pipelined table functions that would retrieve the same data for me, but I assumed it wouldn’t put such a big pressure on my memory usage.
A pipelined table function will come up with a single row for the result set and pipe it out of the function when it’s available. In my opinion there would only be need for enough memory for this single row instead of enough memory for all the rows of the entire result set.
Since the function gets called from SQL the SQL engine needs to be aware of the result it produces. So I need to create types that define a row of the result. And since the function will return more than a single row, I also need to define a NESTED TABLE, a collection of these rows. Using two types I can mimic the appearance of a table.
Now all I have to do is create functions that will give me the result I am interested in.
My ‘normal’ view looks like this:
create or replace view v_emps as
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
from emp e
where e.deptno = 10
union all
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
from emp e
where e.deptno = 20
union all
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
from emp e
where e.deptno = 30
union all
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
from emp e
where e.deptno = 40
/
In the following example I create a new view based on the table functions for the EMP table. The SQL approach is better, easier to read and faster, but it shows what should be done if you want to move over to table functions.
The way such a function look is kind of strange at first sight.
Function f1 return mynestedtabletype pipelined
Is
<declaration>
l_record myrecordtype;
begin
loop
<do stuff to define l_record>
pipe row(l_record); — send the row out
end loop;
return; — return nothing but control
end f1;
First I create a type for the records I return from my function:
CREATE 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)
)
/
Then I create a nested table of the record type I just created. The functions will return (or say they will return) a variable of this type:
CREATE TYPE emps_tt AS TABLE OF emp_t
/
Next is the package which will contain the table functions. I create 4 functions to retrieve all the datasets I need.
CREATE OR REPLACE PACKAGE emp_tf IS
— Author : Patrick Barel
— Purpose : Table Functions for EMP
— Public function and procedure declarations
FUNCTION get_accounting RETURN emps_tt PIPELINED;
FUNCTION get_research RETURN emps_tt PIPELINED;
FUNCTION get_sales RETURN emps_tt PIPELINED;
FUNCTION get_operations RETURN emps_tt PIPELINED;
END emp_tf;
/
CREATE OR REPLACE PACKAGE BODY emp_tf IS
— Function and procedure implementations
FUNCTION get_accounting RETURN emps_tt
PIPELINED IS
CURSOR c_emps IS
SELECT e.empno, e.ename, e.job, e.mgr
,e.hiredate, e.sal, e.comm
FROM emp e
WHERE e.deptno = 10;
r_emps c_emps%ROWTYPE;
l_returnvalue emp_t;
BEGIN
OPEN c_emps;
LOOP
FETCH c_emps INTO r_emps;
l_returnvalue := emp_t(r_emps.empno, r_emps.ename, r_emps.job
,r_emps.mgr, r_emps.hiredate
,r_emps.sal, r_emps.comm);
EXIT WHEN c_emps%NOTFOUND;
PIPE ROW(l_returnvalue);
END LOOP;
CLOSE c_emps;
RETURN;
END get_accounting;
—
FUNCTION get_research RETURN emps_tt
PIPELINED IS
CURSOR c_emps IS
SELECT e.empno, e.ename, e.job, e.mgr
,e.hiredate, e.sal, e.comm
FROM emp e
WHERE e.deptno = 20;
r_emps c_emps%ROWTYPE;
l_returnvalue emp_t;
BEGIN
OPEN c_emps;
LOOP
FETCH c_emps
INTO r_emps;
l_returnvalue := emp_t(r_emps.empno, r_emps.ename, r_emps.job
,r_emps.mgr, r_emps.hiredate
,r_emps.sal, r_emps.comm);
EXIT WHEN c_emps%NOTFOUND;
PIPE ROW(l_returnvalue);
END LOOP;
CLOSE c_emps;
RETURN;
END get_research;
—
FUNCTION get_sales RETURN emps_tt
PIPELINED IS
CURSOR c_emps IS
SELECT e.empno, e.ename, e.job, e.mgr
,e.hiredate, e.sal, e.comm
FROM emp e
WHERE e.deptno = 30;
r_emps c_emps%ROWTYPE;
l_returnvalue emp_t;
BEGIN
OPEN c_emps;
LOOP
FETCH c_emps
INTO r_emps;
l_returnvalue := emp_t(r_emps.empno, r_emps.ename, r_emps.job
,r_emps.mgr, r_emps.hiredate
,r_emps.sal, r_emps.comm);
EXIT WHEN c_emps%NOTFOUND;
PIPE ROW(l_returnvalue);
END LOOP;
CLOSE c_emps;
RETURN;
END get_sales;
—
FUNCTION get_operations RETURN emps_tt
PIPELINED IS
CURSOR c_emps IS
SELECT e.empno, e.ename, e.job, e.mgr
,e.hiredate, e.sal, e.comm
FROM emp e
WHERE e.deptno = 40;
r_emps c_emps%ROWTYPE;
l_returnvalue emp_t;
BEGIN
OPEN c_emps;
LOOP
FETCH c_emps
INTO r_emps;
l_returnvalue := emp_t(r_emps.empno, r_emps.ename, r_emps.job
,r_emps.mgr, r_emps.hiredate
,r_emps.sal, r_emps.comm);
EXIT WHEN c_emps%NOTFOUND;
PIPE ROW(l_returnvalue);
END LOOP;
CLOSE c_emps;
RETURN;
END get_operations;
—
BEGIN
NULL;
END emp_tf;
/
In the implementation of the functions I could of course use BULK OPERATIONS to speed up the processing instead of the row-by-row (or slow-by-slow) approach I used now, but using bulk operations use more memory (the retrieved data is loaded into memory) and that is exactly what I am trying to avoid. Using BULK COLLECT with a limit clause might be an option, but that is something to explore later on.
The new view looks like this:
create or replace view v_emps_tf as
select * from table(emp_tf.get_accounting)
union all
select * from table(emp_tf.get_research)
union all
select * from table(emp_tf.get_sales)
union all
select * from table(emp_tf.get_operations)
/
Pretty much the same as the original one, but now based on my functions.
Let’s take a look at the ‘strange’ things here. When you look at the function specification it says it will return a nested table type, but in fact it will return nothing. Every result gets piped out of the function (PIPE ROW) when it is done.
Another thing is the from part of the queries. We tell the SQL engine to treat the results of the function as if it were a table. For this we use the TABLE() operator.
Doing some checks (select * from v_emp minus select * from v_emp_tf) tells me the result of both views is the same, since there are no rows returned. When I turn timing on I notice that the ‘normal’ SQL approach is faster than the table function approach. But the issue wasn’t speed, it was memory usage.
Dynamic Actions in APEX
After visiting the OPP/APEXPosed conference in Brussels I decided to write a post on how to create a relatively simple dynamic action in APEX. When you are working with password fields your only visual check to see if the entered text in both fields match may be the number of characters entered. Of course you can check for equality on submit but that may be a bit too late for the user.
For this post I will work with a textfield instead of a password field, but both will work the same.
First of all we create a page.
A simple blank page will do.
Give it a title. 
Add a simple HTML region to hold the items.
Don’t use tabs (for this demo ;-))
Review what you just chose:
and press Finish.
The page should be created succesfully:
The page rendering part should look something like this:
When you open the context menu on the region you can choose to create a Page Item:
For the demonstration we will choose Text Field (that way what we enter will be visible)
Give the item a name:
Give the item a label: 
Review the chosen options:
The item doesn’t have to be conditionally displayed so you can just press ‘Create Item’ here:
Repeat these steps for the second page item:
Now the page rendering portion will look something like this:
Using the context menu we can Create a Dynamic Action.
Give it a name and possibly a sequence.
Choose the event to use for this Dynamic Action to fire. In this case I think On Key Release will be the best choice. Choose the Selection Type, this action will rely on changes of items. Choose the items you want to fire this action. In condition choose the kind of condition you want to use. Choose JavaScript Expression and type in your Javascript expression.
document.getElementById(’P100_TEXTFIELDA’).value ==
document.getElementById(’P100_TEXTFIELDB’).value
Then select the kind of Dynamic Action you wish to create. We will be creating an Advanced Action
Select what you will be doing when the condition evaluates to TRUE:
In this case we want to change the background color of the textbox, so we choose to Set the Style. We also want to execute this action when the page is loaded.
When the condition evaluates to FALSE we want to do the same action, but with a different color
Then select the kind of page elements you want to be affected by this dynamic action.
Next we choose on which fields this dynamic action will fire:
The Dynamic action now shows up in the Page Rendering section of our page.
Running the page shows our Dynamic Action in action:
I hope this helps in your understanding of the process of building Dynamic Actions in APEX. Of course this is a pretty simple example and you can probably come up with better ideas for the use of Dynamic Action, but as you can see, this is done with next to no coding (just the Javascript for the condition took some coding).
Updates to pre-requisite patch list
KScope 11
About 4 weeks from now KScope11 will be well underway. I am really looking forward to attending this conference. I think it is more ‘developer-centric’ than for instance Oracle Open World is. I still have to create my schedule for the days, but I know of at least one session where I will be present.
That will be my own session on Pipelined Table Functions. It will be on Tuesday, June 28th. Here’s the abstract of my presentation:
“If you can do it in SQL, use SQL.” But sometimes even the very powerful version of SQL that Oracle provides is not enough and you need more, like loops, conditions, etc. If you can make the output of a function like it’s a table, then you can use it in SQL and have access to all the power PL/SQL provides. After this session, you will know how to create functions that can be used as tables in SQL.
Before that I will do a preview session (or test run if you will) on Tuesday, June 14th at AMIS in the Netherlands. If you are not attending KScope11 in Long Beach and you live in or near the Netherlands then you are invited to join us. If you are attending KScope11 then you are of course invited to join me there.
Hope to meet you either in Nieuwegein or in Long Beach.
Getting a WebLogic Server supporting ADF Up and Running
When Jdeveloper 11G came out out I was excited! Excited at all the new features and innovations that were being put out by Oracle. Well that excitement has long wore off once I started trying to use the new Version.