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
