Close

The 12-Coins Puzzle in PL/SQL

The 12-Coins is a classic balance puzzle where the goal is find a fake coin from one of 12 using only 3 trials on the balance scale. Furthermore, once you find the fake coin you should be able to determine if it is lighter or heavier than the real coins. The solution involves dividing the…

The 8-Queens Puzzle in PL/SQL

Several months ago I used the classic 8-Queens chess puzzle as an illustration of using a little bit of math to determine intractable problems. In that article I teased a code solution for the puzzle but never followed up… until now. There are 92 distinct solutions, including rotations and reflection duplicates. The package’s demo procedure…

Oracle Intervals – Year to Month

An INTERVAL YEAR TO MONTH, can have up to 9 digits of year precision. By default, a column or pl/sql variable will have 2 digits. The month portion of the interval is always limited to 2 digits. The year limits can be changed by specifying (0-9) in the declaration. select INTERVAL ‘123-06’ YEAR(3) TO MONTH…

Oracle Intervals – Day to Second

An INTERVAL DAY TO SECOND, can have up to 9 digits of sub-second precision (nanoseconds.) By default, a column or pl/sql variable will have 6 digits (microseconds.) In addition to the subsecond precision, a default INTERVAL DAY TO SECOND will also be limited to 2 digits in the day counter. These limits can be changed…

Oracle Intervals – Overview

The interval types are often under utilized, even completely unknown to many developers. They can provide a wealth of functionality though when used properly. Within the SQL context there are two types of intervals: INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH. The names indicate the maximum and minimum units of measure. That is,…

Connection Basics

Before you can dig into your data with SQL and PL/SQL you must first get connected to your database. While there are many architectures, tools, and drivers for connecting such as JDBC, ODBC, ROracle, node-oracledb, Oracle Provider for OLE DB, OCI, cx_Oracle, etc. They essentially all boil down to just two methods: either the Oracle…

Date and Timestamp Formats and Structure

Using Dates for dates and the dangers of Implicit conversions As mentioned in previous articles, a common mistake is choosing a data type other than a Date or Timestamp to hold time-based data.  Numeric values like 20120424  or even worse 04242012.  While both of these are human readable as yyyymmdd and mmddyyyy respectively; they have…

Data type quirks in Oracle

In my previous article I stressed the importance of using correct data types. However in some cases you may be forced to choose one type when you really need to use two or more. Also, in some cases, you may think you’re using consistent data types but Oracle will insist on changing them anyway, using…

Using Correct Data Types

One of the fundamental aspects of many programming languages is the notion of data types. SQL and PL/SQL are no exception. This article is not an argument one way or the other on whether strongly-typed languages are superior to weakly-typed, or untyped languages. The argument is moot. If you’re working with an Oracle database, you…

Oracle and the Ends of Time

Contemplating the beginning and ending of time can be a complicated exercise in the mathematics of cosmology. Within the realms of an Oracle database though it’s much easier to get our heads around. By definition within Oracle, the beginning of time is -4712-01-01 00:00:00.The end of time is 9999-12-31 23:59:59.999999999. Thus all of time, past…

CQ Notification deregistration with java

The Oracle Change Notification or Continuous Query Notification functionality is a useful means of triggering events in remote sessions when a transaction commits. Sometimes though a client may set up a registration and then disappear leaving orphaned registrations. Using the DBMS_CHANGE_NOTIFICATION package (documented by it’s synonym DBMS_CQ_NOTIFICATION) these old entries can be removed easily with…

Password paper update

I added an appendix for special user accounts that don’t follow the normal password rules to my paper describing the password hashing and storage. Some users have no hash at all but are instead stored with fixed values. ANONYMOUS – SYS.USER$.PASSWORD has a value of exactly 16 spaces.  The SPARE4 column is NULL.  Since the…

Dynamic SQL Templates

Frequently, when dynamic sql is used it takes a form similar to this: v_sql := ‘select ‘ || CASE WHEN choice1 IN (‘A’, ‘B’) THEN ‘col1, col2’ WHEN choice = ‘C’ THEN ‘col2,col3’ ELSE ‘col4,col5’ END || ‘ from ‘ || CASE WHEN choice2 IN (‘X’, ‘Y’, ‘Z’) THEN ‘table1’ ELSE ‘table2’ END || ‘…

SYS_CONTEXT DB_NAME bug fixed in 12cR2

