おらおら！

おらくるっのストアドプロシージャで使われている
カーソル定義を解析したかったけどできなかった中途半端な内容。
レコードの中身を解析すりゃいいんじゃねって途中で思ったけど
それもまだいけてないこっちのほうがゴールに近いかも。

HOW_TO_USE.sqlのここを自由に改変のところに流したいSQLを打ちこむだけ。
データの長さ（data_length）ってテーブルで格納できる長さ（table.column.length）のことを指してて、データの長さ（data_value.length）って意味じゃない。

COLUMN TYPEとかCOLUMN MAX LENGTHとかにしたほうがいい絶対。
データの長さを追加したいね。LENBなのかLENなのかはっきりさせたいよね

```sql:ANALIZE_SYS_CURSORL.pkg.sql
create or replace PACKAGE ANALIZE_SYS_CURSORL_PKG AS
    TYPE t_column_info IS RECORD (
        row_id      NUMBER,
        col_id      NUMBER,
        column_name NVARCHAR2(30),
        data_type   NVARCHAR2(106),
        data_length NUMBER,
        data_value  NVARCHAR2(1000)
    );
    TYPE t_column_info_table IS TABLE OF t_column_info INDEX BY PLS_INTEGER;

    PROCEDURE GET_CURSOL ( 
        cur_target IN OUT SYS_REFCURSOR,
        v_result OUT t_column_info_table
    );
END ANALIZE_SYS_CURSORL_PKG;
```


```sql:ANALIZE_SYS_CURSORL.bdy.sql
create or replace PACKAGE BODY ANALIZE_SYS_CURSORL_PKG AS
  PROCEDURE SET_COLUMNS (
    n_cur_cnt     IN    NUMBER
    ,n_col_cnt    IN    NUMBER
    ,v_desc_tab   IN    DBMS_SQL.DESC_TAB3
  ) 
  IS 
    v_col_typ NVARCHAR2(1000);
    n_col_typ NUMBER;
    d_col_typ DATE;
    l_col_typ LONG;
  BEGIN 
    FOR i IN 1 .. n_col_cnt LOOP
      CASE v_desc_tab(i).col_type
        WHEN 1 THEN
          DBMS_SQL.DEFINE_COLUMN(n_cur_cnt,i ,v_col_typ, v_desc_tab(i).col_max_len);
        WHEN 2 THEN
          DBMS_SQL.DEFINE_COLUMN(n_cur_cnt,i ,n_col_typ);
        WHEN 8 THEN
          DBMS_SQL.DEFINE_COLUMN(n_cur_cnt,i ,l_col_typ);
        WHEN 12 THEN
          DBMS_SQL.DEFINE_COLUMN(n_cur_cnt,i ,d_col_typ);
      END CASE;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END SET_COLUMNS;

  PROCEDURE get_cursol(
    cur_target     IN OUT NOCOPY SYS_REFCURSOR
    , v_result     OUT t_column_info_table
  )IS
    v_desc_tab        DBMS_SQL.DESC_TAB3;
    v_col_cnt         NUMBER;
    v_column_info     t_column_info;
    v_cursor          NUMBER;
    v_idx             PLS_INTEGER := 1;
    v_column_value    VARCHAR2(50);
    n_column_value    NUMBER;
    n_rowID           NUMBER := 1;
  BEGIN
    -- カーソルを変換
    v_cursor := DBMS_SQL.TO_CURSOR_NUMBER(cur_target);

    -- カラム情報を取得
    DBMS_SQL.DESCRIBE_COLUMNS3(v_cursor, v_col_cnt, v_desc_tab);
    SET_COLUMNS(v_cursor,v_col_cnt,v_desc_tab);
    
    LOOP
      EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) = 0;
      FOR i IN 1..v_col_cnt LOOP
        v_column_info.row_id        := n_rowID;
        v_column_info.col_id        := i;
        v_column_info.column_name   := v_desc_tab(i).col_name;
        v_column_info.data_length   := v_desc_tab(i).col_max_len;
        CASE v_desc_tab(i).col_type
          WHEN 1 THEN
            v_column_info.data_type := 'VARCHAR2, NVARCHAR2';
            DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_column_info.data_value);
          WHEN 2 THEN
            v_column_info.data_type := 'NUMBER, FLOAT';
            DBMS_SQL.COLUMN_VALUE(v_cursor, i, n_column_value);
            v_column_info.data_value := n_column_value;
          WHEN 8 THEN
            v_column_info.data_type := 'LONG';
            DBMS_SQL.COLUMN_VALUE(v_cursor, i, n_column_value);
            v_column_info.data_value := n_column_value;
          WHEN 11 THEN
            v_column_info.data_type := 'ROWID';
          WHEN 12 THEN
            v_column_info.data_type := 'DATE';
          WHEN 23 THEN
            v_column_info.data_type := 'RAW';
          WHEN 24 THEN
            v_column_info.data_type := 'LONG RAW';
          WHEN 96 THEN
            v_column_info.data_type := 'CHAR, NCHAR';
          WHEN 100 THEN
            v_column_info.data_type := 'BINARY_FLOAT';
          WHEN 101 THEN
            v_column_info.data_type := 'BINARY_DOUBLE';
          WHEN 106 THEN
            v_column_info.data_type := 'MLSLABEL';
          WHEN 112 THEN
            v_column_info.data_type := 'CLOB, NCLOB';
          WHEN 113 THEN
            v_column_info.data_type := 'BLOB';
          WHEN 114 THEN
            v_column_info.data_type := 'BFILE';
          WHEN 180 THEN
            v_column_info.data_type := 'TIMESTAMP(6)';
          WHEN 181 THEN
            v_column_info.data_type := 'IMESTAMP(6) WITH TIME ZONE';
          WHEN 182 THEN
            v_column_info.data_type := 'NTERVAL YEAR(2) TO MONTH';
          WHEN 183 THEN
            v_column_info.data_type := 'INTERVAL DAY(2) TO SECOND(6)';
          WHEN 231 THEN
            v_column_info.data_type := 'TIMESTAMP(6) WITH LOCAL TIME ZONE';
          ELSE
            v_column_info.data_type := 'OTHERS' || TO_CHAR(v_desc_tab(i).col_type);
        END CASE;
        v_result(v_idx)             := v_column_info;
        v_idx                       := v_idx + 1;
      END LOOP;
      n_rowID                       := n_rowID + 1;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END get_cursol;
END ANALIZE_SYS_CURSORL_PKG;
```


