Tag: techtip

Multi-table insert

An interesting question came through on AskTom recently.  The requirement was to perform a single pass through a source table, and load the data into three target tables. Now that’s trivially achieved with a multi-table insert, but there was a subtle “twist” on this…

max_enabled_roles – nice touch

Some people get very excited with roles, and quickly the number of roles proliferates to huge numbers…Until this happens ORA-28031: maximum of 148 enabled roles exceeded But in helping someone out on AskTom, I just found a nice touch in 11.2.0.4.  I had granted…

Simple demo of message propagation

If you’re using AQ, then it’s simple to setup simple enqueue and dequeue facilities on your local database to provide all sorts of asynchronous style processing in your applications.  As long as you’re applications are designed and built to handle it, the “fire and…

Performing a large correlated update

We had a question on AskTom recently asking for the best way to update a new column on a very large table.  Four in-place update options are at our disposal: Serial update SQL Parallel DML update SQL DBMS_PARALLEL_EXECUTE PL/SQL batching (as long as sensible…

Code enhancements without changing code

An interesting suggestion came through on AskTom this week, which prompted the following exploration. Let us assume you are populating a table with INSERT INTO EMP SELECT * FROM SOURCE_EMPS and it falls over with ORA-12899: value too large for column To capture the…

BULK COLLECT into nested table

I had an observation come to me last week about PL/SQL and populating nested tables. “The BULK COLLECT into statement cannot be used repeatedly to append results into a table. Instead, it silently truncates the target table each time. “ This is true.  However,…

Truncating a timestamp to the second

We had an interesting AskTom question recently, about why certain TRUNC commands would generate an error when applied to a TIMESTAMP value.  At first glance, TRUNC seems fine. SQL> select trunc(localtimestamp,’MM’) to_the_month from dual; TO_THE_MO ——— 01-OCT-16 1 row selected. SQL> select trunc(localtimestamp,’YYYY’) to_the_year…