Script – Disable FK Constraints Referencing a Table

Below is an Oracle SQL script to generate DISABLE and ENABLE commands for all foreign key constraints referencing a table. For more info, see Finding table dependencies (foreign key constraints) in Oracle – walking the dependency tree.

table_dep.sql

REM ###########################################################
REM Generates enable and disable constrains for all tables
REM with FK dependencies on the specified table.
REM USAGE: table_dep <owner> <name>
REM ###########################################################

SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF

COLUMN cmdpar1 NEW_VALUE table_owner NOPRINT
COLUMN cmdpar2 NEW_VALUE table_name NOPRINT

SET TERMOUT OFF
SELECT 
 UPPER( '&1' ) cmdpar1, 
 UPPER( '&2' ) cmdpar2
FROM dual;
SET TERMOUT ON

PROMPT #########################################
PROMPT DISABLE SCRIPTS
PROMPT #########################################
SELECT DISTINCT 'ALTER TABLE ' || c.owner || '.' || c.table_name || ' DISABLE CONSTRAINT ' || c.constraint_name || ';'
FROM dba_constraints r, dba_constraints c
WHERE r.owner = c.r_owner
 AND r.constraint_name = c.r_constraint_name
START WITH r.owner = '&TABLE_OWNER' AND r.table_name = '&TABLE_NAME'
CONNECT BY NOCYCLE r.owner = PRIOR c.owner
 AND r.table_name = PRIOR c.table_name;
 
PROMPT
PROMPT
 
PROMPT #########################################
PROMPT ENABLE SCRIPTS
PROMPT #########################################
SELECT DISTINCT 'ALTER TABLE ' || c.owner || '.' || c.table_name || ' ENABLE CONSTRAINT ' || c.constraint_name || ';'
FROM dba_constraints r, dba_constraints c
WHERE r.owner = c.r_owner
 AND r.constraint_name = c.r_constraint_name
START WITH r.owner = '&TABLE_OWNER' AND r.table_name = '&TABLE_NAME'
CONNECT BY NOCYCLE r.owner = PRIOR c.owner
 AND r.table_name = PRIOR c.table_name;


SET FEEDBACK ON
SET HEADING ON
SET VERIFY ON

UNDEFINE &table_owner
UNDEFINE &table_name

Oracle PL/SQL – Exceptions and Native Dynamic SQL

EXECUTE IMMEDIATE statements act as separate sessions, so it is important to note that exception propagation from these statements also follows the same model. When an exception propagates beyond an EXECUTE IMMEDIATE entry point, a rollback is incurred. This may come as a surprise since normal procedure calls do not rollback, leaving transaction control in the hands of the programmer. Here is an example to demonstrate. First, some support objects:

SQL> CREATE TABLE test_tab
  2  (
  3    id NUMBER
  4  );

Table created.


SQL> CREATE OR REPLACE
  2  PROCEDURE error_proc
  3  IS
  4  BEGIN
  5
  6    INSERT INTO test_tab ( id )
  7    VALUES ( 1 );
  8
  9    RAISE_APPLICATION_ERROR( -20001, 'BOOM!' );
 10
 11  END;
 12  /

Procedure created.

Now, an example of exception propagation during a normal procedure call. Notice that, although the exception is thrown, the executed DML remains intact.

SQL> SELECT *
  2  FROM test_tab;

no rows selected


SQL> BEGIN
  2
  3    error_proc;
  4
  5  EXCEPTION
  6    WHEN OTHERS THEN
  7      NULL;
  8  END;
  9  /

PL/SQL procedure successfully completed.


SQL> SELECT *
  2  FROM test_tab;

        ID
----------
         1


SQL> ROLLBACK;

Rollback complete.

Finally, an almost identical example using Dynamic SQL. Notice that this time the DML is lost to a rollback incurred by the EXECUTE IMMEDIATE.

SQL> SELECT *
  2  FROM test_tab;

no rows selected


