Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    3

    Unanswered: Using a ref cursor across databases

    I created a procedure to read a cursor which is the output of the function in one database and insert the values to a table in a second database.
    Here is how it looks like
    -----------------------------------------------------------------------
    create or replace procedure SP_EmployeeList
    is
    begin
    DECLARE
    s_cursor HRD.record_cursor; -- TYPE REF CURSOR defined in HRD Package
    s_record HRD.record_name; -- TYPE RECORD defined in HRD Package
    begin
    s_cursor := HRMS.DEPT.EmployeeList@HRMSQA; --- EmployeeList function returning a ref cursor, HRMSQA is a db link
    loop
    fetch s_cursor into s_record;
    exit when s_cursor%notfound;
    --dbms_output.put_line( s_record.field_a );
    insert into temp_emp_details(emp_code,emp_name)
    values(s_record.field_a,s_record.field_b);
    end loop;
    close s_cursor;
    end;
    end SP_EmployeeList;

    -----------------------------------------------------------------------
    The procedure is successfully compiled, but when I run it ,gives an ORA-24338:statement handle not executed error in the line where I fetch the 'cursor' values. Could someone let me know what may be missing here?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Could someone let me know what may be missing here?
    You never actually OPENed the cursor.

    Can be done a plain SQL below

    insert into temp_emp_details(emp_code,emp_name)
    select emp_code,emp_name from HRMS.DEPT.EmployeeList@HRMSQA;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2010
    Posts
    3
    Thank you so much for the quick response. I am new to this, so please pardon my ignorance.

    I tried your sql, it is giving a syntax error "SQL command not properly ended"
    near the package definition HRMS.DEPT.

    I have made sure that HRMS.DEPT is the package name and this works beautifully in HRMSQA database.(when accessed directly)
    Thank you for your help!
    Last edited by dbnewbe; 05-13-10 at 20:30.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is a real challenge to debug code that can not be seen.

    If you used sqlplus along with COPY & PASTE, then we could see exactly what you do & how Oracle responds.

    Code:
    bcm@bcm-laptop:~$ sqlplus
    
    SQL*Plus: Release 11.2.0.1.0 Production on Thu May 13 16:59:14 2010
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    Enter user-name: hr/oracle
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select count(*) from emp;
    
      COUNT(*)
    ----------
    	18
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    bcm@bcm-laptop:~$
    Above is just an example of how to do COPY & PASTE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2010
    Posts
    3
    Thank you so much anacedent for your recommendation.
    The direct insert from the cursor is working now.

    I prefixed the schema name in front of the package name and it started working.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •