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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
| -- Create the types to support the table function.
DROP TYPE T_SQL_Parser;
DROP TYPE T_Row_Parser;
CREATE TYPE T_Row_Parser IS OBJECT (
col_type NUMBER,
col_maxlen NUMBER,
col_name VARCHAR2(32767),
col_name_len NUMBER,
col_schema_name VARCHAR2(32),
col_schema_name_len NUMBER,
col_charsetid NUMBER,
col_charsetform NUMBER,
col_precision NUMBER,
col_scale NUMBER,
col_null_ok NUMBER,
col_type_name VARCHAR2(32767),
col_type_name_len NUMBER);
/
CREATE TYPE T_SQL_Parser IS TABLE OF T_Row_Parser;
/
-- Build the table function itself.
CREATE OR REPLACE FUNCTION Parse_SQL (SQLQuery VARCHAR2)
RETURN T_SQL_Parser
AS
lcur_SQL NUMBER;
lnbr_Result NUMBER;
lnbr_FalseIs0ThenTrue NUMBER;
lnbr_ColumnCount NUMBER;
lrec_tab3 DBMS_SQL.DESC_TAB3;
lnbr_ColumnNbr NUMBER;
ltab_SQLParser T_SQL_Parser := T_SQL_Parser();
BEGIN
lcur_SQL := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(lcur_SQL, SQLQuery, DBMS_SQL.NATIVE);
lnbr_Result := DBMS_SQL.EXECUTE(lcur_SQL);
DBMS_SQL.DESCRIBE_COLUMNS3(lcur_SQL, lnbr_ColumnCount, lrec_tab3);
lnbr_ColumnNbr := lrec_tab3.first;
IF (lnbr_ColumnNbr IS NOT NULL) THEN
LOOP
IF (lrec_tab3(lnbr_ColumnNbr).col_null_ok) THEN
lnbr_FalseIs0ThenTrue := 1;
ELSE
lnbr_FalseIs0ThenTrue := 0;
END IF;
ltab_SQLParser.extend;
ltab_SQLParser(ltab_SQLParser.last) := T_Row_Parser(lrec_tab3(lnbr_ColumnNbr).col_type,
lrec_tab3(lnbr_ColumnNbr).col_max_len,
lrec_tab3(lnbr_ColumnNbr).col_name,
lrec_tab3(lnbr_ColumnNbr).col_name_len,
lrec_tab3(lnbr_ColumnNbr).col_schema_name,
lrec_tab3(lnbr_ColumnNbr).col_schema_name_len,
lrec_tab3(lnbr_ColumnNbr).col_charsetid,
lrec_tab3(lnbr_ColumnNbr).col_charsetform,
lrec_tab3(lnbr_ColumnNbr).col_precision,
lrec_tab3(lnbr_ColumnNbr).col_scale,
lnbr_FalseIs0ThenTrue,
lrec_tab3(lnbr_ColumnNbr).col_type_name,
lrec_tab3(lnbr_ColumnNbr).col_type_name_len);
lnbr_ColumnNbr := lrec_tab3.next(lnbr_ColumnNbr);
EXIT WHEN (lnbr_ColumnNbr IS NULL);
END LOOP;
END IF;
DBMS_SQL.CLOSE_CURSOR(lcur_SQL);
RETURN ltab_SQLParser;
END;
/
-- Test it.
SELECT *
FROM TABLE(Parse_SQL('SELECT Champ1, Champ2, Champ3
FROM Table1 T1 INNER JOIN Table2 T2 ON T1.Champ1 = T2.Champ1')); |
Partager