Pass Parameters to Oracle Procedure

[InsaneProgrammer]

I am trying to pass 10 parameters to an Oracle procedure and get a recordset returned. The procedure executes correctly in SQL*Plus and when called from an Oracle form. I have searched microsofts knowledge base, oracles metalink, and google extensively and tried numerous ways. The code from my latest attempt is below.

<%
Conn = &quot;UID=xxx;PWD=xxx;driver=&quot; _
& &quot;{Microsoft ODBC for Oracle};SERVER=xxx;&quot;

Set Cn = Server.CreateObject(&quot;ADODB.Connection&quot

With cn
.ConnectionString = Conn
.Open
End with

Set Rs = Server.CreateObject(&quot;ADODB.Recordset&quot

Set cmdProjects = server.CreateObject(&quot;ADODB.Command&quot

pProjectNumber = &quot;&quot;
pProjectStatus = &quot;0&quot;
pProjectType = &quot;0&quot;
pProjectManager = &quot;0&quot;
pBranchName = &quot;0&quot;
pScopeColor = &quot;0&quot;
pScheduleColor = &quot;0&quot;
pBudgetColor = &quot;0&quot;
pOtherColor = &quot;0&quot;
pColor = &quot;0&quot;

With cmdProjects
.activeconnection = Cn
.commandtext = &quot;projects_pkg.filtered_projects&quot;
.commandtype = adCmdStoredProc
.parameters.append pProjectNumber
.parameters.append pProjectStatus
.parameters.append pProjectType
.parameters.append pProjectManager
.parameters.append pBranchName
.parameters.append pScopeColor
.parameters.append pScheduleColor
.parameters.append pBudgetColor
.parameters.append pOtherColor
.parameters.append pColor

Set Rs = .Execute (,,adCmdStoredProc)

End With

Do While Not Rs.eof
response.write Rs(0) & &quot;, &quot; & Rs(1) & &quot;, &quot; & Rs(2) & &quot;, &quot; & Rs(3) & &quot;, &quot; & Rs(4) & &quot;, &quot; & Rs(5) & &quot;, &quot; & Rs(6) & &quot;, &quot; & Rs(7)
response.write &quot;<BR>&quot;
Rs.MoveNext
Loop
%>

The error message I get is:
Provider error '80020005'

Type mismatch.
The line in red is where the error occurs. Any help would be greatly appreciated.



InsaneProgrammer.com

 

[ehsant]

Hi...
I think because pProjectNumber variable has String type
as you declared it like this (pProjectNumber = &quot;&quot,
and it should be integer in the procedure, you get type mismatch...

----
TNX.
E.T.

 

[InsaneProgrammer]

All of the parameters in the procedure are varchar2 (string data types).

InsaneProgrammer.com

 

[onpnt]

have you tried NULL instead of the empty space for pProjectNumber



___________________________________________________________________

[sub]

http://www.onpntwebdesigns.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page[/sub]

 

[Turkbear]

Hi, I have had sucess using 'pure' sql strings instead of building on to the command object:

This snippet shows how we first obtain a unique identifier( needed later in our stuff), then after obtaining some parameters from the user's form, we call a SP with input variables ( we don't get a recordset back since the SP is used to populate an existing table)

SqlRid = "Select report_seq.nextval from dual"
rs1 = objDC.Execute(SqlRid)
rptNbr = rs1(0)
Begdate=Request.Form("dc1")
EndDate=Request.Form("dc2")
SqlStr = "DECLARE RetVal NUMBER;BEGIN RetVal := SNOW_AND_ICE_REPORT_BUILD ("
SqlStr = SqlStr & rptNbr & ", 'TP%', 'PC','2406','9640','JOB_RSPS', 'Y', 'N', 'N', NULL,'" & BegDate & "','" & EndDate & "')"
SqlStr = SqlStr & ";COMMIT;END; "
 objDC.Execute(SqlStr)

With some adaptation, you may want to try this method to see if it works better.

 

[InsaneProgrammer]

onpnt-- I have tried putting in null. I've also tried putting in numbers of existing projects but I get the same error regardless.

Turkbear-- Thanks for the recommendation. I'll give it a try.

InsaneProgrammer.com