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# DEBUGDROP function month/cfquery cfcatch type=any!-- ignore --/cfcatch /cftry cftry cfquery name=qry datasource=#dsn# DEBUGDROP function year/cfquery cfcatch type=any!-- ignore --/cfcatch /cftry cfquery name=qry datasource=#dsn# DEBUGCREATE FUNCTION month (MyDate DATE) RETURN NUMBER IS BEGIN RETURN extract(MONTH FROM MyDate); END month/cfquery cfquery name=qry datasource=#dsn# DEBUGCREATE 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 ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193075 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
RE: Creating Oracle Functions in CFQUERY revisited
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# DEBUGDROP function month/cfquery cfcatch type=any!-- ignore --/cfcatch /cftry cftry cfquery name=qry datasource=#dsn# DEBUGDROP function year/cfquery cfcatch type=any!-- ignore --/cfcatch /cftry cfquery name=qry datasource=#dsn# DEBUGCREATE FUNCTION month (MyDate DATE) RETURN NUMBER IS BEGIN RETURN extract(MONTH FROM MyDate); END month/cfquery cfquery name=qry datasource=#dsn# DEBUGCREATE 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
Re: Creating Oracle Functions in CFQUERY revisited
James Holmes wrote: You stil have AS in the Year Fn RETURN NUMBER AS BEGIN Should be IS Changed, still getting the same error. As a side note, why wouldn't invalid syntax cause the create function statement to error? - Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193079 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
RE: Creating Oracle Functions in CFQUERY revisited
Hmm, does the Month function work? As for the error (or lack of it), it might be some weird driver issue. I'll try your exact code in a sec and post back. -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Friday, 4 February 2005 11:08 To: CF-Talk Subject: Re: Creating Oracle Functions in CFQUERY revisited James Holmes wrote: You stil have AS in the Year Fn RETURN NUMBER AS BEGIN Should be IS Changed, still getting the same error. As a side note, why wouldn't invalid syntax cause the create function statement to error? - Rick ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193080 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
RE: Creating Oracle Functions in CFQUERY revisited
As a side note, why wouldn't invalid syntax cause the create function statement to error? you can use both AS and IS, it doesn't seem to have any effect (at least in SQL*Plus): SQL*Plus: Release 9.2.0.1.0 - Production on Fri Feb 4 16:25:07 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production SQL CREATE OR REPLACE FUNCTION c_to_f (in_c NUMBER) 2 RETURN NUMBER 3 IS 4 out_f NUMBER; 5 BEGIN 6 out_f := (in_c * 9 / 5) + 32; 7 RETURN out_f; 8 END c_to_f; 9 / Function created. SQL SELECT SUBSTR (object_name, 1, 25), object_type, status 2FROM user_objects 3 WHERE object_name = 'C_TO_F'; SUBSTR(OBJECT_NAME,1,25) OBJECT_TYPESTATUS - -- --- C_TO_FFUNCTION VALID SQL DROP FUNCTION c_to_f; Function dropped. SQL CREATE OR REPLACE FUNCTION c_to_f (in_c NUMBER) 2 RETURN NUMBER 3 AS 4 out_f NUMBER; 5 BEGIN 6 out_f := (in_c * 9 / 5) + 32; 7 RETURN out_f; 8 END c_to_f; 9 / Function created. SQL SELECT SUBSTR (object_name, 1, 25), object_type, status 2FROM user_objects 3 WHERE object_name = 'C_TO_F'; SUBSTR(OBJECT_NAME,1,25) OBJECT_TYPESTATUS - -- --- C_TO_FFUNCTION VALID SQL -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Friday, February 04, 2005 4:08 PM To: CF-Talk Subject: Re: Creating Oracle Functions in CFQUERY revisited James Holmes wrote: You stil have AS in the Year Fn RETURN NUMBER AS BEGIN Should be IS Changed, still getting the same error. As a side note, why wouldn't invalid syntax cause the create function statement to error? - Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193086 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
RE: Creating Oracle Functions in CFQUERY revisited
I've got it - you are missing the final semicolon after END month and END year. Running without them leads to this: CREATE FUNCTION month (MyDate DATE) RETURN NUMBER IS BEGIN RETURN extract(MONTH FROM MyDate); END month Warning: Function created with compilation errors Notice that no ORA error was thrown - it was created. It just compiled badly. Hence, CF doesn't error either (I did this with TOAD). Add the semicolons and you'll be OK. It all still needs to be on one line. -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Friday, 4 February 2005 11:08 To: CF-Talk Subject: Re: Creating Oracle Functions in CFQUERY revisited James Holmes wrote: You stil have AS in the Year Fn RETURN NUMBER AS BEGIN Should be IS Changed, still getting the same error. As a side note, why wouldn't invalid syntax cause the create function statement to error? - Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193088 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
Re: Creating Oracle Functions in CFQUERY revisited
James Holmes wrote: Hmm, does the Month function work? As for the error (or lack of it), it might be some weird driver issue. I'll try your exact code in a sec and post back. Let me know... I'm running CFMX 6.1 Updater 1 on Windows, and using the default Oracle JDBC Driver, connecting to an Oracle 9i database. - Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193089 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
Re: Creating Oracle Functions in CFQUERY revisited
YEAR is a reserved word in Oracle. Try a different name for the function. Janet. At 09:55 AM 2/4/2005 -0500, you wrote: 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 ~| 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:193090 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
RE: Creating Oracle Functions in CFQUERY revisited
It may be, but the function still works anyway. -Original Message- From: Janet Schmitt [mailto:[EMAIL PROTECTED] Sent: Friday, 4 February 2005 11:42 To: CF-Talk Subject: Re: Creating Oracle Functions in CFQUERY revisited YEAR is a reserved word in Oracle. Try a different name for the function. Janet. At 09:55 AM 2/4/2005 -0500, you wrote: 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 ~| 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:193091 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
Re: Creating Oracle Functions in CFQUERY revisited
James, that did the trick! Crazy, in ALL other SQL queries sent via CFQUERY, if you end them in ;, you get an Invalid Character error... Here's a question, Mr. Oracle Man... Why can't I do this? SELECT *, 'foo' as foo FROM BLOG_CONFIG It's easy enough to work around by specifying the field names, but it's still pretty stupid that not to work. It doesn't work in SQL*Plus or cfquery. SQL select *, 'foo' as foo from BLOG_CONFIG; select *, 'foo' as foo from BLOG_CONFIG * ERROR at line 1: ORA-00923: FROM keyword not found where expected SQL select 'foo' as foo, * from BLOG_CONFIG; select 'foo' as foo, * from BLOG_CONFIG * ERROR at line 1: ORA-00936: missing expression ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193096 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
RE: Creating Oracle Functions in CFQUERY revisited
SELECT BLOG_CONFIG.*, 'foo' as foo FROM BLOG_CONFIG And you thought that would be hard, didn't you... -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Saturday, 5 February 2005 12:04 To: CF-Talk Subject: Re: Creating Oracle Functions in CFQUERY revisited James, that did the trick! Crazy, in ALL other SQL queries sent via CFQUERY, if you end them in ;, you get an Invalid Character error... Here's a question, Mr. Oracle Man... Why can't I do this? SELECT *, 'foo' as foo FROM BLOG_CONFIG It's easy enough to work around by specifying the field names, but it's still pretty stupid that not to work. It doesn't work in SQL*Plus or cfquery. SQL select *, 'foo' as foo from BLOG_CONFIG; select *, 'foo' as foo from BLOG_CONFIG * ERROR at line 1: ORA-00923: FROM keyword not found where expected SQL select 'foo' as foo, * from BLOG_CONFIG; select 'foo' as foo, * from BLOG_CONFIG * ERROR at line 1: ORA-00936: missing expression ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193098 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
Re: Creating Oracle Functions in CFQUERY revisited
James Holmes wrote: SELECT BLOG_CONFIG.*, 'foo' as foo FROM BLOG_CONFIG And you thought that would be hard, didn't you... Hehheheh.. I'll keep that in mind! You know, making BlogCFM work for 5 different databases has been an interesting exercise. Once it's done, I'm hoping to talk the alumni center here into using it, then at least the work will have been work-related. (Saeed, if you're out there, you be quiet!) - Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193099 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
