Hi Tried the code u sent , i got the below error "17-jan-2005" is an invalid date format. The error occurred in C:\cwhb\Myriad\RecordSheets\component\1bookSearch.cfm: line 29 27 : <cfstoredproc procedure="#sp#" datasource="#ds#"> 28 : <!--- <cfprocparam dbvarname="rs_datefrom" cfsqltype="CF_SQL_DATE" value="#ARGUMENTS.cfromdate#" type="In" > ---> 29 : <cfprocparam dbvarname="rs_datefrom" cfsqltype="CF_SQL_DATE" value="#LSdateFormat(ARGUMENTS.cfromdate,'dd-mmm-yyyy')#" type="In" > 30 : <cfprocparam dbvarname="rs_dateto" cfsqltype="CF_SQL_DATE" value="#LSdateFormat(ARGUMENTS.ctodate,'dd-mmm-yyyy')#" type="In" > 31 : <!--- <cfprocparam dbvarname="rs_dateto" cfsqltype="CF_SQL_DATE" value="#ARGUMENTS.ctodate#" type="In"> --->
> 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:233344 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54