Been awhile since I had to use Oracle, but is sysdate a function? Can you try:
datetext := to_char(sysdate(), 'yyyy-mm-dd');
> 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:338789
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
