Posted by: m-fu...@artefrance.fr (Marco)
We use for many years CTD 1.5.1 PTF6 on Oracle 8 without any problems.
We use the native oracle router (sqlora32.dll)
Recently we decided to (just try) migrate to CTD3.0, CTD3.1 or CTD4.0,
but we discovered some 'bugs?'. Let me explain it !
We use an "h_sql" cursor.
We call some SQL queries and some PL/SQL commands, in 1.5.1 everything
works fine. But from the 3.0 version, it doesn't work.
We obtain either an ORA-24333 Iteration Zero Count error
or Centura just collapses.
Set SqlDatabase = "MYBASE"
Set SqlUser = "SCHEMA_OWNER"
Set SqlPassword = "OWNER_PASS"
If SqlConnect( h_sql )
Call SqlSetResultSet( h_sql, FALSE )
Call SqlSetParameterAll( h_sql, 5128, 1, '', TRUE )
Set s_sql = "SELECT ..... INTO ... FROM ... WHERE .... "
Call SqlPrepare( h_sql, s_sql )
Call SqlExecute( h_sql )
Call SqlFetchNext( h_sql, n_retour )
Call SqlPLSQLCommand( h_sql , 'fct_test(t, s)' )
Connected as the schema owner, it works !
But if we connect as an end-user which is not the owner of the table
or procedures or function PL/SQL, but is just granted, it failed with
ORA-24333 Iteration zero count.
All users are connected on an end-user account and never on the schema
i also tried:
Call SqlOraPLSQLPrepare( p_h_sql, s_bloc )
Call SqlOraPLSQLExecute( p_h_sql )
But it failed too!
I dont't understand why connected as the owner, it works and connected
as an end-user it failed.
Next try, connect a second cursor only for PL/SQL command, so we have
h_sql used only for SQL queries (select, insert, update, delete)
h_pl_sql used only for PL/SQL calls
And now it works, connected as Owner or End-user, all is ok.
But we must change all our app, and modify the cursor for pl/sql
So is there a bug in the centura native router for Oracle ! This is my
conclusion because in 1.5.1 it worked !