SQL> BEGIN
  2
  3    EXECUTE IMMEDIATE 'BEGIN error_proc; END';
  4
  5  EXCEPTION
  6    WHEN OTHERS THEN
  7      NULL;
  8  END;
  9  /

PL/SQL procedure successfully completed.


SQL> SELECT *
  2  FROM test_tab;

no rows selected

Oracle – Find Blocking Sessions

Blocking sessions can occur when two transactions try to update the same set of data. The first transaction will attain a lock on the data and will become the “blocking” transaction. If a second transaction attempts to update that same data, it will not be able to complete until the first transaction terminates (via a COMMIT or ROLLBACK). Below is a simple example of such a scenario:
Transaction 1

SQL> DESC test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VALUE                                              VARCHAR2(30)

SQL> UPDATE test SET value = 'VALUE 1';

1 row updated.

Notice the lack of commit. This keeps the transaction open.

Transaction 2

SQL> UPDATE test SET value = 'VALUE 2';

Notice that this command does not return. It is blocked. Transaction 1 will either need to complete or be killed in order to allow transaction 2 to complete.

There are a number of different queries that will help find blocking sessions. Depending on the type of underlying block, only some of these will return the session that needs to be killed. Here are some of the common ones I use:

SELECT
  'ALTER SYSTEM KILL SESSION ''' || s_blocking.sid || ',' || s_blocking.serial# || ''';' kill_command,
  s_blocked.username blocked_username,
  s_blocked.osuser blocked_osuser,
  s_blocked.machine blocked_machine,
  SUBSTR( sql_blocked.sql_text, 1, 50 ) blocked_sql,
  s_blocking.username blocking_username,
  s_blocking.osuser blocking_osuser,
  s_blocking.machine blocking_machine,
  SUBSTR( sql_blocking.sql_text, 1, 50 ) blocking_sql
FROM v$session s_blocked, v$session s_blocking, v$sql sql_blocked, v$sql sql_blocking
WHERE s_blocked.blocking_session = s_blocking.sid
  AND s_blocked.sql_id = sql_blocked.sql_id(+)
  AND s_blocking.sql_id = sql_blocking.sql_id(+)
  AND s_blocked.blocking_session IS NOT NULL;
SELECT 
  'ALTER SYSTEM KILL SESSION ''' || s_blocking.sid || ',' || s_blocking.serial# || ''';' kill_command,
  s_blocked.username blocked_username,
  s_blocked.osuser blocked_osuser,
  s_blocked.machine blocked_machine,
  SUBSTR( sql_blocked.sql_text, 1, 50 ) blocked_sql,
  s_blocking.username blocking_username,
  s_blocking.osuser blocking_osuser,
  s_blocking.machine blocking_machine,
  SUBSTR( sql_blocking.sql_text, 1, 50 ) blocking_sql
FROM v$lock l_blocking, v$lock l_blocked, v$session s_blocking, v$session s_blocked, v$sql sql_blocking, v$sql sql_blocked
WHERE l_blocking.sid = s_blocking.sid
  AND l_blocked.sid = s_blocked.sid
  AND s_blocking.sql_id = sql_blocking.sql_id(+)
  AND s_blocked.sql_id = sql_blocked.sql_id(+)
  AND l_blocking.block = 1 
  AND l_blocked.request > 0
  AND l_blocking.id1 = l_blocked.id1
  AND l_blocking.id2 = l_blocked.id2;

This one is interesting as it helped me find a session preventing a package from compiling. I could not get any “standard” blocking session queries to return. The root issue was a job that was hung and then killed using DBMS_SCHEDULER.DROP_JOB with the FORCE parameter set to true. The job was no longer present in the job views, but the cache pin remained. This query is adapted from The Oracle Community.

