Home » Other » General » Stored function not returning result set unless recompiled (CentOS 5.4, Oracle 11g)
icon13.gif  Stored function not returning result set unless recompiled [message #463586] Fri, 02 July 2010 01:30 Go to next message
soliax
Messages: 11
Registered: July 2010
Junior Member
Hi,

I have a strange situation going on with a basic Java (1.6.0.17) application talking to an Oracle 11g (11.2.0.1.0) database. Basically what is happening is that sometimes a stored function's return value (a result set) is not making it as far as JDBC/Java, unless I recompile the stored function (with absolutely no changes to the application or function's code). I am always able to successfully see the result set if I call the function directly from within SQL Developer, just not on the Java app/JDBC side.

I've tried running with 3 different physical Oracle 11g servers and tried running the app on a couple of machines. I'm wondering if this is either some kind of caching issue or perhaps a JDBC bug/misconfiguration.

Any help would be greatly appreciated. Attached to this message:

1. stored function code
2. snippet of Java app code
3. ODBC trace output when returned a empty result set (ie failure scenario)
4. ODBC trace out when returned the correct result set (ie success scenario)
* it's helpful if you compare the texts of 3 & 4 with Vim diff or WinMerge etc.
** as they're large I've just extracted the sections that had differences. Leave a message here and I can send you the full ones if necessary.

1. stored function code
===============
FUNCTION getRecordSet (
ActionId Number
)
RETURN CallingList.ref_cursor
IS
myDataCursor CallingList.ref_cursor;
ActionId_ Number;
BEGIN

ActionId_ := ActionId;

IF isActionExpired(ActionId) <= 0 THEN

ActionId_ := 0;

END IF;

OPEN myDataCursor FOR
SELECT
C.ID,
C.CUSTOMER_ID,
C.CAMPAIGN_ID,
c.phone,
C.TRANSFERDN,
(SELECT
TTS_MESSAGE
FROM CAMPAIGN CMP
WHERE CMP.CAMPAIGN_ID = C.CAMPAIGN_ID) "TTS"
FROM
CALLING_LIST C
WHERE
C.ACTION_ID = ActionId_
AND
(
C.CALL_STATUS = 1
AND
C.CALLCOUNT > 0
)
And rownum <=5;

RETURN myDataCursor;

EXCEPTION

WHEN CURSOR_ALREADY_OPEN THEN
RETURN NULL;
WHEN INVALID_CURSOR THEN
RETURN NULL;
WHEN NO_DATA_FOUND THEN
RETURN NULL;

END getRecordSet;

