大家好,我是在Oracle数据库里面建立一个存储过程,先建立一个临时表,然后再将临时表的数据通过透明网关导入SQL Server,sql语句我已经测试没问题。
而且我的存储过程编译没问题,一运行就报错,错误在create GLOBAL TABLE T_GZ_TEMP as一行。请高手执教
CREATE OR REPLACE PROCEDURE p_ToSQLServer
as
BEGINexecute immediate 'create GLOBAL TABLE T_GZ_TEMP as
SELECT t_gz_faultinfo_cp.custunit,
t_gz_faultinfo_cp.happendate,
t_gz_faultinfo.chart, t_gz_faultinfo_cp.pronum, t_gz_faultinfo.NAME,
t_gz_faultinfo.TYPE, t_gz_faultinfo.SYSTEM, t_gz_pheno.faultproid,
t_gz_pheno.describ, t_gz_pheno.recordid,
t_gz_findoccasion.occasioncode, t_gz_findoccasion.occasionnam,
t_gz_disposal.disposalid, t_gz_disposal.detail, t_gz_reason.reasonid,
t_gz_reason.describ describ1, t_gz_duty.dutyid, t_gz_duty.describ describ2
FROM t_gz_faultinfo_cp,
t_gz_faultinfo,
t_gz_pheno,
t_gz_findoccasion,
t_gz_disposal,
t_gz_reason,
t_gz_duty
WHERE t_gz_faultinfo_cp.faultid = t_gz_faultinfo.faultid
AND t_gz_faultinfo_cp.faultproid = t_gz_duty.faultproid
AND t_gz_faultinfo_cp.faultproid = t_gz_disposal.faultproid
AND t_gz_faultinfo_cp.faultproid = t_gz_pheno.faultproid
AND t_gz_faultinfo_cp.faultproid = t_gz_reason.faultproid
AND t_gz_faultinfo_cp.occasion = t_gz_findoccasion.occasionnam
ON COMMIT PRESERVE ROWS';
--创建一个临时表
execute immediate 'insert into GZ_SQLSERVER@OP_TO_SQLMOBILE.US.ORACLE.COM("CUSTUNIT","HAPPENDATE","CHART","PRONUM","NAME","TYPE","SYSTEM","FAULTID","FAULTDESCRIB","RECORDID","OCCASIONCODE","OCCASIONNAME","DISPOSAID","DISPOSADETAIL","REASONID","FAULTREASON","DUTYID","FAULTDUTY")
select * from T_GZ_TEMP';
--将临时表的数据导入SQL Server库中 commit;END p_ToSQLServer;
而且我的存储过程编译没问题,一运行就报错,错误在create GLOBAL TABLE T_GZ_TEMP as一行。请高手执教
CREATE OR REPLACE PROCEDURE p_ToSQLServer
as
BEGINexecute immediate 'create GLOBAL TABLE T_GZ_TEMP as
SELECT t_gz_faultinfo_cp.custunit,
t_gz_faultinfo_cp.happendate,
t_gz_faultinfo.chart, t_gz_faultinfo_cp.pronum, t_gz_faultinfo.NAME,
t_gz_faultinfo.TYPE, t_gz_faultinfo.SYSTEM, t_gz_pheno.faultproid,
t_gz_pheno.describ, t_gz_pheno.recordid,
t_gz_findoccasion.occasioncode, t_gz_findoccasion.occasionnam,
t_gz_disposal.disposalid, t_gz_disposal.detail, t_gz_reason.reasonid,
t_gz_reason.describ describ1, t_gz_duty.dutyid, t_gz_duty.describ describ2
FROM t_gz_faultinfo_cp,
t_gz_faultinfo,
t_gz_pheno,
t_gz_findoccasion,
t_gz_disposal,
t_gz_reason,
t_gz_duty
WHERE t_gz_faultinfo_cp.faultid = t_gz_faultinfo.faultid
AND t_gz_faultinfo_cp.faultproid = t_gz_duty.faultproid
AND t_gz_faultinfo_cp.faultproid = t_gz_disposal.faultproid
AND t_gz_faultinfo_cp.faultproid = t_gz_pheno.faultproid
AND t_gz_faultinfo_cp.faultproid = t_gz_reason.faultproid
AND t_gz_faultinfo_cp.occasion = t_gz_findoccasion.occasionnam
ON COMMIT PRESERVE ROWS';
--创建一个临时表
execute immediate 'insert into GZ_SQLSERVER@OP_TO_SQLMOBILE.US.ORACLE.COM("CUSTUNIT","HAPPENDATE","CHART","PRONUM","NAME","TYPE","SYSTEM","FAULTID","FAULTDESCRIB","RECORDID","OCCASIONCODE","OCCASIONNAME","DISPOSAID","DISPOSADETAIL","REASONID","FAULTREASON","DUTYID","FAULTDUTY")
select * from T_GZ_TEMP';
--将临时表的数据导入SQL Server库中 commit;END p_ToSQLServer;
解决方案 »
- 高难度插入表数据问题
- oracle 在测试环境创建是好的,但是在生产环境上就报红叉,不知道是什么问题??
- 求救!!dblink 问题,请帮帮我
- 在执行一个存储过程的时候,出现ORA-01000: maximum open cursors exceeded错误,请大家指教
- 在DOS下如何写语句编译所有失效的存储过程?
- 高手请帮忙!!!!!!!!!select小问题!!!!!
- 谁有oracle函数帮住文件给我一份!!!!狂谢
- 怎么修改ORACLE服务器端的字符集?
- for update
- 我在用Oracle Enterprise Manager Console 时执行导出操作为什么总报错:VNI-2015
- struts+spring+ibaits操作clob,单个没问题,多个的话单个超过666个汉字就出问题,怎么解决?
- SQL 语句复用问题
加上TEMPORARY还是有问题,不过还是有分给你的回头
改为用非临时表,用完后再drop掉不过还是要感谢楼上,给你分 ,呵呵