Oracle XE | MacLochlainns Weblog

Sometimes I’m amazed at things that come up. A student wondered why the sequences were incrementing by two when they’re defined to increment by one. It turns out that they were using Oracle APEX to create SQL statements to build a table, constraints, and a auto-numbering sequence trigger. Before executing the code, they’d copy it into their re-runnable script that created their schema.

Here’s an example of code that was generated by APEX for a table:

-- Create the table.
CREATE TABLE onesy
( onesy_id NUMBER
, onesy_text VARCHAR2(20));
 
-- Add the primary key constraint.
ALTER TABLE onesy ADD CONSTRAINT onesy_seq PRIMARY KEY (onesy_id);
 
-- Add a database trigger.
CREATE OR REPLACE TRIGGER onesy_trg 
BEFORE INSERT ON onesy
FOR EACH ROW
BEGIN
  :NEW.onesy_id := onesy_s1.NEXTVAL;
END;
/

This works in APEX because it doesn’t create forms that call onesy_seq.NEXTVAL but they did create that logic in their forms. The INSERT statement would look like:

INSERT INTO onesy VALUES (onesy_seq.NEXTVAL, 'One');

Therefore, the INSERT statement incremented the trigger by one and the database trigger incremented it by one. The result is that sequences two-step, which isn’t effective or the desired behavior.

After I explained the two-step problem, they asked if they could only call the trigger when the primary key value was null. While they could do that like this:

-- Add a database trigger.
CREATE OR REPLACE TRIGGER onesy_trg 
BEFORE INSERT ON onesy
FOR EACH ROW
WHEN (NEW.one_id IS NULL)
BEGIN
  :NEW.onesy_id := onesy_s1.NEXTVAL;
END;
/

The problem is that this type of trigger doesn’t stop other possible problems. While it prevents two-stepping the sequence, it doesn’t prevent two other errors.

One possible error that isn’t managed in this scenario is the use of numeric literals beyond the next value of the sequence. It writes the row but eventually the sequence catches up to the higher value and a production insert would fail. It would raise the following exception.

INSERT INTO onesy (onesy_text) VALUES ('Eight')
*
ERROR at line 1:
ORA-00001: UNIQUE CONSTRAINT (STUDENT.PK_ONE) violated

Another possible error can occur when you use a bulk insert operation. Assuming you’re inserting 500 rows at a go, you query the maximum value of the onesy_id column and then create an array of 500 numbers. Then, you perform the bulk INSERT statement. The next call to the trigger would raise another ORA-00001 unique constraint error.

Yes, you could lock the table before you perform the bulk operation. After the bulk operation you would drop and recreate the sequence with a new value equal to the maximum value in the column, and unlock the table. This limits concurrency of operation. You could treat these bulk operations as off-line transactions (batch processing) and it would work nicely.

You could also implement a policy that no bulk operations provide generated column values that link to a sequence. Beyond it’s impracticality to manage, that type of restriction does limit the benefit of bulk operations.

The students wanted a solution. So, here’s my take on a trigger that prevents collision with values above the next sequence value. It assumes that bulk operations will be performed as batch processing where you can disable this trigger.

This trigger disallows numeric literals, logs any attempts to use them, and stops processing when an INSERT statement tries to use anything other than the .NEXTVAL of the sequence. It will only work in an Oracle Database 11g database because the context of using a sequence_name.CURRVAL in a comparison isn’t supported in prior releases. The onesy table is renamed the one table in the example.

-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE msg_s1;
 
CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
DECLARE
 
  /* Define an autonomous transaction scope to the trigger. */
  PRAGMA AUTONOMOUS_TRANSACTION;
 
  /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence
     is called before a .NEXTVAL for the same sequence in the same session. */
  no_sequence_in_scope EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002);
 
BEGIN
 
  /* Check if surrogate key is provided and the sequence not out of transaction scope. */
  IF :NEW.one_id IS NOT NULL AND NOT :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Write message when sequence value is a numeric literal not a sequence
       generated value but a one_s1.NEXTVAL was previously called in the session.
       Commit after write or information is lost because it throws an user-defined
       exception. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing exception. */
    RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:NEW.one_id||']['||:NEW.one_text||'].');
 
  ELSIF :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */
    NULL;
 
  ELSE
 
    /* Increment sequence and assign a value when one isn't provided, like a NULL value. */
    :NEW.one_id := one_s1.NEXTVAL;
 
  END IF;
 
EXCEPTION
 
  /* Handle a no sequence in scope error. */
  WHEN no_sequence_in_scope THEN
 
    /* Write and commit log message for error. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing an exception. */
    RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:NEW.one_id||'].');
 
END;
/

-- Create a sequence for table ONE that starts with 1 and increments by 1. CREATE SEQUENCE msg_s1; CREATE OR REPLACE TRIGGER one_t1 BEFORE INSERT ON one FOR EACH ROW DECLARE /* Define an autonomous transaction scope to the trigger. */ PRAGMA AUTONOMOUS_TRANSACTION; /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence is called before a .NEXTVAL for the same sequence in the same session. */ no_sequence_in_scope EXCEPTION; PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002); BEGIN /* Check if surrogate key is provided and the sequence not out of transaction scope. */ IF :new.one_id IS NOT NULL AND NOT :new.one_id = one_s1.CURRVAL THEN /* Write message when sequence value is a numeric literal not a sequence generated value but a one_s1.NEXTVAL was previously called in the session. Commit after write or information is lost because it throws an user-defined exception. */ INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:new.one_id||']['||:new.one_text||'].'); COMMIT; /* Stop processing by throwing exception. */ RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:new.one_id||']['||:new.one_text||'].'); ELSIF :new.one_id = one_s1.CURRVAL THEN /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */ NULL; ELSE /* Increment sequence and assign a value when one isn't provided, like a NULL value. */ :new.one_id := one_s1.NEXTVAL; END IF; EXCEPTION /* Handle a no sequence in scope error. */ WHEN no_sequence_in_scope THEN /* Write and commit log message for error. */ INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:new.one_id||']['||:new.one_text||'].'); COMMIT; /* Stop processing by throwing an exception. */ RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:new.one_id||'].'); END; /

Since anonymous transaction triggers are tricky, it’s important to note that the message writing requires two commits. One before raising the exception when the .CURRVAL is in session scope and another in the exception handler before raising the error. If you forget those COMMIT statements, this is a sample of the error stack:

INSERT INTO one VALUES (one_s1.nextval,'Six')
                        *
ERROR at line 1:
ORA-06519: active autonomous TRANSACTION detected AND rolled back
ORA-06512: at "STUDENT.ONE_T1", line 31
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'

The trigger raises the following type of exceptions for an offending INSERT statement. The first occurs when the sequence is valid in the session scope, like:

DECLARE
*
ERROR at line 1:
ORA-20001: NOT a SEQUENCE generated VALUE [1].
ORA-06512: at "STUDENT.ONE_T1", line 48
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'
ORA-06512: at line 15

The second occurs when the sequence isn’t valid in the session scope.

INSERT INTO one VALUES (401,'Nine')
            *
ERROR at line 1:
ORA-20002: ID provided BY calling scope IS NOT NEXT SEQUENCE VALUE [401][Nine].
ORA-06512: at "STUDENT.ONE_T1", line 24
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'

A value that’s below the current high-watermark of the sequence raises a unique constraint, like this:

INSERT INTO one VALUES (1,'Eight')
*
ERROR at line 1:
ORA-00001: UNIQUE CONSTRAINT (STUDENT.PK_ONE) violated

The following is a script with all the necessary code components to test the example.

Oracle Script ↓

Unfold this if you’d like to see the complete test case.

-- Conditionally drop tables and sequences.
BEGIN
  FOR i IN (SELECT object_name, object_type FROM user_objects WHERE object_name IN ('ONE','ONE_S1','MSG','MSG_S1')) LOOP
    IF i.object_name = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';  
    ELSE
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;  
    END IF;
  END LOOP;
END;
/
 
-- Create a table ONE.
CREATE TABLE one (one_id NUMBER CONSTRAINT pk_one PRIMARY KEY, one_text VARCHAR2(20));
 
-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE one_s1;
 
-- Create a table MSG.
CREATE TABLE msg (msg_id NUMBER, msg_text VARCHAR2(80));
 
-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE msg_s1;
 
-- Create or replace trigger.
CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
DECLARE
 
  /* Define an autonomous transaction scope to the trigger. */
  PRAGMA AUTONOMOUS_TRANSACTION;
 
  /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence
     is called before a .NEXTVAL for the same sequence in the same session. */
  no_sequence_in_scope EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002);
 
BEGIN
 
  /* Check if surrogate key is provided and the sequence not out of transaction scope. */
  IF :NEW.one_id IS NOT NULL AND NOT :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Write message when sequence value is a numeric literal not a sequence
       generated value but a one_s1.NEXTVAL was previously called in the session.
       Commit after write or information is lost because it throws an user-defined
       exception. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing exception. */
    RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:NEW.one_id||']['||:NEW.one_text||'].');
 
  ELSIF :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */
    NULL;
 
  ELSE
 
    /* Increment sequence and assign a value when one isn't provided, like a NULL value. */
    :NEW.one_id := one_s1.NEXTVAL;
 
  END IF;
 
EXCEPTION
 
  /* Handle a no sequence in scope error. */
  WHEN no_sequence_in_scope THEN
 
    /* Write and commit log message for error. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing an exception. */
    RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:NEW.one_id||'].');
 
END;
/
 
-- Define a schema collection of strings.
CREATE OR REPLACE TYPE one_index AS TABLE OF VARCHAR2(20);
/
 
