System.Data.OracleClient -- OracleException "No data found"

Jun 5, 2007 at 9:18 PM
I'm having an issue while trying to use the oracle persistence service with System.Data.OracleClient. When I run the samples I the client throws an OracleException with the following message:

ORA-01403: no data found
ORA-06512: at "WORKFLOW.WORKFLOW_PKG", line 118
ORA-01403: no data found
ORA-06512: at line 1

Heres the stack trace:

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
at WFTools.Services.Persistence.Ado.AdoResourceAccessor.InsertInstanceState(PendingWorkItem workItem, Guid ownerId, DateTime ownedUntil) in D:\Software Dev\WFTools\Services\Persistence\Ado\AdoResourceAccessor.cs:line 202
at WFTools.Services.Persistence.GenericWorkflowPersistenceService.Commit(Transaction transaction, ICollection workItems) in D:\Software Dev\WFTools\Services\Persistence\GenericWorkflowPersistenceService.cs:line 452
at System.Workflow.Runtime.WorkBatch.PendingWorkCollection.Commit(Transaction transaction)
at System.Workflow.Runtime.WorkBatch.Commit(Transaction transaction)
at System.Workflow.Runtime.VolatileResourceManager.Commit()
at System.Workflow.Runtime.WorkflowExecutor.DoResourceManagerCommit()
at System.Workflow.Runtime.Hosting.WorkflowCommitWorkBatchService.CommitWorkBatch(CommitWorkBatchCallback commitWorkBatchCallback)

Anyone else experience something similar?
I executed the sql scripts to create the tables and package and I'm connecting remotely to an Oracle 10g db.
I'm using the latest oracle client release.
Jun 6, 2007 at 6:31 PM
Edited Jun 6, 2007 at 6:31 PM
After a little remote debugging, I've narrowed down the source of the error. I'm still not quite sure where to go from here but what I've found is listed below.

The InsertInstanceState stored procedure throws a NO_DATA_FOUND exception in the ELSE code block of the IF statement below if the instance id does not exist. The select statement doesn't set l_exists to null, instead it throws the exception thus not allowing the instance to get persisted.

IF p_STATUS = c_STATUS_COMPLETED OR p_STATUS = c_STATUS_TERMINATED
THEN
-- remove all instance state and completed scopes
DELETE FROM INSTANCE_STATE
WHERE
INSTANCE_ID = p_INSTANCE_ID
AND (
(OWNER_ID = p_OWNER_ID AND OWNED_UNTIL >= l_NOW)
OR (OWNER_ID IS NULL AND OWNED_UNTIL IS NULL)
);

DELETE FROM COMPLETED_SCOPE
WHERE
INSTANCE_ID = p_INSTANCE_ID;
ELSE
SELECT 1 -- exception is thrown here if p_INSTANCE_ID does not exist in the INSTANCE_STATE table
INTO
l_EXISTS
FROM
INSTANCE_STATE
WHERE
INSTANCE_ID = p_INSTANCE_ID;
Jun 6, 2007 at 7:54 PM
I fixed the problem.

I did some more research on SELECT INTO and in oracle it will throw an exception everytime if no record is returned. So anyway the fix was to wrap the select into query in a begin end block and catch the NODATAFOUND exception. Handle the exception by setting l_EXISTS to NULL and raise all other exceptions.

Heres the code block to fix the issue:

BEGIN
SELECT 1
INTO
l_EXISTS
FROM
INSTANCE_STATE
WHERE
INSTANCE_ID = p_INSTANCE_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_EXISTS := NULL;
WHEN OTHERS THEN
RAISE;
END;
Coordinator
Jun 7, 2007 at 1:11 PM
Hi Tariq,

Thanks for the bug report!

I've made the following change to the package in the place where you spotted the bug...

SELECT
COUNT(*)
INTO
l_EXISTS
FROM
DUAL
WHERE
EXISTS (SELECT NULL FROM INSTANCESTATE WHERE INSTANCEID = pINSTANCEID);

This will work in all situations and will prevent the exception being thrown at all, which should help performance a fair bit!

Will be checked in shortly.

Thanks,

Dean