1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
| CREATE OR REPLACE TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION Exporter(
strRequete IN VARCHAR2,
strSeparateur IN VARCHAR2 DEFAULT ';') RETURN VARCHAR2_TABLE PIPELINED IS
ctx DBMS_XMLGEN.CTXHANDLE;
rcResultat NUMBER;
iRetour NUMBER;
iNbColonnes INTEGER;
recTable DBMS_SQL.DESC_TAB;
strColonnes VARCHAR2(4000);
BEGIN
rcResultat := DBMS_SQL.OPEN_CURSOR();
DBMS_SQL.PARSE(rcResultat, strRequete, DBMS_SQL.NATIVE);
iRetour := DBMS_SQL.EXECUTE(rcResultat);
DBMS_SQL.DESCRIBE_COLUMNS(rcResultat, iNbColonnes, recTable);
FOR j IN 1..iNbColonnes LOOP
strColonnes := strColonnes||recTable(j).col_name;
IF (j < iNbColonnes) THEN
strColonnes := strColonnes||strSeparateur;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(rcResultat);
PIPE ROW(strColonnes);
ctx := DBMS_XMLGEN.NEWCONTEXT(strRequete);
DBMS_XMLGEN.SETNULLHANDLING(ctx, DBMS_XMLGEN.EMPTY_TAG);
FOR Colonnes IN (
SELECT
CAST(
XMLTRANSFORM(
COLUMN_VALUE,
XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl'||
'="http://www.w3.org/1999/XSL/Transform">'||
'<xsl:output method="text"/><xsl:variable name'||
'="new_line" select="''
''" /><xsl:template'||
' match="ROWSET"><xsl:apply-templates select="'||
'ROW"/></xsl:template><xsl:template match="ROW"'||
'><xsl:for-each select="*"><xsl:value-of select'||
'="."/><xsl:if test="position() != last()"><xsl'||
':value-of select="'''||strSeparateur||'''"/></xsl:if></xsl:for-'||
'each><xsl:value-of select="$new_line" /></xsl:'||
'template></xsl:stylesheet>'
)
) AS VARCHAR2(4000)) ValeurColonne
FROM TABLE(XMLSEQUENCE(DBMS_XMLGEN.GETXMLTYPE(ctx).EXTRACT('ROWSET/ROW'))))
LOOP
PIPE ROW(Colonnes.ValeurColonne);
END LOOP;
END Exporter;
/
/*
Exemple
*/
SELECT *
FROM TABLE(Exporter('SELECT * FROM USER_PROCEDURES', '|')); |
Partager