Re: Re: Errors ORA-06550 and PLS-00306 in Procedure

From: <april.mcclellan_at_HydroOne.com>
Date: 12 Mar 2001 16:17:11 +0100
Message-ID: <2F96BE4C7CC6D211A55F0008C7A43976017424CC@OHSC-EX4>

If I issue the SQL statement, exactly as shown in the Procedure, the SQL statement works.

Below is the calling code. The Procedure is called from a MS Access form using a pass-through query and DAO. In response to the other reply, the Sequence does not retrun a character, it returns a number. The sequence is not declared as a variable in the Procedure. The difference between the two methods below is that one method calls the stored Procedure which passes all the required values except the sequence, which is issued from the Procedure. The second method is a direct SQL statement which does call the seqence.

With Me

'Method 1

    strSql = vbNullString
    strSql = "BEGIN SPSAVESTATUSCHANGE ('" & .ContPrefix & "', '" & .ContYr & "', '" & .ContSlaNum & "',"

    strSql = strSql & "'" & .ContSLAVersion & "','" & .ContAwdNum & "','" & .NMProjNo & "', "

    strSql = strSql & .ProjectRevNo & ",'" & gu_CurrentUser.Division & "','" & ctlValue & ","

    strSql = strSql & "'" & Trim(strUser) & "', "     strSql = strSql & "TO_DATE(" & "'" & dtDate & "'" & ", 'YYYY-MM-DD:HH:MI:SSAM')"
    strSql = strSql & "); END;"     

'Method 2
' NOTE : issuing the SQL statement directly as shown below does work,
this is the same SQL statement in the Procedure

'strSql = "INSERT INTO tblStatusChanges VALUES
(SEQ_TBLSTATUSCHANGES.nextval,'" & .ContPrefix & "', '" & .ContYr & "', '" & .ContSlaNum & "',"

'strSql = strSql & "'" & .ContSLAVersion & "','" & .ContAwdNum & "','" &
.NMProjNo & "', "

'strSql = strSql & .ProjectRevNo & ",'" & ctlValue & "','" &
gu_CurrentUser.Division & "', "

'strSql = strSql & "'" & Trim(strUser) & "', "
   ' strSql = strSql & "TO_DATE(" & "'" & dtDate & "'" & ", 'YYYY-MM-DD:HH:MI:SSAM')"
'strSql = strSql & ");"
    

    Set qdf = db.CreateQueryDef("")
    With qdf

        .Connect = gstrCurrentCnn ' global connection
        .ReturnsRecords = False
        .SQL = strSql
        .Execute dbFailOnError

    End With
End With

So why does the procedure not work while the same SQL statement works?

Thanks for your help,

April McClellan
Internet: mailto:April.McClellan_at_hydroone.com

-- 
Posted from thor.hydroone.com [192.75.116.12] 
via Mailgate.ORG Server - http://www.Mailgate.ORG

Received on Mon Mar 12 2001 - 09:17:11 CST