SELECT
  'ALTER SYSTEM KILL SESSION ''' || s_blocking.sid || ',' || s_blocking.serial# || ''';' kill_command,
  s_blocking.username blocking_username,
  kl.kglnaobj blocking_objectname,
  CASE kl.kglobtyp
    WHEN 1 THEN 'index'
    WHEN 2 THEN 'table'
    WHEN 3 THEN 'cluster'
    WHEN 4 THEN 'view'
    WHEN 5 THEN 'synonym'
    WHEN 6 THEN 'sequence'
    WHEN 7 THEN 'procedure'
    WHEN 8 THEN 'function'
    WHEN 9 THEN 'package'
    WHEN 11 THEN 'package body'
    WHEN 12 THEN 'trigger'
    ELSE 'others'
  END blocking_objtype,
  s_blocking.program blocking_program,
  sql_blocking.sql_text blocking_sql
FROM x$kglpn kpin, v$session s_blocking, x$kglob kl, v$session_wait seswait, v$sql sql_blocking
WHERE kpin.kglpnuse = s_blocking.saddr
  AND kpin.kglpnhdl = kl.kglhdadr
  AND kl.kglhdadr = seswait.p1raw
  AND seswait.event = 'library cache pin'
  AND s_blocking.sql_id = sql_blocking.sql_id;

Oracle – Update Multiple Columns with a Single Subquery

In Oracle, it is possible to update multiple columns with data from a single subquery. The syntax is only slightly different than the typical update statement. The columns being assigned values must be enclosed by parentheses. An example follows:

SQL> DESC test1_tab;
 Name                            Null?    Type
 ------------------------------- -------- ----------------------------
 VAL1                                     VARCHAR2(20)
 VAL2                                     VARCHAR2(20)
 VAL3                                     VARCHAR2(20)

SQL> SELECT *
  2  FROM test1_tab;

VAL1                 VAL2                 VAL3
-------------------- -------------------- --------------------
A                    VAL1_A               VAL2_A
B                    VAL1_B               VAL2_B
C                    VAL1_C               VAL2_C


SQL> DESC test2_tab;
 Name                            Null?    Type
 ------------------------------- -------- ----------------------------
 VAL1                                     VARCHAR2(20)
 VAL2                                     VARCHAR2(20)
 VAL3                                     VARCHAR2(20)

SQL> SELECT *
  2  FROM test2_tab;

VAL1                 VAL2                 VAL3
-------------------- -------------------- --------------------
A                    TEST2_VAL1_A         TEST2_VAL2_A
B                    TEST2_VAL1_B         TEST2_VAL2_B
C                    TEST2_VAL1_C         TEST2_VAL2_C


SQL> UPDATE test1_tab t1
  2  SET ( val2, val3 ) =
  3    ( SELECT val2, val3 FROM test2_tab t2
  4      WHERE t2.val1 = t1.val1 )
  5  WHERE t1.val1 = 'B';

1 row updated.


SQL> SELECT *
  2  FROM test1_tab;

VAL1                 VAL2                 VAL3
-------------------- -------------------- --------------------
A                    VAL1_A               VAL2_A
B                    TEST2_VAL1_B         TEST2_VAL2_B
C                    VAL1_C               VAL2_C

ORACLE – Using DENSE_RANK and KEEP to simplify multi-layer analytic queries

In queries, I commonly need to extract only the top of a sorted list of results. As time has progressed, ROW_NUMBER() and the OVER() clause have proven essential in writing simple and fast queries to meet this need. Below is an example. In this case, we have an table that captures unit pricing of products by product name and date. We want to write a function that simply returns a text version of the price for display and “NO PRICE FOUND” if nothing is available.

SQL> DESC unit_price
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_NAME                                       VARCHAR2(50)
 PRICE_DATE                                         DATE
 PRICE                                              NUMBER


SQL> SELECT *
  2  FROM unit_price
  3  ORDER BY product_name, price_date;

