Array passed to Oracle with SqlPLSQLCommand crashes TD4.2

[colea]

I'm maintaining some code that uses SqlPLSQLCommand to call an Oracle 10g stored procedure. Two of the parameters to this procedure are arrays. When these arrays have 33 or fewer elements in them then everything performs fine, however if they have 34 or more elements then the application crashes (and so does the IDE when I was debugging it).

I'm pretty sure it's a problem with the SqlPLSQLCommand and not the stored procedure or Oracle, as traces from Oracle indicate that the procedure is never called and the same procedure called with the same values passed as parameters runs sucessfully when called from the SQLPlus Worksheet. The same traces indicate that DBMS_DESCRIBE.DESCRIBE_PROCEDURE is being called before the crash, which (I understand from some googling) is called inside SqlPLSQLCommand.

I was wondering if this is a known issue, and if so is there a way around this problem? Any suggestions would be greatly appreciated.

Thanks,
Gus

[colea]

Is there more information I could provide that would help with answering this?

I should point out that I have no real experience with using Team Developer, I am just maintaining a product that has been written using it.

[tlauzi]

Hi,

I would say this is a known issue (for me).
We do not use SqlPLSqlCommand and arrays, because in our tests, we had also crashings (not with 33 elements but with about 1000).
I could not remember all, but there are some other limitations...

Polymorphic functions/function overloading is possible in Oracle, but then SqlPLSqlCommand stops working an crashes.
e.g.
procedure test(str1 varchar2(32) );
SQL Console: execute test('teststring'); -- works
TD: SqlPLSqlCommand(" test(str1) ") -- works

now modify...
procedure test(str1 varchar2(32) , str2 varchar2(32) default NULL);
SQL Console: execute test('teststring'); -- works
TD: SqlPLSqlCommand(" test(str1) ") -- doesn´t work anymore

Regards,
Thomas L.

[Steve Leighton]

Suggestion:

Try SqlOraPLSQLPrepare() then SqlOraPLSQLExecute(). You can test the Prepare works before performing the Execute.
Note that I always make the Array params either Global variables( least desirable ) or instance variables and the Stored Proc call a Functional Class function. I run TD4.1 with Ora10G and declare my arrays as a fixed lenth value eg Number: ianNumberArrayParam[50]. Works just fine.

eg:

!!
!! First set up your SQL statement to include the Stored Proc name + array params
!!

Code: Select all

Set sSql = 'ims.ims_test.Do_Test ( :isStringParam, :inNumberParam,                                      :iasStringArrayParam, :ianNumberArrayParam, :isError    )'

!!
!! Second , check if Prepare works, and then Execute if success
!!

Code: Select all

When SqlError
                    Call ichSqlSession.LogSqlError( 'Prepare' )
                    Set ichSqlSession.sSql = sSql
                    Set bRet = FALSE

If bRet and SqlOraPLSQLPrepare( ichSqlSession.GetHandle(  ), sSql )

!!
!! Check if Execute works
!!

Code: Select all

       
          When SqlError
                    Call ichSqlSession.LogSqlError( 'Execute' )
                    Set ichSqlSession.sSql = sSql
                    Set bRet = FALSE

          If bRet and SqlOraPLSQLExecute( ichSqlSession.GetHandle( ) )
                  Return TRUE !! or whatever
          Return FALSE  !! or whatever

[colea]

Thanks for your help guys.

Steve, your suggestion worked perfectly.

[mikewill]

A bit late but just to add to the knowledgebase...
I found that connecting local dedicated cursors to use with PLSQLCommand would eventually cause a crash. Unfortunately it was rather intermittent, though if the cursor were connected early on in the run it would be ok. In the end, connecting a global cursor on startup to use with PLSQLCommand fixed the problem.

[kohhc]

Hi,

any feedback whether Unify is investigating this array issue? I am using TD5.2 with parameters of 100 elements into the procedure. Used to work perfectly in TD3.0.