-- Define a schema collection of strings.
CREATE OR REPLACE TYPE one_list AS TABLE OF VARCHAR2(20);
/
 
 
-- Anonymous block bulk testing program.
DECLARE
  -- Declare two scalar collection variables.
  lv_index ONE_INDEX := one_index();
  lv_list  ONE_LIST := one_list('One','Two','Three','Four','Five');
BEGIN
 
  -- Initialize the collection of the ONE_INDEX collection.
  lv_index.EXTEND(lv_list.COUNT);
  FOR i IN lv_list.FIRST..lv_list.LAST LOOP
    dbms_output.put_line('['||i||']['||lv_list(i)||']');
    lv_index(i) := i;
  END LOOP;
 
  -- Perform a trivial example of a bulk insert.  
  FORALL i IN lv_list.FIRST..lv_list.LAST
    INSERT INTO one VALUES (lv_index(i),lv_list(i));
 
END;
/
 
-- Testing INSERT statements until sequence value increments beyond bulk insert values.
INSERT INTO one VALUES (6,'Six');
INSERT INTO one VALUES (one_s1.nextval,'Seven');
INSERT INTO one VALUES (1,'Eight');
INSERT INTO one VALUES (401,'Nine');
INSERT INTO one VALUES (NULL,'Ten');
 
COLUMN msg_id   FORMAT 999 HEADING "Msg|ID"
COLUMN msg_text FORMAT A72 HEADING "Msg Text"
 
-- 
SELECT * FROM one;
SELECT * FROM msg;
 
-- Anonymous block bulk testing program.
DECLARE
  -- Declare two scalar collection variables.
  lv_list  ONE_LIST := one_list('Twenty-one','Twenty-two','Twenty-three','Twenty-four','Twenty-five');
BEGIN
 
  -- Perform a trivial example of a bulk insert.  
  FORALL i IN lv_list.FIRST..lv_list.LAST
    INSERT INTO one VALUES (NULL,lv_list(i));
 
END;
/
 
-- Query tables.
SELECT * FROM one;
SELECT * FROM msg;

-- Conditionally drop tables and sequences. BEGIN FOR i IN (SELECT object_name, object_type FROM user_objects WHERE object_name IN ('ONE','ONE_S1','MSG','MSG_S1')) LOOP IF i.object_name = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END IF; END LOOP; END; / -- Create a table ONE. CREATE TABLE one (one_id NUMBER CONSTRAINT pk_one PRIMARY KEY, one_text VARCHAR2(20)); -- Create a sequence for table ONE that starts with 1 and increments by 1. CREATE SEQUENCE one_s1; -- Create a table MSG. CREATE TABLE msg (msg_id NUMBER, msg_text VARCHAR2(80)); -- Create a sequence for table ONE that starts with 1 and increments by 1. CREATE SEQUENCE msg_s1; -- Create or replace trigger. CREATE OR REPLACE TRIGGER one_t1 BEFORE INSERT ON one FOR EACH ROW DECLARE /* Define an autonomous transaction scope to the trigger. */ PRAGMA AUTONOMOUS_TRANSACTION; /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence is called before a .NEXTVAL for the same sequence in the same session. */ no_sequence_in_scope EXCEPTION; PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002); BEGIN /* Check if surrogate key is provided and the sequence not out of transaction scope. */ IF :new.one_id IS NOT NULL AND NOT :new.one_id = one_s1.CURRVAL THEN /* Write message when sequence value is a numeric literal not a sequence generated value but a one_s1.NEXTVAL was previously called in the session. Commit after write or information is lost because it throws an user-defined exception. */ INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:new.one_id||']['||:new.one_text||'].'); COMMIT; /* Stop processing by throwing exception. */ RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:new.one_id||']['||:new.one_text||'].'); ELSIF :new.one_id = one_s1.CURRVAL THEN /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */ NULL; ELSE /* Increment sequence and assign a value when one isn't provided, like a NULL value. */ :new.one_id := one_s1.NEXTVAL; END IF; EXCEPTION /* Handle a no sequence in scope error. */ WHEN no_sequence_in_scope THEN /* Write and commit log message for error. */ INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:new.one_id||']['||:new.one_text||'].'); COMMIT; /* Stop processing by throwing an exception. */ RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:new.one_id||'].'); END; / -- Define a schema collection of strings. CREATE OR REPLACE TYPE one_index AS TABLE OF VARCHAR2(20); / -- Define a schema collection of strings. CREATE OR REPLACE TYPE one_list AS TABLE OF VARCHAR2(20); / -- Anonymous block bulk testing program. DECLARE -- Declare two scalar collection variables. lv_index ONE_INDEX := one_index(); lv_list ONE_LIST := one_list('One','Two','Three','Four','Five'); BEGIN -- Initialize the collection of the ONE_INDEX collection. lv_index.EXTEND(lv_list.COUNT); FOR i IN lv_list.FIRST..lv_list.LAST LOOP dbms_output.put_line('['||i||']['||lv_list(i)||']'); lv_index(i) := i; END LOOP; -- Perform a trivial example of a bulk insert. FORALL i IN lv_list.FIRST..lv_list.LAST INSERT INTO one VALUES (lv_index(i),lv_list(i)); END; / -- Testing INSERT statements until sequence value increments beyond bulk insert values. INSERT INTO one VALUES (6,'Six'); INSERT INTO one VALUES (one_s1.nextval,'Seven'); INSERT INTO one VALUES (1,'Eight'); INSERT INTO one VALUES (401,'Nine'); INSERT INTO one VALUES (NULL,'Ten'); COLUMN msg_id FORMAT 999 HEADING "Msg|ID" COLUMN msg_text FORMAT A72 HEADING "Msg Text" -- SELECT * FROM one; SELECT * FROM msg; -- Anonymous block bulk testing program. DECLARE -- Declare two scalar collection variables. lv_list ONE_LIST := one_list('Twenty-one','Twenty-two','Twenty-three','Twenty-four','Twenty-five'); BEGIN -- Perform a trivial example of a bulk insert. FORALL i IN lv_list.FIRST..lv_list.LAST INSERT INTO one VALUES (null,lv_list(i)); END; / -- Query tables. SELECT * FROM one; SELECT * FROM msg;

If I’ve fat fingered any typing or made logical errors, please let me know.

Somebody posted a comment inquiring about the release of Oracle 11g XE. They felt it had been delayed. As far as I know, the last word on that came from Andy Mendelsohn last October, as published in this InfoWorld article.

The Oracle Database 11g XE is released and the download link is here.

When Andy Mendelsohn originally announced Oracle 11g XE in an interview with the NY Times, he clearly said it would be based on the terminal release of Oracle 11g. I also suspect it’ll include APEX 4.0, which was recently released. Maybe we’ll see the release at or near Oracle Open World 2010.

I don’t endorse the negative remarks in the InfoWorld article about why Oracle 11g XE isn’t released. While I have no direct knowledge of the forthcoming release, I think there’s an alternative explanation for any delay. Based on my involvement with the Oracle 10g XE release, I believe Oracle wants a rock solid starter version. At least, that was a major concern when they released Oracle 10g XE.

My students wanted some additional examples on correlated update statements. I’ve been struggling with what the right way may be to illustrate them.

Correlated subqueries are a hard concept to explain to those new to SQL. While correlated update statements seem impossibly obscure to many or inordinately complex. New SQL developers often flee to the comfort of procedural programs when it comes to update statements.

This uses my video store data model. It’s a correlated update statement to clean up potential corrupt data. More or less something a DBA might run to ensure a business rule hasn’t been violated over time. It checks for the correct foreign key value in a table when a dependent table contains one or more than one row of data.

The aqua-green box highlights a subquery that aggregates foreign key columns and groups the result with the foreign key value. The results from this subquery become a run-time view or derived table. The result set is a foreign key value and a substitute string literal value for each row in the contact table. These results correlate to the update statement’s rows based on the input parameter. The input parameter is a column from each updated row.

A unique key (or check constraint) exists on the combination of the common_lookup_table, common_lookup_column, and common_lookup_type columns. This ensures that only one row is returned and assigned to the member_type column in the member table. The update statement naturally works in either Oracle or MySQL without any porting changes.

Correlated Update Statement ↓

Expand this section to see the clear text for the foregoing image.

UPDATE member m
SET    member_type =
 (SELECT common_lookup_id
  FROM   common_lookup
  WHERE  common_lookup_table = 'MEMBER'
  AND    common_lookup_column = 'MEMBER_TYPE'           
  AND    common_lookup_type =
          (SELECT  dt.member_type
           FROM   (SELECT   c.member_id
                   ,        CASE
                              WHEN COUNT(c.member_id) > 1 THEN 'GROUP'
                              ELSE 'INDIVIDUAL'
                            END AS member_type
                   FROM     contact c
                   GROUP BY c.member_id) dt
           WHERE   dt.member_id = m.member_id));

While this type of solution is powerful in its own right, I thought it might be interesting to see their procedural equivalents. These correlated subqueries run for each row returned by the master query (or outermost statement). Therefore, they act like functions.

Procedural equivalents (or user-defined functions) simplify the update statement like so:

UPDATE member m
SET    member_type = get_member_type(m.member_id);

If you’re interested in seeing how you would implement this solution in a user-defined function, just expand the dropdown that interest you.

Oracle User-Defined Function (UDF) ↓

Expand this section to see how to map this logic to a PL/SQL schema-level function.

