OracleでSQL結果から列の型情報を取得する

完全にメモ
あとで色々直す。

CREATE OR REPLACE PACKAGE BODY TEST
IS
  FUNCTION RESULT_VARCHAR2 RETURN VARCHAR2
  IS
  BEGIN
    RETURN 'TEST';
  END;
  
  PROCEDURE TESTPRC
  IS
    STMT CLOB;
    CUR NUMBER;
    COLCNT NUMBER;
    IDX NUMBER;
    -- https://docs.oracle.com/cd/E16662_01/doc/timesten.1121/b63042/d_sql.htm#CHDGJIHF
    -- DESC_TAB, DESC_TABはcol_type, col_max_len, col_name, col_name_len, col_schema_name, col_schema_name_len, col_precision, col_scale, col_charsetid, col_charsetform, col_null_ok
    -- DESC_TAB3は上記に加えてcol_type_name, col_type_name_len (2つは今後のための予約済み項目で 12cでないと利用できない)
    -- oracle12c https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_sql.htm#CHDDFEGC
    COLDESC DBMS_SQL.DESC_TAB3; -- DESC_TABは9i OK, DESC_TAB2は10g OK, DESC_TAB3は11g OK
  BEGIN
    CUR := DBMS_SQL.OPEN_CURSOR;
    --STMT := 'SELECT 1 as c1, 1.1 as c2, sysdate as c3, systimestamp as c4, pkg_date.GET_BEGIN_PERIOD_DATE(2019) as c5, ''1'' as c6, ''abc'' as c7, TEST.RESULT_VARCHAR2() AS c8 from dual';
    STMT := 'SELECT * from TEST_TBL';

    DBMS_SQL.PARSE(CUR, STMT, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS3(CUR, COLCNT, COLDESC); -- DESCRIBE_COLUMNSは9i OK, DESCRIBE_COLUMNS2 は10g OK, DESCRIBE_COLUMNS3 は 11g OK
    DBMS_OUTPUT.PUT_LINE('Statement: ' || STMT);
    FOR IDX IN 1 .. COLCNT
    LOOP
        -- VIRTUAL列は結果に基づいて型が決定される
        CASE COLDESC(IDX).col_type
        WHEN 1 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': VARCHAR2, NVARCHAR2'||':'|| COLDESC(IDX).col_max_len);
        WHEN 2 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': NUMBER, FLOAT'||':'|| COLDESC(IDX).col_max_len);
        WHEN 8 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': LONG'||':'|| COLDESC(IDX).col_max_len);
        WHEN 11 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': ROWID'||':'|| COLDESC(IDX).col_max_len);
        WHEN 12 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': DATE'||':'|| COLDESC(IDX).col_max_len);
        WHEN 23 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': RAW'||':'|| COLDESC(IDX).col_max_len);
        WHEN 24 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': LONG RAW'||':'|| COLDESC(IDX).col_max_len);
        WHEN 96 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': CHAR, NCHAR'||':'|| COLDESC(IDX).col_max_len);
        WHEN 100 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': BINARY_FLOAT'||':'|| COLDESC(IDX).col_max_len);
        WHEN 101 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': BINARY_DOUBLE'||':'|| COLDESC(IDX).col_max_len);
        WHEN 106 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': MLSLABEL'||':'|| COLDESC(IDX).col_max_len);
        WHEN 112 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': CLOB, NCLOB'||':'|| COLDESC(IDX).col_max_len);
        WHEN 113 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': BLOB'||':'|| COLDESC(IDX).col_max_len);
        WHEN 114 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': BFILE'||':'|| COLDESC(IDX).col_max_len);
        WHEN 180 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': TIMESTAMP(6)'||':'|| COLDESC(IDX).col_max_len);
        WHEN 181 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': TIMESTAMP(6) WITH TIME ZONE'||':'|| COLDESC(IDX).col_max_len);
        WHEN 182 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': INTERVAL YEAR(2) TO MONTH'||':'|| COLDESC(IDX).col_max_len);
        WHEN 183 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': INTERVAL DAY(2) TO SECOND(6)'||':'|| COLDESC(IDX).col_max_len);
        WHEN 231 THEN
          DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': TIMESTAMP(6) WITH LOCAL TIME ZONE'||':'|| COLDESC(IDX).col_max_len);
        ELSE
            DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': OTHERS (' || TO_CHAR(COLDESC(IDX).col_type) || ')');
        END CASE;
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(CUR);
  EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE()) || ': ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        DBMS_SQL.CLOSE_CURSOR(CUR);
  END;
END TEST;
/

たくさんの列情報を得たいので、DBMS_OUTPUTによって標準出力して、その結果をプログラムで得たいと考えるので。
VB.NETだと下記URLを参考にする。
https://winofsql.jp/VA003334/infoboard_page.php?mid=dnettool&id=100531224632