经典ASP使用OraOleadb驱动程序调用Oracle存储过程 [英] Classic ASP calling Oracle stored procedure with OraOleadb Driver

问题描述

我面临一个非常奇怪的问题，可能是我错过了一些东西.

I am facing a very strange problem may be I have missed something.

在这种情况下，我们有一个使用Oracle 11G作为后端数据库的ASP应用程序.

Here is the scenario, we have an ASP application which uses Oracle 11G as backend database.

我们正在调用接受这些参数的存储过程.

We are calling a store stored procedure which is accepting these parameters.

PROCEDURE PR_SUMMARY_IN(theAccountId in varchar2,
                        theAwardId in number,
                        theDueDate in varchar2,
                        theSubmittedDate in varchar2,
                        theReportDescription in varchar2,
                        theFormId in varchar2,
                        theReturnCode out number) 
AS
    v_submitted_date date;
BEGIN
      IF theSubmittedDate IS NOT NULL THEN                                    
                v_submitted_date := to_date(theSubmittedDate,'MM/DD/YYYY');--error here         
            ELSE                                                                    
                v_submitted_date := NULL;                                           

            END IF;

       insert into abc (.....) values (....)
    end

以及以下参数值

'3407840001'
8714898
'05/09/2016'
'05/09/2016'
'Test'
'1'

当我从SQLPlus运行此过程时，它可以工作，但是当我从ASP代码中调用它时，它会引发错误

When I am running this procedure from SQLPlus, it works, but when I am calling it from ASP code, it throws an error

ORA-20502:ORA-01858:在需要数字的地方找到了一个非数字字符.

ORA-20502: ORA-01858: a non-numeric character was found where a numeric was expected.

下面是ASP代码快照

due_date = Request.Form("due_date" & i)
        IF Len(due_date) = 0 THEN
            theDueDt = NULL
        ELSE
            theDueDt = "'" & due_date & "'"
        END IF

        submitted_date = Request.Form("submitted_date" & i)
        IF Len(submitted_date) = 0 THEN
            theSubmittedDt = NULL
        ELSE
            theSubmittedDt = "'" & submitted_date & "'"
        END IF

        report_description = Request.Form("report_description" & i)
        IF Len(report_description) = 0 THEN
            theReportDesc = NULL
        ELSE
            theReportDesc = "'" & Replace(report_description,"'","''") & "'"
        END IF

        form_id = Request.Form("form_id" & i)
        IF Len(form_id) = 0 THEN
            theFrmId = NULL
        ELSE
            theFrmId = "'" & Replace(form_id,"'","''") & "'"
        END IF  

        cmd.CommandType = 4
        cmd.CommandText = "deadlines_summary.PR_SUMMARY_IN" 

        cmd.Parameters.Append cmd.CreateParameter("theAccountId", 12, 1, 100, Request.Form ("aid"))
        cmd.Parameters.Append cmd.CreateParameter("theAwardId", adNumeric, 1, 100, award_id)
        cmd.Parameters.Append cmd.CreateParameter("theDueDate", 12, 1, 100, theDueDt)
        cmd.Parameters.Append cmd.CreateParameter("theSubmittedDate", 12, 1, 100, theSubmittedDt)
        cmd.Parameters.Append cmd.CreateParameter("theReportDescription", 12, 1, 100, theReportDesc)
        cmd.Parameters.Append cmd.CreateParameter("theFormId", 12, 1, 100, theFrmId)

        cmd.Parameters.Append cmd.CreateParameter("theReturnCode", 131, 2, 10)


        IF Len(report_description) > 0 THEN    
            set rs = cmd.execute
        END IF

请提出任何建议

出于测试目的，我在SP中将局部变量声明为varchar2，并以mm/dd/yyyy格式分配了值'12/09/2016'.

For testing purpose I have declared local variable as varchar2 in SP and assigned value '12/09/2016' in mm/dd/yyyy format.

，并低于日志.之前和之后.

and got below log. Before and after.

如果值是硬编码的，则不使用单引号将其处理.想知道如何?

if value hard coded, it treated without single quote. Wondering how?

CREATED_ON----------MSG--------------------------------------------------------------------------------

05/09/2016          1.1 theDueDate '12/09/2016' tmp_theDueDate 12/09/2016 v_due_date

05/09/2016          1.2 theSubmittedDate '11/09/2016' tmp_theSubmittedDate 11/09/2016 v_submitted_date

05/09/2016          2.1 theSubmittedDate '11/09/2016' tmp_theSubmittedDate 11/09/2016 v_submitted_date 2016-11-09 00:00:00

05/09/2016          2.2 theDueDate '12/09/2016' tmp_theDueDate 12/09/2016 v_due_date 2016-12-09 00:00:00

推荐答案

根据

According this page, parameter type adVariant (i.e. 12) is not supported by ADO.

您应该使用常量，以使您的代码更具可读性，例如

You should use constants in order to make your code more readable, e.g.

Const adUseClient = 3
Const adOpenStatic = 3
Const adCmdText = 1
Const adCmdStoredProc = 4

Const adVarChar = 200 
Const adNumeric = 131 
Const adChar = 129
Const adBigInt = 20 
Const adInteger = 3

Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adParamReturnValue = 4

cmd.Parameters.Append cmd.CreateParameter("theAccountId", adVarChar, adParamInput, , Request.Form ("aid"))
cmd.Parameters.Append cmd.CreateParameter("theAwardId", adNumeric, adParamInput, , award_id)
cmd.Parameters.Append cmd.CreateParameter("theDueDate", adVarChar, adParamInput, 100, theDueDt)
cmd.Parameters.Append cmd.CreateParameter("theSubmittedDate", adVarChar, adParamInput, 100, theSubmittedDt)
cmd.Parameters.Append cmd.CreateParameter("theReportDescription", adVarChar, adParamInput, 100, theReportDesc)
cmd.Parameters.Append cmd.CreateParameter("theFormId", adVarChar, adParamInput, 100, theFrmId)
cmd.Parameters.Append cmd.CreateParameter("theReturnCode", adNumeric, adParamOutput)

也许试试这个:

cmd.CommandType = adCmdText
cmd.CommandText = "{CALL deadlines_summary.PR_SUMMARY_IN(?,?,?,?,?,?,?)}"

数字参数不需要大小值.

Numeric parameters do not require a size value.

您还应该尝试使用参数类型adDate，而不是将日期转换为字符串值.

You should also try to use parameter type adDate instead of converting the dates into string values.

使用绑定参数时，必须删除引号，即仅使用theSubmittedDt = submitted_date而不是theSubmittedDt = "'" & submitted_date & "'".

You must remove the quotes when you use bind parameter, i.e. use simply theSubmittedDt = submitted_date instead of theSubmittedDt = "'" & submitted_date & "'".

这篇关于经典ASP使用OraOleadb驱动程序调用Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！