SQL & PL/SQL

Hi,

I created a simple procedure in one database and tried to access it from another as follows:

DB: abcq
user: abc

create or replace procedure myproc (pcat number, src sys_refcursor)
as
begin
open src for select cat_id "ID", cat_name "Category" from category where cat_id = pcat or pcat is null;
end;

I tested it in abcq:
var bc refcursor;
exec myproc(212,:bc);
print bc;

PL/SQL procedure successfully completed.

BC
ID Category
--- --------------------------------
*212 Studies*

I then did the following in another database:
DB: abcd
user abc

created a database link dbq for user abc in db abcq.
tested the link:
select cat_id, cat_name from category@dbq where cat_id = 212;
CAT_ID CAT_NAME
---------- --------------------------
*212 Studies*


Then I tried to execute the procedure I created remotely from abcd:
var bc refcursor;
exec myproc@dbq(212,:bc);
print bc;

PL/SQL procedure successfully completed.

ERROR:
ORA-24338: statement handle not executed

no rows selected

All this was in SQL*Plus.

I tried Oracle SQL Developer environment. In that case, the procedure was successfully completed and when I printed bc, it gave me the following:

BC
ID Category
------- ------------------------------


It gave the correct column headings, but no data!

I scoured the web, but was unable to find any answer. One thing I did notice in one of the Oracle documentation sites, and I paraphrase: '... any remote procedure call is assumed to be for an update...' which I thought was strange. So, my questions are:

Is it possible to execute a procedure that returns a cursor remotely and can we access the data in that cursor? If so, what am I doing wrong?

Thank you.

Vinod

Edited by: user2808224 on Aug 18, 2011 9:40 AM

Edited by: user2808224 on Aug 18, 2011 9:43 AM