PRODUCT_NAME                                       PRICE_DAT      PRICE
-------------------------------------------------- --------- ----------
SCREWDRIVER                                        21-DEC-13       6.25
SCREWDRIVER                                        22-DEC-13       6.27
SCREWDRIVER                                        23-DEC-13        6.4
SHOVEL                                             21-DEC-13      10.48
SHOVEL                                             22-DEC-13      10.49
SHOVEL                                             23-DEC-13       10.6

6 rows selected.


SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2
  3    scredriver_price VARCHAR2( 50 );
  4    wrench_price VARCHAR2( 50 );
  5
  6    FUNCTION get_latest_price( prodname VARCHAR2 )
  7    RETURN VARCHAR2
  8    IS
  9      latest_price VARCHAR2( 50 );
 10    BEGIN
 11
 12      SELECT TO_CHAR( price )
 13      INTO latest_price
 14      FROM
 15      (
 16        SELECT
 17          price,
 18          ROW_NUMBER() OVER ( ORDER BY price_date DESC ) rn
 19        FROM unit_price
 20        WHERE product_name = prodname
 21      )
 22      WHERE rn = 1;
 23
 24      RETURN latest_price;
 25
 26    EXCEPTION
 27      WHEN no_data_found THEN
 28        RETURN 'NO PRICE FOUND';
 29    END;
 30
 31  BEGIN
 32
 33    scredriver_price := get_latest_price( 'SCREWDRIVER' );
 34    DBMS_OUTPUT.PUT_LINE( 'SCREWDRIVER PRICE: ' || scredriver_price );
 35
 36    wrench_price := get_latest_price( 'WRENCH' );
 37    DBMS_OUTPUT.PUT_LINE( 'WRENCH PRICE: ' || wrench_price );
 38
 39  END;
 40  /
SCREWDRIVER PRICE: 6.4
WRENCH PRICE: NO PRICE FOUND

PL/SQL procedure successfully completed.

The get_latest_price() function does a good job of quickly extracting the latest available price for the passed product. You might argue that this is the most readable approach to extracting the required data. However, there are two points of get_latest_price() that make it slightly complicated:

1) The query that has two layers: an inner query to extract a list of of prices and their associated row numbers ordered by date, and an outer query to select only the first row of the inner query.
2) Since a NO_DATA_FOUND is an expected condition, an EXCEPTION block is needed to handle the return.

Below is an example of using the KEEP and DENSE_RANK analytic functions to achieve the same behavior with a single-layer query and no EXCEPTION block. Depending on your personal preference, you may not find this version easier to read. I also do not believe there is any cost/benefit in terms of speed.

SQL> DECLARE
  2
  3    scredriver_price VARCHAR2( 50 );
  4    wrench_price VARCHAR2( 50 );
  5
  6    FUNCTION get_latest_price( prodname VARCHAR2 )
  7    RETURN VARCHAR2
  8    IS
  9      latest_price VARCHAR2( 50 );
 10    BEGIN
 11
 12      SELECT NVL( TO_CHAR( MAX( price ) KEEP ( DENSE_RANK FIRST ORDER BY price_date DESC ) ), 'NO PRICE FOUND' )
 13      INTO latest_price
 14      FROM unit_price
 15      WHERE product_name = prodname;
 16
 17      RETURN latest_price;
 18
 19    END;
 20
 21  BEGIN
 22
 23    scredriver_price := get_latest_price( 'SCREWDRIVER' );
 24    DBMS_OUTPUT.PUT_LINE( 'SCREWDRIVER PRICE: ' || scredriver_price );
 25
 26    wrench_price := get_latest_price( 'WRENCH' );
 27    DBMS_OUTPUT.PUT_LINE( 'WRENCH PRICE: ' || wrench_price );
 28
 29  END;
 30  /
SCREWDRIVER PRICE: 6.4
WRENCH PRICE: NO PRICE FOUND

PL/SQL procedure successfully completed.

Optimized approach to addressing LISTAGG() and the ORA-01489 error