A few years ago I wrote a small article about finding the various name of a database through different means. One of which was SYS_CONTEXT with the USERENV namespace. At the time (April 2016) the latest version of the Oracle database as 12cR1 where a query like this: select SYS_CONTEXT(‘userenv’,’db_name’) from dual; Would return the…

Bug in JSON_OBJECT with FORMAT JSON

Normally, when syntax is listed as “optional” there should be no functional difference between the inclusion or omission of that syntax. However, with the JSON_OBJECT function, the optional “FORMAT JSON” clause not only changes functionality if you include it, it actually causes the resulting json output to be invalid! This easily verified with a simple…

Extracting APEX Access Control Users

From the APEX Developer’s Guide: When you export an application with the Access Control feature, the application roles, Administrator, Contributor, and Reader, are exported.  However, the users assigned to these roles are not exported. [spelling correction “your”-“you” mine] If the export option won’t put the users into a script for deployment, the obvious question then…

DDL dump of multiple schemas

A few weeks ago I received a question about how to generate DDL for all the tables, procedures, functions, and packages of a set of schemas. Furthermore, the asker wanted each object’s script to be in a distinct file within a directory hierarchy. Specifically, given a base directory, each schema should have its own subdirectory…

Goodbye to another great Collaborate.

Thank you to all that attended my presentations this year both live or in the virtual conference. As promised here are the links to my slides. https://www.dropbox.com/s/d7kjh5rp70ucul0/2019_352_Stuber_ppt.pptx?dl=0 https://www.dropbox.com/s/lqqhb6m8b3vrv0p/2019_354_Stuber_ppt.pptx?dl=0 For session 354, I mentioned expanded versions of some of the dictionary queries. Those maybe found at the following links… https://seanstuber.com/2018/03/08/who-is-using-temp-and-what-are-they-doing/ https://seanstuber.com/2018/03/04/who-is-blocking-whom-and-what-are-they-doing/ Thank you again to all…

Introduction to ASH Data, part 4

What is ASH? AWR and Activity Types Average Active Sessions Graphical Visualizations (this page) Up to this point in my ASH series all of the output was simply rows and columns of data, sometimes augmented with an ascii, text based chart. This time I’ll explore a few different tools to construct better visualizations. One of…

Introduction to ASH Data, part 3

What is ASH? AWR and Activity Types Average Active Sessions (this page) Graphical Visualizations In the first 2 chapters of this series I queried the ASH data with respect to distinct sample times. While this is helpful maximizing the granularity of the analysis, it can sometimes be too much detail, too many data points for…

Using collections to create variable parameter functions.

If you’re familiar with java, c, c# (or, I’m sure other languages as well) then you’ve probably encountered functions that allow a variable number of input parameters without requiring a fixed set of overloading. Java int my_variable_function(String str, int …intlist) c int my_variable_function(char *str, …) c# int my_variable_function(string str, params int[] intlist) PL/SQL does not…

Introduction to ASH Data, part 2

What is ASH? AWR and Activity Types (this page) Average Active Sessions Graphical Visualizations Previously I showed a few queries using (G)V$ACTIVE_SESSION_HISTORY, but only mentioned the DBA_HIST_ACTIVE_SESS_HISTORY view. Here I’ll extend the query patterns into this historical repository. Where V$ACTIVE_SESSION_HISTORY is a once-per-second snapshot of sessions from memory and kept in memory, the DBA_HIST_ACTIVE_SESS_HISTORY data…

Looking Through Holes in Time

While testing some date/time functions today I came across an interesting quirk in Oracle’s Gregorian calendar and different date representations. In the Gregorian calendar, the day after October 4, 1582 is October 15, 1582 (not all countries adopted the calendar immediately, so they have different gaps, but Oracle observes the calendar as it was originally…

Introduction to ASH Data

What is ASH? (this page) AWR and Activity Types Average Active Sessions Graphical Visualizations The Oracle database captures session activity approximately once each second. This data can then be used to identify usage patterns and performance problems. Many performance experts have written very good suites of scripts and tools to query, analyze, and chart the…

Simple Performance Diagnostics in APEX

Recently I received a call that an APEX application was experiencing poor performance.  They asked me to investigate what was happening on the database or in APEX itself. First, I ask if they have any application logs that give some indication of where the problems might be.  The answer came back no.  There was no…

Using MERGE to INSERT overlapping source data

Recently I’ve been working with a data source that generates a new file each day for me to import. That seemed simple enough, I created an external table to read the file and then run an insert statement to load the data into a target table. However, the source files sometimes overlap each other. That…