storedProc return values

[scrsadmin]

I have the following oracle stored proc that takes one input and returns 7 values. My vbscript is below the stored proc. When I execute the vbscript it works fine as long as there is no null value in the record that is returned. For example in the database index4 is empty or null. The script errors out when trying to echo the results for index4 with a type mismatch. How do I resolve this so that it returns a blank or null when it finds a empty record in one of the columns?


Example database records.
Column name value
iaMainfolder T150420399
Index2 testdata1
Index3 testdata2
Index4 null or blank (no record)
Index5 null or blank (no record)
Index6 null or blank (no record)
Index7 null or blank (no record)
Index8 null or blank (no record)

ORACLE STORED PROC
***************************************************************
create or replace
PROCEDURE "IASP_FOLDEREXISTS"
( Index1 IN VARCHAR2
, Index2 OUT VARCHAR2
, Index3 OUT VARCHAR2
, Index4 OUT VARCHAR2
, Index5 OUT VARCHAR2
, Index6 OUT VARCHAR2
, Index7 OUT VARCHAR2
, Index8 OUT VARCHAR2
, Exist OUT NUMBER
) AS
BEGIN
select mf.index2 ,mf.Index3, mf.Index4, mf.Index5, mf.Index6, mf.Index7, mf.Index8,1 INTO Index2,Index3,Index4,Index5,Index6,Index7,Index8,Exist from iaMainfolder mf Where mf.appname='SCEIP' and mf.mainindex1=index1 and mf.DeleteFlag=0 ;
exception
when NO_DATA_FOUND
then Exist:=0;
'END ;
'***************************************************************

VBSCRIPT *******************************

Dim strCon
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=tst)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=SERVERNAME))); uid=USERID;pwd=PASSWORD;"

Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
oCon.Open strCon
Const adVarChar = 200
Const adInteger = 3
Const adParamOutput = 2
Const adParamInput = 1
Const recordSize = 200
inputTest1 = "T150420399"
'Set cmdStoredProc = New ADODB.Command
Set cmdStoredProc = wscript.CreateObject("ADODB.Command")
Set cmdStoredProc.ActiveConnection = oCon
cmdStoredProc.CommandText = "IAFOLDER.IASP_FOLDEREXISTS"
cmdStoredProc.CommandType = 4 'defines cmd type as stored proc

cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("inputTest1",adVarChar,adParamInput,recordSize,inputTest1)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out1",adVarChar,adParamOutput,recordSize)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out2",adVarChar,adParamOutput,recordSize)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out3",adVarChar,adParamOutput,recordSize)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out4",adVarChar,adParamOutput,recordSize)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out5",adVarChar,adParamOutput,recordSize)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out6",adVarChar,adParamOutput,recordSize)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out7",adVarChar,adParamOutput,recordSize)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out8",adVarChar,adParamOutput,recordSize)
Set rs = cmdStoredProc.Execute()
'Dim sresult AS String = Ctype(cmdStoredProc.Parameters("out1").value(),String)
num = 0
Do While Not IsEmpty(cmdStoredProc.Parameters(num))
WScript.Echo cmdStoredProc.Parameters(num)
num = num+1
loop
oCon.Close

 

[scrsadmin]

I add this to the do loop and changed the loop to run while num <> 8.
If Not IsNull(cmdStoredProc.Parameters(num)) Then


WScript.Echo cmdStoredProc.Parameters(num)

Else
WScript.Echo "NULL Value"
End if
num = num+1