Classic ASP calling Oracle stored procedure with O

问题:

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

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

and the parameter values as below

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

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: a non-numeric character was found where a numeric was expected.

Below is ASP code snapshot

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

Any suggestions please

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

回答1:

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)

Maybe try this one:

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

Numeric parameters do not require a size value.

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

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

回答2:

So here we go. Some how I managed to make above code running by changing below code

FROM

       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

TO

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

Sending parameter value without quote and works. Need to get the root cause and this strange behavior. Thanks for suggestion guys.