Calling oracle function using database link

Question

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"

What precise problem do you have? Please give us something to go on - error messages, description of behaviour, etc — APC
– APC, Commented May 5, 2011 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
– Kapila Witharana, Commented May 6, 2011 at 0:00

Andrew · Accepted Answer · 2011-10-17 06:26:59Z

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:

Use PL-SQL Data table. OR
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)

answered Oct 17, 2011 at 6:26

Andrew

6,3414 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan · Accepted Answer · 2011-05-06 02:49:22Z

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);

answered May 6, 2011 at 2:49

Dan

11.1k7 gold badges55 silver badges82 bronze badges

3 Comments

Kapila Witharana Over a year ago

Still it gives "ORA-24338: statement handle not executed" error

Dan Over a year ago

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?

Kapila Witharana Over a year ago

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.

Collectives™ on Stack Overflow

Calling oracle function using database link

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related