小弟做了一个PROCEDURE。
希望保存某个表的外键信息,并且对于该外键进行一个整理。
可是总是包ORA--06502的问题。出错的地方就在下面的两句。
                v_child_column := v_empty;
                v_parent_column :=  v_empty;
看了半天也没找出问题的所在。请那位大侠指点一下。
CREATE OR REPLACE PROCEDURE GYOMUKANRI.DUMMY
IS    TYPE BCH019_REC_TYPE IS RECORD
    (   V_CTABLE_NAME          VARCHAR2(30) ,
        V_CCOLUMN_NAME         VARCHAR2(30) ,
        V_PTABLE_NAME          VARCHAR2(30) ,
        V_PCOLUMN_NAME         VARCHAR2(30) ,
        V_CONSTRAINT_NAME      VARCHAR2(30) ,
        V_POSITION             NUMBER
        );
    /* テーブル変数定義 */
    TYPE BCH019_TBL_TYPE IS TABLE OF
        BCH019_REC_TYPE INDEX BY BINARY_INTEGER;
        
    PM_tbl_con_inf BCH019_TBL_TYPE;
        /* 一時変数  外部キー結構情報 */
        PV_tbl_con_inf     BCH019_TBL_TYPE;
        v_child_column     VARCHAR2(32767);
        v_parent_column    VARCHAR2(32767);
        v_empty            VARCHAR2(32767);
        v_cnt              NUMBER :=0;
        V_PM_CONT          NUMBER :=0;
        
        V_DEMO             NUMBER :=0;
        
        /* カソル定義:ローカル */
        CURSOR CUR_Cons IS
            SELECT
                child.TABLE_NAME      AS NO01 ,
                cp.COLUMN_NAME        AS NO02 ,
                parent.TABLE_NAME     AS NO03 ,
                pc.COLUMN_NAME        AS NO04 ,
                child.CONSTRAINT_NAME AS NO05
            FROM
                USER_CONSTRAINTS child ,
                USER_CONSTRAINTS parent ,
                USER_CONS_COLUMNS cp ,
                USER_CONS_COLUMNS pc
            WHERE
                child.CONSTRAINT_TYPE = 'R' AND
                child.R_CONSTRAINT_NAME = PARENT.CONSTRAINT_NAME AND
                child.CONSTRAINT_NAME = cp.CONSTRAINT_NAME AND
                parent.CONSTRAINT_NAME = pc.CONSTRAINT_NAME AND
                cp.POSITION = pc.POSITION AND
                parent.TABLE_NAME = 'FIN_INST_TBL'
            ORDER BY
                child.TABLE_NAME,
                child.CONSTRAINT_NAME,
                cp.POSITION;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('------FNC_SelectCon--------------');
        FOR c1_rec IN CUR_Cons LOOP
            PV_tbl_con_inf(v_cnt).V_CTABLE_NAME     := c1_rec.NO01;
            PV_tbl_con_inf(v_cnt).V_CCOLUMN_NAME    := c1_rec.NO02;
            PV_tbl_con_inf(v_cnt).V_PTABLE_NAME     := c1_rec.NO03;
            PV_tbl_con_inf(v_cnt).V_PCOLUMN_NAME    := c1_rec.NO04;
            PV_tbl_con_inf(v_cnt).V_CONSTRAINT_NAME := c1_rec.NO05;
            v_cnt := v_cnt + 1;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('------FNC_SelectCon----v_cnt['||v_cnt||']');
        v_child_column := null;
        v_parent_column:= null;
        v_empty:=NULL;
        FOR i IN 0..(PV_tbl_con_inf.count-1) LOOP
        DBMS_OUTPUT.PUT_LINE('------FNC_SelectCon----i['||i||']');
        V_DEMO := V_DEMO+1;
            IF (v_child_column IS NULL) THEN
                v_child_column := PV_tbl_con_inf(i).V_CCOLUMN_NAME;
                v_parent_column := PV_tbl_con_inf(i).V_PCOLUMN_NAME;
            ELSE
                v_child_column := v_child_column || ',' || rtrim(PV_tbl_con_inf(i).V_CCOLUMN_NAME);
                v_parent_column:= v_parent_column || ',' || rtrim(PV_tbl_con_inf(i).V_PCOLUMN_NAME);
            END IF;
        DBMS_OUTPUT.PUT_LINE('------FNC_SelectCon----v_child_column['||v_child_column||']');
        DBMS_OUTPUT.PUT_LINE('------FNC_SelectCon----v_parent_column['||v_parent_column||']');
            -- 外部キーなど
            IF(i = (PV_tbl_con_inf.count-1) ) THEN
        DBMS_OUTPUT.PUT_LINE('------FNC_SelectCon----v_cnt'||v_cnt);
                PM_tbl_con_inf(V_PM_CONT).V_CTABLE_NAME := PV_tbl_con_inf(i).V_CTABLE_NAME;
                PM_tbl_con_inf(V_PM_CONT).V_CCOLUMN_NAME := v_child_column;
                PM_tbl_con_inf(V_PM_CONT).V_PTABLE_NAME := PV_tbl_con_inf(i).V_PTABLE_NAME;
                PM_tbl_con_inf(V_PM_CONT).V_PCOLUMN_NAME := v_parent_column;
                PM_tbl_con_inf(V_PM_CONT).V_CONSTRAINT_NAME := PV_tbl_con_inf(i).V_CONSTRAINT_NAME;
                v_child_column := NULL;
                v_parent_column := NULL;
                V_PM_CONT := V_PM_CONT + 1;
            ELSIF  (PV_tbl_con_inf(i).V_CONSTRAINT_NAME <> PV_tbl_con_inf(i+1).V_CONSTRAINT_NAME) THEN
                PM_tbl_con_inf(V_PM_CONT).V_CTABLE_NAME := PV_tbl_con_inf(i).V_CTABLE_NAME;
                PM_tbl_con_inf(V_PM_CONT).V_CCOLUMN_NAME := v_child_column;
                PM_tbl_con_inf(V_PM_CONT).V_PTABLE_NAME := PV_tbl_con_inf(i).V_PTABLE_NAME;
                PM_tbl_con_inf(V_PM_CONT).V_PCOLUMN_NAME := v_parent_column;
                PM_tbl_con_inf(V_PM_CONT).V_CONSTRAINT_NAME := PV_tbl_con_inf(i).V_CONSTRAINT_NAME;
                v_child_column := v_empty;
                v_parent_column :=  v_empty;
                V_PM_CONT := V_PM_CONT + 1;
            ELSE
                NULL;
            END IF;
        END LOOP; 
        /* 検索処理が終わったら、TRUEを返す */
    EXCEPTION
        WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('------FNC_SelectCon--OTHERS--i['||V_DEMO||']');
DBMS_OUTPUT.PUT_LINE('------FNC_SelectCon--SQLCODE'||SQLCODE);
end;