Two previous posts have discussed the topic of LISTAGG() and the ORA-01489: result of string concatenation is too long error. This occurs when aggregations of strings exceed the 32767 limit of the VARCHAR2 datatype. The first article, Oracle LISTAGG() — ORA-01489: result of string concatenation is too long, provides a way to “truncate” aggregations being passed to LISTAGG() to show as much data as possible without exceeding the VARCHAR2 size limit. The second, ORA-01489 – Aggregating using CLOBs to handle long strings, provides a user-defined aggregation function that works on CLOBs. This approach addresses the size limitations of VARCHAR2, but suffers heavily in performance due to CLOB processing. This article presents one additional alternative. This user-defined aggregate function is designed to operate on VARCHAR2 datatypes, but relies on CLOBs internally to move beyond the VARCHAR2 size limitation. A significant performance improvement is realized thanks to faster processing and appending of VARCHAR2s when available. CLOBs are only used once the length of data exceeds the VARCHAR2 limit.

SQL> CREATE OR REPLACE
  2  TYPE VarCharAggregator
  3  AS OBJECT
  4  (
  5    separator VARCHAR2( 20 ),     -- Delimeter for the fields
  6    separator_length NUMBER,      -- Length of the delimeter
  7    buffer VARCHAR2( 4000 ),      -- Used to aggregate strings until the maximum VARCHAR2 length is exceeded
  8    buffer_length NUMBER,         -- Current length of the data in buffer
  9    aggregate_clob CLOB,          -- The aggregated CLOB value into which the buffer is moved as it exceeds the VARCHAR2 length
 10    has_clob_data VARCHAR2( 1 ),  -- Flag to indicate whether any data has been moved to the aggregate_clob
 11
 12    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT VarCharAggregator )
 13      RETURN NUMBER,
 14    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT VarCharAggregator, value IN VARCHAR2 )
 15      RETURN NUMBER,
 16    MEMBER FUNCTION ODCIAggregateTerminate( self IN VarCharAggregator, returnValue OUT CLOB, flags IN number )
 17      RETURN NUMBER,
 18    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT VarCharAggregator, ctx2 IN VarCharAggregator )
 19      RETURN NUMBER
 20  );
 21  /

Type created.


SQL> CREATE OR REPLACE
  2  TYPE BODY VarCharAggregator
  3  IS
  4
  5    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT VarCharAggregator )
  6    RETURN NUMBER
  7    IS
  8    BEGIN
  9
 10      sctx := VarCharAggregator( ',', LENGTH( ',' ), '', 0, NULL, 'N' );
 11      DBMS_LOB.CREATETEMPORARY( sctx.aggregate_clob, TRUE, DBMS_LOB.CALL );
 12
 13      RETURN ODCIConst.Success;
 14
 15    END;
 16
 17    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT VarCharAggregator, value IN VARCHAR2 )
 18    RETURN NUMBER
 19    IS
 20      value_length NUMBER;
 21    BEGIN
 22
 23      value_length := LENGTH( value );
 24
 25      -- Move "buffer" into aggregate_clob once appending "value" to "buffer" will exceed the maximum VARCHAR2 length
 26      IF ( self.buffer_length > 0 ) AND ( value_length + self.buffer_length > ( 4000 - self.separator_length ) ) THEN
 27
 28        IF self.has_clob_data = 'Y' THEN
 29          DBMS_LOB.APPEND( self.aggregate_clob, TO_CLOB( self.separator ) );
 30        END IF;
 31
 32        DBMS_LOB.APPEND( self.aggregate_clob, TO_CLOB( self.buffer ) );
 33        self.has_clob_data := 'Y';
 34        self.buffer := '';
 35        self.buffer_length := 0;
 36
 37      END IF;
 38
 39      -- Insert a separator value if we already have data in the buffer
 40      IF self.buffer_length > 0 THEN
 41        self.buffer := self.buffer || self.separator;
 42      END IF;
 43
 44      -- Append "value" to our buffer
 45      self.buffer := self.buffer || value;
 46      self.buffer_length := self.buffer_length + value_length;
 47
 48      RETURN ODCIConst.Success;
 49
 50    END;
 51
 52    MEMBER FUNCTION ODCIAggregateTerminate( self IN VarCharAggregator, returnValue OUT CLOB, flags IN number)
 53    RETURN NUMBER
 54    IS
 55    BEGIN
 56
 57      returnValue := self.aggregate_clob;
 58
 59      IF LENGTH( self.buffer ) > 0 THEN
 60
 61        IF self.has_clob_data = 'Y' THEN
 62          DBMS_LOB.APPEND( returnValue, self.separator );
 63        END IF;
 64
 65        DBMS_LOB.APPEND( returnValue, self.buffer );
 66
 67      END IF;
 68
 69      RETURN ODCIConst.Success;
 70
 71    END;
 72
 73    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT VarCharAggregator, ctx2 IN VarCharAggregator )
 74    RETURN NUMBER
 75    IS
 76    BEGIN
 77
 78      DBMS_LOB.APPEND( self.aggregate_clob, self.separator );
 79      DBMS_LOB.APPEND( self.aggregate_clob, ctx2.aggregate_clob );
 80
 81      RETURN ODCIConst.Success;
 82
 83    END;
 84
 85  END;
 86  /

