You might try cfsqltype="cf_sql_numeric"; oracle sometimes thinks of
datatypes as different from what Allaire does.
if that doesn't work I would try to execute the SP outside of CF using
SQLPlus or a similar tool:
DECLARE firstfield VARCHAR2(100);
secondfield VARCHAR2(100);
change5 integer;
change_5 integer;
(etc)
BEGIN
firstfield := 'foura1';
secondfield := 'foura2';
calc_diffs(firstfield, secondfield, change5, change_5 etc...);
END;
-----Original Message-----
From: Deanna L. Schneider [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 28, 2000 10:14 AM
To: CF-Talk
Subject: oracle stored procedures (long)
Hi folks,
This is probably more of an oracle question than anything, but I'm trying to
work with a stored procedure that our oracle dba gave me. She is, of course,
gone for the holidays. I'm using the following code to call the procedure:
<cfstoredproc procedure="calc_diffs"
datasource="#adsn#"
username="#auname#"
password="#apword#"
debug="yes"
returncode="yes">
<cfprocparam type="in" dbvarname="column_name1" value="foura1"
cfsqltype="cf_sql_varchar">
<cfprocparam type="in" dbvarname="column_name2" value="foura2"
cfsqltype="cf_sql_varchar">
<cfprocparam type="out" dbvarname="change5" variable="var5"
cfsqltype="cf_sql_integer">
<cfprocparam type="out" dbvarname="change5" variable="var4"
cfsqltype="cf_sql_integer">
<cfprocparam type="out" dbvarname="change3" variable="var3"
cfsqltype="cf_sql_integer">
<cfprocparam type="out" dbvarname="change2" variable="var2"
cfsqltype="cf_sql_integer">
<cfprocparam type="out" dbvarname="change1" variable="var1"
cfsqltype="cf_sql_integer">
<cfprocparam type="out" dbvarname="change0" variable="var0"
cfsqltype="cf_sql_integer">
<cfprocparam type="out" dbvarname="change_1" variable="var_1"
cfsqltype="cf_sql_integer">
<cfprocparam type="out" dbvarname="change_2" variable="var_2"
cfsqltype="cf_sql_integer">
<cfprocparam type="out" dbvarname="change_3" variable="var_3"
cfsqltype="cf_sql_integer">
<cfprocparam type="out" dbvarname="change_4" variable="var_4"
cfsqltype="cf_sql_integer">
<cfprocparam type="out" dbvarname="change_5" variable="var_5"
cfsqltype="cf_sql_integer">
</cfstoredproc>
----------------------------------------------------------------------------
--------
This is the stored procedure:
create or replace procedure calc_diffs
(column_name1 in varchar2,
column_name2 in varchar2,
change5 out integer,
change4 out integer,
change3 out integer,
change2 out integer,
change1 out integer,
change0 out integer,
change_1 out integer,
change_2 out integer,
change_3 out integer,
change_4 out integer,
change_5 out integer) as
counter integer;
begin
for i in -5 .. 5
loop
execute immediate
'select count(*) from coopep.sfeval where '
|| column_name2 || ' - ' || column_name1 || ' = :b1 ' INTO counter
USING to_char(i);
if i = -5 then
change_5 := counter;
elsif i = -4 then
change_4 := counter;
elsif i = -3 then
change_3 := counter;
elsif i = -2 then
change_2 := counter;
elsif i = -1 then
change_1 := counter;
elsif i = 0 then
change0 := counter;
elsif i = 1 then
change1 := counter;
elsif i = 2 then
change2 := counter;
elsif i = 3 then
change3 := counter;
elsif i = 4 then
change4 := counter;
else
change5 := counter;
end if;
end loop;
end calc_diffs;
-----------------------------------------------------------------------
When I try to run it, I get the following error:
Oracle Error Code = 1036
ORA-01036: illegal variable name/number
Thoughts?
Thanks!
-Deanna
************************************************************
Deanna Schneider
Interactive Media Developer
UWEX Cooperative Extension Electronic Publishing Group
103 Extension Bldg
432 N. Lake Street
Madison, WI 53706
(608) 265-7923
~~~~~~~~~~~~~ Paid Sponsorship ~~~~~~~~~~~~~
Get Your Own Dedicated Win2K Server! Instant Activation for $99/month w/Free
Setup from SoloServer PIII600 / 128 MB RAM / 20 GB HD / 24/7/365 Tech Support
Visit SoloServer, https://secure.irides.com/clientsetup.cfm.
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
