Oracle Stored procedure error from CF

[peace4theapes]

I have a stored proc working on a oracle server, connected through JDBC to the
CF server. I ran the stored proc on the oracle server through PL/SQL developer
without any glitches. When I call the proc from a cfm page I get the following
error:

Error Executing Database Query.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-00917: missing comma ORA-06512: at
"CLS.STATS", line 45 ORA-06512: at line 1

Could anyone please throw some light on what's wrong here? It would be a great
help, I am twitching my brain for the last 2 days. I am including the proc and
the CF code below.

Thanks,
Prashant

STORED PROC(listlen and Listget are Oracle funcs developed to work like CF
funcs):

create or replace procedure stats(adlist varchar2,ht_type varchar2) is

temppos int;
hit_type varchar2(100) := ht_type;
len int;
str varchar2(100);
AD_NUMBER int;
WEB_ID varchar2(100);
ATEX_AD_NUMBER varchar2(100);
DEALER_NUMBER varchar2(100);
PRODUCT_ID varchar2(100);
AD_TYPE char(100);
CLASS_CODE char(10);
adcount int;
tempnumber int;


begin
len := ListLen(adlist,'~');

for i in 1..len loop
str := ListGetAtPos(adlist,'~',i);
temppos := ListLen(str, ',');
adcount :=0;

AD_NUMBER := TRIM(ListGetAtPos(str,',',1));
tempnumber :=ad_number;
WEB_ID := TRIM(ListGetAtPos(str,',',2));
ATEX_AD_NUMBER := TRIM(ListGetAtPos(str,',',3));
DEALER_NUMBER := TRIM(ListGetAtPos(str,',',4));
PRODUCT_ID := TRIM(ListGetAtPos(str,',',5));
AD_TYPE := SUBSTR(TRIM(' ' from ListGetAtPos(str,',',6)),1,1);
CLASS_CODE := SUBSTR(TRIM(' ' from ListGetAtPos(str,',',7)),1,5);


