Re: is it possible to use a function to

Hi Khaty I do the following on oracle 9i, I don't know if this is useful on 8i

CREATE OR REPLACE TYPE TFunctionTable AS TABLE OF INTEGER;
/

CREATE OR REPLACE FUNCTION FunctionTable( nCount NUMBER)

    RETURN TFunctionTable PIPELINED
IS
BEGIN
    FOR nI IN 1.. nCount LOOP
 PIPE ROW(nI);

    END LOOP;
    RETURN;
END;
/

SELECT * FROM TABLE(FunctionTable(3));
COLUMN_VALUE
1
2
3

a more complex example:

CREATE OR REPLACE FUNCTION fut_string_to_table( cString VARCHAR2, cSeparador VARCHAR2 )
RETURN TYP_STRING_TO_TABLE
PIPELINED IS
cItem VARCHAR2(50);
BEGIN
FOR nI IN 1..LENGTH(cString) LOOP
 cItem := cItem || SUBSTR( cString, nI,1 );  IF NVL(SUBSTR( cString, nI,1 ),'~') = NVL(cSeparador,'~') THEN   PIPE ROW(SUBSTR(cItem,1,LENGTH(cItem)-1) );   cItem := NULL;
 END IF;
END LOOP;
 PIPE ROW(cItem);
RETURN;
END;
SELECT * FROM TABLE( fut_string_to_table( 'A,B,C', ',' ) ); 1.A.i Method B
But for more complex functions table (more columns), there is a bug in this release for windows but you can do a function table in this way too, for example to show error messages: drop type typ_errores;
CREATE OR REPLACE TYPE tyo_errores AS OBJECT(nCod NUMBER, cDesc VARCHAR2(1000) )
/

CREATE OR REPLACE TYPE typ_errores AS TABLE OF tyo_errores;
/

CREATE OR REPLACE
FUNCTION fut_errores( nDesde NUMBER DEFAULT 1, nHasta NUMBER DEFAULT 100000) return typ_errores
as
l_data typ_errores := typ_errores();
cErrorMsg VARCHAR2(1000);
BEGIN
FOR i in nDesde..nHasta LOOP
cErrorMsg := SQLERRM(-i);
IF NOT cErrorMsg LIKE '%not found%' AND NOT cErrorMsg LIKE '%non-ORACLE%' THEN l_data.extend;
l_data(l_data.count) := tyo_errores( -i,cErrorMsg ); END IF;
end loop;
return l_data;
end;
/

select * from the ( select cast( fut_errores(1,100000) as typ_errores ) from dual )

--
http://www.freelists.org/webpage/oracle-l