```sql:HOW_TO_USE.bdy.sql
SET SERVEROUTPUT ON

DECLARE
    v_cursor SYS_REFCURSOR;
    v_column CURSOR_PKG.t_column_info;
    v_meta_data CURSOR_PKG.t_column_info_table;
    ---ほんとはこっちを解析したかった。
    CURSOR c_employee_projects IS
      SELECT e.employee_id, e.first_name, e.last_name, p.project_id, p.project_name, ep.role
      FROM employees e
      JOIN employee_projects ep ON e.employee_id = ep.employee_id
      JOIN projects p ON ep.project_id = p.project_id;
    ---ほんとはこっちを解析したかった。
BEGIN
    OPEN v_cursor for  
    --ここを自由に改変
      SELECT e.employee_id, e.first_name, e.last_name, p.project_id, p.project_name, ep.role
      FROM employees e
      JOIN employee_projects ep ON e.employee_id = ep.employee_id
      JOIN projects p ON ep.project_id = p.project_id;
    --ここを自由に改変
    
    -- メタデータを取得
    CURSOR_PKG.GET_CURSOL(v_cursor ,v_meta_data);
    
        DBMS_OUTPUT.PUT_LINE('-----------------------------');
    FOR i IN 1 .. v_meta_data.COUNT LOOP
        v_column := v_meta_data(i);
        DBMS_OUTPUT.PUT_LINE('row_id: ' || v_column.row_id);
        DBMS_OUTPUT.PUT_LINE('col_id: ' || v_column.col_id);
        DBMS_OUTPUT.PUT_LINE('Column Name: ' || v_column.column_name);
        DBMS_OUTPUT.PUT_LINE('Data Type: ' || v_column.data_type);
        DBMS_OUTPUT.PUT_LINE('Data Length: ' || v_column.data_length);
        DBMS_OUTPUT.PUT_LINE('Data Value: ' || v_column.data_value);
        DBMS_OUTPUT.PUT_LINE('-----------------------------');
    END LOOP;
    -- カーソルを閉じる
    --CLOSE v_cursor;
END;
```

```出力結果

PL/SQLプロシージャが正常に完了しました。

-----------------------------
row_id: 1
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 101
-----------------------------
row_id: 1
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: John
-----------------------------
row_id: 1
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Doe
-----------------------------
row_id: 1
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1001
-----------------------------
row_id: 1
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project A
-----------------------------
row_id: 1
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: gg
-----------------------------
row_id: 2
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 102
-----------------------------
row_id: 2
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Jane
-----------------------------
row_id: 2
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Smith
-----------------------------
row_id: 2
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1001
-----------------------------
row_id: 2
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project A
-----------------------------
row_id: 2
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: cc
-----------------------------
row_id: 3
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 103
-----------------------------
row_id: 3
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Michael
-----------------------------
row_id: 3
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Brown
-----------------------------
row_id: 3
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1002
-----------------------------
row_id: 3
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project B
-----------------------------
row_id: 3
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: dd
-----------------------------
row_id: 4
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 104
-----------------------------
row_id: 4
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Emily
-----------------------------
row_id: 4
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Davis
-----------------------------
row_id: 4
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1002
-----------------------------
row_id: 4
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project B
-----------------------------
row_id: 4
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: aa
-----------------------------
row_id: 5
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 105
-----------------------------
row_id: 5
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Daniel
-----------------------------
row_id: 5
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Wilson
-----------------------------
row_id: 5
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1003
-----------------------------
row_id: 5
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project C
-----------------------------
row_id: 5
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: ee
-----------------------------
row_id: 6
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 106
-----------------------------
row_id: 6
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Emma
-----------------------------
row_id: 6
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Taylor
-----------------------------
row_id: 6
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1003
-----------------------------
row_id: 6
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project C
-----------------------------
row_id: 6
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: ed
-----------------------------


PL/SQLプロシージャが正常に完了しました。

```