This article describes some of the more advanced PL/SQL capabilities that can be exploited to improve applicationperformance as well as add new functionality to the database. Topics covered include advantages of nested tablesover index_by tables, using table functions to return rowsfrom a function, bulk binding, native compilation, returningcursors, and streaming data output using pipelined tablefunctions. While these topics are important to thesophisticated PL/SQL developer in their own right, mostimportantly this background information is necessary tounderstand Oracle’s strategy for implementing an ETL solutionwithin the database. It will be shown how all these conceptscome together in the new Oracle 9i ETL functionality whichuses PL/SQL and 9i external tables to transform data withoutthe use of third party ETL tools....

Every programming language has what is called a type system, namely the setof data types that the language implements. Among other things, a language’stype system determines how a value of a particular type is represented. SincePL/SQL was developed by Oracle as the procedural extension to SQL, theselanguages have an overlapping type system. For example PL/SQL NUMBERtype is the same data type as the SQL NUMBER type. This is one of the mainadvantages to using PL/SQL as opposed to another language like Java for dataintensive operations, There is no type conversion cost when binding valuesfrom a SQL query back to the PL/SQL. However, being a superset, not allPL/SQL data types are part of the SQL type system. For example, the typesBOOLEAN and BINARY_INTEGER are only found in PL/SQL.