Type body created.


SQL> CREATE OR REPLACE
  2  FUNCTION VARCHARAGG( input VARCHAR2 )
  3  RETURN CLOB
  4  PARALLEL_ENABLE AGGREGATE
  5  USING VarCharAggregator;
  6  /

Function created.

When aggregating 500 rows, this revised version runs approximately 2.5 times faster than the CLOB-only function of the previous article. When aggregating 1000 rows, it runs approximately twice as fast. An example of usage follows:

SQL> SELECT VARCHARAGG( value )
  2  FROM test_strings;

VARCHARAGG(VALUE)
---------------------------------
test1,test2,test3

Oracle – Writing a BLOB to an operating system file

Earlier, I wrote about reading a BLOB from file in an Oracle PL/SQL procedure. Here is an example of writing a BLOB out to file. Just as before, the user will need access to a DIRECTORY object that points to the OS file destination of choice. See the article on loading BLOBs for more information on this.

The write_blob_to_file procedure in the example below provides the functionality to write to file. It has been wrapped in an anonymous block to demonstrate usage:

SQL> DECLARE
  2
  3    temp_blob BLOB;
  4
  5    PROCEDURE write_blob_to_file( filename VARCHAR2, contents BLOB )
  6    AS
  7      begin_index NUMBER DEFAULT 1;
  8      read_size NUMBER;
  9      buffer RAW( 32767 );
 10      outfile UTL_FILE.FILE_TYPE;
 11    BEGIN
 12
 13      -- Open the output file
 14      outfile := UTL_FILE.FOPEN( 'TEST_DIR', filename, 'WB', 32767 );
 15
 16      -- Loop through the BLOB and keep writing in 32767 chunks
 17      WHILE begin_index <= DBMS_LOB.GETLENGTH( contents )
 18      LOOP
 19
 20        read_size := 32767;
 21        DBMS_LOB.READ( contents, read_size, begin_index, buffer );
 22        begin_index := begin_index + read_size;
 23
 24        UTL_FILE.PUT_RAW( outfile, buffer );
 25
 26      END LOOP;
 27
 28      -- Flush and close the output file
 29      UTL_FILE.FFLUSH( outfile );
 30      UTL_FILE.FCLOSE( outfile );
 31
 32    END;
 33
 34  BEGIN
 35
 36    DBMS_LOB.CREATETEMPORARY( temp_blob, FALSE, DBMS_LOB.SESSION );
 37    DBMS_LOB.WRITE( temp_blob, 7, 1, UTL_RAW.CAST_TO_RAW( 'TESTING' ) );
 38
 39    write_blob_to_file( 'testout.dat', temp_blob );
 40
 41  END;
 42  /

PL/SQL procedure successfully completed.