【Oracle】DBMS APIでテーブル定義型の確認
SQL文字列を引数にして、そのSQL文字列のテーブル構成情報を出力する。
既存プログラムのSQLのフィールド解析とかで使えそう。
まずは型を適当に作成
CREATE OR REPLACE TYPE VALUE10 AS OBJECT ( VALUE01 VARCHAR2(500), VALUE02 VARCHAR2(500), VALUE03 VARCHAR2(500), VALUE04 VARCHAR2(500), VALUE05 VARCHAR2(500), VALUE06 VARCHAR2(500), VALUE07 VARCHAR2(500), VALUE08 VARCHAR2(500), VALUE09 VARCHAR2(500), VALUE10 VARCHAR2(500) ); / CREATE OR REPLACE TYPE VALUE10_TABLE IS TABLE OF VALUE10;
ファンクションを型にあわせて作成。
CREATE OR REPLACE FUNCTION FNC_SQL_CHK ( STRSQL VARCHAR2 ) RETURN VALUE10_TABLE PIPELINED IS /*定義表*/ DESC_TBL DBMS_SQL.DESC_TAB; /*カーソルのカラム数*/ COL_COUNT NUMBER(3); /*カーソルID*/ CUR_NUM NUMBER(38); /*カーソル*/ REF_CUR SYS_REFCURSOR; /*レコード型*/ W_RET_REC VALUE10; BEGIN /*カーソルオープン*/ OPEN REF_CUR FOR STRSQL; /*カーソルID取得*/ CUR_NUM := DBMS_SQL.TO_CURSOR_NUMBER(REF_CUR); /*カーソルで取得した時の定義情報を取得する*/ DBMS_SQL.DESCRIBE_COLUMNS(CUR_NUM,COL_COUNT,DESC_TBL); /*定義情報を展開する*/ FOR I IN 1..COL_COUNT LOOP /*初期化*/ W_RET_REC := VALUE10(' ',' ',' ',' ',' ',' ',' ',' ',' ',' '); /*情報代入*/ /*列名*/ W_RET_REC.VALUE01 := DESC_TBL(I).COL_NAME; /*タイプ(数字)*/ W_RET_REC.VALUE02 := TO_CHAR(DESC_TBL(I).COL_TYPE); /*タイプ(文字列)*/ W_RET_REC.VALUE03 := CASE DESC_TBL(I).COL_TYPE WHEN 1 THEN 'VARCHAR2' WHEN 2 THEN 'NUMBER' WHEN 12 THEN 'DATE' ELSE 'その他' END; /*最大長*/ W_RET_REC.VALUE04 := TO_CHAR(DESC_TBL(I).COL_MAX_LEN); /*精度*/ W_RET_REC.VALUE05 := TO_CHAR(DESC_TBL(I).COL_PRECISION); /*列名の長さ*/ W_RET_REC.VALUE06 := TO_CHAR(DESC_TBL(I).COL_NAME_LEN); /*NULL許可*/ W_RET_REC.VALUE07 := CASE DESC_TBL(I).col_null_ok WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END; PIPE ROW ( W_RET_REC ); END LOOP; /*終了*/ RETURN; /*エラー*/ EXCEPTION WHEN OTHERS THEN /*念のためカーソルをオープンしてた場合は閉じる*/ IF REF_CUR%ISOPEN THEN CLOSE REF_CUR; END IF; /*エラー内容追加*/ /*初期化*/ W_RET_REC := VALUE10(' ',' ',' ',' ',' ',' ',' ',' ',' ',' '); W_RET_REC.VALUE10 := SQLERRM; PIPE ROW ( W_RET_REC ); RETURN; END;
列名の長さって必要なのかな?
なんか無駄に存在している気がする。