Thanks, Larry. Yes I was limiting the resultset just to see if I could get anything back at all first. And I'm sure you are right that I could get away with 1 Get procedure, but I'm just now getting my feet wet with PL/SQL and haven't gotten to the point of checking parameters for NULL yet.

I *thought* we had to use the {resultset} syntax for OUT parameters that return cursors, but Mark's most-recent message seems to indicate that he has it working without that -- I'm not sure how.

The syntax of passing the {resultset ...} parameter works for me IF I use the coding style in my package where I have to define a table for each columm, loop through the result set, and populate each table array. That's fine, but way more code than seems to be necessary. If I could get it to work with the simple SELECT into a REF CURSOR the way you and Mark seem to indicate, I'm ready to go the package route for my data access.

But if I use this {resultset...} syntax against a package procedure that returns a REF CURSOR, I end up with the error Connectivity error: [Microsoft][ODBC driver for Oracle]Cannot bind PL/SQL Records or Cursor References, which I think was my initial issue.

I'm pulling my hair out, which is very hard because I now have even less than shows in that ~5-year old picture!. Thanks for your help.


>I don't see any reason why the same syntax shouldn't work for both GetByPK and GetAll. You could also make it the same procedure and make the IN parameter optional with a default of NULL. If it's NULL, do a select all instead of filtering it with a WHERE clause. It looks like you have limited the amount of data that can be returned. Change the call to:

>cSQL = [{call Test_Data.GetAll({resultset 100000,npk,ctestchar, ctestvc2, dtestdate, ntestdecimal})}]
>? sqlexec(xx, cSQL, "c_test")

>The first number in the resultset determines the number of rows returned. I don't know if 100,000 is permissible. I have only tested using up to 12000 without any problems. This technique returns 9955 rows from Oracle on a 10MBit connection in about 1.3 seconds. The same query using a Ref Cursor and the Oracle ODBC driver takes 14 seconds.
>
>BTW, I am using Microsft ODBC for Oracle driver version 2.573.7713.00.
>
>HTH.