紧急求救中.批量创建view脚本执行不成功.

小弟在写一个脚本，
目的是通过远程连接
在本地批量建立view。

另外要给建立的view里增加一个dwdm字段(以前的view里没有);
因为在本地建立view是有的view本身就是基于viw建立的，所以需要先建立基于table的view，然后才能建立基于view的view。但是我这里一直调试不通， 请老大帮忙看看啊

思路:
因为需要根据@dbl_aa(暂且定为AA机器,)处的试图结构，建立本地的view,首先判断需要建立的view是表还是view,
1\从dba_views表里取出text,里的 from BB where 中的对象BB,来判断BB是表还是view,如果BB是表，那么建立该视图.如果不是的
2\再次从dba_view里判断对象是不是view,如果是view的话， 建立他
(需要注意的是,本地view的名字已经改为F_SB_开头的view)


CREATE OR REPLACE PROCEDURE TEST_WL222(OUT_CODE OUT VARCHAR2,
OUT_MSG OUT VARCHAR2) IS
/*BEGIN
DECLARE*/
FN NUMBER;
SN NUMBER;
BN NUMBER;
V_SQL VARCHAR2(4000);
TEXT_NAME VARCHAR2(4000);
V_SQL2 VARCHAR2(4000);
V_SQL3 VARCHAR2(4000);
TT NUMBER;
T_view VARCHAR2(4000);
v_name VARCHAR2(4000);
BEGIN
OUT_CODE := '更新成功';
OUT_MSG := '无';
--去dba_views里的text中的from后的对象名


FOR T_TAB IN (SELECT * FROM DBA_VIEWS@DBL_AA WHERE OWNER = 'OLIVE') LOOP
V_SQL3 :=NULL ;
SELECT INSTR(LOWER(T_TAB.TEXT), ' from ', 1) + 5 INTO FN FROM DUAL;
SELECT INSTR(LOWER(T_TAB.TEXT), ' where ', 1) INTO SN FROM DUAL;
BN := SN - FN;
V_SQL := TRIM(SUBSTR(T_TAB.TEXT, FN, BN));

INSERT INTO USR_ZJ.TEST_WL
(VIEW_NAME, TEXT_NAME,ISVIEW,START_TIME)
VALUES
(T_TAB.VIEW_NAME, V_SQL,NULL,NULL);
---- 判断是否是一级视图，创建一级视图，并在表test_wl里增加一个y标志。
SELECT 1 INTO TT FROM DBA_VIEWS@DBL_AA WHERE VIEW_NAME <> V_SQL AND owner='OLIVE';
IF TT = 1 THEN
-- IF V_SQL NOT IN (SELECT VIEW_NAME FROM DBA_VIEWS WHERE OWNER='OLIVE') THEN
SELECT REPLACE(T_TAB.TEXT, 'from', ',"DWDM" from') INTO V_SQL3 FROM DUAL;
V_SQL2 := 'create or replace view USR_ZJ.F_SB_' || T_TAB.view_name || '_TEST' ||
' AS ' || V_SQL3;
EXECUTE IMMEDIATE V_SQL2;
UPDATE test_wl SET ISVIEW ='N' WHERE VIEW_NAME=T_TAB.VIEW_NAME AND TEXT_NAME=V_SQL AND view_name=text_name;
UPDATE TEST_WL SET START_TIME=SYSDATE WHERE VIEW_NAME=T_TAB.VIEW_NAME;
END IF;

END LOOP;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
BEGIN
OUT_CODE := '创建一级视图失败';
OUT_MSG := SQLERRM;
ROLLBACK;
END;
--创建二级视图
BEGIN
FOR t_view IN (select a.text,a.view_name from dba_views@DBL_AA a ,test_wl b where a.owner ='OLIVE' AND a.view_name=b.VIEW_NAME) LOOP
v_sql :=t_view.text;
v_name :=t_view.view_name;
-- where upper(cls_view) = SUBSTR(t_view.view_name, 6, 45);
select replace(v_sql, 'from', ',"DWDM" from') into v_sql3 from dual;
v_sql2 := 'create or replace view USR_ZJ.F_SB_'||substr(t_view.view_name,1,20)||'_TEST' || ' AS ' || v_sql3;
EXECUTE IMMEDIATE v_sql2;
UPDATE TEST_WL SET START_TIME=SYSDATE WHERE VIEW_NAME=t_view.VIEW_NAME;
--commit;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
BEGIN
out_code := '建立二级视图失败';
out_msg := sqlerrm;
--UPDATE test_wl SET v_sql=SQLERRM||v_sql2 WHERE VIEW_NAME=t_view.view_name;
ROLLBACK;
END;
END;
END TEST_WL222;

小弟就此先拜谢了!!!!!
紧急求救中ing~~~~~~~~~~~~~~~~~