A classic ASP script migrated from msdaora to OraOLEDB will no long execute an Oracle SP it used to.
The SP looks like this :
TYPE tbl_CONSOLIDATED_ID IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER;
TYPE tblSAM_DD_TEXT IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
TYPE tblSAM_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
--
PROCEDURE Get_Associated_Samples(
EMPID_IN IN MAS_EMPLOYEE.EMP_AUTOID%TYPE,
ID_OUT OUT tbl_CONSOLIDATED_ID,
SAMDDTEXT_OUT OUT tblSAM_DD_TEXT,
SAMTYPE_OUT OUT tblSAM_TYPE
);
And the ASP to invoke it looks like this :
Const cProcName = "{call VERIFICATION_SUPPORT.Get_Associated_Samples(?,{resultset 100, ID_OUT, SAMDDTEXT_OUT, SAMTYPE_OUT})}"
Set MASconnLocal = Server.CreateObject("ADODB.Connection")
strCNLocalPath = ORAOLEDBCONNECTIONSTRING
MASconnLocal.Open(strCNLocalPath)
Set cmdStoredProc = Server.CreateObject ("ADODB.Command")
Set cmdStoredProc.ActiveConnection = MASconnLocal
cmdStoredProc.CommandText = cProcName
cmdStoredProc.CommandType = 1
Set paramEMPID = cmdStoredProc.CreateParameter ("EMPID_IN", adInteger, adParamInput)
paramEMPID.Value = strEMPID
cmdStoredProc.Parameters.Append paramEMPID
Set rs = cmdStoredProc.Execute
The error reported in the IIS log is :
ORA-06550:_line_1__column_54:_PLS-00201:_identifier_'ID_OUT'_must_be_declared_ORA-06550
What to do ?
From my reading so far I've read suggestions that I should, within the ASP, define parameters for each of the output parameters in the same way that I have for paramEMPID, something like this :
dim paramIDOut
dim paramSamDDTextOut
dim paramSamTypeOut
set paramIDOut = cmdStoredProc.CreateParameter ("ID_OUT" )
set paramSamDDTextOut = cmdStoredProc.CreateParameter ("SAMDDTEXT_OUT")
set paramSamTypeOut = cmdStoredProc.CreateParameter ("SAMTYPE_OUT")
cmdStoredProc.Parameters.Append paramIDOut
cmdStoredProc.Parameters.Append paramSamDDTextOut
cmdStoredProc.Parameters.Append paramSamTypeOut
However to do a 'CreateParameter' requires more arguments than I have shown there and I don't know what argument values are suitable when the SP Parameter being referred to is, for instance, a 'TABLE OF VARCHAR2(50).
I do appreciate the contemporary way of doing this is to return a ref cursor but the application is old and not long for the world, I would much prefer to change as little as possible.