CREATE OR REPLACE FUNCTION get_member_type
(pv_member_id NUMBER) RETURN NUMBER IS
 
  -- Define a collection of strings.
  TYPE type_options IS TABLE OF VARCHAR2(10);
 
  -- Declare local variables.
  lv_dependent NUMBER := 1;
  lv_return_id NUMBER;
 
  -- Declare a local variable of the collection.
  lv_member_type TYPE_OPTIONS := type_options('INDIVIDUAL','GROUP');
 
  -- Define a dynamic cursor to count the number of foreign key values. 
  CURSOR count_contact (cv_member_id NUMBER) IS
    SELECT COUNT(c.member_id)
    FROM   contact c
    WHERE  c.member_id = cv_member_id;
 
  -- Define a dynamic cursor to find a key for an individual or group member type.    
  CURSOR get_lookup_id (cv_type VARCHAR2) IS
    SELECT common_lookup_id
    FROM   common_lookup
    WHERE  common_lookup_context = 'MEMBER'
    AND    common_lookup_type = cv_type;
 
BEGIN
 
  -- Get the number of foreign key values for a contact.
  OPEN  count_contact(pv_member_id);
  FETCH count_contact
  INTO  lv_dependent;
  CLOSE count_contact;
 
  -- Open the dynamic cursor with the required value.  
  IF lv_dependent = 1 THEN
    OPEN get_lookup_id(lv_member_type(1));
  ELSE
    OPEN get_lookup_id(lv_member_type(2));
  END IF;
 
  -- Get the correct surrogate primary key value.
  FETCH get_lookup_id
  INTO  lv_return_id;
  CLOSE get_lookup_id;
 
  -- Return the correct primary key for use as a foreign key.
  RETURN lv_return_id;
END;
/

MySQL User-Defined Function (UDF) ↓

Expand this section to see how to map this logic to a Persistent Stored Module (PSM) function.

SELECT 'DROP FUNCTION IF EXISTS get_member_type' AS "Statement";
DROP FUNCTION IF EXISTS get_member_type;
 
SELECT 'DELIMITER $$' AS "Statement";
DELIMITER $$
 
SELECT 'CREATE FUNCTION get_member_type' AS "Statement"$$
CREATE FUNCTION get_member_type(pv_member_id INT) RETURNS INT 
BEGIN
 
  /* Define three local variables. */
  DECLARE lv_contact_number  INT;
  DECLARE lv_member_type     CHAR(30);
  DECLARE lv_return_value    INT;
 
  /* Define a dynamic cursor to count the number of foreign key values. */  
  DECLARE contact_cursor CURSOR FOR
    SELECT COUNT(c.member_id)
    FROM   contact c
    WHERE  c.member_id = pv_member_id;
 
  /* Define a dynamic cursor to find a key for an individual or group member type. */
  DECLARE common_lookup_cursor CURSOR FOR
    SELECT common_lookup_id
    FROM   common_lookup
    WHERE  common_lookup_table = 'MEMBER'
    AND    common_lookup_column = 'MEMBER_TYPE'           
    AND    common_lookup_type = lv_member_type;
 
  /* Get the number of foreign key values for a contact. */    
  OPEN  contact_cursor;
  FETCH contact_cursor
  INTO  lv_contact_number;
  CLOSE contact_cursor;
 
  /* Assign group membership when more than one foreign key value is found; and
     assign individual membership when only one foreign key value is found. */  
  IF lv_contact_number > 1 THEN
    SET lv_member_type = 'GROUP';
  ELSE
    SET lv_member_type = 'INDIVIDUAL';
  END IF;
 
  /* Get the correct surrogate primary key value. */  
  OPEN  common_lookup_cursor;
  FETCH common_lookup_cursor
  INTO  lv_return_value;
  CLOSE common_lookup_cursor;
 
  /* Return the correct primary key for use as a foreign key. */  
  RETURN lv_return_value;
END;
$$
 
SELECT 'DELIMITER $$' AS "Statement"$$
DELIMITER ;

You can query the results of the update statement with the following.

Change Confirmation Query ↓

Expand this section to see the query that lets you examine the changes. It runs in either Oracle or MySQL without any changes.

SELECT   m.member_id
,        dt.quantity
,        m.member_type
,        cl.common_lookup_type
FROM     member m JOIN (SELECT   member_id
                        ,        COUNT(c.member_id) AS quantity
                        FROM     contact c
                        GROUP BY c.member_id) dt
ON       m.member_id = dt.member_id JOIN common_lookup cl
ON       m.member_type = cl.common_lookup_id
ORDER BY m.member_id;

As always, I look forward to helping and gaining insight.

One of my students asked how you could show index from table_name; in Oracle. They were chagrined when I told them there wasn’t an equivalent command. Outside of using Quest’s Toad or Oracle SQL*Developer, you can query the data catalog, like so:

-- SQL*Plus formatting commands.
COLUMN index_name FORMAT A32
COLUMN column_position FORMAT 999 HEADING "COLUMN|POSITION"
COLUMN column_name FORMAT A32
 
-- Ordinary query with a substitution variable.
SELECT i.index_name
,      ic.column_position
,      ic.column_name
FROM   user_indexes i JOIN user_ind_columns ic
ON     i.index_name = ic.index_name
WHERE  i.table_name = UPPER('&input')

Naturally, this is a subset of what’s returned by the show index from table_name; syntax. There is much more information in these tables but I only wanted to show an example.

The UPPER function command ensures that the table name is found in the database. Unless you’ve created a case sensitive object, they’re stored in uppercase strings.

While a single SQL statement works well, a little organization in PL/SQL makes it more readable. A display_indexes function provides that organization. It only displays normal indexes, not LOB indexes, and it depends on a schema-level collection of strings. This is the user-defined type (UDT) that I used for the collection.

CREATE OR REPLACE TYPE index_table AS TABLE OF VARCHAR2(200);
/

The following is the definition of the function:

CREATE OR REPLACE FUNCTION display_indexes
( pv_table_name VARCHAR2 ) RETURN INDEX_TABLE IS
 
  -- Declare an iterator for the collection return variable.
  index_counter  NUMBER := 1;
  column_counter NUMBER;
 
  -- Declare and initialize local collection variable as return type.
  index_desc INDEX_TABLE := index_table();
 
  -- Get indexes.
  CURSOR index_name (cv_table_name VARCHAR2) IS
    SELECT   i.index_name
    FROM     user_indexes i
    WHERE    i.table_name = cv_table_name
    AND      i.index_type = 'NORMAL'
    ORDER BY 1;
 
  -- Get index columns.    
  CURSOR index_columns (cv_index_name VARCHAR2) IS
    SELECT   ic.column_position
    ,        ic.column_name
    FROM     user_ind_columns ic
    WHERE    ic.index_name = cv_index_name
    ORDER BY 1;
 
BEGIN
 
  -- Assign the table name to the collection.
  index_desc.EXTEND;
  index_desc(index_counter) := UPPER(pv_table_name);
  index_counter := index_counter + 1;
 
  FOR i IN index_name(UPPER(pv_table_name)) LOOP
 
    -- Assign the index name to the collection.
    index_desc.EXTEND;
    index_desc(index_counter) := LPAD(i.index_name,2 + LENGTH(i.index_name),' ');
 
    -- Set column counter on entry to nested loop.
    column_counter := 1;
 
    FOR j IN index_columns(i.index_name) LOOP
 
      IF column_counter = 1 THEN
 
        -- Increment the column counter, extend space, and concatenate to string.
        column_counter := column_counter + 1;
        index_desc.EXTEND;
        index_desc(index_counter) := index_desc(index_counter) || '(' || LOWER(j.column_name);
 
      ELSE
 
        -- Add a subsequent column to the list.
        index_desc(index_counter) := index_desc(index_counter) || ',' || LOWER(j.column_name);
 
      END IF;
 
    END LOOP;
 
    -- Append a close parenthesis and incredment index counter.
    index_desc(index_counter) := index_desc(index_counter) || ')';
    index_counter := index_counter + 1;
 
  END LOOP;
 
  -- Return the array.
  RETURN index_desc;
END;
/

You can call the function with this syntax:

SELECT column_value AS "TRANSACTION INDEXES"
FROM   TABLE(display_indexes('TRANSACTION'));

It returns the following formatted output for the TRANSACTION table, which is much nicer than the SQL output. Unfortunately, it will take more effort to place it on par with the show index from table_name; in MySQL.

TRANSACTION INDEXES
------------------------------------------------------------------------------------------------------------------------------
TRANSACTION
  PK_TRANSACTION(transaction_id)
  UQ_TRANSACTION(rental_id,transaction_type,transaction_date,payment_method_type,payment_account_number,transaction_account)

As always, I hope it helps folks.

The \G option in MySQL lets you display rows of data as sets with the columns on the left and the data on the write. I figured it would be fun to write those for Oracle when somebody pointed out that they weren’t out there in cyberspace (first page of a Google search ;-)).

I started the program with a student’s code. I thought it a bit advanced for the student but didn’t check if he’d snagged it somewhere. Thanks to Niall Litchfield, I now know that the base code came from an earlier post of Tom Kyte. Tom’s example code failed when returning a Blob, BFile, or CFile column.

Naturally, there are two ways to write this. One is a procedure and the other is the function. This post contains both. The procedure is limited because of potential buffer overflows associated with the DBMS_OUTPUT package’s display. A function isn’t limited because you can return a collection from the function.

Required setup to use the DBMS_SQL package ↓

The DBMS_SQL package requires permissions. There are two ways to provide those permissions. One is more secure and sensible in a production system and the other is great in a development test system.

Production or Test System

If this is a production system, you probably want to grant permissions only to the SYSTEM schema. This follows the practice of narrowing access to powerful features and control systems.

