4

I have created a oracle function called getEmployee(id in varchar) in my remote database and I'm trying to call it from my local database using database link.

In getEmployee, I'm trying to return a cursor with employee data.(Table: Employee (ID, Name, address)):

SELECT schema.getEmployee@dblink(id) 
  FROM DUAL;

How can I get the result set with column name (ID, Name, address)?

According to Contrad, I changed my local function like this;

FUNCTION LocalGetEmployee(ID in varchar2)
RETURN Schema.SomeRefCursor
AS  

OUTPUT Schema.SomeRefCursor;

BEGIN 

  OUTPUT := schema.getEmployee@dblink(ID);

  RETURN OUTPUT;
END;

But, when I call this function from Java Code, the following error is raised:

"ORA-24338: statement handle not executed"

thehennyy
  • 4,020
  • 1
  • 22
  • 31
Kapila Witharana
  • 61
  • 1
  • 2
  • 6
  • What precise problem do you have? Please give us something to go on - error messages, description of behaviour, etc – APC May 05 '11 at 17:58
  • If I execute above SELECT statement through Java code, is there any way to retrieve cursor (returning from the function) with column names? – Kapila Witharana May 06 '11 at 00:00
  • What is the return *type* of the remote function? – Dan May 06 '11 at 01:31
  • 1
    possible duplicate of [Calling an oracle function from Java](http://stackoverflow.com/questions/5918044/calling-an-oracle-function-from-java) – OMG Ponies May 07 '11 at 02:19

2 Answers2

6

Fetching the Ref Cursor at Remote site:

Let’s say we have two sites involved in Distributed transaction, Server1 and Server2. The Ref Cursor opened on Server1 procedure, cannot be fetched at Server2 site. If we try to fetch this cursor oracle raises an exception:

[ORA-02055: distributed update operation failed; rollback required
 ORA-24338: statement handle not executed]

“We cannot use the Ref Cursor over DBLink”

Solutions:

  1. Use PL-SQL Data table. OR
  2. Provide select grant and use select command over DBLink from initiator site instead of opening the Cursor.

Source: Distributed transaction in Oracle (Over Oracle DBLink)

Andrew
  • 6,231
  • 2
  • 29
  • 37
0

As far as I can tell your question isn't really about database links but rather how, from a Java client, to call a function that returns a cursor and retrieve the data from that cursor. I believe the only way to do this in Java is to wrap the function call in a bit of "procedural" code. I don't have Oracle in front of me so this is some guesswork:

String fncall = "begin ? :=  schema.getEmployee@dblink(?) end";
CallableStatement stm = con.prepareCall(fncall);
stm.registerOutParameter(1, Types.CURSOR);
stm.setInt(2, 123);
stm.execute();
ResultSet rs = (ResultSet) stm.getObject(1);
Dan
  • 10,990
  • 7
  • 51
  • 80
  • Still it gives "ORA-24338: statement handle not executed" error – Kapila Witharana May 06 '11 at 02:59
  • Hm. What JDBC driver are you using? What does your connection string look like? Can you post your actual Java code? Also, are you calling `rs.next()` before attempting to retrieve data? – Dan May 06 '11 at 03:44
  • I'm using Oracle JDBC driver. Also, I'm using same Java code you posted here. What do you mean by 'calling rs.next() before attempting to retrieve data?'? I execute the statement before calling resultSet.next() method. – Kapila Witharana May 06 '11 at 04:00