(0 comments, 28 posts)
This user hasn't shared any profile information
Posts by Andre Crone
On my recent project we have a large package holding constants values. From a design point of view this is a very elegant solution because this way the constants are all defined in a centralized way. But the usage of this package lead to enormous performance issues. This post tells why we had these problems and how we solved them.
An example of a constants package could be:create or replace package my_constants is my_name constant varchar2(30) := 'Andre'; end; /
One of the problems is that package constants cannot be used in Oracle Forms or directly from SQL. So a function was written that dynamically retrieved the constants value from the package. This function can be used in Forms and SQL, (more…)
I wanted to generate an Excel file with data coming from an Oracle database. One of the best ways to do this is to generate an Microsoft Excel XML file. Starting from Office 2003 this XML format is supported. This way you are able to generate a native Excel file in stead of a CSV file.
In a previous post I explained how SQLX can be used to generate XML. With SQLX I was able to generate the Excel XML with only one query. The input data (Excel cell data) is supplied into the query by using a table of objects. This table of objects can be instantiated and filled by any query that supplies the data that you want to have in your Excel file. The advantage of this table of objects is that the Excel data is provided by a parameter making the query generic.
The problem I was facing was simple. You have a table with data. The data in that table should be validated. Invalid rows should be deleted. For each deleted row an entry in a logfile should be created. The validation of the rows could be performed by expensive queries. The simple solution would like:for r_rec in c_badrows loop .... write data to the logfile end loop delete from mytable where ...
This way the query to lookup the bad rows has to be performed twice. (more…)
Yesterday I had a few mind boggling experiences with Oracle Lite. Together with our customer and my colleague Arjaan, I tried to install Oracle Lite. This is a very important project with 300 Oracle Lite snapshots in the future.
Again I experienced what I wrote in my previous post. It’s a nice product with a lot of potential, but the documentation is very poor. The installer is awkward and the packaging tool (wtgpack) is far from production ready. The Oracle Lite database itself is very good both in performance and in its SQL support.This is what we experienced: (more…)
On my current assignment I am working on interfaces between Tibco and Oracle PL/SQL applications. Tibco is a middleware solution that implements a messaging based interface solution between different software systems. For the interfaces I have implemented a generic bridge between Tibco and Oracle. This PL/SQL implementation provides one stored procedure that Tibco can call. This procedure will then determine the message type based on the root element of the incoming XML message. The interface will then lookup, from it’s configuration table, a specific stored procedure that can handle the actual XML message.
The argument of my stored procedure is a CLOB holding the incoming XML message. Then I called this procedure directly from PL/SQL there was no problem. Calling this procedure from Tibco (a Java based application) the call resulted in an "ORA-24805: LOB type mismatch" error. <!–more>This looked strange and google didn’t seem very helpfull in this. The first thing I did with the CLOB data was to create an xmltype variable holding the actual XML data. This way we are able to use all XML DB functionality on the data. The actual call generating the ORA message was:
procedure More >
Today I attended the workshop that Steve Adams is giving in the Netherlands this week. We were with a group of four AMIS employees. The day started in a strange way. We all got a calculator. The reason why became clear during the day. Steve really showed us the internals of the Oracle database. We now know everyting to know about database blocks. We know how the block headers are build up. We know how log headers look like. We know the exact amount of bytes these headers are build of. And of course what these bytes mean.
The calculator was for calculating adresses and offsets that you can find in the Oracle data blocks. My head is still working on all this. But I am very curious about the upcoming two days. This is stuff you will not see (or need) every day. But it will help you when nothing helps you out with problems like performance tuning. We will post a more detailed review of the three days that Steve is in Utrecht soon.
. . . Andre Crone. .
—//—So, what did bring this first day?