Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: USAGE OF PIPELINED RETURNS INVALID DATATYPE

Re: USAGE OF PIPELINED RETURNS INVALID DATATYPE

From: Billy <vslabs_at_onwe.co.za>
Date: 10 Aug 2005 22:50:29 -0700
Message-ID: <1123739429.836784.127160@g47g2000cwa.googlegroups.com>


nekkalapudi.siva_at_gmail.com wrote:

> I have a package like this

<snipped>

.. which is incorrectly designed and written.

You cannot define PL/SQL user types in a package and expect to use them via SQL. Important to realise that PL/SQL has a seperate engine than SQL. PL/SQL != SQL. In the SQL engine you can *only* deal with SQL data types. Which means the usual VARCHAR2, DATE, NUMBER data types etc, and also user defined types (see CREATE TYPE command).

The SQL engine cannot deal with PL/SQL beyond a PL/SQL function that returns a valid SQL data type (and does not break the SQL states applicable at runtime). The SQL engine cannot access a PL/SQL procedure. It cannot access a constant, static var or type definition in a PL/SQL package.

Create a SQL data type. E.g.
SQL> create or replace type TMyRow as Object( c1 varchar2(20), n1 number );

Create a collection type, e.g.
SQL> create or replace type TMyTable as table of TMyRow;

Now use the collection type as the result of a pipelined table function.

Also make sure that the reasons for using a pipelined table function is as valid as the coding of it. Using pipelines is an exception to the rule. Never the norm. Use the right tool to solve the problem. Pipelines are an excellent tool. But a very specialised one for very specialised problems. Don't use it as a chisel/screwdriver/hammer.

--
Billy
Received on Thu Aug 11 2005 - 00:50:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US