Re: CFSTOREDPROC and invalid SQL
Thanks to everyone for their replies. If someone is searching for this in the future the solution seems to be as follows: If you have a stored proc that DOES NOT require params in or out of the stored proc, the string to call looks like this Mixed Case: Non Mixed Case: If you have a stored proc that DOES require params in or out of the stored proc, the string to call looks like this Non Mixed Case: And then used in: Not sure if needing/not needing the "()" is just common sense or not, but I got stuck on it for a bit. ~| 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:338831 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFSTOREDPROC and invalid SQL
This may not be what you're after, but try replacing
datetext := to_char(sysdate, '-mm-dd');
with
SELECT TO_CHAR(sysdate, '-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, '-mm-dd');
>
> END;
> /
>
>
> ---
> The CF Code to call it:
>
>
>
>
>
> debug="yes" returncode="yes">
>/>
>
>
>
>
> 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 : debug="yes" returncode="yes">
> 50 : />
> 51 :
> 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
Re: CFSTOREDPROC and invalid SQL
Been awhile since I had to use Oracle, but is sysdate a function? Can you try:
datetext := to_char(sysdate(), '-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, '-mm-dd');
>
> END;
> /
>
>
> ---
> The CF Code to call it:
>
>
>
>
>
> debug="yes" returncode="yes">
>dbvarname="datetext" />
>
>
>
>
> 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 : dataSource="databasemonster" debug="yes" returncode="yes">
> 50 : dbvarname="datetext" />
> 51 :
> 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
Re: CFSTOREDPROC and invalid SQL
I had incorrect information in my error in the previous two posts.
It should read:
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 :
50 :
51 :
52 :
SQL {call FUNDING."date_text"()( (param 1) )}
DATASOURCE oracle1srvr
VENDORERRORCODE 900
SQLSTATE 42000
~|
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:338772
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFSTOREDPROC and invalid SQL
>> returncode="yes">
>
>> What's with the param 1 in front of the call? then another
>> param 2??
>
>A complete guess from a non-Oracle person, but ... could it be for the return
>code?
Ok, I've removed returncode and debug and now have the following.
Returns:
SQL {call P3DEVELOPER."date_text"()( (param 1) )}
All the rest is the same error.
~|
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:338771
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFSTOREDPROC and invalid SQL
> returncode="yes"> > What's with the param 1 in front of the call? then another > param 2?? A complete guess from a non-Oracle person, but ... could it be for the return code? ~| 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:338770 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