The first step requires the SYS user to grant permissions and authority to re-grant to individual users. You connect as the privileged user, like:

sqlplus / AS sysdba

When connected as the SYS, you run the following two commands:

GRANT EXECUTE ON dbms_sys_sql TO system;
GRANT EXECUTE ON dbms_sql TO system;

You should then define the procedure or function as a CURRENT_USER module. This type of module is known as an invoker’s right program. The code is owned by the SYSTEM schema but you run it on your own objects in your less privileged schema.

You can do that by replacing the function and procedure headers with these:

CREATE OR REPLACE PROCEDURE display_vertical
( TABLE_NAME VARCHAR2, where_clause VARCHAR2 ) AUTHID CURRENT_USER IS

CREATE OR REPLACE FUNCTION vertical_query
( TABLE_NAME VARCHAR2, where_clause VARCHAR2 ) RETURN query_result
AUTHID CURRENT_USER IS

After you compile the procedure and function in the SYSTEM schema, you should grant access to a schema (more restricted) or public (as generic tools). You should also create synonyms. The following commands assume you want to deploy these as generic tools. As the SYSTEM user, it grants privileges and then creates public synonyms.

-- Grant privileges.
GRANT EXECUTE ON display_vertical TO PUBLIC;
GRANT EXECUTE ON vertical_query TO PUBLIC;
 
-- Create public synonyms.
CREATE PUBLIC SYNONYM display_vertical FOR system.display_vertical;
CREATE PUBLIC SYNONYM vertical_query FOR system.vertical_query;

You should now be able to call these from any schema to work with their own tables and views.

Student Development System

If this is a test system and you’re new to Oracle, the following should help you. This shows you how to implement these in a Definer’s right model, inside a STUDENT schema.

This isn’t a secure design, but it allows you to keep your testing limited to a STUDENT schema. When these permissions aren’t granted the examples won’t work at all.

The first step requires the SYS user to grant permissions and authority to re-grant to individual users. You connect as the privileged user, like:

sqlplus / AS sysdba

When connected as the SYS, you run the following two commands:

GRANT EXECUTE ON dbms_sys_sql TO system WITH GRANT OPTION;
GRANT EXECUTE ON dbms_sql TO system WITH GRANT OPTION;

You don’t have to exit to reconnect as the SYSTEM user. Just type the following at the SQL command prompt (substitute your password ;-)).

CONNECT system/password

When connected as the SYSTEM user, you run the following two commands:

GRANT EXECUTE ON dbms_sys_sql TO student;
GRANT EXECUTE ON dbms_sql TO student;

You should now be able to compile the function and procedure.

Procedure for \G output ↓

The procedure nice because there’s only a dependency on the buffer size for the DBMS_OUTPUT package. The procedure only returns column values that are printable at the console, and it only returns the first 40 characters of long text strings.

Here’s the procedure definition:

-- Create procedure.
CREATE OR REPLACE PROCEDURE display_vertical
( TABLE_NAME VARCHAR2, where_clause VARCHAR2 ) IS
 
  -- Open a cursor for a query against all columns in a table. 
  base_stmt  INTEGER := dbms_sql.open_cursor;
 
  -- Open a cursor for a dynamically constructed query, which excludes
  -- any non-displayable columns with text.
  stmt  INTEGER := dbms_sql.open_cursor;
 
  -- Declare local variables.
  colValue   VARCHAR2(4000);      -- Declare a maximum string length for column values.
  STATUS     INTEGER;             -- Declare a variable to hold acknowledgement of DBMS_SQL.EXECUTE
  tableDesc  dbms_sql.desc_tab2;  -- Declare a table to hold metadata for the queries.
  colCount   NUMBER;              -- Declare a variable for the column count.
  rowIndex   NUMBER := 0;         -- for displaying the row number retrieved from the cursor
  colLength  NUMBER := 0;         -- for keeping track of the length of the longest column name
 
  -- Declare local variable for the dynamically constructed query.
  dynamic_stmt VARCHAR2(4000) := 'SELECT ';
 
  -- Declare an exception for a bad table name, raised by a call to
  -- the dbms_assert.qualified_sql_name function.
  table_name_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(table_name_error, -942);
 
  -- Declare exception handlers for bad WHERE clause statements.
 
  -- Declare an exception for a missing WHERE keyword.
  missing_keyword EXCEPTION;
  PRAGMA EXCEPTION_INIT(missing_keyword, -933);  
 
  -- Declare an exception for a bad relational operator.
  invalid_relational_operator EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_relational_operator, -920);  
 
  -- Declare an exception for a bad column name.
  invalid_identifier EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_identifier, -904);  
 
  -- Declare an exception for a missing backquoted apostrophe.
  misquoted_string EXCEPTION;
  PRAGMA EXCEPTION_INIT(misquoted_string, -1756);  
 
  -- Declare a function that replaces non-displayable values with text messages.
  FUNCTION check_column( p_name   VARCHAR2
                       , p_type   NUMBER ) RETURN VARCHAR2 IS
 
    -- Return column name or literal value.
    retval VARCHAR2(30);			   
 
  BEGIN
 
    -- Find strings, numbers, dates, timestamps, rowids and replace non-display values.
    IF p_type IN (1,2,8,9,12,69,96,100,101,112,178,179,180,181,231) THEN
 
      -- Assign the column name for a displayable column value.
      retval := p_name;
 
    ELSE
 
      -- Re-assign string literals for column names where values aren't displayable.      
      SELECT DECODE(p_type, 23,'''RAW not displayable.'''
                          ,105,'''MLSLABEL not displayable.'''
                          ,106,'''MLSLABEL not displayable.'''
                          ,113,'''BLOB not displayable.'''
                          ,114,'''BFILE not displayable.'''
                          ,115,'''CFILE not displayable.'''
                          ,'''UNDEFINED not displayable.''')
      INTO retval
      FROM dual;
 
    END IF;
 
    -- Return the column name or a apostrophe delimited string literal.
    RETURN retval;
  END check_column;
 
BEGIN
 
  -- Prepare unfiltered display cursor.
  dbms_sql.parse(base_stmt, 'SELECT * FROM ' || dbms_assert.simple_sql_name(TABLE_NAME) || ' ' || where_clause, dbms_sql.native);
 
  -- Describe the table structure:
  -- --------------------------------------------------------
  --  1. Store metadata in tableDesc
  --  2. Store the number of columns in colCount
  -- --------------------------------------------------------
  dbms_sql.describe_columns2(base_stmt, colCount, tableDesc);
 
  -- Define individual columns and assign value to colValue variable.
  FOR i IN 1..colCount LOOP
 
    -- Define columns for each column returned into tableDesc.
    dbms_sql.define_column(base_stmt, i, colValue, 4000);
 
    -- Find the length of the longest column name.
    IF LENGTH(tableDesc(i).col_name) > colLength THEN
      colLength := LENGTH(tableDesc(i).col_name);
    END IF;
 
    -- Replace non-displayable column values with displayable values.
    IF i < colCount THEN
      dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS '
                   || tableDesc(i).col_name || ', ';
    ELSE
      dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS '
                   || tableDesc(i).col_name || ' ' || 'FROM ' || dbms_assert.simple_sql_name(TABLE_NAME)
                   || ' ' || where_clause;
    END IF;
 
  END LOOP;
 
  -- Provide conditional debugging instruction that displays dynamically created query.
  $IF $$DEBUG = 1 $THEN
     dbms_output.put_line(dynamic_stmt);
  $END
 
  -- Prepare unfiltered display cursor. 
  dbms_sql.parse(stmt, dynamic_stmt, dbms_sql.native);
 
  -- Describe the table structure:
  -- --------------------------------------------------------
  --  1. Store metadata in tableDesc (reuse of existing variable)
  --  2. Store the number of columns in colCount
  -- --------------------------------------------------------
  dbms_sql.describe_columns2(stmt, colCount, tableDesc);
 
  -- Define individual columns and assign value to colValue variable.
  FOR i IN 1..colCount LOOP
    dbms_sql.define_column(stmt, i, colValue, 4000);
  END LOOP;
 
  -- Execute the dynamic cursor.
  STATUS := dbms_sql.execute(stmt);
 
  -- Fetch the results, row-by-row.
  WHILE dbms_sql.fetch_rows(stmt) > 0 LOOP
 
    -- Reset row counter for display purposes.
    rowIndex := rowIndex + 1;
    dbms_output.put_line('********************************** ' || rowIndex || '. row **********************************');
 
    -- For each column, print left-aligned column names and values.
    FOR i IN 1..colCount LOOP
 
      -- Limit display of long text.  
      IF tableDesc(i).col_type IN (1,9,96,112) THEN
 
        -- Display 40 character substrings of long text.  
  	dbms_sql.column_value(stmt, i, colValue);
        dbms_output.put_line(RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || SUBSTR(colValue, 1,40));	  
 
      ELSE
 
        -- Display full value as character string.  
        dbms_sql.column_value(stmt, i, colValue);
        dbms_output.put_line(RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || colValue);
 
      END IF;
 
    END LOOP;
 
  END LOOP;
 
EXCEPTION
  -- Customer error handlers.
  WHEN table_name_error THEN
    dbms_output.put_line(SQLERRM);
  WHEN invalid_relational_operator THEN
    dbms_output.put_line(SQLERRM);
  WHEN invalid_identifier THEN
    dbms_output.put_line(SQLERRM);
  WHEN missing_keyword THEN
    dbms_output.put_line(SQLERRM);
  WHEN misquoted_string THEN
    dbms_output.put_line(SQLERRM);
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
/

