我有如下的存储过程:
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语句开始的那行,看了半天也找不出原因,请帮忙,谢谢~~
解决方案 »
- 这样写有什么问题?
- 急急! 在线急等,关于JOB问题!
- 给scott用户制定一个单独的表空间的问题,版本Oracle 10g,求指教...
- 求写一条存储过程。。新手期待中。。
- 请好心人解决一下Oracle中的关系代数问题!!!
- 我的库中有无效对象(函数,过程,视图),编译后,可以通过。但是,隔几个小时候,又出现了上次的无效对象,不知道是什么原因造成
- 我写的一个oracle数据库管理工具不知道是否能连接10g
- 删除回滚段UNDOTBS1再新建失败后不能打开表了
- in 参数问题
- wo为什么得不到I的值。
- 关于sql中distinct操作的一个问题,希望高手指点
- Oracle插入记录问题——插入数量出错
还有要确保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;给你一个我们项目的例子