You stil have AS in the Year Fn RETURN NUMBER AS BEGIN
Should be IS -----Original Message----- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Friday, 4 February 2005 10:55 To: CF-Talk Subject: Creating Oracle Functions in CFQUERY revisited Okay... James suggested yesterday that I create my oracle functions inside a CFC, and make sure to put the SQL to create the function all on one line. But it's not helping.. Here is my code: ------------test.cfm------------------------ <cfset DBTYPE = "oracle"> <cfset DSN = "ADSPRD_blogcfm"> <cfif dbtype contains "oracle"> <cfset functions = CreateObject("component","functions")> <cfset results = functions.createOracleFunctions(DSN)> Oracle functions created. <p> </cfif> <cfquery name="qry" datasource="#DSN#"> SELECT Year(SYSDATE) FROM DUAL </cfquery> <cfdump var="#qry#"> <cfquery name="qry" datasource="#DSN#"> SELECT Month(SYSDATE) FROM DUAL </cfquery> <cfdump var="#qry#"> ------------test.cfm------------------------ ---------------functions.cfc----------------- <cfcomponent> <cffunction name="createOracleFunctions" access="public" output="yes" returnType="void"> <cfargument name="dsn" type="string" required="yes"> <cfset var qry = ""> <cftry> <cfquery name="qry" datasource="#dsn#" DEBUG>DROP function month</cfquery> <cfcatch type="any"><!-- ignore --></cfcatch> </cftry> <cftry> <cfquery name="qry" datasource="#dsn#" DEBUG>DROP function year</cfquery> <cfcatch type="any"><!-- ignore --></cfcatch> </cftry> <cfquery name="qry" datasource="#dsn#" DEBUG>CREATE FUNCTION month (MyDate DATE) RETURN NUMBER IS BEGIN RETURN extract(MONTH FROM MyDate); END month</cfquery> <cfquery name="qry" datasource="#dsn#" DEBUG>CREATE FUNCTION year (MyDate DATE) RETURN NUMBER AS BEGIN RETURN extract(YEAR FROM MyDate); END year</cfquery> <Cfreturn> </cffunction> </cfcomponent> ---------------functions.cfc----------------- All four queries execute fine... no errors... then when I try to access the Year() function, I still get the error: [Macromedia][Oracle JDBC Driver][Oracle]ORA-06575: Package or function YEAR is in an invalid state ARGH! - Rick ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193077 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
