.code, .code pre { font-size: small; color: #00007F; font-family: “Courier New” Courier monospace; background-color: #ffffff; /*white-space: pre;*/ } .code pre { margin: 0em; } .code .rem { color: #008000; } .code .kwrd { color: #007F7F; } .code .comm { color: #ff0000; } .code .str { color: #006080; } .code .op { color: #0000c0; } .code .preproc { color: #cc6633; } .code .asp { background-color: #ffff00; } .code .html { color: #800000; } .code .attr { color: #ff0000; } .code .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .code .lnum { color: #606060; }

On my new job we wanted to provide some logging. Especially about what program is currently running. Of course it would be easy enough to add a line at the start of the program with the name of that program and one at the end. But, as things go during development, names (and types) of programs tend to change and we would have to change those lines accordingly. Something that can (and will) be easily forgotten.

Why not have the code tell us where we are. That way we wouldn’t have to change our code if the program name changes. But unfortunately there is no such thing as a function to tell me where I am.

So, I decided to write one of my own. In the old days, using Novell network software, there was a function called WhoAmI, so I decided to name my function WhereAmI. What does Oracle offer me to tell me where I am. There is such a thing as DBMS_UTILITY.FORMAT_CALL_STACK. This provides a nice call stack of all the programs called in sequence like this:

   1:  ----- PL/SQL Call Stack -----

   2:    object      line  object

   3:    handle    number  name

   4:  287562CC         7  function UTIL.WHEREAMI

   5:  29DEBFF8         7  anonymous block

The first three line are header information. The fourth line is where we are when this function is being called, being our function, so the first line of interest to us is the fifth line:

   1:  29DEBFF8         7  anonymous block

Actually we only need the text in the third column. That tells us exactly where we are. Using string searching capabilities we remove everything we don’t need. First of all, we search for the position of the first space, then we copy the rest of the string and by using the trim functionality we remove any (leading and trailing) spaces from it. Now we have:

   1:  7  anonymous block

If we execute this trick again we are left with the information we are interested in:

   1:  anonymous block

Using this function at the start and end of our programs where to see the exact name of the program we are entering or exiting no matter what the type is, or if it is packaged or not. Only thing it doesn’t show me is whether it’s a private program or a local program.

The complete code for the function is like this:

   1:  FUNCTION whereami RETURN VARCHAR2 IS

   2:    l_callstack   VARCHAR2(2000);

   3:    l_returnvalue VARCHAR2(100);

   4:  BEGIN

   5:      <<code>>

   6    -- Get the current callstack

   7:    l_callstack := dbms_utility.format_call_stack;

   8:   -- The line of interest is the fourth line.

   9:    l_returnvalue := substr(l_callstack

  10:                           ,instr(l_callstack, chr(10), 1, 4) + 1

  11:                           ,(instr(l_callstack, chr(10), 1, 5) - instr(l_callstack, chr(10), 1, 4)));

  12:    -- All we are interested in is the type and the name of the program.

  13:    l_returnvalue := TRIM(substr(l_returnvalue, instr(l_returnvalue, ' ', 1)));

  14     l_returnvalue := TRIM(substr(l_returnvalue, instr(l_returnvalue, ' ', 1)));

  15:    RETURN l_returnvalue;

  16  END whereami;

should be displayed as:

How can this be done. The answer is in the query.

I decided to try some stuff and came up with the solution. First I need about 10 records.

SELECT rownum rn FROM user_source WHERE rownum < 11;

Using subquery factoring helps me build the query step by step. Next, I need to split the results into three groups. Using the MOD function I can tell in which group a record should reside.

WITH t AS

(SELECT rownum rn FROM user_source WHERE rownum < 11)

select MOD(rn,3) rn2

        ,rn from t;

Now I want to split the results into different sets. Let’s begin by creating a set of the first records.

WITH t AS

(SELECT rownum rn FROM user_source WHERE rownum < 11),

   WHERE rn2 = 1;

If I create three set of data (unique) then I can join them again

WITH t AS

(SELECT rownum rn FROM user_source WHERE rownum < 11),

  JOIN all3 ON (r2 = r3);

What happened to number 10? The records don’t always exist in the second and third set, so I have to use an outer join to see all the records available.

WITH t AS

(SELECT rownum rn FROM user_source WHERE rownum < 11),

  LEFT OUTER JOIN all3 ON (r2 = r3);

Now the result is exactly the way I (or actually the customer) wanted. All that needs to be done now is adapt this query so that it uses the real data. But the idea is in place.

[ENDIF]
[INCLUDE]Types Package Specification
SHOW ERRORS
[INCLUDE]Query Package Specification
SHOW ERRORS
[INCLUDE]Change Package Specification
SHOW ERRORS
[IF]{include_utility_packages}[EQ]{qda_true}
[INCLUDE]Utilities package specification
SHOW ERRORS
[ENDIF]
[INCLUDE]Query Package Body
SHOW ERRORS
[INCLUDE]Change Package Body
SHOW ERRORS
[IF]{include_utility_packages}[EQ]{qda_true}
[INCLUDE]Utilities package body
SHOW ERRORS
[ENDIF]
#
# 1.4 Apply logic here from the audit trigger script
[SETALIAS]l_pky_gen[TO]{qda_false}
[IF]{qda_generating_pky}[EQ]{qda_true}[AND]onepkycol
[SETALIAS]l_pky_gen[TO]{qda_true}
[ENDIF]
[IF]{l_pky_gen}[EQ]{qda_true}[OR][-]
{grp_int_table}[EQ]{qda_true}[OR][-]
{have_audit_columns}[EQ]{qda_true}
[INCLUDE]Audit triggers
[ENDIF]
And of course I need to copy the arguments from the scripts that are executed when I run the entire script group.
And there it is, a single script to install the packages for the QDA.

The steps include examples of creating a user table and a custom authorization function in a package. It also includes some pointers on how to handle passwords. I did everything exactly as described in the book, changed the authorization scheme to the newly created one and tried to log in to my application. But it failed. Even with the correct credentials. What is going on here? Must be something I did wrong. Let’s try to create the schema again (dispose of the ‘old’ one) and now follow all the steps in the wizard.
In the book they said to create the scheme from here (with minimal information) and then change the properties later. But I want to go through the entire wizard so I decided to click the ‘Next>’ button.
The 6th page of the wizard is where the real action takes place. I want to use my own custom function to authenticate the session.
The function should return a boolean and take username and password as input.
The function is declared in the package as follows:

FUNCTION authenticate(p_username IN VARCHAR2
,p_password IN VARCHAR2) RETURN BOOLEAN;Now it’s time to test the new scheme. Change the current authorization scheme to the one just created.
Then try to run the application again.
Login with some faulty credentials and notice that I am not authenticated.
When trying to login with correct credentials I am allowed access to the application.

Now what is the difference between this authorization scheme and the one I created earlier. I think it has something to do with the session not valid part of the authentication. When I created it by filling in just the name and the description of the scheme it used the –BUILTIN- ‘Not Valid URL’. In my new scheme it says here to use the login page instead. It seems that did the trick. Checking the Application Express scheme shows me roughly the same settings. I think I nailed this one. It took me a lot of time and I want to thank my friends Jorrit and Alex for helping me where they could (although they had not needed this yet) especially where to look or provide me with more documentation.

I have been (and am still) rebuilding a webpage I have built using PHP and MySQL (http://bar-solutions.com/ezlinks). I am pretty pleased with the result so far (http://tryapexnow.com/apex/f?p=2231 username/password: demo/demo)

It is fun to build code (PL/SQL code of course) to have the pages do what I want them to. I have been using a nice regular expression by Alex Nuijten to split the defined tags into separate entries.

If you have any issues, bugs etc, please don’t hesitate to mention them. Maybe I will solve it (if at all possible ;-))

By comparing both the scripts that have been created I thought I could find the difference on how the application would be created.

It appeared that, besides from some time issues, the main difference lies in the way the application is being created. With a run-only application the call to create the application is like this:

–application/create_application
begin
wwv_flow_api.create_flow(
…
p_build_status => ‘RUN_ONLY’,
…
end;
/

With a run-and-build application the call to create the application is like this:

–application/create_application
begin
wwv_flow_api.create_flow(
…
p_build_status => ‘RUN_AND_BUILD’,
…
);
end;
/
Well, that looked promising. I decided that I had to change the current export script as this had the RUN_ONLY option in place. I couldn’t wreck it anymore than it already was.

I changed the script. ran it, and there it was. My application was available for editing again. Instead of finding out what table data to change and spending hours finding out where Oracle stores the way an application behaves, it took me just the time to export (twice), do the comparison, make the change en run the import. Less than an hours work. Thank Oracle for such an easy way to rescue my application and save me from hours, if not days, of recreating it.

I also did a presentation in the tools track for Allround Automations’ product ‘PL/SQL Developer’. This audience was of course smaller, but it allowed this presentation to be more interactive. The guys from Allround were nice enough to sent out some shirts and books and the audience really enjoyed those. I could barely save one for myself ;-).

I have been busy for months preparing for this seminar. Not full time of course, but it has taken up a lot of my thoughts over the last couple of months. Now what should I do with my time. Maybe prepare for OPP2010 ;-). Don’t even know when and if there will be one. Don’t even know if I will be invited as a presenter again.