-- Create procedure. CREATE OR REPLACE PROCEDURE display_vertical ( table_name VARCHAR2, where_clause VARCHAR2 ) IS -- Open a cursor for a query against all columns in a table. base_stmt INTEGER := dbms_sql.open_cursor; -- Open a cursor for a dynamically constructed query, which excludes -- any non-displayable columns with text. stmt INTEGER := dbms_sql.open_cursor; -- Declare local variables. colValue VARCHAR2(4000); -- Declare a maximum string length for column values. status INTEGER; -- Declare a variable to hold acknowledgement of DBMS_SQL.EXECUTE tableDesc dbms_sql.desc_tab2; -- Declare a table to hold metadata for the queries. colCount NUMBER; -- Declare a variable for the column count. rowIndex NUMBER := 0; -- for displaying the row number retrieved from the cursor colLength NUMBER := 0; -- for keeping track of the length of the longest column name -- Declare local variable for the dynamically constructed query. dynamic_stmt VARCHAR2(4000) := 'SELECT '; -- Declare an exception for a bad table name, raised by a call to -- the dbms_assert.qualified_sql_name function. table_name_error EXCEPTION; PRAGMA EXCEPTION_INIT(table_name_error, -942); -- Declare exception handlers for bad WHERE clause statements. -- Declare an exception for a missing WHERE keyword. missing_keyword EXCEPTION; PRAGMA EXCEPTION_INIT(missing_keyword, -933); -- Declare an exception for a bad relational operator. invalid_relational_operator EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_relational_operator, -920); -- Declare an exception for a bad column name. invalid_identifier EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_identifier, -904); -- Declare an exception for a missing backquoted apostrophe. misquoted_string EXCEPTION; PRAGMA EXCEPTION_INIT(misquoted_string, -1756); -- Declare a function that replaces non-displayable values with text messages. FUNCTION check_column( p_name VARCHAR2 , p_type NUMBER ) RETURN VARCHAR2 IS -- Return column name or literal value. retval VARCHAR2(30); BEGIN -- Find strings, numbers, dates, timestamps, rowids and replace non-display values. IF p_type IN (1,2,8,9,12,69,96,100,101,112,178,179,180,181,231) THEN -- Assign the column name for a displayable column value. retval := p_name; ELSE -- Re-assign string literals for column names where values aren't displayable. SELECT DECODE(p_type, 23,'''RAW not displayable.''' ,105,'''MLSLABEL not displayable.''' ,106,'''MLSLABEL not displayable.''' ,113,'''BLOB not displayable.''' ,114,'''BFILE not displayable.''' ,115,'''CFILE not displayable.''' ,'''UNDEFINED not displayable.''') INTO retval FROM dual; END IF; -- Return the column name or a apostrophe delimited string literal. RETURN retval; END check_column; BEGIN -- Prepare unfiltered display cursor. dbms_sql.parse(base_stmt, 'SELECT * FROM ' || dbms_assert.simple_sql_name(table_name) || ' ' || where_clause, dbms_sql.native); -- Describe the table structure: -- -------------------------------------------------------- -- 1. Store metadata in tableDesc -- 2. Store the number of columns in colCount -- -------------------------------------------------------- dbms_sql.describe_columns2(base_stmt, colCount, tableDesc); -- Define individual columns and assign value to colValue variable. FOR i IN 1..colCount LOOP -- Define columns for each column returned into tableDesc. dbms_sql.define_column(base_stmt, i, colValue, 4000); -- Find the length of the longest column name. IF LENGTH(tableDesc(i).col_name) > colLength THEN colLength := LENGTH(tableDesc(i).col_name); END IF; -- Replace non-displayable column values with displayable values. IF i < colCount THEN dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS ' || tableDesc(i).col_name || ', '; ELSE dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS ' || tableDesc(i).col_name || ' ' || 'FROM ' || dbms_assert.simple_sql_name(table_name) || ' ' || where_clause; END IF; END LOOP; -- Provide conditional debugging instruction that displays dynamically created query. $IF $$DEBUG = 1 $THEN dbms_output.put_line(dynamic_stmt); $END -- Prepare unfiltered display cursor. dbms_sql.parse(stmt, dynamic_stmt, dbms_sql.native); -- Describe the table structure: -- -------------------------------------------------------- -- 1. Store metadata in tableDesc (reuse of existing variable) -- 2. Store the number of columns in colCount -- -------------------------------------------------------- dbms_sql.describe_columns2(stmt, colCount, tableDesc); -- Define individual columns and assign value to colValue variable. FOR i IN 1..colCount LOOP dbms_sql.define_column(stmt, i, colValue, 4000); END LOOP; -- Execute the dynamic cursor. status := dbms_sql.execute(stmt); -- Fetch the results, row-by-row. WHILE dbms_sql.fetch_rows(stmt) > 0 LOOP -- Reset row counter for display purposes. rowIndex := rowIndex + 1; dbms_output.put_line('********************************** ' || rowIndex || '. row **********************************'); -- For each column, print left-aligned column names and values. FOR i IN 1..colCount LOOP -- Limit display of long text. IF tableDesc(i).col_type IN (1,9,96,112) THEN -- Display 40 character substrings of long text. dbms_sql.column_value(stmt, i, colValue); dbms_output.put_line(RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || SUBSTR(colValue, 1,40)); ELSE -- Display full value as character string. dbms_sql.column_value(stmt, i, colValue); dbms_output.put_line(RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || colValue); END IF; END LOOP; END LOOP; EXCEPTION -- Customer error handlers. WHEN table_name_error THEN dbms_output.put_line(SQLERRM); WHEN invalid_relational_operator THEN dbms_output.put_line(SQLERRM); WHEN invalid_identifier THEN dbms_output.put_line(SQLERRM); WHEN missing_keyword THEN dbms_output.put_line(SQLERRM); WHEN misquoted_string THEN dbms_output.put_line(SQLERRM); WHEN OTHERS THEN dbms_output.put_line(SQLERRM); END; /

You can run the procedure with the following syntax:

EXECUTE display_vertical('ITEM','WHERE item_title LIKE ''Star%''');

It’ll return the following display of data:

********************************** 1. ROW **********************************
ITEM_ID           : 1002
ITEM_BARCODE      : 24543-02392
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars I
ITEM_SUBTITLE     : Phantom Menace
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 04-MAY-99
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 2. ROW **********************************
ITEM_ID           : 1003
ITEM_BARCODE      : 24543-5615
ITEM_TYPE         : 1010
ITEM_TITLE        : Star Wars II
ITEM_SUBTITLE     : Attack OF the Clones
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 16-MAY-02
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 3. ROW **********************************
ITEM_ID           : 1004
ITEM_BARCODE      : 24543-05539
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars II
ITEM_SUBTITLE     : Attack OF the Clones
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 16-MAY-02
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         : This IS designed TO be a long enough str
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 4. ROW **********************************
ITEM_ID           : 1005
ITEM_BARCODE      : 24543-20309
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars III
ITEM_SUBTITLE     : Revenge OF the Sith
ITEM_RATING       : PG13
ITEM_RELEASE_DATE : 19-MAY-05
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.

Function for \G output ↓

The function is the best solution. It does have a dependency on a user-defined type (UDT). The function, like the procedure, only returns column values that are printable at the console. It also parses the first 40 characters from long text strings.

Before you create the function, you must create a UDT collection variable. The following syntax creates a schema-level UDT.

CREATE OR REPLACE TYPE query_result AS TABLE OF VARCHAR2(77);
/

Here’s the function definition:

CREATE OR REPLACE FUNCTION vertical_query
( TABLE_NAME VARCHAR2, where_clause VARCHAR2 ) RETURN query_result IS
 
  -- Open a cursor for a query against all columns in a table. 
  base_stmt	INTEGER := dbms_sql.open_cursor;
 
  -- Open a cursor for a dynamically constructed query, which excludes
  -- any non-displayable columns with text.
  stmt      INTEGER := dbms_sql.open_cursor;
 
  -- Declare local variables.
  colValue	VARCHAR2(4000);      -- Declare a maximum string length for column values.
  STATUS		INTEGER;             -- Declare a variable to hold acknowledgement of DBMS_SQL.EXECUTE
  tableDesc	dbms_sql.desc_tab2;  -- Declare a table to hold metadata for the queries.
  colCount	NUMBER;              -- Declare a variable for the column count.
  rowIndex	NUMBER := 0;         -- for displaying the row number retrieved from the cursor
  colLength	NUMBER := 0;         -- for keeping track of the length of the longest column name
 
  -- Declare local variable for the dynamically constructed query.
  dynamic_stmt VARCHAR2(4000) := 'SELECT ';
 
  -- Declare a index for the return collection.
  rsIndex NUMBER := 0;
 
  -- Declare a collection variable and instantiate the collection.
  result_set QUERY_RESULT := query_result();
 
  -- Declare an exception for a bad table name, raised by a call to
  -- the dbms_assert.qualified_sql_name function.
  table_name_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(table_name_error, -942);
 
  -- Declare exception handlers for bad WHERE clause statements.
 
  -- Declare an exception for a missing WHERE keyword.
  missing_keyword EXCEPTION;
  PRAGMA EXCEPTION_INIT(missing_keyword, -933);  
 
  -- Declare an exception for a bad relational operator.
  invalid_relational_operator EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_relational_operator, -920);  
 
  -- Declare an exception for a bad column name.
  invalid_identifier EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_identifier, -904);  
 
  -- Declare an exception for a missing backquoted apostrophe.
  misquoted_string EXCEPTION;
  PRAGMA EXCEPTION_INIT(misquoted_string, -1756);  
 
  -- ------------------------------------------------------------------
 
  -- Declare a function that replaces non-displayable values with text messages.
  FUNCTION check_column( p_name   VARCHAR2
                       , p_type   NUMBER ) RETURN VARCHAR2 IS
 
    -- Return column name or literal value.
    retval VARCHAR2(30);			   
 
  BEGIN
 
    -- Find strings, numbers, dates, timestamps, rowids and replace non-display values.
    IF p_type IN (1,2,8,9,12,69,96,100,101,112,178,179,180,181,231) THEN
 
      -- Assign the column name for a displayable column value.
      retval := p_name;
 
    ELSE
 
      -- Re-assign string literals for column names where values aren't displayable.      
      SELECT DECODE(p_type, 23,'''RAW not displayable.'''
                          ,105,'''MLSLABEL not displayable.'''
                          ,106,'''MLSLABEL not displayable.'''
                          ,113,'''BLOB not displayable.'''
                          ,114,'''BFILE not displayable.'''
                          ,115,'''CFILE not displayable.'''
                          ,'''UNDEFINED not displayable.''')
      INTO retval
      FROM dual;
 
    END IF;
 
    -- Return the column name or a apostrophe delimited string literal.
    RETURN retval;
  END check_column;
 
  -- ------------------------------------------------------------------
 
