fused: CFPROCPARAM Error ORA-06502

[Jack Shepard]

Hello,

            Calling "RETRIEVEPROC.GetSysDate" stored procedure works on MX 6, 
but blows up on MX7.  When the database value is copied to the out parameter 
raises an Oracle error.  We have applied Cold Fusion MX 7 Updater 7.0.1 and 
7.0.2.   This is making my head spin!  Any assistance will be appreciated.  

 

I have tried many combinations of the following and nothing works:

.. sqlptype = “inout”
.. maxlength  =”10”
.. fill inout parameters with 10 space before calling stored procedure.

 

 

********************** Error Message ************************************


[Macromedia][SequeLink JDBC Driver][ODBC Socket][Oracle][ODBC][Ora]ORA-06502: 
PL/SQL: numeric or value error: character string buffer too small ORA-06512: at 
"MY.RETRIEVEPROC", line 99 ORA-06512: at line 1
 

************************  System Info *******************************

OS:  Win 2003 server R2  (Service pack 1)
Coldfusion:  MX 7 Standard
Oracle: 9i
Database connection:   OBC Socket.
 

*********************** Coldfusion Code ********************************

 

<!----- Procedure returns 2 values, ToDate (Today's date) and the date in the 
pass (FromDate).
        From date is calculated by sysdate - input parameter QNumDateBack
        If input parameter QNumDateBack is not passed, the procedure default is 
60
----->                 

<cfset lcRecvdFrom="">
<cfset lcRecvdTo="">

 

<cftry>

        <cfstoredproc datasource="#dsn#" procedure="RetrieveProc.GetSysDate">
           <cfprocparam variable="FromDate" dbvarname="QFromDate"      
type="Out"   cfsqltype="CF_SQL_VARCHAR">             
           <cfprocparam variable="ToDate"   dbvarname="QToDayDate"     
type="Out"   cfsqltype="CF_SQL_VARCHAR">
           <cfprocparam value=60            dbvarname="QNumDateBack"   
type="In"    cfsqltype="CF_SQL_NUMERIC">              
        </cfstoredproc>
 

        <cfset lcRecvdFrom=#trim(FromDate)#>  
        <cfset lcRecvdTo  =#trim(ToDate)#>        

 
        <cfcatch type="Any">

                        <!--- error occurred --->
                        <cfdump var =”cfatch”><cfabort>
         </cfcatch>  
</cftry>

 

 

************************************ Stored Procedure Code: 
********************************

  procedure GetSysDate( QFromDate    OUT VARCHAR2,
                        QToDayDate   OUT VARCHAR2,
                        QNumDateBack IN NUMBER DEFAULT 60)

  is

    ToDate      VARCHAR2(10);
    FromDate    VARCHAR2(10);

  begin

    QFromDate  := NULL;

    QToDayDate := NULL;
 

     begin

        select to_char(sysdate,'MM/DD/YY') INTO ToDate FROM DUAL;

        QToDayDate:= ToDate;                          


        select to_char(sysdate - QNumDateBack,'MM/DD/YY') INTO FromDate FROM 
DUAL

        QFromDate := FromDate;
                           
    end;               

               
  end GetSysDate

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:256207
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4