Professional Web Applications Themes

Oracle Stored procedure error from CF - Coldfusion Database Access

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. ...

  1. #1

    Default Oracle Stored procedure error from CF

    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


    peace4theapes Guest

  2. #2

    Default Re: Oracle Stored procedure error from CF

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

    Phil
    paross1 Guest

  3. #3

    Default Re: Oracle Stored procedure error from CF

    If there are no nulls there will be a discrepancy in the number of columns that
    need to be pd. 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 Guest

  4. #4

    Default Re: Oracle Stored procedure error from CF

    I get the same error without NULLs too.
    peace4theapes Guest

  5. #5

    Default Re: Oracle Stored procedure error from CF

    Where is line 45 referenced in Error?
    CF_Oracle Guest

  6. #6

    Default Re: Oracle Stored procedure error from CF

    line 47 is the ELSE after the insert statement
    peace4theapes Guest

  7. #7

    Default Re: Oracle Stored procedure error from CF

    line 45 is the ELSE after the insert statement
    peace4theapes Guest

  8. #8

    Default Re: Oracle Stored procedure error from CF

    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

    paross1 Guest

  9. #9

    Default Re: Oracle Stored procedure error from CF

    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;

    peace4theapes Guest

  10. #10

    Default Re: Oracle Stored procedure error from CF

    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

    paross1 Guest

  11. #11

    Default Re: Oracle Stored procedure error from CF

    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?
    peace4theapes Guest

  12. #12

    Default Re: Oracle Stored procedure error from CF

    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

    paross1 Guest

  13. #13

    Default Re: Oracle Stored procedure error from CF

    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 Guest

  14. #14

    Default Re: Oracle Stored procedure error from CF

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

Similar Threads

  1. oracle mx stored procedure
    By kyle969 in forum Coldfusion Database Access
    Replies: 3
    Last Post: August 12th, 07:37 PM
  2. stored procedure from oracle to pgsql
    By Nefnifi, Kasem in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 19th, 05:07 PM
  3. oracle, asp, stored procedure
    By Mike D in forum ASP Database
    Replies: 4
    Last Post: June 4th, 09:55 PM
  4. asp to call Oracle stored procedure
    By eddie wang in forum ASP Database
    Replies: 0
    Last Post: March 2nd, 10:08 PM
  5. Re-create stored procedure in Oracle 8.0.5
    By Dusan Bolek in forum Oracle Server
    Replies: 0
    Last Post: January 10th, 07:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139