I know I learned a lot preparing for the sessions. I know how to use the stuff I was talking about, but I needed more in depth knowledge, which I got from reading documentation, blogs, books etc. So, until next year, I will be doing something different with my spare time. Maybe I can focus on becoming on OCP on PL/SQL.

Het programma voor deze avond:
16:30
"Pipelined table functions" - Patrick Barel
Pipelined table functions offer an ideal convergence of the elegance and simplicity of PL/SQL with the performance of SQL. Complex data transformations are effortless to develop and support with PL/SQL, yet to achieve high-performance data processing, we often resort to set-based SQL solutions. Pipelined functions bridge the gap between the two methods effortlessly, but they also have some unique performance features of their own, making them a superb performance optimization tool.

18:00
diner: Chinees

Na diner (rond 19:00):
"SQL Holmes: The Case of the Missing Performance" - Alex Nuijten
During this presentation, a case study is unfolded to reveal the true cause of a slow performing query. Did the database just "have a bad day"? Was the evil DBA to blame? The PL/SQL developer who didn’t get enough coffee? Or was it the application sending the "wrong" query in the first place?
In this classic "whodunnit" you will take a tour past the crime scene. Investigate the query, use the tools of the trade and collect all the relevant information. Follow the trail to uncover the truth and nothing but the truth…

