在sql server 2000中可以使用数据库的超级链接,调用存储过程获取结果集。oracle也能实现这个功能吗,获取返回的结果集游标?我今天测试了一下,好象不行,是不是还有什么地方需要设置。谢谢!
解决方案 »
- ORACLE:绝对值函数问题
- 求一个SQL语句!!!!!!!!!!!!!!!!!!!!!!
- linux 下 oracle问题
- SQL*Plus WorkSheet 中如何将变量的值显示出来(解决即结贴)
- 求救!怎样用SQL Server连接Oracle数据库?急
- 如何将oracle服务器的时间格式化为‘yyyy-mm-dd'格式后插入类型未date的字段中?
- ORCALE和MSSQL 的全文检索
- 触发器问题!!!郁闷啊!!
- 写一个查询,遇到了问题,里面包含关键字,高手进
- ORA 01401
- 存储过程中如何实现交互选择执行(可作为新手经典改错题)
- 请问怎样把oracle数据库导出成sql语句啊?
I have a problem with ref cursors.I'll try to explain it(sorry if my english is
not very good).
I have 2 databases and i want to return values from one DBto the other.
In the DB that i want to recieve the data i have the call(with a procedure) and
i create a variable
wich the type is REF CURSOR from the second DB. In example:
--the variable
vResultCursor user_DB2.pk_k1.vSqlCursorD@DB2;
--where pk1 is a package in which i declare the REF CURSOR variable
..
--The call
user_DB2.pk_k1.P_1@DB2(vResultCursor);
--where P1 is the procedure in wich i open the cursor and
after that i want to work with this cursorloop
--vx is varchar2
FETCH vResultCursor INTO vx;
EXIT WHEN vResultCursor%NOTFOUND;
insert into tbl_probe values (sysdate,'vx',vx);
commit;
end loop;
close vResultCursor;In the first DB i have in PK_K1 the declaration of the ref cursor, and the
procedure wich open the dinamic
cursor:
CREATE OR REPLACE PACKAGE PK_K1 IS
TYPE vSqlCursorD IS REF CURSOR;
PROCEDURE P_RESOLVECURSOR (vSQLCURSOR OUT vSqlCursorD);
END PK_K1;CREATE OR REPLACE PACKAGE BODY PK_K1 ISPROCEDURE P_RESOLVECURSOR (vSQLCURSOR OUT vSqlCursorD) IS
vSqlCursortxt VARCHAR2(4096);
BEGIN
vSqlCursortxt:= 'SELECT * FROM DUAL';
OPEN vSQLCURSOR FOR vSqlCursortxt;
EXCEPTION
WHEN OTHERS THEN
IF (vSQLCURSOR%ISOPEN) THEN
CLOSE vSQLCURSOR;
END IF;
END;
END PK_K1;
The problem that i have is, that if i make a procedure in the package PK_K1 and
i call the procedure P_RESOLVECURSOR
it works, but when i call from the other DB it doesnt work. The error is ERROR
ORA-01001 when whe make the FETCH
I gave the EXECUTE grant from one DB to the OTHER
GRANT EXECUTE ON PK_K1 TO USERDB1;
could u help me?
Thanks
Followup:ref cursors cannot be used over a dblink like that.
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1448....
Note: * Using a REF CURSOR variable in a server-to-server RPC results in an
error. However, a REF CURSOR variable is permitted in a server-to-server RPC if
the remote database is a non-Oracle database accessed through a Procedural
Gateway.
* LOB parameters are not permitted in a server-to-server RPC...... Passing a cursor from one DB to the other March 23, 2006
Reviewer: Jorge from SpainThank for the explanation, we solve the problem opening and closing the cursor
in one DB and passig the data to the other server in an TABLE Object by means of
a function.
Thanks a lot