Casting a table: variations on a theme

A heads up from William Robertson and I've taken yet another look at the TABLE command (which allows you to issue SQL against a PL/SQL table as if it was a database table). As he points out here, it seems that the TABLE cast has trouble working out the data type when you're doing a SELECT *, but can often work out the data type for the same statement with the column list specified.

Obviously, if I hung around on Ask Tom or Metalink I'd find it much easier to find these nuances out... and I'd do it in a far less public way. But hey, I'd probably forget it then, wouldn't I! ;-)

Anyway, I've put together 3 scripts that some illustrate slight variations that are available. Note I've included DROP statements to get rid of any nasty residue, not to imply that you should drop these types in any real application.

So, first up is a variation that uses a SELECT * and a TABLE + CAST selecting from a PL/SQL table of a primitive datatype

SET SERVEROUTPUT ON SIZE 100000

CREATE TYPE char_table_type AS TABLE OF VARCHAR2(1);
/

DECLARE
  --
  char_table           char_table_type;
  --
  CURSOR cur_tab IS
    SELECT *
    FROM   TABLE( CAST( char_table AS char_table_type ) );
  --
BEGIN
  --
  char_table  :=   char_table_type('A','B','C','D','E','F','G','H','I');
  --
  FOR tab_rec IN cur_tab LOOP
    DBMS_OUTPUT.PUT_LINE( tab_rec.column_value );
  END LOOP;
  --
END;
/

DROP TYPE char_table_type;
/

The second variation uses a SELECT column list, and a TABLE selecting from a PL/SQL table of a primitive datatype. Note that the column name is inferred.

SET SERVEROUTPUT ON SIZE 100000

CREATE OR REPLACE TYPE char_table_type AS TABLE OF VARCHAR2(1);
/

DECLARE
  --
  char_table           char_table_type;
  --
  CURSOR cur_tab IS
    SELECT column_value
    FROM   TABLE( char_table );
  --
BEGIN
  --
  char_table  :=   char_table_type('A','B','C','D','E','F','G','H','I');
  --
  FOR tab_rec IN cur_tab LOOP
    DBMS_OUTPUT.PUT_LINE( tab_rec.column_value );
  END LOOP;
  --
END;
/

DROP TYPE char_table_type;
/

The last variation uses a SELECT column list, and a TABLE selecting from a PL/SQL table of objects. Note that the column name is no longer implied, but there is a requirement to construct objects in order to use this variation.

SET SERVEROUTPUT ON SIZE 100000

CREATE TYPE char_table_row AS OBJECT ( letter VARCHAR2(1) );
/

CREATE TYPE char_table_type AS TABLE OF char_table_row;
/

DECLARE
  --
  char_table           char_table_type;
  --
  CURSOR cur_tab IS
    SELECT letter
    FROM   TABLE( char_table );
  --
BEGIN
  --
  char_table  :=   char_table_type( char_table_row ('A')
     ,char_table_row ('B')
     ,char_table_row ('C')
     ,char_table_row ('D')
     ,char_table_row ('E')
     ,char_table_row ('F')
     ,char_table_row ('G')
     ,char_table_row ('H')
     ,char_table_row ('I') );
  --
  FOR tab_rec IN cur_tab LOOP
    DBMS_OUTPUT.PUT_LINE( tab_rec.letter );
  END LOOP;
  --
END;
/

DROP TYPE char_table_type;
/

DROP TYPE char_table_row;
/

More investigation will take place, and I'm sure this won't be the last post on the topic!

Technorati Tags: , Robert+Baillie, oracle, table, cast, software+development, software, database, 9i, ask+tom, metalink