firstly my apologies to all if I am replicating an entry, but I created a log a week or so ago but cannot now find any reference to it, so assume I did not properly save it, hence re-entering here.
I have seen a reference to a similar problem associated with the java connector, and an attached reference to that saying the problem I was about to describe was fixed in 10.1.21 but it is still an issue folloiwng upgrading to 10.1.21 so hence raising it again.
Essentially if I define a view then refer to that view within an EXISTS statement in a WHERE clause within a stored procedure, the thread crashes and the connection is lost the second time that the procedure is called, for example:
FROM MYTABLE MT
WHERE EXISTS (SELECT *
FROM VIEW_TABLE_ACCESS_CHECK MV
WHERE MV.ENTITY_UID = MT.ENTITY_UID
AND MV.ACCESS_USER = SESSION_USER()
AND MV.ACCESS_TYPE = 'SELECT' );
The problem referred to in other contexts is that there is a problem with prepared statements crashing. the work around mentioned in the connector bug report is not to use prepared statements for queries like this. However my understanding of procedures is that by definition they are prepared when they are loaded into cach and re-used so parameter to not used prepared statements does not apply to procedures.
I am using this approach to build a security framework that tests whether users are authorised to access data and only allows data that is approrpriate privileged, so its got to go in a procedure as I dont want any access to these tables excepth were allowed by the procedure.
I have a workaround which is to insert the entire body of the view in the query, but this really explodes the code. Also I was hoping to extend the model for checking and in paritcular include oqgraph in this model, which if I can use a view, then simply re-write / extend the view then I can apply the change to the entire code base at a stroke. Unfortuantely without access to a view in this situation I have a huge amount of code changes to do to change that model.
Consequently being able to use views in this context if it is possible to get resolution of what looks like a long standing bug would be appreciated.