Weblog for the AMIS Technology corner
Archive for September, 2005
Death of the End Date – How LAG and LEAD help fight redundancy
Sep 30th
Yesterday just prior to our AMIS Query on ADF Faces I was talking to Toon Koppelaars about quite something else: the end_date column in tables that contain contiguous records with BEGIN_DATE and END_DATE where there is no overlap and there are no gaps. Every BEGIN_DATE is the day after the END_DATE of the previous record and every END_DATE is the day before the BEGIN_DATE of the next record. Except of course for the first and last record. We agreed that in this situation, the END_DATE is really redundant information: it is always available from the next record. Situations where you will find this type of record include product price history, resume entries, inventory records, allocation data etc.
You could enter a debate whether END_DATE is or is not a property of the record. When you do your ER-modelling, would you include the end date as an attribute? And if you do, is that because you are implicitly taking into account the problems you may have if you have to derive the END_DATE from the BEGIN_DATE of a next record? It is a bit like building to fences between two adjacent gardens: once fence can clearly mark the border of both gardens, but somehow – lack of trust in your neighbour’s intentions? – you decide to have two coinciding fences.
Is it a problem if you have this END_DATE column where it is really only redundant or at least denormalized information?Well, you have to go through some additional steps: Read the rest of this entry »
Small introduction to SQL*Net debugging [client side]
Sep 29th
Say it’s that day again. You try to connect to a (new) database and once again you are facing SQL*Net problems.
C:/oracle/product/10.1.0/client/BIN>sqlplus /nolog SQL*Plus: Release 10.1.0.4.0 - Production on Thu Sep 29 10:37:15 2005 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn scott/tiger@lab.amis.nl ERROR: ORA-12154: TNS:could not resolve the connect identifier specified
Blast!.
What can you do?
Once in a while i get a lot of these SQL*Net problems (network changes, etc, the wrong weather type). So to help you (and me), here i will you give you my grey-matter checklist 
(it’s not complete but should help you to resolve 80% of the problems)
Read the rest of this entry »
Jdbc, Clobs and Strings
Sep 29th
Clob handling in jdbc can be a tedious job, especially because it required the use of Oracle specific extensions. With the 10g release this has been simplified and Clobs can now be handled using the standard apis. It’s described in this document.
Especially the SetBigStringTryClob connection property comes in handy. For example, it’s used in the Jira Oracle 10g configuration of the jndi DataSource (connectionProperties=”SetBigStringTryClob=true”).
NetBeans 5.0 BETA released
Sep 29th
The NetBeans team announced today the release of NetBeans 5.0 BETA. To download it, visit the NetBeans website, click on Downloads in the menu bar at the top of the page and select “NetBeans IDE 5.0 BETA download”. Internal AMIS employees can install it from the Data share. Please contact me for the location of the installer.
Summary results for all dates, including the ones that I do not have data for – example of using Partition Outer Join – Oracle10g SQL Feature
Sep 29th
Trying to make myself useful by answering some of the questions on the OTN SQL and PL/SQL Forum, I came across this question: Group Above Report problem…not able to display absent rows in date range. The question could be translated to:
I am trying to aggregate records by date and I want results for every date in a certain range. However, I am missing some dates – because there is no data at all for these dates.
I was hooked, since it is a common situation. Of course, you cannot expect the database to return a result for a certain date if there is no data for that date. That is logical. So instead of querying for all records where date between start_of_date_range and end_of_date range – which will only give you agregation results for all the dates that are actually represented in your base set, you need to be more explicit about the dates you are actually interested in. Read the rest of this entry »
Oracle Designer and Oracle SCM – Last Date Modified and querying in the ROB
Sep 28th
Today I decided to write a small package, an extension, to the Repository Object Browser for Oracle 10g (or 9i) Designer and Oracle 10g SCM. This extension will provide something like a Dashboard, that will show a quick overview of the most recent events in our Repository or Workarea, depending on the scope you select. Events are currently defined as: Check In, Check Out, Change of Object. You can regard this extension as a tailor made application of the functionality already offered by the Repository Search facility.
The Repository Search facility relies on the column DATE_CHANGED in the table I$SDD_OBJECT_VERSIONS to find all objects changed on or after a certain date – or before a certain date of course. However, I just noticed the following: Read the rest of this entry »
Another Pop-Quiz: Whose VPD policy is used when executing SQL in a (definer rights) package?
Sep 27th
Virtual Private Database does not let me go. Today I had another burning question to which I could not see the immediate logical answer. I know that when a Policy has been set up for a table and I execute a query against that table the Policy Function will return a predicate that is added to my own where-clause to further restrain my result set. Depending on who the current user is, this predicate may vary.
My question is: when I embed this query inside a PL/SQL package that has been created as Definer Rights, the default setting, and I invoke the procedure that contains the query, will the VPD predicate that gets applied be the one set up for me – the current USER – or the owner of the package? We know that normally the Definer Rights package is executed under the privileges and name resolution of the Definer or Owner of the package. So what will it be? Read the rest of this entry »
Returning a Ref Cursor based on a PL/SQL Collection
Sep 27th
The question on the OTN Forum was: How do i add rows to a pl/sql table and then use the table to open a ref cursor.. Somehow I was a little intrigued and decided to try to formulate a reply. The first part of the question, although asked in a somewhat unclear way, was obvious. The second part was more interesting. What I came up with – using the TABLE operator – was the following: Read the rest of this entry »
Good Citizenship – Have Client Applications register themselves with the database
Sep 27th
Years ago I have read about the package dbms_application_info for the first time. This is a standard supplied package, shipped with every Oracle Database. It can be used to set information about the client application currently using a certain database connection or session. It sounded nice, but I more or less ignored it, not being convinced of its practical value.
Things have changed for me. Perhaps I have matured as IT professional, perhaps I have thought this through more carefully or perhaps my recent experience on a project where we are migrating a WebForms 6.0 application – generated using Designer 6.0 and Headstart – to Designer 10g, WebForms 10g and Headstart 10g. We encountered at least two excellent reasons for having each of our modules register themselves with the database Read the rest of this entry »
Debugging PL/SQL with DBMS_DEBUG
Sep 27th
A very good instruction on the programmatic interface to Orace’s PL/SQL debug facilities. If you do not happen to have a GUI PL/SQL debugger – such as in JDeveloper or PL/SQL Developer or TOAD – or you are planning on building your own, you will find this article most useful: Debugging PL/SQL with DBMS_DEBUG. It takes you on a step by step development of a package that provides an easy interface, based on DBMS_DEBUG, for doing debugging. Note: this allows for example to run PL/SQL in one SQL*PLus session while debugging in a second SQL*Plus session.
