Yeah Date is what I am thinking. I have had this problem before with just an
Oracle query. Although looking at your query you are using the TO_CHAR and
TO_DATE stuff which should normally fix this.
I think what I did in the end was to format the date how Oracle was going to
expect it dd-mmm-yyyy 24-FEB-2006 for instance.
Something like this might be worth a try. I did have this problem and I did
fix it but I can't remeber completely how I am afraid.
<cfprocparam dbvarname="rs_datefrom" cfsqltype="CF_SQL_DATE"
value="#LSdateFormat(ARGUMENTS.cfromdate,'dd-mmm-yyyy')#" type="In" >
I use LSdateFormat so the american months do not get confused though this might
not be an issue for you.
You might then be able to loose this line which seems to be a bit of an
overcook.
TO_DATE(TO_CHAR(TO_DATE('''|| rs_datefrom
||''',''dd-Mon-yyyy''),''dd/mm/yyyy''),''dd/mm/yyyy'')
Good luck
Martin
> Hi i am facing a issue in coldfusion and oracle storedprocedure i am
> getting invalid number error , below is the detail
>
> Calling Stored procedure from CFM like belwo
>
> <cffunction name="asSearchresult" access="public" returntype="query">
>
>
> <cfargument name="cfromdate" required="false" type="DATE" default="">
> <cfargument name="ctodate" required="false" type="DATE" default="">
> <cfargument name="cRS_deleted" required="false" type="string"
> default="">
>
> <cfargument name="cRS_TYPE" required="false" type="string"
> default="">
> <cfargument name="cRS_Status" required="false" type="string"
> default="">
> <cfargument name="cRS_UPLOADED" required="false"
> type="string"
> default="">
> <!---
> <cfargument name="cafs_office_id" required="false"
> type="string"
> default="">
> <cfargument name="cofs_country_id" required="false"
> type="string"
> default="">
> --->
>
>
> <cfstoredproc procedure="#sp#" datasource="#ds#">
> <cfprocparam dbvarname="rs_datefrom"
> cfsqltype="CF_SQL_DATE"
> value="#ARGUMENTS.cfromdate#" type="In" >
> <cfprocparam dbvarname="rs_dateto"
> cfsqltype="CF_SQL_DATE"
> value="#ARGUMENTS.ctodate#" type="In">
> <cfprocparam dbvarname="RS_deleted"
> cfsqltype="CF_SQL_CHAR"
> value="#ARGUMENTS.cRS_deleted#" type="In">
>
> <cfprocparam dbvarname="RS_type"
> cfsqltype="CF_SQL_CHAR"
> value="#ARGUMENTS.cRS_TYPE#" type="In">
> <cfprocparam dbvarname="RS_Status"
> cfsqltype="CF_SQL_CHAR"
> value="#ARGUMENTS.cRS_Status#" type="In">
>
>
> <cfprocresult name="searchResults">
> </cfstoredproc>
> <!---ad --->
> <cfif searchResults.recordCount gt 0>
> <cfset xresult = searchResults>
> <cfelse>
> <cfset xresult = "">
> </cfif>
> <cfreturn xresult>
> </cffunction>
>
> oracle Stored procedure
> CREATE OR REPLACE PACKAGE RSSearch_Package
> AS TYPE RSSearch_Type IS REF CURSOR;
> END RSSearch_Package;
> /
>
> CREATE OR REPLACE PROCEDURE Rssearch_Procedure (
> RSSearch_Cursor IN OUT Rssearch_Package.RSSearch_Type,
> rs_datefrom IN DATE,
> rs_dateto IN DATE,
> RS_deleted IN CHAR,
> RS_type IN CHAR,
> RS_Status IN CHAR
>
>
>
> )
> AS
> WhereClause VARCHAR(5000);
>
> BEGIN
> IF ((rs_datefrom IS NOT NULL ) AND (rs_dateto IS NOT NULL)) THEN
> --WhereClause :='AND RECORDSHEET.RS_DATE BETWEEN ' ||
> TO_DATE(TO_CHAR(TO_DATE('''|| rs_datefrom ||''',''dd-Mon-yyyy''),
> ''dd/mm/yyyy''),''dd/mm/yyyy'') ||'''' || ' AND ' || ''''||
> TO_DATE(TO_CHAR(TO_DATE('''|| rs_dateto ||''',''dd-Mon-yyyy''),
> ''dd/mm/yyyy''),''dd/mm/yyyy'')||'''';
> -- WhereClause :='AND RECORDSHEET.RS_DATE BETWEEN ' ||
> 'TO_DATE(TO_CHAR(TO_DATE(''' || rs_datefrom || ''',''dd-Mon-yyyy''),
> ''dd/mm/yyyy''),''dd/mm/yyyy'')' || ' AND ' ||
> 'TO_DATE(TO_CHAR(TO_DATE('''|| rs_dateto ||''',''dd-Mon-yyyy''),
> ''dd/mm/yyyy''),''dd/mm/yyyy'')';
> -- WhereClause :='AND RECORDSHEET.RS_DATE BETWEEN ' ||
> '''' ||
> rs_datefrom ||'''' || ' AND ' || ''''|| rs_datefrom || '''' ';
> --WhereClause := 'AND RECORDSHEET.RS_DATE BETWEEN' || ''''||
> rs_datefrom || ''''|| ' AND ' || '''' || rs_dateto || '''';
> WhereClause := 'AND RECORDSHEET.RS_DATE BETWEEN' || rs_datefrom || '
> AND ' || rs_dateto ||'''';
>
> END IF;
> IF (RS_deleted IS NOT NULL) THEN
> WhereClause := WhereClause || ' AND RECORDSHEET.DELETED
> = ' || ''''
> || RS_deleted || '''' ;
> END IF;
>
> IF (RS_Status IS NOT NULL) THEN
> WhereClause := WhereClause || ' AND RECORDSHEET.Status = ' || '''' ||
> RS_Status || '''';
> END IF ;
>
> IF (RS_type IS NOT NULL) THEN
> WhereClause := WhereClause || ' AND RECORDSHEET.type = ' || '''' ||
> RS_type || '''';
> END IF ;
>
> INSERT INTO TBLTEMPWHERE (textdata) VALUES (WhereClause);
>
>
>
> OPEN RSSearch_Cursor FOR
>
> SELECT
> RECORDSHEET.ID,
> RECORDSHEET.PIPELINE_ID,
> RECORDSHEET.STATUS,
> RECORDSHEET.UPLOADED,
> RECORDSHEET.DELETED,
> RECORDSHEET.FULLY_BILLED,
> RECORDSHEET.COUNTRY_ID,
> WIPSTATUS.CODE AS wipStatusCode,
> WIPSTATUS.DESCRIPTION AS wipStatusDescription,
> RECORDSHEET.TYPE,
> RECORDSHEET.SERIAL_NUMBER,
> RECORDSHEET.USERNAME,
> RECORDSHEET.RS_DATE,
> RECORDSHEET.PROJECT_NAME,
> RECORDSHEET.APPROVAL_DATE,
> RECORDSHEET.SUBMISSION_DATE,
> RECORDSHEET.PIPELINE_OFFICE_ID,
> RECORDSHEET.PIPELINE_FORTE_ID,
> DEPARTMENT.COST_CENTRE AS departmentCostCentre,
> DEPARTMENT.COUNTRY_ID AS departmentCountryId,
> DICTATOR_ID,
> DICTATOR.INITIALS AS dictatorInitials,
> DICTATOR.SURNAME AS dictatorSurname,
> DICTATOR.FIRST_NAME AS dictatorFirstName,
> REFERRER_ID,
> CLIENT.ORG_NAME AS clientOrgName,
> CLIENTPARENT.ORG_NAME AS clientParentName,
> OTHERPARTY.ORG_NAME AS otherPartyOrgName,
> PAYOR.ORG_NAME AS payorOrgName,
> PROPERTYADDRESS.PREMISE AS propertyAddressPremise,
> PROPERTYADDRESS.PREMISE_NO AS propertyAddressNumber,
> PROPERTYADDRESS.STREET AS propertyAddressStreet,
> PROPERTYADDRESS.TOWN AS propertyAddressTown,
> PROPERTYADDRESS.PC_MAJOR AS propertyAddressPCMajor,
> PROPERTYADDRESS.PC_MINOR AS propertyAddressPCMinor,
> PROPERTYADDRESS.NON_BRITISH_POST_CODE AS
> propertyAddressNonBritishPC
> FROM
> DBOWNER.RS_EMPLOYEE DICTATOR,
> DBOWNER.ORGANISATION CLIENT,
> DBOWNER.ORGANISATION CLIENTPARENT,
> DBOWNER.ORGANISATION OTHERPARTY,
> DBOWNER.ORGANISATION PAYOR,
> DBOWNER.RECORD_SHEET RECORDSHEET,
> DBOWNER.ADDRESS PROPERTYADDRESS,
> DBOWNER.RS_DEPARTMENT DEPARTMENT,
> DBOWNER.RS_WIP_STATUS WIPSTATUS
> WHERE
> RECORDSHEET.payor_id = PAYOR.ORG_ID (+)
> AND RECORDSHEET.other_party_id = OTHERPARTY.ORG_ID (+)
> AND RECORDSHEET.client_id = CLIENT.ORG_ID (+)
> AND RECORDSHEET.client_parent = CLIENTPARENT.ORG_ID (+)
> AND RECORDSHEET.property_address_id =
> PROPERTYADDRESS.ADDRESS_ID
> (+)
> AND RECORDSHEET.dictator_id = DICTATOR.ID (+)
> AND RECORDSHEET.DEPT = DEPARTMENT.RS_DEPT_ID (+)
> AND RECORDSHEET.WIP_STATUS = WIPSTATUS.ID (+) || WhereClause;
> --ORDER BY rs_date DESC,serial_number;
> END Rssearch_Procedure;
> /
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233342
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