-- dbms_output.put_line('select count(*) into adcount from ad_statistics
where AD_Number = ' ||AD_NUMBER|| ' and statistics_date =
'''||trunc(sysdate)||'''');
-- dbms_output.put_line(adcount);
select count(ad_number) into adcount from ad_statistics where
ad_number = tempnumber and statistics_date = trunc(sysdate);

-- execute immediate 'select count(*) as adcount from ad_statistics
where AD_Number = '||tempnumber||' and statistics_date =
'''||trunc(sysdate)||'''';
-- dbms_output.put_line(adcount);
dbms_output.put_line('Insert into ad_statistics
(AD_NUMBER,WEB_ID,ATEX_AD_NUMBER,DEALER_NUMBER,AD_ TYPE,CLASS_CODE,PRODUCT_ID,sta
tistics_date,'||hit_type||') Values (' || AD_NUMBER || ',' || WEB_ID || ',' ||
ATEX_AD_NUMBER||','||DEALER_NUMBER||','''||SUBSTR( AD_TYPE,1,1)||''','''||SUBSTR(
CLASS_CODE,1,5)||''','|| PRODUCT_ID||','''||trunc(sysdate)||''',1)');
-- dbms_output.put_line('Update ad_statistics ads set '||hit_type||'
= ads.'||hit_type||'+1 where ad_number = '||ad_number||' and statistics_date
='''||trunc(sysdate)||'''');
IF adcount = 0 THEN
-- dbms_output.put_line('bling');
execute immediate 'Insert into ad_statistics
(AD_NUMBER,WEB_ID,ATEX_AD_NUMBER,DEALER_NUMBER,AD_ TYPE,CLASS_CODE,PRODUCT_ID,sta
tistics_date,'||hit_type||') Values (' || AD_NUMBER || ',' || WEB_ID || ',' ||
ATEX_AD_NUMBER||','||DEALER_NUMBER||','''||SUBSTR( AD_TYPE,1,1)||''','''||SUBSTR(
CLASS_CODE,1,5)||''','|| PRODUCT_ID||',''15-NOV-05'',1)';
commit;
ELSE
-- dbms_output.put_line('blang');
execute immediate 'Update ad_statistics ads set '||hit_type||' =
ads.'||hit_type||'+1 where ad_number = '||ad_number||' and statistics_date
='''||trunc(sysdate)||'''';
commit;
END IF;
--
dbms_output.put_line('----------------------------------------------------');
commit;
end loop;


end stats;

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--

CF CODE:

<cfstoredproc datasource="cls" procedure="stats" returncode="No">
<cfprocparam value="#temp#" cfsqltype="CF_SQL_VARCHAR">
<cfprocparam value="ht_favorite" cfsqltype="CF_SQL_VARCHAR">
</cfstoredproc>

VARIABLES:

temp =
4375001,NULL,NULL,66,2,c,878~4375002,NULL,NULL,66, 2,c,878~4375003,NULL,NULL,66,2
,c,878~4375004,NULL,NULL,66,2,c,880~4375005,NULL,N ULL,66,2,c,886~4375006,NULL,NU
LL,66,2,c,886~4375007,NULL,NULL,66,2,c,946N~437500 8,NULL,NULL,66,2,c,888~4375009
,NULL,NULL,66,2,c,908~4375010,NULL,NULL,66,2,c,912 ~4375011,NULL,NULL,66,2,c,922~
4375012,NULL,NULL,66,2,c,922~4375013,NULL,NULL,66, 2,c,922~4375014,NULL,NULL,66,2
,c,926~4375015,NULL,NULL,66,2,c,926~4375016,NULL,N ULL,71,2,c,806~4375017,NULL,NU
LL,71,2,c,806~4375018,NULL,NULL,71,2,c,806~4375019 ,NULL,NULL,71,2,c,946A

[paross1]

What happens if you pass it a list of values that contains no NULLs?

Phil

[peace4theapes]

If there are no nulls there will be a discrepancy in the number of columns that
need to be parsed. or if I just pass a blank field, the function throws an
error because the NULL fields are of type int and it tries to insert a blank
string in that column. I must also mention that the error is thrown only when
the 'insert' part of the proc is being executed from CF. The update part works
just fine.

[peace4theapes]

I get the same error without NULLs too.

[CF_Oracle]

Where is line 45 referenced in Error?

[peace4theapes]

line 47 is the ELSE after the insert statement

[peace4theapes]

line 45 is the ELSE after the insert statement

[paross1]

You might consider temporarily modifying your stored procedure to add an
exception handler, so that you can capture the values of the fields that you
are attempting to insert, then return them to ColdFusion as an OUT varchar
parameter, so that you can list the list from CF. I notice that you are using
dbms_output for debugging when running this from the developer, but isn't much
help when calling this proc from CF, so you may want to capture the data into
long string and return it to CF so that you can output and debug it there.

Phil

[peace4theapes]

could you guide me towards it? This is what I think, but I don't know what to
capture into the variable:

blahvariable varchar(4000);

EXCEPTION
WHEN OTHERS THEN
blahvariable = ???
END;

Return BlahVariable;

[paross1]

Maybe something like this (this is just a SWAG). Since you are using dynamic
SQL, the VALUES in your insert is essentially a concatenated string, so set the
blahvariable with that value just before you attempt the insert.

add an OUT parameter to your declaration:

create or replace procedure stats(adlist varchar2,ht_type varchar2, out_string
OUT VARCHAR2) is

and your local variable declaration

blahvariable varchar(4000);

then add this just before your INSERT

blahvariable := AD_NUMBER || ',' || WEB_ID || ',' ||
ATEX_AD_NUMBER||','||DEALER_NUMBER||','||SUBSTR(AD _TYPE,1,1)||','||SUBSTR(CLASS_
CODE,1,5)||','|| PRODUCT_ID||','15-NOV-05',||'1';

execute immediate 'Insert into ad_statistics
(AD_NUMBER,WEB_ID,ATEX_AD_NUMBER,DEALER_NUMBER,AD_ TYPE,CLASS_CODE,PRODUCT_ID,sta
tistics_date,'||hit_type||') Values (' || AD_NUMBER || ',' || WEB_ID || ',' ||
ATEX_AD_NUMBER||','||DEALER_NUMBER||','''||SUBSTR( AD_TYPE,1,1)||''','''||SUBSTR(
CLASS_CODE,1,5)||''','|| PRODUCT_ID||',''15-NOV-05'',1)';

then add your exception at the end of your procedure, setting the out_string
parameter with the value that you captured before your INSERT

EXCEPTION
WHEN OTHERS
THEN out_string := blahvariable;

END;

Phil

[peace4theapes]

thanks Phil! I did try it and got an exception, but the values in the outstring were fine. How would I find out what the exception was?

[paross1]

Well I'm sure that you probably got your original ORA-06512 exception, but
since you are "handling" it, you supress the error, and capture the data. Of
course, processing stops where the exception occurred and jumped to the
exception handler, but at least you got to see the values that you were trying
to insert. What did your return string have in it?

Phil

[peace4theapes]

I captured the whole insert statement in the outvar. So this was what I got:

Insert into ad_statistics
(AD_NUMBER,WEB_ID,ATEX_AD_NUMBER,DEALER_NUMBER,AD_ TYPE,CLASS_CODE,PRODUCT_ID,sta
tistics_date,ht_favorite) Values (4375001,NULL,NULL,66,'c','878
',2,'16-NOV-05',1)-917ORA-00917: missing comma

If I try it in PL/SQL developer it just inserts it fine with no problems and I
dont see a missing comman anywhere. For reference all the columns are Integers
except for AD_TYPE and CLASS_CODE where I am inserting strings.

[peace4theapes]

Thank you very much Phil!!! there was an extra single quote in there which was causing a problem. Thanks again for your help