....and the isActionExpired function that is called from within getResultSet is (but for all my testing it's been returning 1 with no problems)

FUNCTION isActionExpired (
ActionId number
) RETURN number
AS
Today varchar2(12);
myCount number(6);

BEGIN

myCount := 0;
today := to_char(sysdate, 'dd.mm.yyyy');

SELECT
count(*)
INTO
myCount
FROM
ACTION A
where
ACTION_ID = ActionId
AND
(
SYSDATE BETWEEN ACTION_STARTDATETIME
AND
ACTION_STOPDATETIME
)
and
(
SYSDATE BETWEEN to_date(today || ' ' || A.STARTTIME, 'dd.mm.yyyy HH24:MI:SS') and to_date(today || ' ' || A.ENDTIME, 'dd.mm.yyyy HH24:MI:SS')
)
AND
ACTION_STATUS = 1;

return myCount;

END isActionExpired;


2. snippet of Java app code
=================
... db connect logic...
javax.management.MBeanServer mbs = null;
javax.management.ObjectName name = null;
try {
String loader = Thread.currentThread().getContextClassLoader().toString().replaceAll("[,=:\"]+", "");
name = new javax.management.ObjectName("com.oracle.jdbc:type=diagnosability,name="+loader);
mbs = java.lang.management.ManagementFactory.getPlatformMBeanServer();
mbs.setAttribute(name, new javax.management.Attribute("LoggingEnabled", true));
} catch (Exception e) {
System.out.println("ORACLE TRACE ERROR: " + e.getStackTrace());
}
try {
String query = "begin ? := CALLINGLIST.getRecordSet(?); end;";
CallableStatement stmt = conn.prepareCall(query);
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.setInt(2, actionId);
stmt.execute();
ResultSet rs = (ResultSet) stmt.getObject(1); // So, here it works.
// print the results
int count=0;
while (rs.next()) {
count++;
}
stmt.close();
System.out.println("rs count was: " + count);
} catch (SQLException e) {
System.out.println("Exception occurred: " + e.getMessage());
}


3. ODBC trace output when returned a empty result set (ie failure scenario)
===============================================
Jul 1, 2010 3:30:47 PM oracle.net.ns.Packet receive
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.net.ns.Packet receive
TRACE_20: Debug: type=6, length=121, flags=0
00 79 00 00 06 00 00 00 |.y......|
00 00 06 22 01 06 00 01 |..."....|
0A 00 00 00 07 03 C2 04 |........|
0E 04 C3 5E 22 03 02 C1 |...^"...|
2A 04 33 30 30 32 03 37 |*.3002.7|
37 37 05 48 65 6C 6C 6F |77.Hello|
08 01 06 00 00 01 02 00 |........|
00 00 00 00 00 04 01 05 |........|
01 01 02 05 7B 00 00 01 |....{...|
02 00 03 00 00 00 00 00 |........|
00 00 00 00 00 00 00 00 |........|
00 01 01 00 00 00 00 19 |........|
4F 52 41 2D 30 31 34 30 |ORA-0140|
33 3A 20 6E 6F 20 64 61 |3:.no.da|
74 61 20 66 6F 75 6E 64 |ta.found|
0A |. |

Jul 1, 2010 3:30:47 PM oracle.net.ns.Packet receive
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion CHARBytesToJavaChars
TRACE_16: Enter: [B@1fa1bb6, 0, [C@1b000e7, 1, [I@1315d34, 20
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion _CHARBytesToJavaChars
TRACE_16: Enter: [B@1fa1bb6, 0, [C@1b000e7, 1, 871, [I@1315d34, 20, oracle-character-set-830, oracle-character-set-2000, oracle-character-set-871, false
Jul 1, 2010 3:30:47 PM oracle.sql.CharacterSet convertUTFBytesToJavaChars
TRACE_16: Enter: [B@1fa1bb6, 0, [C@1b000e7, 1, [I@1315d34, true, 20
Jul 1, 2010 3:30:47 PM oracle.sql.CharacterSet convertUTFBytesToJavaChars
TRACE_16: return: 4
Jul 1, 2010 3:30:47 PM oracle.sql.CharacterSet convertUTFBytesToJavaChars
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion _CHARBytesToJavaChars
TRACE_16: return: 4
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion _CHARBytesToJavaChars
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion CHARBytesToJavaChars
TRACE_16: return: 4
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion CHARBytesToJavaChars
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion CHARBytesToJavaChars
TRACE_16: Enter: [B@1fa1bb6, 0, [C@1b000e7, 211, [I@1de256f, 10
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion _CHARBytesToJavaChars
TRACE_16: Enter: [B@1fa1bb6, 0, [C@1b000e7, 211, 871, [I@1de256f, 10, oracle-character-set-830, oracle-character-set-2000, oracle-character-set-871, false
Jul 1, 2010 3:30:47 PM oracle.sql.CharacterSet convertUTFBytesToJavaChars
TRACE_16: Enter: [B@1fa1bb6, 0, [C@1b000e7, 211, [I@1de256f, true, 10
Jul 1, 2010 3:30:47 PM oracle.sql.CharacterSet convertUTFBytesToJavaChars
TRACE_16: return: 3
Jul 1, 2010 3:30:47 PM oracle.sql.CharacterSet convertUTFBytesToJavaChars
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion _CHARBytesToJavaChars
TRACE_16: return: 3
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion _CHARBytesToJavaChars
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion CHARBytesToJavaChars
TRACE_16: return: 3
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion CHARBytesToJavaChars
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion CHARBytesToJavaChars
TRACE_16: Enter: [B@1fa1bb6, 0, [C@1b000e7, 321, [I@16bd8ea, 2000
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion _CHARBytesToJavaChars
TRACE_16: Enter: [B@1fa1bb6, 0, [C@1b000e7, 321, 871, [I@16bd8ea, 2000, oracle-character-set-830, oracle-character-set-2000, oracle-character-set-871, false
Jul 1, 2010 3:30:47 PM oracle.sql.CharacterSet convertUTFBytesToJavaChars
TRACE_16: Enter: [B@1fa1bb6, 0, [C@1b000e7, 321, [I@16bd8ea, true, 2000
Jul 1, 2010 3:30:47 PM oracle.sql.CharacterSet convertUTFBytesToJavaChars
TRACE_16: return: 5
Jul 1, 2010 3:30:47 PM oracle.sql.CharacterSet convertUTFBytesToJavaChars
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion _CHARBytesToJavaChars
TRACE_16: return: 5
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion _CHARBytesToJavaChars
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion CHARBytesToJavaChars
TRACE_16: return: 5
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.DBConversion CHARBytesToJavaChars
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.T4CConnection updateSessionProperties
TRACE_16: Enter: [Loracle.jdbc.internal.KeywordValue;@16e1fb1
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.T4CConnection updateSessionProperties
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.T4CStatement fetch
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement checkValidRowsStatus
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement checkValidRowsStatus
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl <init>
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.T4CResultSetAccessor getCursor
TRACE_16: return: oracle.jdbc.driver.OracleResultSetImpl@e2cb55
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.T4CResultSetAccessor getCursor
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.ResultSetAccessor getObject
TRACE_16: return: oracle.jdbc.driver.OracleResultSetImpl@e2cb55
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.ResultSetAccessor getObject
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleCallableStatement getObject
TRACE_1: return: oracle.jdbc.driver.OracleResultSetImpl@e2cb55
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleCallableStatement getObject
TRACE_1: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleCallableStatementWrapper getObject
TRACE_30: return: oracle.jdbc.driver.OracleResultSetImpl@e2cb55
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleCallableStatementWrapper getObject
TRACE_30: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl next
TRACE_1: Public Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl next
TRACE_20: Debug: closed=false, statement.currentRow=-1, statement.totalRowsVisited=0, statement.maxRows=0, statement.validRows=1, statement.gotLastBatch=true
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl next
TRACE_1: return: true
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl next
TRACE_1: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl next
TRACE_1: Public Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl next
TRACE_20: Debug: closed=false, statement.currentRow=0, statement.totalRowsVisited=1, statement.maxRows=0, statement.validRows=1, statement.gotLastBatch=true
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl internal_close
TRACE_16: Enter: false
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.BaseResultSet close
TRACE_16: Public Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.BaseResultSet close
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection registerHeartbeat
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection registerHeartbeat
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection needLine
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection needLineUnchecked
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection needLineUnchecked
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection needLine
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.T4CStatement closeQuery
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.T4CConnection assertLoggedOn
TRACE_16: Enter: "oracle.jdbc.driver.T4CStatement.closeQuery"
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.T4CConnection assertLoggedOn
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.T4CStatement closeQuery
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement endOfResultSet
TRACE_16: Enter: false
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement prepareForNewResults
TRACE_16: Enter: false, false
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement clearWarnings
TRACE_16: Public Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement clearWarnings
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl internal_close
TRACE_16: Enter: true
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl internal_close
TRACE_16: return:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl internal_close
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement prepareForNewResults
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.T4CStatement clearDefines
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement clearDefines
TRACE_16: Public Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement freeLine
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement freeLine
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement cleanupDefines
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection cacheBuffer
TRACE_16: Enter: [B@8e32e7
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.BufferCache put
TRACE_16: Enter: [B@8e32e7
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.BufferCache put
TRACE_30: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection cacheBuffer
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection cacheBuffer
TRACE_16: Enter: [C@1b000e7
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.BufferCache put
TRACE_16: Enter: [C@1b000e7
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.BufferCache put
TRACE_30: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection cacheBuffer
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement cleanupDefines
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement clearDefines
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.T4CStatement clearDefines
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement endOfResultSet
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl internal_close
TRACE_16: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl next
TRACE_1: return: false
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleResultSetImpl next
TRACE_1: Exit
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleCallableStatementWrapper close
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OraclePreparedStatementWrapper close
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatementWrapper close
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement close
TRACE_1: Public Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.OracleStatement closeOrCache
TRACE_16: Enter: null
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection isStatementCacheInitialized
TRACE_16: Enter:
Jul 1, 2010 3:30:47 PM oracle.jdbc.driver.PhysicalConnection isStatementCacheInitialized


4. ODBC trace out when returned the correct result set (ie success scenario)
===============================================
TRACE_16: Enter:
Jul 1, 2010 3:30:07 PM oracle.net.ns.Packet receive
TRACE_20: Debug: type=6, length=82, flags=0
00 52 00 00 06 00 00 00 |.R......|
00 00 08 01 06 00 00 01 |........|
02 00 00 00 00 00 00 04 |........|
01 05 00 02 05 7B 00 00 |.....{..|
01 02 00 03 00 00 00 00 |........|
00 00 00 00 00 00 00 00 |........|
00 00 01 01 00 00 00 00 |........|
19 4F 52 41 2D 30 31 34 |.ORA-014|
30 33 3A 20 6E 6F 20 64 |03:.no.d|
61 74 61 20 66 6F 75 6E |ata.foun|
64 0A |d. |

Jul 1, 2010 3:30:07 PM oracle.net.ns.Packet receive
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.T4CConnection updateSessionProperties
TRACE_16: Enter: [Loracle.jdbc.internal.KeywordValue;@1fa1bb6
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.T4CConnection updateSessionProperties
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.T4CStatement fetch
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleResultSetImpl internal_close
TRACE_16: Enter: false
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.BaseResultSet close
TRACE_16: Public Enter:
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.BaseResultSet close
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.PhysicalConnection registerHeartbeat
TRACE_16: Enter:
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.PhysicalConnection registerHeartbeat
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.PhysicalConnection needLine
TRACE_16: Enter:
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.PhysicalConnection needLineUnchecked
TRACE_16: Enter:
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.PhysicalConnection needLineUnchecked
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.PhysicalConnection needLine
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.T4CStatement closeQuery
TRACE_16: Enter:
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.T4CConnection assertLoggedOn
TRACE_16: Enter: "oracle.jdbc.driver.T4CStatement.closeQuery"
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.T4CConnection assertLoggedOn
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.T4CStatement closeQuery
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement endOfResultSet
TRACE_16: Enter: false
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement prepareForNewResults
TRACE_16: Enter: false, false
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement clearWarnings
TRACE_16: Public Enter:
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement clearWarnings
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement prepareForNewResults
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.T4CStatement clearDefines
TRACE_16: Enter:
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement clearDefines
TRACE_16: Public Enter:
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement freeLine
TRACE_16: Enter:
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement freeLine
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement cleanupDefines
TRACE_16: Enter:
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.PhysicalConnection cacheBuffer
TRACE_16: Enter: [B@8e32e7
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.BufferCache put
TRACE_16: Enter: [B@8e32e7
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.BufferCache put
TRACE_30: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.PhysicalConnection cacheBuffer
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.PhysicalConnection cacheBuffer
TRACE_16: Enter: [C@1b000e7
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.BufferCache put
TRACE_16: Enter: [C@1b000e7
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.BufferCache put
TRACE_30: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.PhysicalConnection cacheBuffer
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement cleanupDefines
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement clearDefines
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.T4CStatement clearDefines
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleStatement endOfResultSet
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleResultSetImpl internal_close
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleResultSetImpl <init>
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.T4CResultSetAccessor getCursor
TRACE_16: return: oracle.jdbc.driver.OracleResultSetImpl@1315d34
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.T4CResultSetAccessor getCursor
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.ResultSetAccessor getObject
TRACE_16: return: oracle.jdbc.driver.OracleResultSetImpl@1315d34
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.ResultSetAccessor getObject
TRACE_16: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleCallableStatement getObject
TRACE_1: return: oracle.jdbc.driver.OracleResultSetImpl@1315d34
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleCallableStatement getObject
TRACE_1: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleCallableStatementWrapper getObject
TRACE_30: return: oracle.jdbc.driver.OracleResultSetImpl@1315d34
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleCallableStatementWrapper getObject
TRACE_30: Exit
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleResultSetImpl next
TRACE_1: Public Enter:
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleResultSetImpl next
TRACE_20: Debug: closed=true, statement.currentRow=-1, statement.totalRowsVisited=0, statement.maxRows=0, statement.validRows=0, statement.gotLastBatch=false
Jul 1, 2010 3:30:07 PM oracle.jdbc.driver.OracleResultSetImpl next

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463917 is a reply to message #463586] Mon, 05 July 2010 03:19 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Try running only procedure on the SQLPlus , if it gives results on SQL plus then check in other code.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463954 is a reply to message #463917] Mon, 05 July 2010 07:17 Go to previous messageGo to next message
soliax
Messages: 11
Registered: July 2010
Junior Member
Hi,

I tried that but got the same problem:

Ie.,
1. When the stored function doesn't return the correct result set, it's neither picked up by my Java application nor sqlplus. It is however picked up by SQL Developer.
2. If I recompile the stored function's package body, then both the Java app and sqlplus can also see the result set when I execute the function call again.

Regards,
Simon.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463959 is a reply to message #463586] Mon, 05 July 2010 07:25 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member
Quote:
doesn't return the correct result set
And by that you mean ? An exception or what ?

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463960 is a reply to message #463959] Mon, 05 July 2010 07:29 Go to previous messageGo to next message
soliax
Messages: 11
Registered: July 2010
Junior Member
I mean an empty record set is being returned to my Java app/sqlplus but a record set with 1 row (ie the correct record set) is being returned to SQL Developer.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463962 is a reply to message #463960] Mon, 05 July 2010 07:44 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You don't close the ResultSet in Java (and probably SQL*Plus doesn't close it either, perhaps SQL Developer does).

So I guess you run into the CURSOR_ALREADY_OPEN exception, and Null is returned.

Remove the exception handler and see what happens. And then only put those exceptions in the handler that you actually *expect* to happen.

CURSOR_ALREADY_OPEN, INVALID_CURSOR and NO_DATA_FOUND will as far as I can see never happen under "normal" circumstances, but only when there is a real error happening. So don't handle them, let them rise.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463963 is a reply to message #463962] Mon, 05 July 2010 07:49 Go to previous messageGo to next message
soliax
Messages: 11
Registered: July 2010
Junior Member
Thanks for your quick reply. I'll try that.

With the actual Java app I am using now I am closing my Java ResultSet.

After a bit of digging around it seems that the only way to close the Oracle cursor is to actually disconnect from the database. I think if I try closing the cursor from within the stored function it would be premature and Java would lose it's reference to the result set wouldn't it?

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463964 is a reply to message #463963] Mon, 05 July 2010 07:57 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The Oracle cursor gets closed when you close the ResultSet it is passed in.

I'm not sure what happens if you open the cursor and then never return it to the calling program, though, like you do because of that exception handlers.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463966 is a reply to message #463964] Mon, 05 July 2010 08:00 Go to previous messageGo to next message
soliax
Messages: 11
Registered: July 2010
Junior Member
I commented out the exception handling but still have the same issue. No exceptions are thrown when I call from sqlplus.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463967 is a reply to message #463966] Mon, 05 July 2010 08:10 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member
Close and Dispose

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463969 is a reply to message #463967] Mon, 05 July 2010 08:14 Go to previous messageGo to next message
soliax
Messages: 11
Registered: July 2010
Junior Member
Quote:
Close and Dispose

Sorry, could you be a little more specific please?

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463972 is a reply to message #463954] Mon, 05 July 2010 08:42 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
soliax wrote on Mon, 05 July 2010 07:17
Hi,

I tried that but got the same problem:

Ie.,
1. When the stored function doesn't return the correct result set, it's neither picked up by my Java application nor sqlplus. It is however picked up by SQL Developer.
2. If I recompile the stored function's package body, then both the Java app and sqlplus can also see the result set when I execute the function call again.

Regards,
Simon.


copy and paste Post you sql Seesion.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463973 is a reply to message #463969] Mon, 05 July 2010 08:42 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member
Im not a JAVA developer, so I should have been quite.
I mixed it up with a similar problem with some .NET developers using ODP where you always have to close and dispose your resources.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463977 is a reply to message #463973] Mon, 05 July 2010 08:56 Go to previous messageGo to next message
soliax
Messages: 11
Registered: July 2010
Junior Member
I had modified the code to return a couple of OUT parameters (SQL error code and error message during exceptions, but they're not doing much now as I've commented out the exception handlers currently).

Stored Function code:
=====================

FUNCTION getRecordSet (
ActionId Number,
ecode OUT number,
errmsg OUT varchar2
)
RETURN CallingList.ref_cursor
IS
myDataCursor CallingList.ref_cursor;
ActionId_ Number;
BEGIN

ActionId_ := ActionId;

ecode := 0;
errmsg := 'no error';
/*
IF isActionExpired(ActionId) <= 0 THEN

ActionId_ := 0;

END IF;
*/
OPEN myDataCursor FOR
SELECT
C.ID,
C.CUSTOMER_ID,
C.CAMPAIGN_ID,
c.phone,
C.TRANSFERDN,
(SELECT
TTS_MESSAGE
FROM CAMPAIGN CMP
WHERE CMP.CAMPAIGN_ID = C.CAMPAIGN_ID) "TTS"
FROM
CALLING_LIST C
WHERE
C.ACTION_ID = ActionId_
AND
(
C.CALL_STATUS = 1
AND
C.CALLCOUNT > 0
)
And rownum <=5;

RETURN myDataCursor;

CLOSE myDataCursor; // this was just an experiment, doesn't make any difference, I still get the same problem

/*
EXCEPTION

WHEN CURSOR_ALREADY_OPEN THEN
ecode := SQLCODE;
errmsg := 'CURSOR_ALREADY_OPEN :' || SQLERRM;
RETURN NULL;
WHEN INVALID_CURSOR THEN
ecode := SQLCODE;
errmsg := 'INVALID_CURSOR :' || SQLERRM;
RETURN NULL;
WHEN NO_DATA_FOUND THEN
ecode := SQLCODE;
errmsg := 'NO_DATA_FOUND :' || SQLERRM;
RETURN NULL;
WHEN OTHERS THEN
ecode := SQLCODE;
errmsg := SQLERRM;
RETURN NULL;
*/
END getRecordSet;


1. From SQL　Developer:

Calling code:
=============

var c refcursor;
var ecode number;
var errmsg varchar2(2000);
exec :c := callinglist.getrecordset(121,:ecode,:errmsg);
print;

Result:
=======

anonymous block completed
ecode
-
0

c
----------------------------------------------------------------------------------------------------------------
ID CUSTOMER_ID CAMPAIGN_ID PHONE TRANSFERDN TTS
---------------------- ---------------------- ---------------------- -------------------- ---------- -----------
352 933301 1 3001 777 Hello World

errmsg
--------
no error

2. From SQLPLUS:

Calling Code:
=============

SQL> set autoprint on;
SQL> var c refcursor;
SQL> var ecode number;
SQL> var errmsg varchar2(2000);
SQL> exec :c := callinglist.getrecordset(121,:ecode,:errmsg);

Result:
========

PL/SQL procedure completed successfully.


ERRMSG
----------------------------------------

no error


ECODE
----------
0


No records were selected.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463982 is a reply to message #463977] Mon, 05 July 2010 09:22 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member
For the SQL*Plus session type 
print c
after the execution is done.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #463983 is a reply to message #463982] Mon, 05 July 2010 09:29 Go to previous messageGo to next message
soliax
Messages: 11
Registered: July 2010
Junior Member
tahpush wrote on Mon, 05 July 2010 23:22
For the SQL*Plus session type 
print c
after the execution is done.

When correctly getting the record set returned in SQL Developer but not in SQLPlus, here's what I get:

SQL> exec :c := callinglist.getrecordset(121,:ecode,:errmsg);
...
SQL> print c;
SP2-0625: Error printing variable variable_name

My guess being because nothing was returned, nothing was assigned to c.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #464018 is a reply to message #463983] Mon, 05 July 2010 22:16 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
soliax wrote on Mon, 05 July 2010 09:29
tahpush wrote on Mon, 05 July 2010 23:22
For the SQL*Plus session type 
print c
after the execution is done.

When correctly getting the record set returned in SQL Developer but not in SQLPlus, here's what I get:

SQL> exec :c := callinglist.getrecordset(121,:ecode,:errmsg);
...
SQL> print c;
SP2-0625: Error printing variable variable_name

My guess being because nothing was returned, nothing was assigned to c.


Yes nothign was assigne to C

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #464019 is a reply to message #464018] Mon, 05 July 2010 22:20 Go to previous messageGo to next message
soliax
Messages: 11
Registered: July 2010
Junior Member
Any idea what may cause this? By simpling recompiling the stored function's body (with no code or data changes) then if I re-execute the stored function, c gets assigned the result set...

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #464025 is a reply to message #464019] Mon, 05 July 2010 22:39 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
chcek if data exists in Query if not Then Open myDataCursor for selcet -1 from dual;
set the dual query in exception too

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #464029 is a reply to message #464025] Mon, 05 July 2010 23:21 Go to previous messageGo to next message
soliax
Messages: 11
Registered: July 2010
Junior Member
Thanks for the suggestion. I may have narrowed the problem down. Up until now I'd often tested whether the select query was actually getting any data and it always was (I was just using SQL Developer).

Now, when I used a shortened form of the query:

1. select * from calling_list c where c.action_id=121 and c.callcount > 0 and c.call_status=1;
2. select * from calling_list c where c.action_id=121 and c.callcount > 0;

My results were:
- Using SQL developer; both 1. & 2. successfully returned the result set
- Using SQLPLUS, only 2. returned the result set until I recompiled the stored function body and then re-running 1. also returned the result set

The difference being, c.call_status, a simple number type column. Not sure what could be the cause here.

Simon.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #464236 is a reply to message #464029] Wed, 07 July 2010 01:24 Go to previous messageGo to next message
soliax
Messages: 11
Registered: July 2010
Junior Member
I've resolved this problem. It turned out to be a non-problem due to incorrect usage of SQL Developer.

I was selecting 2 lines in SQL Developer (update and commit) but the icon I was pressing was only executing the first line, hence the results of only seeing the expected changes in SQL Developer and nowhere else.

To those who answered, thank you and sorry for wasting your time.

Report message to a moderator

Re: Stored function not returning result set unless recompiled [message #464241 is a reply to message #464236] Wed, 07 July 2010 01:39 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member
Thanks for the feedback

Report message to a moderator

Previous Topic: Help on Oracle topics
Next Topic: Compatibility between TimesTen and Oracle databases
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jan 14 00:34:39 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.