Same mistake, three different errors…

Funny thing I had today. Well funny, not when you want to make progress in your work, but still interesting what I discovered when I tried to isolate the problem…I found a way to make the same mistake and have Oracle return three different errors, dependent on the order of columns in an Insert statement. I cannot explain why, so any feedback I am very willing to receive. Continue reading

One way to find that ORA-942 causing missing grant

Hi all,

It has been too long since the last post. It is not that I do not want to, it is just way too busy…

I just came across a small problem where a .NET application written by someone else caused an ugly Stack trace with a ORA-942 (Table or View does not exist) after being moved to the next environment. The error was not there on development, so they asked me what the problem could be. At first I did my compare of grants to the Role I am supposed to use for the application. The problem is that on development the generic application user has direct grants and it is one of these direct grants that cause the problem. Off course I could take away those grants from the generic user, but that would only make the development environment crash, too. I need to find that grant given to the user which is not in the role (there were many, but the application works fine except for one action, so I do not want to provide all those direct grants to the role, I just want to grant that missing grant) and later on see what happens with the application if I remove all the direct grants from the user and only have the role left. Continue reading

How to deal with DBMS_RLS

These last weeks I am trying to get the VPD option (Virtual Private Database) enabled for my application. This time as a developer with no DBA or SYS privileges. I am used to being a DBA for the databases I work on and am used to being able to get the fullest out of the Oracle database. For one, because I think Oracle provides us with a great deal of built-in features that do things more efficiently than we can do ourselves (more efficient, built-in kernel code) and also especially because it seems useless and time-consuming to write code that is already supplied by built-in packages. Now these provided features may not all be as intuitive as we want to or lack some important features, but that is where we can spend some time extending them or making them more user-friendly by creating “wrapper” packages: packages you write that hide the complexity or unfriendliness of a built-in package or limit the features you can use. Because there are quite some packages out there that contain functions or procedures or even parameters that you do not want to give to your developers or end users. Continue reading

Index statistics on complete refreshed materialized views. What is going on?

A few days ago I discovered some (to my feeling) buggy behavior during a COMPLETE refresh of a -partitioned- materialized view. It was an unfortunate situation, because it was discovered the day after going into production. It was not noticed during the weeks of development and pre-production execution. Seems like we have some improvement to do there. It had to do with the index statistics on GLOBAL indexes of a partitioned materialized view. The first time our ETL ran with some new materialized views implemented on them, the ETL did its job and COMPLETE refreshed a  new MV. So far, so good, that was the intention. However, countries started complaining about huge performance problems with some specific reports and another ETL part (only run on Production, which is one of those things I did not know and we need to improve a next time we test a new implementation) was taking hours and hours where it normally would take a few minutes. Continue reading

Oracle 11 upgrade challenges

PLS-00593: default value of parameter “x” must match that of spec

At one customer, we are upgrading from Oracle to Oracle 11g. It was about time. So far, there are not many challenges. The few problems so far have to do with migrating from a Single Byte character set to a Multibyte character set (on purpose) and some PL/SQL mismatches. Today I was looking into some package bodies that did not compile in 11g. The error was a PLS-00593: default value of parameter “x” must match that of spec.  So we seemed to have a few packages with one or more functions in them in which the parameter default values did not match. So I had to straighten that and I wondered: what is the one it must be if I do not want the risk of changing the application logic that may rely on the default values. The one in the specification, or the one in the body? Continue reading

Take advantage of a Function based index in First_rows mode with an ORDER BY on NULLable columns

One of the things I encounter a lot in a Siebel application I support, is the fact that there are lots of queries with ORDER BY’s that take too long to return the first screen full of records OR are executed just to open a Business Component without any filtering. A typical OLTP application mostly wants the first screen full of records to return as fast as possible, so a FIRST_ROWS optimization is the default way. In Siebel, this FIRST_ROWS optimization mode is hard-coded in the DLL, so no way to even circumvent it.

However especially with the default ORDER BY defined on business components (Sort specification) or the ability for users to define their own ORDER BY, you run into situations where this ORDER BY makes the first records to return too slowly, since the whole result set will mostly be materialized, sorted and then the first rows can be returned, but… Continue reading