20:00
Ter afsluiting:
"Optimizing SQL with Collections" - Patrick Barel
Collections (array-like structures in PL/SQL) are used in two of the most important performance features of PL/SQL: FORALL and BULK COLLECT. This session demonstrates the power of these features and offers in-depth guidance on how to apply them.

The solution is actually rather simple. Using a simple loop. First of all create an index variable of the same type you use to index the collection. It would be kind of silly to declare the collection using a varchar2(10) and then declare an index variable as a varchar2(5). If an index value would be ‘Patrick’ then this wouldn’t fit in the index variable resulting in a error.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Then assign the index variable the value of the first index used. Now start the loop and do the stuff you want with the collection. At the end of the loop assign the next index value to the variable. If you try to read past the end of the collection, then the value of the index will be NULL, which is a great marker to exit the loop.

If you put the exit clause as the first statement in the loop, then there is no reason to check for any items present in the collection. If you want to check how many items are present, then use <collection>.count.

I have created a small script to demonstrate how it can be done:

declare
  subtype index_t is varchar2(10);
  type  birthdates_tt is table of date index by index_t;
  l_birthdates birthdates_tt;
  l_indx index_t;
begin
  -- Fill the table with the birthdates
  l_birthdates('Patrick') := to_date('29-12-1972','DD-MM-YYYY');
  l_birthdates('Dana') := to_date('06-03-1976','DD-MM-YYYY');
  l_birthdates('Quinty') := to_date('18-12-1998','DD-MM-YYYY');
  l_birthdates('Kayleigh') := to_date('19-11-2000','DD-MM-YYYY');
  l_birthdates('Mitchell') := to_date('23-06-2003','DD-MM-YYYY');

  -- make index variable the same as the first from the list
  l_indx := l_birthdates.first;
  -- start a simple loop
  loop
    -- exit the loop when we passed the end
    exit when l_indx is null;
    -- write the value of the index and the value at index in the AA to screen
    dbms_output.put_line(l_indx ||' => '||l_birthdates(l_indx));
    -- select the next index value from the list
    l_indx := l_birthdates.next(l_indx);
  end loop;
end;
/

I hope this information helps in leveraging the power of collections. That is no need to know the index values upfront, because you can traverse the collection at runtime where you don’t know the values of the indexes used. Some of this behavior and more about collections will be part of my presentation about Optimizing SQL with Collections at OPP2009.