Oracle - SqlPrepareAndExecute fails with Fc as array-bind

[holger.mueller]

TD 6.0 SP5 / Windows 7 32-Bit, Oracle 11g Client (11.2.0.3):

Follwing example fails during SqlPrepareAndExecute with an ORA-00933 SQL command not properly ended:

Functional Class: cStructOracle_statistics
Description: NLS_INSTANCE_PARAMETERS
Derived From
Class Variables
Instance Variables
String: isDB_parameter
String: isDB_value

Function: gFcTest
Description:
Returns
Boolean:
Parameters
Static Variables
Local variables
String: sSql
Number: nPointer
FunctionalVar: iStrctOracle_statistics[*]
Class: cStructOracle_statistics
Actions
!
Set sSql =
"SELECT
'Dictonary Gets',
SUM ( gets ),
'Dictonary Misses',
SUM ( getmisses ),
'Dictonary Cache Hit Rate',
SUM ( gets-getmisses ) * 100 / SUM (gets)
FROM
sys.v_$rowcache
INTO
:iStrctOracle_statistics[nPointer+0].isDB_parameter,
:iStrctOracle_statistics[nPointer+0].isDB_value,
:iStrctOracle_statistics[nPointer+1].isDB_parameter,
:iStrctOracle_statistics[nPointer+1].isDB_value,
:iStrctOracle_statistics[nPointer+2].isDB_parameter,
:iStrctOracle_statistics[nPointer+2].isDB_value"
Set nPointer = 0
If Not SqlPrepareAndExecute( ghSql, sSql )
!

If you replace the binds with an local array like, SqlPrepareAndExecute works:

Function: gFcTest
Description:
Returns
Boolean:
Parameters
Static Variables
Local variables
String: sSql
Number: nPointer
String: saParameter[*]
String: saValue[*]
Actions
!
Set sSql =
"SELECT
'Dictonary Gets',
SUM ( gets ),
'Dictonary Misses',
SUM ( getmisses ),
'Dictonary Cache Hit Rate',
SUM ( gets-getmisses ) * 100 / SUM (gets)
FROM
sys.v_$rowcache
INTO
:saParameter[nPointer+0],
:saValue[nPointer+0],
:saParameter[nPointer+1],
:saValue[nPointer+1],
:saParameter[nPointer+2],
:saValue[nPointer+2]"
Set nPointer = 0
If Not SqlPrepareAndExecute( ghSql, sSql )
!

[Jeff Luther]

[nPointer+0]

My guess? That's the problem: the '+0'. What's nPointer assigned? From your code shot, it must be 0/zero, because you never init it. If so, then just use [0], [1], etc.

[holger.mueller]

Sorry Jeff, forgot to post the code-line "Set nPointer = 0" before SqlPrepareAndExecute

[holger.mueller]

It defintily works under 3.1 (you find the line "Set nPointer = 0" directly under the "Set sSql =" before SqlPrepareAndExecute)

[Jeff Luther]

Well, implicitly nPointer = 0 when you start, since TD init's all numbers to 0. I was just commenting that the assumption was nPointer = 0.

It definitely works under 3.1

That may be, but that was a very old TD version, Holger. Frankly, if it worked in v3.1 (and I believe you!) it was largely by 'accident'. I know the compiler got tightened in later TD, maybe 4.x somewhere, so a reference like "[nPointer+0]" is no longer valid and you will need to correct the code. And I don't recall old SQLWindows or later ever supporting this [x+y] array referencing.

I am assuming this is the problem, so change your test and see if the Oracle #933 error goes away. Let us know if this was the problem. Thanks.