0

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)

RyanfaeScotland
  • 1,216
  • 11
  • 30
  • I think knowing the database engine you use may be more useful here than the development language because it seems very related to the ```call procedure``` parameters – Olivier Depriester Jul 24 '19 at 11:30
  • Thanks @OlivierDepriester, added the details to the question. – RyanfaeScotland Jul 24 '19 at 11:34
  • Sorry but the connection provider you use to connect to Oracle seems to be important also : I have found some articles saying that if you use OraOLEDB.Oracle, this parameter should not be bound to the call. You must have it in your connection string – Olivier Depriester Jul 24 '19 at 11:55
  • Apologies @OlivierDepriester, the terminology here sometimes confuses me, are you looking for the driver I use: 'Microsoft ODBC for Oracle' (found through ODBC Data Source Administrator) – RyanfaeScotland Jul 24 '19 at 12:03

2 Answers2

1

Your procedure package_name.Procedure_Name must return a cursor as an out parameter.
This resultset parameter let me think of a parameter defining the number of cursors that can be open at the same time.
The fact is it does not seem to be the right way of doing things because it means that each time the procedure is called, the cursor is not closed.
In your code you must have stg like 

Set myRecordSet = cmdStoredProc.Execute()

This recordset is used to read the cursor content. Please check that it is closed after usage with

myRecordSet.Close()
Set myRecordset = Nothing
Olivier Depriester
  • 1,615
  • 1
  • 7
  • 20
  • Ah interesting. Yes, there is a cursor in the procedure. I assumed it wasn't part of the out parameters since it isn't mentioned in the procedure's signature. This has gave me a good lead to go on, thanks. – RyanfaeScotland Jul 24 '19 at 12:50
  • I've added the details of the procedure into the question. As you can see it does use a cursor as you suspected. My questions still stands though and although your answer has definitely helped refine things I don't think it quite addresses what I'm looking for. If you are interested in updating it then what I'm trying to find out is the term / explanation / documentation for this style of call, where it appears `resultset` is being used to create an array of the output parameters rather than them being set directly. Great help so far though, thanks again. – RyanfaeScotland Jul 24 '19 at 14:22
  • A good thing that you posted the stored procedure code. It is not a cursor issue. Your 2 OUT variables are arrays and ```resultset 1500``` sets the max length of these arrays. I guess the number of rows returned by the ```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;``` query inside the SP increases from times to times and the error you get is equivalent to an 'Out of bounds' – Olivier Depriester Jul 24 '19 at 15:56
  • Yes, I think we are getting to the answer. But you appear to be getting side tracked into trying to solve the problem that this 'style' has caused, as much as I appreciate it please don't! I'm looking for information on this 'technique', not how to solve the issue (which I mentioned just for context)!! Since we spoke I've found some other useful questions that seem related: https://stackoverflow.com/questions/13494938/return-resultset-from-oracle-11g-stored-procedure-using-classic-asp and https://stackoverflow.com/questions/23197422/classic-asp-oraoledb-how-to-define-out-param-containing-table – RyanfaeScotland Jul 24 '19 at 16:19
  • Found what I was looking for - https://learn.microsoft.com/en-us/sql/odbc/microsoft/returning-array-parameters-from-stored-procedures I'll come back to this question later if you are interested in adjusting your answer to fit? If not I'll post my own but link through to the help you gave. – RyanfaeScotland Jul 24 '19 at 16:37
0

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.

RyanfaeScotland
  • 1,216
  • 11
  • 30