Top 60 Oracle Blogs

Recent comments

bind variables

Combinations and consequences

Fellow Perth techie Scott Wesley sent me this interesting puzzle recently. He was using the long awaited feature of being (finally) able to assign a sequence value via the DEFAULT clause in a table definition.

The problem was … the sequence was NOT being assigned. And since that column was defined as NOT NULL, his application was breaking. We had some to-and-fro and finally I managed to reduce it down a very small test case, which I’ll build from scratch here.

Video : Oracle REST Data Services (ORDS) : HTTP Headers and ORDS-Specific Bind Variables

In today’s video we do a quick run through how to use HTTP headers and ORDS-specific bind variables in Oracle REST Data Services.

The video is based on this article.

As always you can see all my ORDS articles here.

Generic data models … generic applications … ugh

There’s a hesitation to publish this example, because publishing it may be interpreted as an endorsement of this approach and it certainly isn’t. Over the years there have been plenty of articles describing the long term pain that typically comes from generic data models. Here’s a few to whet your appetite.

Force Cursor Invalidation

Many times it occurs that an inappropriate execution plan is used which was produced by using the current values of bind variables provided at the time of the hard parse. But later on the variables change so much that another execution plan would be required. Unfortunately there is no automatism in 9i and 10g that would spot this fact. Oracle finally resolved this problem in 11g.

The trick is to virtually set the statistics for the object which is involved in the query. What I mean by virtually is that I read the current statistics and store the same statistics back what makes no harm but the side effect is that the cursor is invalidated and hence it will be re-parsed and hopefully this time optimized for the right values of bind variables.

Here is the code:

CREATE OR REPLACE PROCEDURE Invalidate_statistics (
p_ownname VARCHAR2,
p_tabname VARCHAR2
) IS