4

Is there limit to either the number of params or to the overall size of a params in a TStoredProc ExecProc call?

Currently running a system that is still using the BDE to connect to Oracle and a recent change to the number of parameters to a package procedure as started producing access violations. The params count is now up to 291 and the AV is being created in the ExecProc call of TStoredProc.

If we remove a single param from the list (any param, does not have to be a specific param), the ExecProc call works fine.

I have debugged through the code and the access violation is being thrown with the TStoredProc.BindParams procedure within DBTables.pas. I have several watches set up, one of which is SizeOf(FRecordBuffer) and as I step through this procedure, the value is 65535. This is MaxWord (Windows.pas). I don't see is any specified limits within the DBTables code.

The callstack is TStoredProd.ExecProc -> TStoredProc.CreateCursor -> TStoredProc.GetCursor -> TStoredProc.BindParams and the access violation is thrown in the for-loop that iterates through the FParams.

Thanks in advance, we need to find something we can pinpoint so we can steer clear.

Mike Howard
  • 73
  • 4
  • 1
    Woohoo, you have 291 parameters in a stored procedure ? Aren't you missing some :-) ? – TLama Jul 27 '12 at 17:36
  • 1
    Yeah, Didn't write this thing, but we get to maintain it. (>_<) – Mike Howard Jul 27 '12 at 18:12
  • 1
    > *"I don't see is any specified limits within the DBTables code"* > There're various 'Word's used throughout DBTables, like 'FRecordSize'/'FRecBufSize'... Developers might have simply thought that a 'Word' should be enough for parts of the design.. – Sertac Akyuz Jul 27 '12 at 20:47

1 Answers1

0

I'm not at all versed in Oracle SQL, but since you're maintaining the thing, I would see if I could change the call with all that parameters to a single insert into a new dedicated table (with that many columns plus an autonumber primary key), and change the stored procedure to take this key as input and call the values from this new record to do its job. This may just deliver a bit faster than finding out what's the maximum number of parameters and try to find a fix there. (Though it's a bit of a strange number, as in not a power of 2, it may well be 291...)

Stijn Sanders
  • 35,982
  • 11
  • 45
  • 67