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', '|')); |
