Oracle | MacLochlainns Weblog

Oracle Press authors met at the bookstore to sign books for an hour this morning and visit with our editors (shown at left). Then, we went off to the OTN Lounge and JavaOne at the Hilton.

It’s interesting to see different Java solution sets, especially on embedded devices. I also got a good look at the Vaadin server-side AJAX framework. It was interesting. The QuickConnect AJAX framework isn’t represented at the conference, but it’s a free open source project that may interest some.

Java’s dominance in the Oracle product stack is clear. Java is the crux of the SOA architecture for Oracle’s middleware solutions. Interestingly, I’ve met a number who are new to Java. A number of the vendors here are looking for skilled Java programmers, which leads me to see a lot of opportunity for developers.

I snagged a copy of Oracle Essbase and Oracle OLAP today. Oracle Essbase is part of Oracle Fusion Middleware 11g. I’ve began reading the book this evening. It appears a good starting place for those exploring Oracle Essbase and Oracle OLAP solution space.

This was a day with a busy schedule because of the publisher’s seminar. It’s where the authors, like me, learn about Oracle products and market focus. Andy Mendolsohn went over the Oracle Database 11g R2, and other VPs presented BI/ERP solutions and the effect of Oracle’s Essbase Plus (previously known as Hyperion), Java’s position and direction, Solaris’ position and direction, and the Fusion Applications. A summary of the highlights I noted follows:

The Oracle Database 11g R2 presentation explained the idea of quarterly patch set updates (PSUs), advanced compression, the new Oracle Database Firewall, OCFS (Oracle Clustered File System), and the Exadata Server. Three quarterly PSUs, then a point release is a change but a welcome one.
Advanced compression qualified three subgroups: (a) A 3 times compression for OLTP systems; (b) A 10 times compression for data warehousing, and (c) a 15 to 50 times compression for archive data. If we apply this with the capital cost figure of 40% for storage as valid, compression may substantially reduce costs.
They shared Gartner statistics that 82% of Fortune 500 companies use Oracle BI/ERP solutions was very interesting. The idea that the largest customer deploys an Oracle BI/ERP solution to 1 million users turns the demarcation between traditional data warehouse explorers and farmers into a historical footnote. They positioned Oracle BI EE Plus as targeted to the development of dashboard and BI Publisher (formally XML Publisher). Oracle Essbase Plus for Model-OLAP (Online Analytical Processing – focused on resolving the discrepancies between R-OLAP (Relational-OLAP) and M-OLAP (Model-OLAP). Oracle Scorecard and Strategy Management tools are reserved to planning Long-Range Objectivs (LROs), Short-Range Objectives (SROs), and Key Indicators (KPIs). The last requires a carefully modeling of the objective needs of the business and data model capability.
The Exadata server now comes in two types, the X2-2 and X2-8. The first may be a quarter, half, or full Exadata server while the latter is always a full Exadata server. The X2- has 128 cores, 2 TB of memory, a Linux/Solaris 10 GB Ethernet connection, 14 storage servers with 6 core CPU in each storage server.
Oracle’s commitment to Java is very solid but a question on NetBeans leaves one to believe that it isn’t part of Oracle’s solution space and it may not see much if any evolutionary change. They said that NetBeans would be supported to help external Open Source developers. My guess is that we should plan to migrate NetBeans or bear the cost of owning solutions with a higher integration cost.
Solaris has come through the merger with new strength and a commitment to four goals that I caught: availability, scalability, and efficiency, and security. They also committed to make Solaris fully virtualizable, which will more effectively support private clouds. You may also note that Oracle has improved efficiency of MySQL 5.5 on the Solaris platform.
Fusion Middleware Applications focus on: (a) Best Practice – leveraging the best in class of applications from the Oracle eBusiness Suite, PeopleSoft, JD Edwards, and Siebel; (b) Re-inventing User Experience – A role-based user interface, embedded decision support, and pervasive collaboration; (c) Cloud – Support for both private and public clouds; (d) Platform – Standard-based, configurable, adaptive, and secure Fusion middleware. Customers will have three options for the future. They may continue the current path and upgrade eBusiness Suite to eBusiness Suite, et cetera. Alternatively, they can adopt a co-existence strategy that lets them mix and match components form multiple suites. Lastly, they may embrace the complete suite as a whole.

After the Publisher’s Seminar, we went to the Nikko for Oracle Develop. On the way, we had to cross Powell Street. I snapped a photo of John Harper, co-author of Oracle Database 11g Programming Workbook, with the Cable Car. The Nikko’s door is on the right, and the sessions were on the third floor.

After one session, we went back to the Moscone South Exhibition hall to snag a copy of Guy Harrison’s Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. It’s the fresh version of his prior SQL Tuning books. I’d already bought one two months ago, but I couldn’t resist a signed copy for the office when it was free. I recommend the book as worthwhile and have promised Guy to write a review when I get home.

At the conclusion of the day, John and I went to the Oracle Press party to meet other authors. Ric Niemiec, founder of TUSC and author of Oracle Database 10g Performance Tuning: Tips and Techniques, and we took a photo. He’s also mentioned that the next edition covering Oracle Database 11g will include coverage of the Exadata Server. It’s awesome that he gets to write after the terminal release of the database.

I started the day at JavaOne. It’s at the Hilton on Mason Street. I attended a couple sessions on Java. Then, I went to the JavaDEMO Grounds to check things out rather than catch a sandwich.

There were a few interesting venues but I found the JBoss presentation the most useful on Seam 3. The presentation was worth the time, and the demonstration using the technology at the end was effective for a couple reasons. While the presenter’s environment wasn’t ready, he was able to fix it while working through his discussion of the technical stack. Also, the use of Seam 3 was first class. I’d recommend you stop by if you have a chance to see it.

After seeing the JavaDEMO Grounds, I went back to the Marriott to attend the Data Integration hands-on lab with Data Integrator and the Database Performance Diagnostics and Tuning presentations.

Afterward, a friend and I went to Moscone South to the Exhibition Hall. There we found the air hockey table and other game room. Only one here this year. We finished up by hitting the Moscone South Exhibition Hall. Saw some interesting hardware demos and that’s a wrap for Day 1 at Oracle Open World 2010. Actually, my second day because MySQL Sunday counts in my book.

This week I attended the first ACM conference on Bioinformatics and Computational Biology in Niagara Falls, NY. The next conference is in Rome next January. It was interesting to note who’s using what technology in their research.

Here’s a breakdown:

Databases: MySQL is the de facto winner for research. Oracle for clinical systems, mostly Oracle 10g implementations. That means moving data between the two is a critical skill. Specifically, exporting data from Oracle and importing it into MySQL. Oracle was criticized for being a DBA-preserve and unfriendly to development. When I probed this trend, it seemed to point to DBAs over managing sandbox instances at companies with site licenses. Microsoft SQL Server didn’t find a lot of popularity in the research community.
Programming Skills: C#, C++, Objective-C and PHP were high on the list. C# to import data into Microsoft SharePoint and develop Windows SmartPhones. C++ to extend MySQL. Objective-C to develop iPhone and iPad applications. PHP to build applications to manage studies and facilitate input, but there were a couple using Perl (not many).
Collaboration Tools: Microsoft SharePoint won handily. It’s made a home in the clinical and research communities.

Overall, they want programmers who understand biology and chemistry. They’d like knowledge through Medical Microbiology and Introductory Biochemistry, and they want strong math and statistical knowledge in their programming staff. They like Scrum development frameworks. They seem to emphasize a chief engineering team, which means the developers get maximum face-time with the domain experts. The developers also have to speak and walk the talk of science to be very successful.

As to Niagara Falls, I’m glad that I took my passport. The Canadian side is where I spent most of my extra time and money. It has the best views of the falls, the best food, and ambiance. Goat Island and the Cave of the Winds are the only two features I really liked on the U.S. side of Niagara Falls. The U.S. side is dreary unless you like gambling in the Seneca Niagara Casino & Hotel. Since I’m originally from Nevada, I never entered it to check it out. Technically, when you step on the casino property you enter the Seneca Nation of New York. The New York state government in Albany really needs to address the imbalance or they’ll continue to see Canada score the preponderance of tourist dollars.

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.

MacLochlainns Weblog

Archive for the ‘Oracle’ Category

OOW2010 – Day 3

OOW2010 – Day 2

OOW2010 – Day 1

Bioinformatics Conference

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

Recent Posts

Things Written About

Pages

Blogroll

Archives