BEGIN
 
	-- Prepare unfiltered display cursor.
	dbms_sql.parse(base_stmt, 'SELECT * FROM ' || dbms_assert.simple_sql_name(TABLE_NAME) || ' ' || where_clause, dbms_sql.native);
 
  -- Describe the table structure:
  -- --------------------------------------------------------
  --  1. Store metadata in tableDesc
  --  2. Store the number of columns in colCount
  -- --------------------------------------------------------
  dbms_sql.describe_columns2(base_stmt, colCount, tableDesc);
 
  -- Define individual columns and assign value to colValue variable.
  FOR i IN 1..colCount LOOP
 
    -- Define columns for each column returned into tableDesc.
    dbms_sql.define_column(base_stmt, i, colValue, 4000);
 
    -- Find the length of the longest column name.
    IF LENGTH(tableDesc(i).col_name) > colLength THEN
      colLength := LENGTH(tableDesc(i).col_name);
    END IF;
 
    -- Replace non-displayable column values with displayable values.
    IF i < colCount THEN
      dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS '
                   || tableDesc(i).col_name || ', ';
    ELSE
      dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS '
                   || tableDesc(i).col_name || ' ' || 'FROM ' || dbms_assert.simple_sql_name(TABLE_NAME)
                   || ' ' || where_clause;
    END IF;
 
  END LOOP;
 
  -- Provide conditional debugging instruction that displays dynamically created query.
  $IF $$DEBUG = 1 $THEN
     dbms_output.put_line(dynamic_stmt);
  $END
 
  -- Prepare unfiltered display cursor. 
  dbms_sql.parse(stmt, dynamic_stmt, dbms_sql.native);
 
  -- Describe the table structure:
  -- --------------------------------------------------------
  --  1. Store metadata in tableDesc (reuse of existing variable)
  --  2. Store the number of columns in colCount
  -- --------------------------------------------------------
  dbms_sql.describe_columns2(stmt, colCount, tableDesc);
 
  -- Define individual columns and assign value to colValue variable.
  FOR i IN 1..colCount LOOP
    dbms_sql.define_column(stmt, i, colValue, 4000);
  END LOOP;
 
  -- Execute the dynamic cursor.
  STATUS := dbms_sql.execute(stmt);
 
  -- Fetch the results, row-by-row.
  WHILE dbms_sql.fetch_rows(stmt) > 0 LOOP
 
    -- Reset row counter for output display purposes.
    rowIndex := rowIndex + 1;
 
    -- Increment the counter for the collection and extend space before assignment.
    rsIndex := rsIndex + 1;
    result_set.EXTEND;
    result_set(rsIndex) := '********************************** ' || rowIndex || '. row **********************************';
 
    -- For each column, print left-aligned column names and values.
    FOR i IN 1..colCount LOOP
 
      -- Increment the counter for the collection and extend space before assignment.
      rsIndex := rsIndex + 1;
      result_set.EXTEND;
 
      -- Limit display of long text.  
      IF tableDesc(i).col_type IN (1,9,96,112) THEN
 
        -- Display 40 character substrings of long text.  
        dbms_sql.column_value(stmt, i, colValue);
        result_set(rsIndex) := RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || SUBSTR(colValue, 1,40);	  
 
      ELSE
 
        -- Display full value as character string.  
        dbms_sql.column_value(stmt, i, colValue);
        result_set(rsIndex) := RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || colValue;
 
      END IF;
 
    END LOOP;
 
  END LOOP;
 
  -- Increment the counter for the collection and extend space before assignment.
  FOR i IN 1..3 LOOP
    rsIndex := rsIndex + 1;
    result_set.EXTEND;
 
    CASE i
      WHEN 1 THEN
        result_set(rsIndex) := '****************************************************************************';
      WHEN 2 THEN
        result_set(rsIndex) := CHR(10);    
      WHEN 3 THEN
        result_set(rsIndex) := rowIndex || ' rows in set';    
    END CASE;
  END LOOP;
 
  -- Return collection.
  RETURN result_set;
EXCEPTION
  -- Customer error handlers, add specialized text or collapse into one with the OTHERS catchall.
  WHEN table_name_error THEN
    dbms_output.put_line(SQLERRM);
  WHEN invalid_relational_operator THEN
    dbms_output.put_line(SQLERRM);
  WHEN invalid_identifier THEN
    dbms_output.put_line(SQLERRM);
  WHEN missing_keyword THEN
    dbms_output.put_line(SQLERRM);
  WHEN misquoted_string THEN
    dbms_output.put_line(SQLERRM);
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
/

CREATE OR REPLACE FUNCTION vertical_query ( table_name VARCHAR2, where_clause VARCHAR2 ) RETURN query_result IS -- Open a cursor for a query against all columns in a table. base_stmt INTEGER := dbms_sql.open_cursor; -- Open a cursor for a dynamically constructed query, which excludes -- any non-displayable columns with text. stmt INTEGER := dbms_sql.open_cursor; -- Declare local variables. colValue VARCHAR2(4000); -- Declare a maximum string length for column values. status INTEGER; -- Declare a variable to hold acknowledgement of DBMS_SQL.EXECUTE tableDesc dbms_sql.desc_tab2; -- Declare a table to hold metadata for the queries. colCount NUMBER; -- Declare a variable for the column count. rowIndex NUMBER := 0; -- for displaying the row number retrieved from the cursor colLength NUMBER := 0; -- for keeping track of the length of the longest column name -- Declare local variable for the dynamically constructed query. dynamic_stmt VARCHAR2(4000) := 'SELECT '; -- Declare a index for the return collection. rsIndex NUMBER := 0; -- Declare a collection variable and instantiate the collection. result_set QUERY_RESULT := query_result(); -- Declare an exception for a bad table name, raised by a call to -- the dbms_assert.qualified_sql_name function. table_name_error EXCEPTION; PRAGMA EXCEPTION_INIT(table_name_error, -942); -- Declare exception handlers for bad WHERE clause statements. -- Declare an exception for a missing WHERE keyword. missing_keyword EXCEPTION; PRAGMA EXCEPTION_INIT(missing_keyword, -933); -- Declare an exception for a bad relational operator. invalid_relational_operator EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_relational_operator, -920); -- Declare an exception for a bad column name. invalid_identifier EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_identifier, -904); -- Declare an exception for a missing backquoted apostrophe. misquoted_string EXCEPTION; PRAGMA EXCEPTION_INIT(misquoted_string, -1756); -- ------------------------------------------------------------------ -- Declare a function that replaces non-displayable values with text messages. FUNCTION check_column( p_name VARCHAR2 , p_type NUMBER ) RETURN VARCHAR2 IS -- Return column name or literal value. retval VARCHAR2(30); BEGIN -- Find strings, numbers, dates, timestamps, rowids and replace non-display values. IF p_type IN (1,2,8,9,12,69,96,100,101,112,178,179,180,181,231) THEN -- Assign the column name for a displayable column value. retval := p_name; ELSE -- Re-assign string literals for column names where values aren't displayable. SELECT DECODE(p_type, 23,'''RAW not displayable.''' ,105,'''MLSLABEL not displayable.''' ,106,'''MLSLABEL not displayable.''' ,113,'''BLOB not displayable.''' ,114,'''BFILE not displayable.''' ,115,'''CFILE not displayable.''' ,'''UNDEFINED not displayable.''') INTO retval FROM dual; END IF; -- Return the column name or a apostrophe delimited string literal. RETURN retval; END check_column; -- ------------------------------------------------------------------ BEGIN -- Prepare unfiltered display cursor. dbms_sql.parse(base_stmt, 'SELECT * FROM ' || dbms_assert.simple_sql_name(table_name) || ' ' || where_clause, dbms_sql.native); -- Describe the table structure: -- -------------------------------------------------------- -- 1. Store metadata in tableDesc -- 2. Store the number of columns in colCount -- -------------------------------------------------------- dbms_sql.describe_columns2(base_stmt, colCount, tableDesc); -- Define individual columns and assign value to colValue variable. FOR i IN 1..colCount LOOP -- Define columns for each column returned into tableDesc. dbms_sql.define_column(base_stmt, i, colValue, 4000); -- Find the length of the longest column name. IF LENGTH(tableDesc(i).col_name) > colLength THEN colLength := LENGTH(tableDesc(i).col_name); END IF; -- Replace non-displayable column values with displayable values. IF i < colCount THEN dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS ' || tableDesc(i).col_name || ', '; ELSE dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS ' || tableDesc(i).col_name || ' ' || 'FROM ' || dbms_assert.simple_sql_name(table_name) || ' ' || where_clause; END IF; END LOOP; -- Provide conditional debugging instruction that displays dynamically created query. $IF $$DEBUG = 1 $THEN dbms_output.put_line(dynamic_stmt); $END -- Prepare unfiltered display cursor. dbms_sql.parse(stmt, dynamic_stmt, dbms_sql.native); -- Describe the table structure: -- -------------------------------------------------------- -- 1. Store metadata in tableDesc (reuse of existing variable) -- 2. Store the number of columns in colCount -- -------------------------------------------------------- dbms_sql.describe_columns2(stmt, colCount, tableDesc); -- Define individual columns and assign value to colValue variable. FOR i IN 1..colCount LOOP dbms_sql.define_column(stmt, i, colValue, 4000); END LOOP; -- Execute the dynamic cursor. status := dbms_sql.execute(stmt); -- Fetch the results, row-by-row. WHILE dbms_sql.fetch_rows(stmt) > 0 LOOP -- Reset row counter for output display purposes. rowIndex := rowIndex + 1; -- Increment the counter for the collection and extend space before assignment. rsIndex := rsIndex + 1; result_set.EXTEND; result_set(rsIndex) := '********************************** ' || rowIndex || '. row **********************************'; -- For each column, print left-aligned column names and values. FOR i IN 1..colCount LOOP -- Increment the counter for the collection and extend space before assignment. rsIndex := rsIndex + 1; result_set.EXTEND; -- Limit display of long text. IF tableDesc(i).col_type IN (1,9,96,112) THEN -- Display 40 character substrings of long text. dbms_sql.column_value(stmt, i, colValue); result_set(rsIndex) := RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || SUBSTR(colValue, 1,40); ELSE -- Display full value as character string. dbms_sql.column_value(stmt, i, colValue); result_set(rsIndex) := RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || colValue; END IF; END LOOP; END LOOP; -- Increment the counter for the collection and extend space before assignment. FOR i IN 1..3 LOOP rsIndex := rsIndex + 1; result_set.EXTEND; CASE i WHEN 1 THEN result_set(rsIndex) := '****************************************************************************'; WHEN 2 THEN result_set(rsIndex) := CHR(10); WHEN 3 THEN result_set(rsIndex) := rowIndex || ' rows in set'; END CASE; END LOOP; -- Return collection. RETURN result_set; EXCEPTION -- Customer error handlers, add specialized text or collapse into one with the OTHERS catchall. WHEN table_name_error THEN dbms_output.put_line(SQLERRM); WHEN invalid_relational_operator THEN dbms_output.put_line(SQLERRM); WHEN invalid_identifier THEN dbms_output.put_line(SQLERRM); WHEN missing_keyword THEN dbms_output.put_line(SQLERRM); WHEN misquoted_string THEN dbms_output.put_line(SQLERRM); WHEN OTHERS THEN dbms_output.put_line(SQLERRM); END; /

Before you attempt to run the function, you should set two Oracle SQL*Plus environment commands. One suppresses a message saying what just ran, and the other removes column headers. Clearly, the output is sufficient and the headers are clutter. You set these, as noted below:

SET FEEDBACK OFF
SET PAGESIZE 0

You can run the function with the following syntax (the COLUMN_VALUE is the standard name returned from a scalar schema-level collection.

SELECT column_value
FROM   TABLE(vertical_query('ITEM','WHERE item_title LIKE ''Star%'''));

