MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

A \G Option for Oracle?

with 7 comments

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.

As usual, I hope this helps folks.

Written by maclochlainn

June 14th, 2010 at 1:01 am

Posted in MySQL,Oracle,Oracle XE,pl/sql,sql

Tagged with ,

7 Responses to 'A \G Option for Oracle?'

Subscribe to comments with RSS or TrackBack to 'A \G Option for Oracle?'.

  1. Hi,

    The problem with searching for equivalents in other systems is that oftentimes the equivalent exists under a different name or has been simulated by someone else unaware of the functionality in your system so doesn’t reference the words you would naturally use in a Google search. Sometimes you just have to know what you are looking for before you find it :(. In this case Tom Kyte got there about a decade ago (version 8 at least) with the print_table function.

    Niall Litchfield

    14 Jun 10 at 3:20 am

  2. Hi Michael,

    just a quick question. Is there a reason why you grant the very powerful and dangerous dbms_sys_sql to student? Because I couldn’t find any reference in the code.

    Regards
    Patrick

    Patrick Wolf

    14 Jun 10 at 7:03 am

  3. Niall, thanks for pointing out the original source of my student’s partial solution. I concur fully that when we search for features in one we can miss the other.

    Tom’s print_table is a nice starting place, which is where the student began. Unfortunately. it doesn’t handle a Blob, CFile, or BFile. If you run it with any of those columns, it returns the following error stack:

    BEGIN system.print_table('select item_id, photo from item'); END;
    *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
    ORA-06512: at "SYSTEM.PRINT_TABLE", line 42
    ORA-06512: at line 1

    While I provided a substantially larger processing component as a procedure, a function is a far superior approach for reasons noted in the post. My code examples take a table name and where clause as arguments, whereas Tom’s procedure requires a fully qualified query (albeit one that excludes certain types of columns). My procedure and function pare the columns to only displayable data types.

    maclochlainn

    14 Jun 10 at 11:06 am

  4. Patrick, more or less because the post was for my students. They’re still learning and I try to contain their test databases to the student schema. However, it was a great point. I modified the post to demonstrate both Definer’s right and Invoker’s right models. If you see something that I missed, let me know. I did do it quite quickly.

    maclochlainn

    14 Jun 10 at 11:35 am

  5. Somebody asked why I bothered with writing this. It makes manual inspection of privileges easy, like the one below that checks external directory privileges. Naturally, I implemented it in the SYSTEM schema with a grant execute to public and a public synonym.

    SELECT column_value
    FROM   TABLE(vertical_query('DBA_TAB_PRIVS','WHERE grantee LIKE ''STUDENT'''));

    It returns these four rows, which tells me that everything is fine. More or less the user can read data from the external *.csv file, and write exception and processing log files.

    ********************************** 1. row **********************************
    GRANTEE    : STUDENT
    OWNER      : SYS
    TABLE_NAME : DOWNLOAD
    GRANTOR    : SYSTEM
    PRIVILEGE  : WRITE
    GRANTABLE  : NO
    HIERARCHY  : NO
    ********************************** 2. row **********************************
    GRANTEE    : STUDENT
    OWNER      : SYS
    TABLE_NAME : DOWNLOAD
    GRANTOR    : SYSTEM
    PRIVILEGE  : READ
    GRANTABLE  : NO
    HIERARCHY  : NO
    ********************************** 3. row **********************************
    GRANTEE    : STUDENT
    OWNER      : SYS
    TABLE_NAME : UPLOAD
    GRANTOR    : SYSTEM
    PRIVILEGE  : WRITE
    GRANTABLE  : NO
    HIERARCHY  : NO
    ********************************** 4. row **********************************
    GRANTEE    : STUDENT
    OWNER      : SYS
    TABLE_NAME : UPLOAD
    GRANTOR    : SYSTEM
    PRIVILEGE  : READ
    GRANTABLE  : NO
    HIERARCHY  : NO
    ****************************************************************************
     
    4 rows in set

    maclochlainn

    15 Jun 10 at 10:58 pm

  6. [...] developing a dynamic SQL example …, I found two interesting error messages [...]

  7. [...] find an implementation here, that leverages an example from Tom Kyte [...]

Leave a Reply