[fpc-pascal] fcl-db: oracle SP

[Inoussa OUEDRAOGO]

2008/4/7, Bee <bisma at brawijaya.ac.id>:
> I'm new to Oracle.
>
>
> > For a pipelined function, the syntax is :  select * from table( sf() )
> > The other syntax ( 'select sf from dual' ) is for simple function ,
> > which returns single value.
> >
>
>  What's "pipelined function"? "select * from table(sf())" seems to requires
> a table. Does an SF belong to a table?

A pipeline function is an Oracle way to create "selectable"  SQL
function , like the ones in
MS SQL SERVER ( 2000+ ), or Firebird selectable stored procedure.
Below is a simple Oracle sample  :

CREATE OR REPLACE TYPE TYPE_REC_A AS OBJECT(
    INT_FIELD NUMBER(6),
    STR_FIELD Varchar2(60) )
/

CREATE OR REPLACE TYPE TYPE_TABLE_REC_A AS TABLE OF TYPE_REC_A
/

CREATE OR REPLACE FUNCTION FN_GET_LIST_A
    RETURN TYPE_TABLE_REC_A PIPELINED IS
  REC_ROW TYPE_REC_A ;
BEGIN

  REC_ROW := TYPE_REC_A( 1, 'line 1' );
  PIPE ROW ( REC_ROW );

  REC_ROW := TYPE_REC_A( 2, 'line 2' );
  PIPE ROW ( REC_ROW );

  REC_ROW := TYPE_REC_A( 3, 'line 3' );
  PIPE ROW ( REC_ROW );

  RETURN;

END;
/

select * from table( FN_GET_LIST_A() )

-- 
Inoussa O.