It’ll return the following display of data:

********************************** 1. ROW **********************************
ITEM_ID           : 1002
ITEM_BARCODE      : 24543-02392
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars I
ITEM_SUBTITLE     : Phantom Menace
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 04-MAY-99
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 2. ROW **********************************
ITEM_ID           : 1003
ITEM_BARCODE      : 24543-5615
ITEM_TYPE         : 1010
ITEM_TITLE        : Star Wars II
ITEM_SUBTITLE     : Attack OF the Clones
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 16-MAY-02
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 3. ROW **********************************
ITEM_ID           : 1004
ITEM_BARCODE      : 24543-05539
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars II
ITEM_SUBTITLE     : Attack OF the Clones
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 16-MAY-02
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         : This IS designed TO be a long enough str
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 4. ROW **********************************
ITEM_ID           : 1005
ITEM_BARCODE      : 24543-20309
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars III
ITEM_SUBTITLE     : Revenge OF the Sith
ITEM_RATING       : PG13
ITEM_RELEASE_DATE : 19-MAY-05
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.
****************************************************************************

As usual, I hope this helps folks.

While developing a dynamic SQL example in Oracle 11g that builds a query based on available display columns, I found two interesting error messages. Now instead of noting it for the umpteenth time, I’m documenting it for everybody. The error messages are generated when this DBMS_SQL package’s statement is a SELECT statement, and is executed with either a BLOB, BFILE or CFILE column in the list of returned columns.

26	STATUS := dbms_sql.execute(stmt);

BLOB data type

You get the following error when a column in the query has a BLOB data type. If you alter the query to exclude the column, no error occurs.

BEGIN test('DEMO'); END;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "SYS.DBMS_SQL", line 1575
ORA-06512: at "STUDENT.TEST", line 26
ORA-06512: at line 1

BFILE or CFILE data type

You get the following error when a column in the query has a BFILE or CFILE data type. If you alter the query to exclude the column, no error occurs.

BEGIN test('DEMO'); END;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got FILE
ORA-06512: at "SYS.DBMS_SQL", line 1575
ORA-06512: at "STUDENT.TEST", line 26
ORA-06512: at line 1

It’s never a joy to debug the DBMS_SQL package, at least it’s never a joy for me. I hope this helps somebody sort out an issue more quickly.

Looking through an error on the web, I notices that the solution is nested in Ask Tom. That’s true for so many solutions, but they likewise have long discussions like this one in the OraFAQ Forum.

It seems that most folks search on is the following. The problem appears to be linked to attempts to call the DBMS_SESSION.SET_CONTEXT directly in their code, instead of through a predefined procedure. The procedure is generally inside a security package in a security schema for reference.

BEGIN
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 94
ORA-06512: at line 2

I figured it might help to provide a simple example because I use VPDs in my second database class, and this is where some of my students get hung up. It strikes me others in the Oracle community may get stuck here too.

Create a user with necessary permissions as the SYSTEM user:

CREATE USER sample IDENTIFIED BY sample;
GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE ANY PROCEDURE TO sample;

Create the CONTEXT reference as the SAMPLE user, which uses a function to populate the CONTEXT.

CREATE OR REPLACE CONTEXT sample_ctx USING set_context;

Create the function as the SAMPLE user to set the context. The CONTEXT is a literal value inside the procedure with a name and value pair.

CREATE OR REPLACE PROCEDURE set_context
( pname  VARCHAR2
, pvalue VARCHAR2) IS
BEGIN
  -- Create a session with a previously defined context.
  DBMS_SESSION.SET_CONTEXT('SAMPLE_CTX',pname,pvalue);
END;
/

Set the local session sample_ctx CONTEXT as the SAMPLE user.

EXECUTE set_context('email','sherman@atlanta.org');

You now query the user-defined CONTEXT with case insensitive strings that match the CONTEXT and pname call parameter that you set it. The following shows that query against dual. You should note that it returns a case sensitive string of the pvalue call parameter.

SELECT sys_context('sample_ctx','email') FROM dual;

As always, I hope this helps somebody and saves them time.

I’ve been working with one of my lab tutors to have him take the 1Z0-047 Oracle Database SQL Expert test. He checked out the online practice exam, and found a couple interesting questions and new syntax. At least, it was new to me.

Naturally, I checked it out. I’ve also added it to my online tutorial for the class. Perhaps I’m a creature of habit but a range non-equijion is always a filtered cross product logically. Certainly, the explain plans indicate that this new syntax has zero performance change over the other forms.

I once used the comma-delimited tables (like everybody else), but now I try to always use the newer CROSS JOIN syntax. In both cases the range join is put in the WHERE clause. The new syntax uses an INNER JOIN and an ON clause to hold the range match. Examples of all are below.

Comma-delimited Filtered Cross Join

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c, transaction_join t
WHERE    t.transaction_date BETWEEN c.start_date AND c.end_date
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Filtered CROSS JOIN

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c CROSS JOIN transaction_join t
WHERE    t.transaction_date BETWEEN c.start_date AND c.end_date
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Range filtered INNER JOIN

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c INNER JOIN transaction_join t
ON      (t.transaction_date BETWEEN c.start_date AND c.end_date)
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Without an INDEX on the start and end date of the CALENDAR_JOIN table, the Oracle explain plan for all three queries is:

Query Plan
----------------------------------------------
SELECT STATEMENT   Cost = 9
  SORT ORDER BY
    MERGE JOIN
      SORT JOIN
        TABLE ACCESS FULL TRANSACTION_JOIN
      FILTER
        SORT JOIN
          TABLE ACCESS FULL CALENDAR_JOIN

Naturally, an INDEX on the START_DATE and END_DATE columns improves performance. The results again for all three are the same.

Query Plan
----------------------------------------------
SELECT STATEMENT   Cost = 6
  SORT ORDER BY
    TABLE ACCESS BY INDEX ROWID CALENDAR_JOIN
      NESTED LOOPS
        TABLE ACCESS FULL TRANSACTION_JOIN
        INDEX RANGE SCAN DATE_RANGE

