麻烦各位大侠帮忙看看,我这个存储过程老是报编译错误,应该怎么改呢
create or replace procedure test_student
as
begin
insert into orl_studentmessage
select * from studentmessage@@student//通过透明网关实现的查询sql2005数据库表中内容
minus
select * from orl_studentmessage;
commit;
end test_student;
/
然后我改成
create or replace procedure test_student
as
begin
commit;
end test_student;
/
没有错误
但是写为
create or replace procedure test_student
as
begin
select * from orl_studentmessage;
commit;
end test_student;
/
这样后继续报编译错误,麻烦大家帮忙看看,谢谢
create or replace procedure test_student
as
begin
insert into orl_studentmessage
select * from studentmessage@@student//通过透明网关实现的查询sql2005数据库表中内容
minus
select * from orl_studentmessage;
commit;
end test_student;
/
然后我改成
create or replace procedure test_student
as
begin
commit;
end test_student;
/
没有错误
但是写为
create or replace procedure test_student
as
begin
select * from orl_studentmessage;
commit;
end test_student;
/
这样后继续报编译错误,麻烦大家帮忙看看,谢谢
SQL SERVER和oracle的语法是有区别的
select * from studentmessage@@student//通过透明网关实现的查询sql2005数据库表中内容
minus
select * from orl_studentmessage;
这部分代码是获得sql2005更新的数据
insert into orl_studentmessage
select * from studentmessage@@student//通过透明网关实现的查询sql2005数据库表中内容
minus
select * from orl_studentmessage;
commit;
这部分是向oracle表中插入数据,麻烦大家帮忙
主要是把
insert into orl_studentmessage
select * from studentmessage@@student//通过透明网关实现的查询sql2005数据库表中内容
minus
select * from orl_studentmessage;
写成存储过程,谢谢大家
CREATE OR REPLACE PROCEDURE test_student(o_rn out sys_refcursor)
AS
BEGIN
open o_rn for 'insert into orl_studentmessage
select * from studentmessage@@student
minus
select * from orl_studentmessage' ;
END test_student;
/
虽然创建存储过程成功,但是我应该怎么调用job定时执行呢,每次我写入job后都提示我有参数错误
另外,我这个语句查询出的结果会有很多行数据,用这个语句可以吗,寻求大家帮助
应该是存储过程中对透明网关的那张表没有权限
我以前是通过dblink来比较两个oracle库表的差异,存储过程也是编译错误,后来还是权限的问题,赋个权限后存储过程就能编译通过了。
楼主的过程没有语法错误的,应该就是没有权限创建存储过程
参考:
http://www.itpub.net/viewthread.php?tid=1339117
或者,声明一个变量来接受select到的数据是不是也能正确?
SQL> declare
2 l_stmt varchar2(400) := 'insert into dept select deptno+1,dname,loc from dept where deptno>20';
3 begin
4 execute immediate l_stmt;
5 end;
6 /
PL/SQL procedure successfully completed
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
31 SALES CHICAGO
41 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6 rows selected
SQL> rollback;
Rollback completeSQL>l_stmt里的内容随便你写,只要是能正确执行的。
CREATE OR REPLACE PROCEDURE test_student(o_rn out sys_refcursor)
AS
BEGIN
open o_rn for 'insert into orl_studentmessage
select * from studentmessage@@student
minus
select * from orl_studentmessage' ;
END test_student;
/
麻烦大家帮忙看看应该怎么做呢
方法如下
CREATE OR REPLACE PROCEDURE test_student(o_rn out sys_refcursor)
AS
BEGIN
open o_rn for 'insert into orl_studentmessage
select * from studentmessage@@student
minus
select * from orl_studentmessage' ;
END test_student;
/
但是应该怎样定时执行呢,还麻烦大家帮忙看看,因为老提示参数错误