Search

Top 60 Oracle Blogs

Recent comments

plsql

Arbitrary length addition and subtraction

This one just for fun today. An AskTOM question came in about arbitrary length arithmetic because “NUMBER(38) was not enough”. After some back-and-forth discussions it turned out that the business need under the requirement was managing bit strings. The implementation was currently converting the bits to decimals, hence the need for potentially very large number handling.

The problem was ultimately tackled with using RAW datatypes and holding the bits as raw strings, but I thought it would be interesting to throw together an addition and subtraction facility where the boundaries could exceed NUMBER(38).

So using nested tables, I had some fun with the code below.

Active Data Guard – limitations on ROWTYPE

I had an AskTOM question come in with an issue trying to PL/SQL on an Active Data Guard (ADG) database (which of course is running in read-only mode). The PL/SQL block seems innocuous; it does not DML and yet refuses to run:



SQL> DECLARE
  2    tst_row  dual%ROWTYPE;
  3  BEGIN
  4    null;
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

I did a trace on the code, and made an interesting discovery, and hence the cause of the error. When we reference a ROWTYPE definition, we need a mechanism to refer to and compile against that definition of that type. Hence we temporarily “create” a type definition to handle that. Tracing the above anonymous block, you’ll see the following entries in the trace file

18c versus 19c

I had someone say to me at an event recently: “We’re are going to upgrade to 18c, because 19c is new and is probably less stable”.

Let me sum up that sentiment simply: It’s Wrong Smile

Now, don’t get me wrong. I am not claiming that every Oracle release is perfect, contains zero bugs, never has a regression, will mow your lawn, take your kids to school, clean your house and sort out all the climate change issues in the world.

UTL_FILE_DIR and 18c revisited

A couple of years back (wow…time flies!) I made a video and a post about the de-support of UTL_FILE_DIR in 18c. This was good thing because the number of people opting for “utl_file_dir=*” in their init.ora file, and hence opening themselves up to all sorts of risks seemed to be large! (The post elaborates on this more with an example of erasing your database with a short UTL_FILE script Smile)

Cloning a schema with one line

In the world of DevOps, continuous integration and repeatable test cases, the demand for being able to

  • quickly build a suite of database objects,
  • utilise it for a series of tests,
  • then throw the objects away

has become far more common. This is one of the many great use cases for pluggable databases with all of the powerful cloning facilities available. In particular, now that you can take advantage of pluggable databases without* incurring additional license fees, there are some great opportunities there…but that is the topic for another post.

The definition of proof

One of the pieces of advice that I often see on the ‘net is that undo space is somehow this incredibly precious thing, and as a consequence, one should always keep the amount of uncommitted changes in the database to a small size.

Personally I think that is baloney (Ed-in reality, as an Australian I have a slightly more powerful choice of term, but lets keep things PG-rated </p />
</em></p></div>

    	  	<div class=

Same dog, different leash – functions in SQL

Let’s start with this somewhat odd looking result. I have an inline function that returns a random number between 0 and 20, and I call that for each row in ALL_OBJECTS and then I extract just those rows for which the generated random number is 10. Seems simple enough….but why do I get results for which the value of the second column is most certainly not 10?

SMON_SCN_TIME and ORA-8161? Digging deeper

In the recent versions of the Oracle database, we’ve had the ability to convert between a System Change Number (SCN) and the approximate time to which that SCN pertains. These functions are unsurprisingly called SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN. The only potential misnomer here is that even though the functions are called “timestamp” and return a datatype of timestamp, on most platforms you are going to notice that the granularity doesn’t run down into fractions of seconds


SQL> select scn_to_timestamp(14816563713652) from dual;

SCN_TO_TIMESTAMP(14816563713652)
---------------------------------------------------------
08-JUN-19 02.30.59.000000000 AM

This all looks great until you start poking around too far into the past, and you end up in territory like this:

With and without WITH_PLSQL within a WITH SQL statement

OK, let’s be honest right up front. The motivation for this post is solely to be able to roll out a tongue twisting blog post title Smile. But hopefully there’s some value as well in here for you if you’re hitting the error:

ORA-32034: unsupported use of WITH clause

First some background. A cool little enhancement to the WITH clause came along in 12c that allowed PLSQL functions to be defined within the scope of the executing SQL statement. To see the benefit of this, consider the following example that I have a personal affinity with (given my surname).

Let’s say I’ve allowed mixed-case data in a table that holds names.

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.

https://rodgersnotes.wordpress.com/2010/09/21/muck-massively-unified-code-key-generic-three-table-data-model/