Unless I’m missing something, it looks like its only a matter of style. However, make sure you know that new one because it appears that it’s on the OCP exam. 😉

Comments are always welcome …

I’ve been working with Oracle so long, sometimes it’s frustrating when I find a syntax feature isn’t in another database. I ran into another example tonight. There isn’t a multiple column look up operator in MySQL. For example, you can do this in Oracle:

DELETE FROM common_lookup
WHERE  (common_lookup_table,common_lookup_column) IN
         (('TRANSACTION','PAYMENT_METHOD_TYPE')
         ,('TRANSACTION','TRANSACTION_TYPE')
         ,('RENTAL_ITEM','RENTAL_ITEM_TYPE'));

When I transformed it to comply with MySQL, it seems just as clean. In fact, with strings it’s simpler.

1
2
3

DELETE FROM common_lookup
WHERE  common_lookup_table IN ('TRANSACTION','RENTAL_ITEM')
AND    common_lookup_column IN ('TRANSACTION_TYPE','PAYMENT_METHOD_TYPE','RENTAL_ITEM_TYPE');

Then, I thought about it. Oracle would let me write a single subquery returning the two columns, whereas MySQL requires two subqueries in their syntax. Likewise, MySQL doesn’t support the WITH clause, which would let me reference a single query result in the scope of the master query (Oracle and SQL Server do support that).

I guess we can hope that Oracle will implement the feature in MySQL now that they own it. 🙂 Let me know if I’ve missed some fabulous syntax alternative.

A Gauss posted a question on my from last year’s Utah Oracle User’s Group Training Days presentation. If I understood his question correctly, this should help him work with his legacy code. Honestly, as I wrote the example something Bryn Llewellyn said kept banging around in my head, “Just because we can, doesn’t mean we should.” He was speaking of writing poorly engineered code.

Sometimes, we don’t get the opportunity to re-factor existing code. That leaves us with writing wrappers that aren’t pretty or effective. A realization and preface to showing everyone how to accomplish these tasks, and perhaps a watch out warning if you choose this path. I suspect that there may be a better way but I don’t know their code tree.

Here’s the question, as I understand it. They’ve got a library function in PL/SQL that returns a system reference cursor and is principally consumed by an external Java program. This type of architecture is more or less an Adapter OOAD pattern that I wrote about here, over a year and a half ago. The question comes to how to you wrap this approach and make it work in PL/SQL natively too.

The answer depends on some earlier posts because I don’t have a great deal of time to write new examples. It uses a COMMON_LOOKUP table, which is more or less a bunch of small tables grouped into a big table for use in user interaction forms. That way the values don’t get lost in a large code base and are always consistently maintained. These types of tables exist in all major ERP and CRM applications.

The base code for the example is found here, where I discussed how you can effectively use object tables – collections of user-defined object types (Oracle 9iR2 forward if I remember correctly). You can grab the full code at the bottom of the page by clicking the Code Script widget to unfold the code. That code also depends on the Oracle Database 11g PL/SQL Programming downloadable code, which you can download by clicking the link to the zip file location.

Here are the steps to wrap a function that returns a PL/SQL reference cursor so that it can also return a PL/SQL associative array.

Create a package specification to hold all the components that are required to manage the process. Assuming that they may have anchored the system reference cursor to something other than a table like a shared cursor, which is a cumbersome implementation design. (I actually chose to exclude this from the book because it’s a stretch as a good coding practice. At least, it is from my perspective. Also, I couldn’t find an example in the Oracle documentation, which led me to believe they didn’t think it’s a great idea either or I could have glossed over it.) You should note that the PL/SQL RECORD, Associative Array (collection), and the REF CURSOR are defined in this package specification.

-- Create a package to hold the PL/SQL record structure.
CREATE OR REPLACE PACKAGE example IS
 
  -- Force cursors to be read as if empty every time.
  PRAGMA SERIALLY_REUSABLE;
 
  -- Package-level record structure that mimics SQL object type.
  TYPE common_lookup_record IS RECORD
  ( common_lookup_id      NUMBER
  , common_lookup_type    VARCHAR2(30)
  , common_lookup_meaning VARCHAR2(255));
 
  -- Package-level collection that mimics SQL object table.
  TYPE common_lookup_record_table IS TABLE OF common_lookup_record
  INDEX BY PLS_INTEGER;
 
  -- Cursor structure to support a strongly-typed reference cursor.
  CURSOR c IS
    SELECT   common_lookup_id
    ,        common_lookup_type
    ,        common_lookup_meaning
    FROM     common_lookup;
 
  -- Package-level strongly-typed system reference cursor.
  TYPE cursor_lookup IS REF CURSOR RETURN c%ROWTYPE;
 
END;
/

Write a function to return a strongly typed system reference cursor that’s anchored to a cursor defined in the package. This is fairly straightforward when the package specification is done right. You should notice right away that anchoring the original cursor in the package was a horrible practice because you must repeat it all again in the function. In my opinion, you shouldn’t anchor any system reference cursor explicitly to anything other than a table. The cursor could have used the generic weak cursor data type – SYS_REFCURSOR. Doing so, saves all the extra lines required by a potential shared cursor.

CREATE OR REPLACE FUNCTION get_common_lookup_cursor
( TABLE_NAME VARCHAR2, column_name VARCHAR2)
RETURN example.cursor_lookup IS
 
  -- Define a local variable of a strongly-typed reference cursor.
  lv_cursor EXAMPLE.CURSOR_LOOKUP;
 
BEGIN
 
  -- Open the cursor from a static cursor
  OPEN lv_cursor FOR
    SELECT common_lookup_id
    ,      common_lookup_type
    ,      common_lookup_meaning
    FROM   common_lookup
    WHERE  common_lookup_table = TABLE_NAME
    AND    common_lookup_column = column_name;
 
  -- Return the cursor handle.
  RETURN lv_cursor;
 
END;
/

Write a wrapper function that takes the reference cursor as a formal parameter and returns an Associative Array. You should note that this can’t be called from a SQL context. You must only use it in a PL/SQL context because system reference cursors are PL/SQL only data types.

CREATE OR REPLACE FUNCTION convert_common_lookup_cursor
( pv_cursor EXAMPLE.CURSOR_LOOKUP) 
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Local PL/SQL-only variable.
  out_record  EXAMPLE.COMMON_LOOKUP_RECORD;
  out_table   EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  LOOP
    FETCH pv_cursor INTO out_record;
    EXIT WHEN pv_cursor%NOTFOUND;
 
    -- Assign it one row at a time to an associative array.
    out_table(counter) := out_record;
 
    -- Increment the counter.
    counter := counter + 1;
 
  END LOOP;
 
  -- Return the record collection.
  RETURN out_table;
 
END;
/

You can test the program in an anonymous block, like the one below. It defines a local Associative Array variable and then assigns the system reference cursor through the wrapper.

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Define a local associative array.
  process_table  EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
  -- Print title block.
  dbms_output.put_line('Converting a SYS_REFCURSOR to TABLE');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Run the dynamic variables through the cursor generating function and then convert it.
  process_table := convert_common_lookup_cursor(get_common_lookup_cursor('ITEM','ITEM_TYPE'));
 
  -- Read the content of the Associative array.  
  FOR i IN 1..process_table.COUNT LOOP
    dbms_output.put('['||process_table(i).common_lookup_id||']');
    dbms_output.put('['||process_table(i).common_lookup_type||']');
    dbms_output.put_line('['||process_table(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

I hope this answers Gauss’s question. While writing it, I could envision another question that might pop-up. How do you convert an object table type to a PL/SQL context. It was an omission not to include it in that original post on object table types. Here’s how you wrap an object table type into a PL/SQL scope collection.

You might have guessed. It’s done with another wrapper function. At least this is the easiest way to convert the SQL data type to a PL/SQL data type that I see. If you’ve another approach, a better way, let us know.

CREATE OR REPLACE FUNCTION get_common_lookup_record_table
( TABLE_NAME  VARCHAR2
, column_name VARCHAR2 )
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Define a dynamic cursor that takes two formal parameters.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT   *
    FROM     TABLE(get_common_lookup_object_table(UPPER(table_name_in),UPPER(table_column_name_in)));
 
  -- A local PL/SQL-only collection variable.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  FOR i IN c(TABLE_NAME, column_name) LOOP
    list(counter) := i;
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_record_table;
/

You can then test this in an anonymous block, like so:

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Declare a local PL/SQL-only collection and assign the value from the function call.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
  BEGIN
 
  -- Print title block.
  dbms_output.put_line('Converting a SQL Collection to a PL/SQL Collection');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Assign wrapped SQL collection to a PL/SQL-only collection.
  list := get_common_lookup_record_table('ITEM','ITEM_TYPE');
 
  -- Call the record wrapper function.
  FOR i IN 1..list.COUNT LOOP
    dbms_output.put('['||list(i).common_lookup_id||']');
    dbms_output.put('['||list(i).common_lookup_type||']');
    dbms_output.put_line('['||list(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

As always, I hope this helps somebody without paying a fee for content. 😉

MacLochlainns Weblog

Archive for the ‘Oracle XE’ Category

Two-stepping Sequences

Oracle 11g XE Delay?

Correlated Update Statement

Show indexes in Oracle

A \G Option for Oracle?

A couple DBMS_SQL limits

User-defined SYS_CONTEXT

SQL Certified Expert Exam

Multiple Column Lookups?

Wrap a cursor function

Recent Posts

Things Written About

Pages

Blogroll

Archives