Search code examples
oracle-databaseasp-classicodbcadoterminology

What is the name of the 'resultset' argument in an ADODB.Command calling an Oracle stored procedure in Classic.ASP?

I have the pleasure of maintaining a legacy application using Classic.ASP for the frontend and an Oracle database for the backend.

We have an ongoing issues where we need to routinely update queries like the following to have an ever increasing value for the 'resultset' parameter

Set cmdStoredProc = Server.CreateObject("ADODB.Command")
cmdStoredProc.CommandText = "{call package_name.Procedure_Name(?,{resultset 1500, v_out_one, v_out_two})}"

It started at 500, then a bug fix made it 1000, then 1500, and now it has became an issue again on my watch.

Rather than follow in my predecessor's footsteps and arbitrarily increase it I'd like to know as much as possible about this feature but am struggling to find any documentation on it.

Is there a specific name given to this feature / argument / parameter? Knowing this should be enough to allow me to find out more about it but a brief explanation of it or link to documentation on it would be advantageous.

From the comments / answers it has become apparent that having the definition of the procedure that is being called could be useful:

PROCEDURE Procedure_Name
   (n_site_id_in IN TABLENAME.site_org_id%TYPE,
    v_out_one OUT t_c_out_one,
    v_out_two OUT t_c_out_two)
IS   
--Select the CC and account code and descriptions into a cursor
CURSOR c1 IS
SELECT a.out_one,
       a.out_two
FROM  TABLENAME a
WHERE a.site_org_id = n_site_id_in
ORDER BY a.out_one, a.out_two;

i INTEGER DEFAULT 1;

BEGIN

  FOR get_c1 IN c1 LOOP
   v_out_one(i) := get_c1.out_one;
   v_out_two(i) := get_c1.out_two;

   i := i + 1;
  END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('no data found');
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('sqlerrm '||SQLERRM);
 RAISE;
END Procedure_Name;

From this we can see the procedure has 3 parameters defined, 1 IN and 2 OUT, yet the call to the procedure seems to convert the 2 OUT parameters to a collection based on resultset.

The driver in use is 'Microsoft ODBC for Oracle' (MSORCL32.DLL)

Solution

  • The 'resultset' argument does not have any special name, it is just known as the resultset parameter.

    There are multiple ways it can be used:

    Return all the columns in a single result set (as it currently is):

    Set cmdStoredProc = Server.CreateObject("ADODB.Command")
cmdStoredProc.CommandText = "{call package_name.Procedure_Name(?,{resultset 1500, v_out_one, v_out_two})}"

    Return each column as a single result set (to return 2 separate result sets):

    Set cmdStoredProc = Server.CreateObject("ADODB.Command")
cmdStoredProc.CommandText = "{call package_name.Procedure_Name(?,{resultset 1500, v_out_one}, {resultset 1500, v_out_two})}"

    Read more about it here: https://learn.microsoft.com/en-us/sql/odbc/microsoft/returning-array-parameters-from-stored-procedures

    As assumed, it is used to set the limit on the amount of records that can be returned from the procedure call.

    The definition of the procedure shows that it is returning 2 arrays as output so an error will be thrown if either of them exceeds the limit set in the resultset parameter.