Sunday, March 18, 2007

Tutorial on the MODEL clause

Joe Fuda has started up a new resource for Oracle developers. SQL Snippets looks like one to keep an eye on. I liked the tutorial on the MODEL clause which gives a clear explanation of the components.

The only thing lacking is a RSS feed, but I recalled Eddie Awad mentioning PONYFISH which allows you to define a feed for a site that doesn't have one, and created one here

Thursday, March 15, 2007

Pipelined functions implicitly create SQL level TYPES

In the "I learn something new every day" category, Solomon Yakobson pointed out something in a post on the Quest Pipelines forum, "under PL/SQL Tables, Nested Tables, & VARRAYS » Problem in executing Function which returns collection".

Apparently, under the sheets a PIPELINED function will create SQL types and collection types.

If you run...

CREATE OR REPLACE PACKAGE PFP_CONSTANTS_PKG IS
TYPE v_rec IS RECORD
(
v_date DATE,
v_mon NUMBER(2)
);
TYPE v_array IS TABLE OF v_rec;
function dummy return v_array pipelined;
END;
/
and then check USER_TYPES, you'll find that it creates three types prefixed by SYS_PLSQL_, followed by a number (which looks like it corresponds to the OBJECT_ID visible in USER_OBJECTS) and the rest of the identifier.

So if you find some types with that sort of prefix, you'll be able to track back to the package which created them.