This may not be what you're after, but try replacing datetext := to_char(sysdate, 'yyyy-mm-dd');
with SELECT TO_CHAR(sysdate, 'yyyy-mm-dd') INTO datetext FROM dual; Thanks, Eric Cobb ECAR Technologies, LLC http://www.ecartech.com http://www.cfgears.com Brent Nicholas wrote: > Hi all, > > So I'm very stuck and tired of saying mean things to my computer... so I hope > you are able to see something I'm missing. > > In short, in order to trouble shoot a larger stored proc call, I've created a > very simple one to get working first. It just returns a value. > > Platforms: Oracle11g / CFMX7 (oracle drivers) / IIS6 > > ---------------- > The Stored Proc: > > CREATE OR REPLACE PROCEDURE FUNDING."OMGItsMixedCase" (datetext out varchar2 > ) is --AUTHID CURRENT_USER IS > BEGIN -- executable part starts here > > datetext := to_char(sysdate, 'yyyy-mm-dd'); > > END; > / > > > ----------------------- > The CF Code to call it: > > <!--- I know setting the storedProc var works since I can call a different > storedProc on another schema with this method, though it requires nothing > passed in. ie: this code approach works elsewhere ---> > > <cfset storedProc = "FUNDING." & """OMGItsMixedCase""()"> > > <cfstoredproc procedure="#storedProc#" dataSource="databasemonster" > debug="yes" returncode="yes"> > <cfprocparam type="out" cfsqltype="CF_SQL_VARCHAR" dbvarname="datetext" > /> > </cfstoredproc> > > > -------------------- > The error: (nemisis) > > [Macromedia][Oracle JDBC Driver][Oracle]ORA-00900: invalid SQL statement > > The error occurred in D:\somepath\act_updateProgramFund.cfm: line 50 > 48 : > 49 : <cfstoredproc procedure="#storedProc#" dataSource="databasemonster" > debug="yes" returncode="yes"> > 50 : <cfprocparam type="out" cfsqltype="CF_SQL_VARCHAR" dbvarname="datetext" > /> > 51 : </cfstoredproc> > 52 : > > SQL { (param 1) = call P3DEVELOPER."date_text"()( (param 2) )} > DATASOURCE oracle1srvr > VENDORERRORCODE 900 > SQLSTATE 42000 > > > What's with the param 1 in front of the call? then another param 2?? > > Thanks for your time and effort, > Brent > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338793 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
