我有如下的存储过程:
create or replace procedure P_test is ln_lsh number;
ls_dblink varchar2(5); --动态表示dblink
ls_sql varchar2(10000); --动态sql语句
ls_sjzt varchar2:='0003'; cursor cur_sbysmx is
select a.sbpc,
a.sblsh,
a.dysblsh,
a.hdfs,
a.jffs,
a.jfzt,
substr(a.jflx, 1, 1) jflx,
a.sbbm,
a.tzny,
a.ssrq,
a.xzdm,
a.zmdm,
a.je,
a.glbm,
c.tbsdm,
c.ssq,
c.dwmc,
c.dwxz,
c.yb,
c.dwdz,
c.lxr,
c.lxdh,
c.swdjzh,
c.jgdm,
c.jfyh,
c.yhhh,
c.khyh,
c.yhzh,
c.yhhm
from t_sbysmx a, t_dwjbxx c
where a.sbbm = c.sbbm
and a.jfzt = c.jfzt
and a.tzny = '200810'
and a.xzdm = '36'
and a.sjzt = '0000'
and a.glbm in ('HD', 'CH', 'ZC', 'PY')
and a.sbpc = '1801';begin
for rec_sbysmx in cur_sbysmx loop
DBMS_OUTPUT.PUT_LINE(rec_sbysmx.sbbm);
select sb_sbxlh.nextval@gzsb into ln_lsh from dual;
ls_dblink := '@'||lower(rec_sbysmx.glbm) || 'qz';
execute immediate 'update t_sbysmx' || ls_dblink ||
' set sjzt = ' || ls_sjzt || ','||
'lsh =' || ln_lsh || ','||
'tbsdm = '||rec_sbysmx.tbsdm|| ','||
'dwmc ='||rec_sbysmx.dwmc|| ','||
'bz='||to_char(sysdate, 'YYYYMMDDHH24MISS ')|| ','||
'where sblsh='|| rec_sbysmx.sblsh||
' and '|| 'glbm='||rec_sbysmx.glbm;
commit;
end loop;end;其中 ls_dblink 是一个dblink的名称,是想远程来更新一个表的,编译通过,但是运行时遇到这样的问题:begin p_test_execute; end;ORA-01747: user.table.column, table.column 或列说明无效
ORA-06512: 在 "P_TEST", line 63
ORA-06512: 在 line 163行的位置就是excecute语句开始的那行,看了半天也找不出原因,请帮忙,谢谢~~
create or replace procedure P_test is ln_lsh number;
ls_dblink varchar2(5); --动态表示dblink
ls_sql varchar2(10000); --动态sql语句
ls_sjzt varchar2:='0003'; cursor cur_sbysmx is
select a.sbpc,
a.sblsh,
a.dysblsh,
a.hdfs,
a.jffs,
a.jfzt,
substr(a.jflx, 1, 1) jflx,
a.sbbm,
a.tzny,
a.ssrq,
a.xzdm,
a.zmdm,
a.je,
a.glbm,
c.tbsdm,
c.ssq,
c.dwmc,
c.dwxz,
c.yb,
c.dwdz,
c.lxr,
c.lxdh,
c.swdjzh,
c.jgdm,
c.jfyh,
c.yhhh,
c.khyh,
c.yhzh,
c.yhhm
from t_sbysmx a, t_dwjbxx c
where a.sbbm = c.sbbm
and a.jfzt = c.jfzt
and a.tzny = '200810'
and a.xzdm = '36'
and a.sjzt = '0000'
and a.glbm in ('HD', 'CH', 'ZC', 'PY')
and a.sbpc = '1801';begin
for rec_sbysmx in cur_sbysmx loop
DBMS_OUTPUT.PUT_LINE(rec_sbysmx.sbbm);
select sb_sbxlh.nextval@gzsb into ln_lsh from dual;
ls_dblink := '@'||lower(rec_sbysmx.glbm) || 'qz';
execute immediate 'update t_sbysmx' || ls_dblink ||
' set sjzt = ' || ls_sjzt || ','||
'lsh =' || ln_lsh || ','||
'tbsdm = '||rec_sbysmx.tbsdm|| ','||
'dwmc ='||rec_sbysmx.dwmc|| ','||
'bz='||to_char(sysdate, 'YYYYMMDDHH24MISS ')|| ','||
'where sblsh='|| rec_sbysmx.sblsh||
' and '|| 'glbm='||rec_sbysmx.glbm;
commit;
end loop;end;其中 ls_dblink 是一个dblink的名称,是想远程来更新一个表的,编译通过,但是运行时遇到这样的问题:begin p_test_execute; end;ORA-01747: user.table.column, table.column 或列说明无效
ORA-06512: 在 "P_TEST", line 63
ORA-06512: 在 line 163行的位置就是excecute语句开始的那行,看了半天也找不出原因,请帮忙,谢谢~~
还有要确保db_link事先测试通过.
这个拼接起来就是下面这样的?
@rec_sbysmx.glbmqz你的DBLINK链接是叫@rec_sbysmx这个吗?表明叫glbmqz?
set sjzt = ' || ls_sjzt || ','||
'lsh =' || ln_lsh || ','||
'tbsdm = '||rec_sbysmx.tbsdm|| ','||
'dwmc ='||rec_sbysmx.dwmc|| ','||
'bz='||to_char(sysdate, 'YYYYMMDDHH24MISS ')|| ','||
后面多了个逗号
set sjzt = ' || ls_sjzt || ','||
'lsh =' || ln_lsh || ','||
'tbsdm = '||rec_sbysmx.tbsdm|| ','||
'dwmc ='||rec_sbysmx.dwmc|| ','||
'bz='||to_char(sysdate, 'YYYYMMDDHH24MISS ')||
你说得没错,我刚刚把那个逗号去掉后,就跳过了这个错误说明一下:我的dblink是:@hdqz,已经测试通过了,直接运行update语句是成功的。
但是发现运行时,报错:begin p_test; end;ORA-00904: "HD": 标识符无效
ORA-06512: 在 "P_TEST", line 64
ORA-06512: 在 line 1这又是怎么回事呢?
db_link已经事先测试通过了的
userid in varchar2 default 'PUB') AS
/*
过程名:ADD_PUB
功能 :pub用户下的增量迁移
参数 :links 目标数据库连接
userid来源用户
*/
v_sql VARCHAR2(1000);
v_cur_sql VARCHAR2(1000);
TYPE C_CUR IS REF CURSOR;
V_C C_CUR;
CURSOR cu IS
SELECT tt.table_name, c.column_name
FROM ALL_TABLES TT
LEFT JOIN (SELECT TABLE_NAME, COUNT(*) fk --无外键的先处理,有外键的后处理
FROM ALL_CONSTRAINTS Q1
WHERE OWNER = userid
AND Q1.CONSTRAINT_TYPE = 'R'
GROUP BY q1.table_name) q ON TT.TABLE_NAME = Q.TABLE_NAME
LEFT JOIN ALL_CONSTRAINTS AC ON AC.OWNER = userid
AND AC.TABLE_NAME = TT.TABLE_NAME
AND ac.constraint_type = 'P'
LEFT JOIN all_cons_columns C ON c.OWNER = userid
AND c.constraint_name =
ac.constraint_name
WHERE tt.OWNER = userid
AND TT.TABLE_NAME != 'CFG_S_SENDINFO'
-- AND TT.TABLE_NAME != 'TC_IMAGE'
AND TT.TABLE_NAME != 'PLAN_TABLE'
ORDER BY nvl(q.fk, 0);
V_COLUMN_NAME ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
v_DATA_TYPE ALL_TAB_COLUMNS.DATA_TYPE%TYPE;
v_blob PLAN_TABLE.OTHER%TYPE;
v_id cfg_s_sendinfo.id%TYPE;
v_id1 plan_table.id%TYPE;
STIME DATE;
stime1 DATE;
v_coun NUMBER;
v_table VARCHAR2(100);
BEGIN
STIME := SYSDATE;
IF links IS NULL THEN
RAISE_APPLICATION_ERROR('-20001', '请输入要迁移的数据库连接');
END IF;
FOR c1 IN cu LOOP
v_table := c1.table_name;
IF c1.column_name IS NOT NULL THEN
--有主键的表处理
stime1 := SYSDATE;
v_sql := 'insert into ' || c1.table_name || '@' || links || ' a
select * from ' || C1.TABLE_NAME || ' b
WHERE not exists(select 1 from ' || c1.table_name ||
' b,' || c1.table_name || '@' || links || ' a where b.' ||
c1.column_name || '=a.' || c1.column_name || ')';
-- dbms_output.put_line(1);
EXECUTE IMMEDIATE v_sql;
v_coun := SQL%ROWCOUNT;
IF V_COUN > 0 THEN
PETL_PUB_FUN.INSERT_ETL_LOG(PETL_PUB_FUN.LOCALNET,
'PUB',
v_table || '新增加了' || v_coun || '条数据',
'增量数据',
'成功',
2,
stime1,
SYSDATE);
END IF;
COMMIT;
ELSE
--无主键的表
V_SQL := 'insert into ' || c1.table_name || '@' || links ||
' a select * from ' || C1.TABLE_NAME ||
' b WHERE not exists(select 1 from ' || c1.table_name ||
' b,' || c1.table_name || '@' || links || ' a where ';
OPEN V_C FOR
SELECT ATC.COLUMN_NAME, ATC.DATA_TYPE
FROM ALL_TAB_COLUMNS ATC
WHERE ATC.OWNER = 'PUB'
AND ATC.TABLE_NAME = C1.TABLE_NAME;
LOOP
FETCH V_C
INTO V_COLUMN_NAME, v_DATA_TYPE;
EXIT WHEN V_C%NOTFOUND;
IF v_DATA_TYPE != 'BLOB' THEN
V_SQL := V_SQL || 'b.' || V_COLUMN_NAME || '=a.' || V_COLUMN_NAME ||
' AND ';
END IF;
END LOOP;
V_SQL := V_SQL || '1=1)';
CLOSE v_c;
-- dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql;
v_coun := SQL%ROWCOUNT;
IF V_COUN > 0 THEN
PETL_PUB_FUN.INSERT_ETL_LOG(PETL_PUB_FUN.LOCALNET,
'PUB',
v_table || '新增加了' || v_coun || '条数据',
'增量数据',
'成功',
2,
stime1,
SYSDATE);
END IF;
END IF;
END LOOP;
COMMIT;
PETL_PUB_FUN.INSERT_ETL_LOG(PETL_PUB_FUN.LOCALNET,
'PUB',
'增量数据',
'增量数据',
'成功',
2,
STIME,
SYSDATE);
EXCEPTION
WHEN OTHERS THEN
PETL_PUB_FUN.INSERT_ETL_LOG(PETL_PUB_FUN.LOCALNET,
'PUB',
v_table,
'增量数据',
SQLERRM,
'2',
STIME,
SYSDATE);
END ADD_PUB;给你一个我们项目的例子