21st Century PL/SQL

April 24, 2006

Programmatic DESCRIBE for an opened cursor

For example - a simple unit test confirming the existence of column by name and datatype in an open ref cursor.....

create or replace package entity body
as

function all_entities return generic_cursor_t
is
      v_cursor  generic_cursor_t;
begin
      open v_cursor for
               select e.*
               from   entity e;
      return v_cursor;
end all_entities;

end entity;

....

-- Structural test 1 
-- ( Ensure entity.all_entities rowset 
--   contains the entity_id column )

procedure test_all_entities_has_id
is
begin
      test.assert_row_exists ( ref_cursor => DBMS_DESCRIBE.DESCRIBE_CURSOR( entity.all_entities ), 
where_clause => \\\'column_name like \\\'\\\'ENTITY_ID\\\'\\\' and data_type like \\\'\\\'INTEGER\\\'\\\'\\\',
message => \\\'All entities rowset function does not contain identity column\\\'
);
end;

ie. DBMS_DESCRIBE.DESCRIBE_CURSOR - Given an open ref cursor allows programmatic reasoning about the current column definitions. (Note: Custom functions and some XML operators can already take REF CURSORs as input argument and the latter obviously have some capacity to describe the columns by name and type even though the original cursor was not constructed using DBMS_SQL).

Posted by Rhodry Maelwaedd at 01:06 PM | Add your thoughts (1)