Home » SQL & PL/SQL » SQL & PL/SQL » Ref Cursor in DB-Link
icon5.gif  Ref Cursor in DB-Link [message #267551] Thu, 13 September 2007 12:44 Go to next message
ashtonkm
Messages: 10
Registered: August 2007
Junior Member
Any idea why I would be getting this error? Since I am getting the "statement handle not executed" exception is it possible that the cursor is coming back and not being opened?

Database version is 10.2 and version of remote database is 9.2.

SQL> 
SQL> 
SQL> CREATE DATABASE LINK CMIS
  2   CONNECT TO TES_CMIS_PTC
  3   IDENTIFIED BY **********
  4   USING 'A220';

Database link created.

SQL> 
SQL> define PrincipalRFN        = "0000045973865"
SQL> 
SQL> set linesize 500
SQL> 
SQL> set serveroutput on
SQL> 
SQL> declare
  2    type cursorType is ref cursor;
  3    ResultsCursor cursorType;
  4    results_mrn             varchar2(13);
  5    results_name            varchar2(80);
  6    results_unit            number(7);
  7    results_birthdate          varchar2(8);
  8    results_gender          varchar2(1);
  9    results_member   varchar2(1);
 10    results_melchrank varchar2(1);
 11    results_spouse_mrn         varchar2(13);
 12  
 13  begin
 14  
 15    CMIS40.CMIS_TES.SEARCHBYMRN@cmis(mrn_in         => '&PrincipalRFN'
 16                        ,results_cursor => ResultsCursor);
 17    loop
 18      fetch ResultsCursor into results_mrn,
 19                               results_name,
 20                               results_unit,
 21                               results_birthdate,
 22                               results_gender,
 23                               results_member,
 24                               results_melchrank,
 25                               results_spouse_mrn;
 26      exit when ResultsCursor%notfound;
 27      dbms_output.put_line('Member....');
 28      dbms_output.put_line('MRN         = ' || results_mrn);
 29      dbms_output.put_line('Name        = ' || results_name);
 30      dbms_output.put_line('Unit        = ' || results_unit);
 31      dbms_output.put_line('Birthdate   = ' || results_birthdate);
 32      dbms_output.put_line('Gender      = ' || results_gender);
 33      dbms_output.put_line('member    = ' || results_member);
 34      dbms_output.put_line('Mrank = ' || results_melchrank);
 35      dbms_output.put_line('Spouse MRN  = ' || results_spouse_mrn);
 36    end loop;
 37  
 38  end;
 39  
 40  /
old  15:   CMIS40.CMIS_TES.SEARCHBYMRN@cmis(mrn_in         => '&PrincipalRFN'
new  15:   CMIS40.CMIS_TES.SEARCHBYMRN@cmis(mrn_in         => '0000045973865'
declare
*
ERROR at line 1:
ORA-24338: statement handle not executed
ORA-06512: at line 18


SQL> 


The funny thing is I can connect directly as the pass-through-account and it works great. The only thing I changed was line 15 where I took off the "@cmis". Since it works here I assume the problem originates from the DB-Link. Sorry the code is kind of long but it is all pretty simple.

SQL> CONNECT tes_cmis_ptc/**********@a220
Connected.
SQL> @test
SQL> 
SQL> define PrincipalRFN        = "0000045973865"
SQL> 
SQL> set linesize 500
SQL> 
SQL> set serveroutput on
SQL> 
SQL> declare
  2    type cursorType is ref cursor;
  3    ResultsCursor cursorType;
  4    results_mrn             varchar2(13);
  5    results_name            varchar2(80);
  6    results_unit            number(7);
  7    results_birthdate          varchar2(8);
  8    results_gender          varchar2(1);
  9    results_member   varchar2(1);
 10    results_melchrank varchar2(1);
 11    results_spouse_mrn         varchar2(13);
 12  
 13  begin
 14  
 15    CMIS40.CMIS_TES.SEARCHBYMRN(mrn_in         => '&PrincipalRFN'
 16                        ,results_cursor => ResultsCursor);
 17    loop
 18      fetch ResultsCursor into results_mrn,
 19                               results_name,
 20                               results_unit,
 21                               results_birthdate,
 22                               results_gender,
 23                               results_member,
 24                               results_melchrank,
 25                               results_spouse_mrn;
 26      exit when ResultsCursor%notfound;
 27      dbms_output.put_line('Member....');
 28      dbms_output.put_line('MRN         = ' || results_mrn);
 29      dbms_output.put_line('Name        = ' || results_name);
 30      dbms_output.put_line('Unit        = ' || results_unit);
 31      dbms_output.put_line('Birthdate   = ' || results_birthdate);
 32      dbms_output.put_line('Gender      = ' || results_gender);
 33      dbms_output.put_line('member    = ' || results_member);
 34      dbms_output.put_line('Mrank = ' || results_melchrank);
 35      dbms_output.put_line('Spouse MRN  = ' || results_spouse_mrn);
 36    end loop;
 37  
 38  end;
 39  
 40  /
old  15:   CMIS40.CMIS_TES.SEARCHBYMRN(mrn_in         => '&PrincipalRFN'
new  15:   CMIS40.CMIS_TES.SEARCHBYMRN(mrn_in         => '0000045973865'
Member....
MRN         = 0000045973865
Name        = Smith, Bob
Unit        = 24295
Birthdate   = 19840217
Gender      = M
member    = 
Mrank = 
Spouse MRN  =

PL/SQL procedure successfully completed.

SQL> 


[Updated on: Thu, 13 September 2007 14:04]

Report message to a moderator

Re: Ref Cursor in DB-Link [message #267570 is a reply to message #267551] Thu, 13 September 2007 13:29 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
CMIS40.CMIS_TES.SEARCHBYMRN@cmis 
means that you are calling the package/proc created on the remote server, but your anynomous pl/sql in on the local server.

Try changing the ref cursor select statement in the local package to select across the db link. dbms_output on a remote server has similar pitfalls...

[Updated on: Thu, 13 September 2007 13:30]

Report message to a moderator

Re: Ref Cursor in DB-Link [message #267572 is a reply to message #267570] Thu, 13 September 2007 13:43 Go to previous messageGo to next message
ashtonkm
Messages: 10
Registered: August 2007
Junior Member
Andrew what you are saying makes sense but I'm not quite sure how I would go about it. Could you possibility provide a small example. Thanks
Re: Ref Cursor in DB-Link [message #267589 is a reply to message #267572] Thu, 13 September 2007 15:27 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
In CMIS40.CMIS_TES.SEARCHBYMRN, you can open the cursor like this:

1.) use db link name directlty:
OPEN cv_cur FOR SELECT a FROM abc@db_link;

2.) hide the db link using a synonym:
-- create or replace synonym my_abc for abc@db_link;
OPEN cv_cur FOR SELECT a FROM my_abc;

3.) hide the db link using a view:
-- create or replace view v_abc as select * from abc@db_link;
OPEN cv_cur FOR SELECT a FROM v_abc;


the advantage of view/synonym is that it keeps the db link out of your code.

[Updated on: Thu, 13 September 2007 15:27]

Report message to a moderator

Previous Topic: Append multiple results to single row
Next Topic: get name of files and copy this files
Goto Forum:
  


Current Time: Thu Dec 08 06:00:45 CST 2016

Total time taken to generate the page: 0.08366 seconds