小弟做了一个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;
希望保存某个表的外键信息,并且对于该外键进行一个整理。
可是总是包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;
超长的原因是因为BCH019_REC_TYPE的尺寸太小了.
在做||运算的时候抱ORA--06502.
估计ORACLE的||算法实现的有问题.谢谢大家掺和.
散分.