Re: is it possible to use a function to
- From: Juan Carlos Reyes Pacheco <juancarlosreyesp@xxxxxxxxx>
- To: kduret@xxxxxxxxxxxxxxxxxxxx
- Date: Thu, 24 Feb 2005 19:23:06 -0400
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 )
--
//www.freelists.org/webpage/oracle-l
Other related posts:
