1 2 Previous Next 17 Replies Latest reply: Feb 8, 2008 10:00 AM by 372016 RSS

    ORA-1858: a non-numeric character was found where a numeric was expected

    621554
      Hi, I have the Function below to do an insert or update depending on situation but I get an exception each time I run my code can anyone please help me find the error in this??



      FUNCTION exceptionlog_insert_update_fn (
      v_currentmethod IN VARCHAR,
      v_customerrormessage IN VARCHAR,
      d_datelogged IN DATE,
      v_helplink IN VARCHAR,
      v_http_costcenter IN VARCHAR,
      v_http_email IN VARCHAR,
      v_http_host IN VARCHAR,
      v_http_sm_authentic IN VARCHAR,
      v_http_sm_authorized IN VARCHAR,
      v_http_sm_sdomain IN VARCHAR,
      v_http_standardid IN VARCHAR,
      v_http_user_agent IN VARCHAR,
      v_innerexception IN VARCHAR,
      n_logstatus IN NUMBER,
      v_exceptionmessage IN VARCHAR,
      v_pagepath IN VARCHAR,
      v_referrer IN VARCHAR,
      v_exceptionsource IN VARCHAR,
      v_stacktrace IN VARCHAR,
      v_squery IN VARCHAR
      )
      RETURN NUMBER
      IS
      existingexceptionlog NUMBER;
      n_rowcount NUMBER;
      BEGIN
      SELECT exceptionlogid
      INTO existingexceptionlog
      FROM admin_exceptionlog
      WHERE pagepath = v_pagepath
      AND exceptionmessage = v_exceptionmessage
      AND referrer = v_referrer
      AND customerrormessage = v_customerrormessage;

      IF existingexceptionlog > 0
      THEN
      UPDATE admin_exceptionlog
      SET occurrences = 1
      --datelastmodified = SYSDATE
      WHERE exceptionlogid = existingexceptionlog;

      n_rowcount := SQL%ROWCOUNT;

      IF n_rowcount > 0
      THEN
      -- An Update occured RETURN O indicating this
      --
      RETURN 0;
      END IF;
      ELSE
      ----- If It does not already exist in the DB, Do an INSERT
      -- RETRIEVE THE ID INFORMATION FIRST
      INSERT INTO admin_exceptionlog
      (exceptionlogid, currentmethod,
      customerrormessage, datelogged, helplink,
      http_costcenter, http_email, http_host,
      http_sm_authentic, http_sm_authorized,
      http_sm_sdomain, http_standardid,
      http_user_agent, innerexception, logstatus,
      exceptionmessage, pagepath, referrer,
      exceptionsource, stacktrace, squery, occurrences,
      datelastmodified
      )
      VALUES (admin_exceptionlogid_seq.NEXTVAL, v_currentmethod,
      v_customerrormessage, d_datelogged, v_helplink,
      v_http_costcenter, v_http_email, v_http_host,
      v_http_sm_authentic, v_http_sm_authorized,
      v_http_sm_sdomain, v_http_standardid,
      v_http_user_agent, v_innerexception, n_logstatus,
      v_exceptionmessage, v_pagepath, v_referrer,
      v_exceptionsource, v_stacktrace, v_squery, 1,
      SYSDATE
      );

      n_rowcount := SQL%ROWCOUNT;
      IF n_rowcount > 0
      THEN
      -- An Update occured RETURN O indicating this
      --
      RETURN 0;
      END IF;
      END IF;
      EXCEPTION
      WHEN OTHERS
      THEN
      RETURN 1;
      END exceptionlog_insert_update_fn;
        • 1. Re: ORA-1858: a non-numeric character was found where a numeric was expected
          24208
          Hi,

          ODP.NET uses "bind by position" by default. If you have not set BindByName=true then you would be using the default. Not sure what your client code is doing, but I would start there...

          - Mark
          • 2. Re: ORA-1858: a non-numeric character was found where a numeric was expected
            621554
            My Code is in C#. How do I set this property please?? And is it Case sensitive?? I have posted my code below.


            private void Save()
            {
            int eventId = 0;
            OracleConnection myconnection = new OracleConnection(this._connectionString);
            try
            {
            OracleCommand cmd = new OracleCommand("pkg_ADMIN_ExceptionLog_Handler.ExceptionLog_Insert_Update_fn", myconnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("ReturnID", OracleDbType.Int32, eventId, ParameterDirection.ReturnValue);
            cmd.Parameters.Add("V_CURRENTMETHOD", OracleDbType.Date, data.CurrentMethod, ParameterDirection.Input);
            cmd.Parameters.Add("V_CUSTOMERRORMESSAGE", OracleDbType.Varchar2, data.CustomErrorMessage, ParameterDirection.Input);
            cmd.Parameters.Add("D_DATELOGGED", OracleDbType.Varchar2,DateTime.Now.ToString(), ParameterDirection.Input);
            cmd.Parameters.Add("V_HELPLINK", OracleDbType.Varchar2, data.HelpLink, ParameterDirection.Input);
            cmd.Parameters.Add("V_HTTP_COSTCENTER", OracleDbType.Varchar2, data.HTTP_COSTCENTER, ParameterDirection.Input);
            cmd.Parameters.Add("V_HTTP_EMAIL", OracleDbType.Varchar2, data.HTTP_EMAIL, ParameterDirection.Input);
            cmd.Parameters.Add("V_HTTP_HOST", OracleDbType.Varchar2, data.HTTP_HOST, ParameterDirection.Input);
            cmd.Parameters.Add("V_HTTP_SM_AUTHENTIC", OracleDbType.Varchar2, data.HTTP_SM_AUTHENTIC, ParameterDirection.Input);
            cmd.Parameters.Add("V_HTTP_SM_AUTHORIZED", OracleDbType.Varchar2, data.HTTP_SM_AUTHORIZED, ParameterDirection.Input);
            cmd.Parameters.Add("V_HTTP_SM_SDOMAIN", OracleDbType.Varchar2, data.HTTP_SM_SDOMAIN, ParameterDirection.Input);
            cmd.Parameters.Add("V_HTTP_STANDARDID", OracleDbType.Varchar2, data.HTTP_STANDARDID, ParameterDirection.Input);
            cmd.Parameters.Add("V_HTTP_USER_AGENT", OracleDbType.Varchar2, data.HTTP_USER_AGENT, ParameterDirection.Input);
            cmd.Parameters.Add("V_INNEREXCEPTION", OracleDbType.Varchar2, data.InnerException, ParameterDirection.Input);
            cmd.Parameters.Add("N_LOGSTATUS", OracleDbType.Int32, data.LogStatus, ParameterDirection.Input);
            cmd.Parameters.Add("V_EXCEPTIONMESSAGE", OracleDbType.Varchar2, data.Message, ParameterDirection.Input);
            cmd.Parameters.Add("V_PAGEPATH", OracleDbType.Varchar2, data.PagePath, ParameterDirection.Input);
            cmd.Parameters.Add("V_REFERRER", OracleDbType.Varchar2, data.Referer, ParameterDirection.Input);
            cmd.Parameters.Add("V_EXCEPTIONSOURCE", OracleDbType.Varchar2, data.Source, ParameterDirection.Input);
            cmd.Parameters.Add("V_SQUERY", OracleDbType.Varchar2, data.sQuery, ParameterDirection.Input);
            cmd.Parameters.Add("V_STACKTRACE", OracleDbType.Varchar2, data.StackTrace, ParameterDirection.Input);
            cmd.Parameters.Add("V_TARGETSITE", OracleDbType.Varchar2, data.TargetSite, ParameterDirection.Input);

            //open connection and run the application
            myconnection.Open();
            cmd.ExecuteNonQuery();
            int result = (int)cmd.Parameters["returnID"].Value;
            if (result != 0) // Only successfull if a zero was returned
            throw new Exception("Log entry data " + data.ToString() + " wasn't saved properly.");
            }
            catch(Exception ex)
            {

            }
            finally
            {
            myconnection.Close();
            }



            }

            Message was edited by:
            Kobojunkie
            • 3. Re: ORA-1858: a non-numeric character was found where a numeric was expected
              24208
              Hi,

              A couple of quick observations:

              In your pl/sql you have:
              ...
              d_datelogged IN DATE,
              ...

              in the .net code you have:
              ...
              cmd.Parameters.Add("D_DATELOGGED", OracleDbType.Varchar2,DateTime.Now.ToString(), ParameterDirection.Input);
              ...

              Also, the .net code has this parameter:
              ...
              cmd.Parameters.Add("V_TARGETSITE", OracleDbType.Varchar2, data.TargetSite, ParameterDirection.Input);
              ...

              I do not see an input parameter for that in the pl/sql code...

              To set bind by name:

              OracleCommand.BindByName = true;

              - Mark
              • 4. Re: ORA-1858: a non-numeric character was found where a numeric was expected
                621554
                I fixed all the much I could in the code as you pointed out but that did not seem to fix the problem at all. I now have ....


                FUNCTION exceptionlog_insert_update_fn (
                v_currentmethod IN VARCHAR,
                v_customerrormessage IN VARCHAR,
                d_datelogged IN DATE,
                v_helplink IN VARCHAR,
                v_http_costcenter IN VARCHAR,
                v_http_email IN VARCHAR,
                v_http_host IN VARCHAR,
                v_http_sm_authentic IN VARCHAR,
                v_http_sm_authorized IN VARCHAR,
                v_http_sm_sdomain IN VARCHAR,
                v_http_standardid IN VARCHAR,
                v_http_user_agent IN VARCHAR,
                v_innerexception IN VARCHAR,
                n_logstatus IN NUMBER,
                v_exceptionmessage IN VARCHAR,
                v_pagepath IN VARCHAR,
                v_referrer IN VARCHAR,
                v_exceptionsource IN VARCHAR,
                v_stacktrace IN VARCHAR,
                v_squery IN VARCHAR
                )
                RETURN NUMBER
                IS
                --DECLARE
                existingexceptionlog NUMBER :=0;
                n_rowcount NUMBER :=0;

                BEGIN
                SELECT exceptionlogid
                INTO existingexceptionlog
                FROM admin_exceptionlog
                WHERE pagepath = v_pagepath
                AND exceptionmessage = v_exceptionmessage
                AND referrer = v_referrer
                AND customerrormessage = v_customerrormessage;

                IF existingexceptionlog > 0
                THEN
                UPDATE admin_exceptionlog
                SET occurrences = 1,
                datelastmodified = d_datelogged
                WHERE exceptionlogid = existingexceptionlog;

                n_rowcount := SQL%ROWCOUNT;

                IF n_rowcount > 0
                THEN
                -- An Update occured RETURN O indicating this
                --
                RETURN 0;
                END IF;
                ELSE
                ----- If It does not already exist in the DB, Do an INSERT
                -- RETRIEVE THE ID INFORMATION FIRST

                INSERT INTO admin_exceptionlog
                (exceptionlogid, currentmethod,
                customerrormessage, datelogged, helplink,
                http_costcenter, http_email, http_host,
                http_sm_authentic, http_sm_authorized,
                http_sm_sdomain, http_standardid,
                http_user_agent, innerexception, logstatus,
                exceptionmessage, pagepath, referrer,
                exceptionsource, stacktrace, squery, occurrences,
                datelastmodified
                )
                VALUES (admin_exceptionlogid_seq.NEXTVAL, v_currentmethod,
                v_customerrormessage, d_datelogged, v_helplink,
                v_http_costcenter, v_http_email, v_http_host,
                v_http_sm_authentic, v_http_sm_authorized,
                v_http_sm_sdomain, v_http_standardid,
                v_http_user_agent, v_innerexception, n_logstatus,
                v_exceptionmessage, v_pagepath, v_referrer,
                v_exceptionsource, v_stacktrace, v_squery, 1,
                SYSDATE
                );

                n_rowcount := SQL%ROWCOUNT;
                IF n_rowcount > 0
                THEN
                -- An Update occured RETURN O indicating this
                --
                RETURN 0;
                END IF;
                END IF;
                EXCEPTION
                WHEN OTHERS
                THEN
                RETURN 1;
                END exceptionlog_insert_update_fn;
                • 5. Re: ORA-1858: a non-numeric character was found where a numeric was expected
                  621554
                  I also noticed from my code that when I do a dig on the Exception being returned.... I have this

                  + TargetSite     {Void PreBind_Date()}     System.Reflection.MethodBase {System.Reflection.RuntimeMethodInfo}


                  I don't know if this means anything to you
                  • 6. Re: ORA-1858: a non-numeric character was found where a numeric was expected
                    24208
                    Hi,

                    Did you change the .net code to pass a DATE to "d_datelogged" since it is declared as a DATE parameter?

                    - Mark
                    • 7. Re: ORA-1858: a non-numeric character was found where a numeric was expecte
                      485735
                      Do you know that Visual Studio comes with a debugger? You launch it with F5. It's awesome.

                      Michael O
                      http://blog.crisatunity.com
                      • 8. Re: ORA-1858: a non-numeric character was found where a numeric was expecte
                        621554
                        yes, I changed my c# code ....

                        private void Save()
                        {
                        int eventId = 0;
                        OracleConnection myconnection = new OracleConnection(this._connectionString);
                        try
                        {
                        OracleCommand cmd = new OracleCommand("pkg_ADMIN_ExceptionLog_Handler.ExceptionLog_Insert_Update_fn", myconnection);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.BindByName = true;
                        cmd.Parameters.Add("ReturnID", OracleDbType.Int32, eventId, ParameterDirection.ReturnValue);
                        cmd.Parameters.Add("V_CURRENTMETHOD", OracleDbType.Date, data.CurrentMethod, ParameterDirection.Input);
                        cmd.Parameters.Add("V_CUSTOMERRORMESSAGE", OracleDbType.Varchar2, data.CustomErrorMessage, ParameterDirection.Input);
                        cmd.Parameters.Add("D_DATELOGGED", OracleDbType.Date,DateTime.Now, ParameterDirection.Input);
                        cmd.Parameters.Add("V_HELPLINK", OracleDbType.Varchar2, data.HelpLink, ParameterDirection.Input);
                        cmd.Parameters.Add("V_HTTP_COSTCENTER", OracleDbType.Varchar2, data.HTTP_COSTCENTER, ParameterDirection.Input);
                        cmd.Parameters.Add("V_HTTP_EMAIL", OracleDbType.Varchar2, data.HTTP_EMAIL, ParameterDirection.Input);
                        cmd.Parameters.Add("V_HTTP_HOST", OracleDbType.Varchar2, data.HTTP_HOST, ParameterDirection.Input);
                        cmd.Parameters.Add("V_HTTP_SM_AUTHENTIC", OracleDbType.Varchar2, data.HTTP_SM_AUTHENTIC, ParameterDirection.Input);
                        cmd.Parameters.Add("V_HTTP_SM_AUTHORIZED", OracleDbType.Varchar2, data.HTTP_SM_AUTHORIZED, ParameterDirection.Input);
                        cmd.Parameters.Add("V_HTTP_SM_SDOMAIN", OracleDbType.Varchar2, data.HTTP_SM_SDOMAIN, ParameterDirection.Input);
                        cmd.Parameters.Add("V_HTTP_STANDARDID", OracleDbType.Varchar2, data.HTTP_STANDARDID, ParameterDirection.Input);
                        cmd.Parameters.Add("V_HTTP_USER_AGENT", OracleDbType.Varchar2, data.HTTP_USER_AGENT, ParameterDirection.Input);
                        cmd.Parameters.Add("V_INNEREXCEPTION", OracleDbType.Varchar2, data.InnerException, ParameterDirection.Input);
                        cmd.Parameters.Add("N_LOGSTATUS", OracleDbType.Int32, data.LogStatus, ParameterDirection.Input);
                        cmd.Parameters.Add("V_EXCEPTIONMESSAGE", OracleDbType.Varchar2, data.Message, ParameterDirection.Input);
                        cmd.Parameters.Add("V_PAGEPATH", OracleDbType.Varchar2, data.PagePath, ParameterDirection.Input);
                        cmd.Parameters.Add("V_REFERRER", OracleDbType.Varchar2, data.Referer, ParameterDirection.Input);
                        cmd.Parameters.Add("V_EXCEPTIONSOURCE", OracleDbType.Varchar2, data.Source, ParameterDirection.Input);
                        cmd.Parameters.Add("V_STACKTRACE", OracleDbType.Varchar2, data.StackTrace, ParameterDirection.Input);
                        cmd.Parameters.Add("V_SQUERY", OracleDbType.Varchar2, data.sQuery, ParameterDirection.Input);

                        //cmd.Parameters.Add("V_TARGETSITE", OracleDbType.Varchar2, data.TargetSite, ParameterDirection.Input);


                        //open connection and run the application
                        myconnection.Open();
                        cmd.ExecuteNonQuery();
                        int result = (int)cmd.Parameters["returnID"].Value;
                        if (result != 0) // Only successfull if a zero was returned
                        throw new Exception("Log entry data " + data.ToString() + " wasn't saved properly.");
                        }
                        catch(Exception ex)
                        {

                        }
                        finally
                        {
                        myconnection.Close();
                        }



                        }



                        I am seriously trying to learn to use the debugger but I am a newbie and clueless here.... I am from using SQL 2000 and 2005 and so I am puzzled here..
                        • 9. Re: ORA-1858: a non-numeric character was found where a numeric was expecte
                          24208
                          OK... have you verified that the procedure works as expected via another tool such as SQL*Plus etc? Have you determined that the values being passed to the stored procedure are correct? I have seen this error message generally when dealing with dates and date formatting. I do not see anything readily apparent with the order of parameters, etc.

                          - Mark
                          • 10. Re: ORA-1858: a non-numeric character was found where a numeric was expecte
                            621554
                            Could you please take a look at the RETURNVALUE line in my code please? Is it formatted right ?

                            I did remove the line that feeds date in from the front end and instead decided to insert SYSDATE directly into my code. but that does not seem to take either.... Do I need to format it for a DATE column FIELD in the Table?? Basically I want to save both the date and Time which I will use later on for processing in my code.



                            create or replace PACKAGE BODY pkg_admin_exceptionlog_handler
                            AS
                            FUNCTION exceptionlog_insert_update_fn (
                            v_currentmethod IN VARCHAR,
                            v_customerrormessage IN VARCHAR,
                            v_helplink IN VARCHAR,
                            v_http_costcenter IN VARCHAR,
                            v_http_email IN VARCHAR,
                            v_http_host IN VARCHAR,
                            v_http_sm_authentic IN VARCHAR,
                            v_http_sm_authorized IN VARCHAR,
                            v_http_sm_sdomain IN VARCHAR,
                            v_http_standardid IN VARCHAR,
                            v_http_user_agent IN VARCHAR,
                            v_innerexception IN VARCHAR,
                            n_logstatus IN INTEGER,
                            v_exceptionmessage IN VARCHAR,
                            v_pagepath IN VARCHAR,
                            v_referrer IN VARCHAR,
                            v_exceptionsource IN VARCHAR,
                            v_stacktrace IN VARCHAR,
                            v_squery IN VARCHAR
                            )
                            RETURN INTEGER
                            IS
                            --DECLARE
                            existingexceptionlog INTEGER :=0;
                            n_rowcount INTEGER :=0;

                            BEGIN
                            SELECT exceptionlogid
                            INTO existingexceptionlog
                            FROM admin_exceptionlog
                            WHERE pagepath = v_pagepath
                            AND exceptionmessage = v_exceptionmessage
                            AND referrer = v_referrer
                            AND customerrormessage = v_customerrormessage;

                            IF existingexceptionlog > 0
                            THEN
                            UPDATE admin_exceptionlog
                            SET occurrences = 1,
                            datelastmodified = SYSDATE
                            WHERE exceptionlogid = existingexceptionlog;

                            n_rowcount := SQL%ROWCOUNT;

                            IF n_rowcount > 0
                            THEN
                            -- An Update occured RETURN O indicating this
                            --
                            RETURN 0;
                            END IF;
                            ELSE
                            ----- If It does not already exist in the DB, Do an INSERT
                            -- RETRIEVE THE ID INFORMATION FIRST

                            INSERT INTO admin_exceptionlog
                            (exceptionlogid ,
                            customerrormessage,
                            datelogged, helplink,
                            http_costcenter, http_email, http_host,
                            http_sm_authentic, http_sm_authorized,
                            http_sm_sdomain, http_standardid,
                            http_user_agent, innerexception, logstatus,
                            exceptionmessage, pagepath, referrer,
                            exceptionsource, stacktrace, squery, occurrences,
                            datelastmodified
                            )
                            VALUES (admin_exceptionlogid_seq.NEXTVAL,
                            v_customerrormessage , SYSDATE , v_helplink,
                            v_http_costcenter, v_http_email, v_http_host,
                            v_http_sm_authentic, v_http_sm_authorized,
                            v_http_sm_sdomain, v_http_standardid,
                            v_http_user_agent, v_innerexception, n_logstatus,
                            v_exceptionmessage, v_pagepath, v_referrer,
                            v_exceptionsource, v_stacktrace, v_squery, 1,
                            SYSDATE
                            );

                            n_rowcount := SQL%ROWCOUNT;
                            IF n_rowcount > 0
                            THEN
                            -- An Update occured RETURN O indicating this
                            --
                            RETURN 0;
                            END IF;
                            END IF;
                            EXCEPTION
                            WHEN OTHERS
                            THEN
                            RETURN 2;
                            END exceptionlog_insert_update_fn;
                            • 11. Re: ORA-1858: a non-numeric character was found where a numeric was expecte
                              MarkHoxey
                              Have you checked that the data types of the underlying database table columns are defined to match the data types that you are attempted to insert/update? Your code may be correct but the table definition is different/wrong.

                              BTW, if the first SELECT...INTO statement fails to find any data then it will raise a NO_DATA_FOUND exception and jump to the exception handler and not simply leave the existingexceptionlog variable set to 0. This means the insert part of your function will never be called. Consider using the MERGE statement instead.

                              Mark
                              • 12. Re: ORA-1858: a non-numeric character was found where a numeric was expecte
                                621554
                                I do not know how to use MERGE and did not find much information on it. But I looked into modifying it and once again, it compiles in this case but when I run it using the debugger, no code is inserted into the table at all. And it exits with a row number = 0 at line

                                RETURN L_rowcount;

                                Is there something about running procedures on Oracle that I seem to be missing here please??? I went ahead and included information on my table as well.




                                CREATE OR REPLACE FUNCTION FN_INSERT_UPDATE_EXCEPTIONLOG
                                (
                                V_HELPLINK IN ADMIN_EXCEPTIONLOG.HELPLINK%TYPE,
                                V_HTTP_EMAIL IN ADMIN_EXCEPTIONLOG.HTTP_EMAIL%TYPE,
                                V_HTTP_HOST IN ADMIN_EXCEPTIONLOG.HTTP_HOST%TYPE,
                                V_HTTP_SM_AUTHENTIC IN ADMIN_EXCEPTIONLOG.HTTP_SM_AUTHENTIC%TYPE,
                                V_HTTP_COSTCENTER IN ADMIN_EXCEPTIONLOG.HTTP_COSTCENTER%TYPE,
                                V_HTTP_SM_SDOMAIN IN ADMIN_EXCEPTIONLOG.HTTP_SM_SDOMAIN%TYPE,
                                V_CUSTOMERRORMESSAGE IN ADMIN_EXCEPTIONLOG.CUSTOMERRORMESSAGE%TYPE,
                                V_CURRENTMETHOD IN ADMIN_EXCEPTIONLOG.CURRENTMETHOD%TYPE,
                                V_HTTP_SM_AUTHORIZED IN ADMIN_EXCEPTIONLOG.HTTP_SM_AUTHORIZED%TYPE,
                                V_HTTP_STANDARDID IN ADMIN_EXCEPTIONLOG.HTTP_STANDARDID%TYPE,
                                V_INNEREXCEPTION IN ADMIN_EXCEPTIONLOG.INNEREXCEPTION%TYPE,
                                V_EXCEPTIONMESSAGE IN ADMIN_EXCEPTIONLOG.EXCEPTIONMESSAGE%TYPE,
                                V_LOGSTATUS IN ADMIN_EXCEPTIONLOG.LOGSTATUS%TYPE,
                                V_PAGEPATH IN ADMIN_EXCEPTIONLOG.PAGEPATH%TYPE,
                                V_REFERRER IN ADMIN_EXCEPTIONLOG.REFERRER%TYPE,
                                V_EXCEPTIONSOURCE IN ADMIN_EXCEPTIONLOG.EXCEPTIONSOURCE%TYPE,
                                V_STACKTRACE IN ADMIN_EXCEPTIONLOG.STACKTRACE%TYPE,
                                V_TARGETSITE IN ADMIN_EXCEPTIONLOG.TARGETSITE%TYPE,
                                V_SQUERY IN ADMIN_EXCEPTIONLOG.SQUERY%TYPE
                                ) RETURN NUMBER AS

                                L_datevariable Date :=Sysdate;
                                L_exceptionlogid integer :=0;
                                L_rowcount number;


                                BEGIN

                                -- Detect any existing entries with the unique
                                -- combination of columns as in this constraint:
                                -- constraint WORKER_T_UK2
                                -- unique (
                                -- CUSTOMERRORMESSAGE,
                                -- LOGSTATUS,
                                -- PAGEPATH )
                                begin
                                select ExceptionLogID
                                into L_exceptionlogid
                                from ADMIN_EXCEPTIONLOG AE
                                where AE.CUSTOMERRORMESSAGE = V_CUSTOMERRORMESSAGE
                                AND AE.LOGSTATUS = V_LOGSTATUS
                                AND AE.PAGEPATH = V_PAGEPATH;
                                exception
                                when NO_DATA_FOUND then
                                L_exceptionlogid := 0; -- Is this really needed?
                                when OTHERS then
                                raise_application_error(-20003, SQLERRM||
                                ' on select WORKER_T_T'||
                                ' in filename insert_with_plsql_detection_for_update.sql');
                                end;
                                -- Conditionally insert the row
                                if L_exceptionlogid is NULL then
                                -- Now, let's get the next id sequence
                                -- we can finally insert a row!
                                begin
                                insert into ADMIN_EXCEPTIONLOG (
                                CURRENTMETHOD,
                                CUSTOMERRORMESSAGE,
                                DATELOGGED,
                                HELPLINK,
                                HTTP_COSTCENTER,
                                HTTP_EMAIL,
                                HTTP_HOST,
                                HTTP_SM_AUTHENTIC,
                                HTTP_SM_AUTHORIZED,
                                HTTP_SM_SDOMAIN,
                                HTTP_STANDARDID,
                                INNEREXCEPTION,
                                LOGSTATUS,
                                EXCEPTIONMESSAGE,
                                PAGEPATH,
                                REFERRER,
                                EXCEPTIONSOURCE,
                                STACKTRACE,
                                TARGETSITE,
                                SQUERY,
                                OCCURRENCES)
                                values (
                                V_CURRENTMETHOD,
                                V_CUSTOMERRORMESSAGE,
                                L_datevariable,
                                V_HELPLINK,
                                V_HTTP_COSTCENTER,
                                V_HTTP_EMAIL,
                                V_HTTP_HOST,
                                V_HTTP_SM_AUTHENTIC,
                                V_HTTP_SM_AUTHORIZED,
                                V_HTTP_SM_SDOMAIN,
                                V_HTTP_STANDARDID,
                                V_INNEREXCEPTION,
                                V_LOGSTATUS,
                                V_EXCEPTIONMESSAGE,
                                V_PAGEPATH,
                                V_REFERRER,
                                V_EXCEPTIONSOURCE,
                                V_STACKTRACE,
                                V_TARGETSITE,
                                V_SQUERY, 1
                                );

                                L_rowcount := sql%rowcount;
                                exception
                                when OTHERS then
                                raise_application_error(-20006, SQLERRM||
                                ' on insert WORKER_T'||
                                ' in filename insert_with_plsql_detection_for_update.sql');
                                end;
                                else
                                begin
                                update ADMIN_EXCEPTIONLOG AE
                                set AE.DATELOGGED = Sysdate
                                , AE.OCCURRENCES = AE.OCCURRENCES + 1
                                where AE.EXCEPTIONLOGID = L_exceptionlogid;

                                L_rowcount := sql%rowcount;
                                exception
                                when OTHERS then
                                raise_application_error(-20007, SQLERRM||
                                ' on update WORKER_T'||
                                ' in filename insert_with_plsql_detection_for_update.sql');
                                end;
                                end if;

                                RETURN L_rowcount;
                                END FN_INSERT_UPDATE_EXCEPTIONLOG;





                                CREATE TABLE "SYSTEM"."ADMIN_EXCEPTIONLOG"
                                (     "EXCEPTIONLOGID" NUMBER NOT NULL ENABLE,
                                     "CURRENTMETHOD" VARCHAR2(400 BYTE),
                                     "CUSTOMERRORMESSAGE" VARCHAR2(4000 BYTE),
                                     "DATELOGGED" DATE,
                                     "HELPLINK" VARCHAR2(4000 BYTE),
                                     "HTTP_COSTCENTER" VARCHAR2(4000 BYTE),
                                     "HTTP_EMAIL" VARCHAR2(4000 BYTE),
                                     "HTTP_HOST" VARCHAR2(4000 BYTE),
                                     "HTTP_SM_AUTHENTIC" VARCHAR2(4000 BYTE),
                                     "HTTP_SM_AUTHORIZED" VARCHAR2(4000 BYTE),
                                     "HTTP_SM_SDOMAIN" VARCHAR2(4000 BYTE),
                                     "HTTP_STANDARDID" VARCHAR2(4000 BYTE),
                                     "INNEREXCEPTION" VARCHAR2(4000 BYTE),
                                     "LOGSTATUS" NUMBER NOT NULL ENABLE,
                                     "EXCEPTIONMESSAGE" VARCHAR2(4000 BYTE),
                                     "PAGEPATH" VARCHAR2(4000 BYTE),
                                     "REFERRER" VARCHAR2(4000 BYTE),
                                     "EXCEPTIONSOURCE" VARCHAR2(4000 BYTE),
                                     "STACKTRACE" VARCHAR2(4000 BYTE),
                                     "TARGETSITE" VARCHAR2(4000 BYTE),
                                     "SQUERY" VARCHAR2(4000 BYTE),
                                     "OCCURRENCES" NUMBER,
                                     CONSTRAINT "ADMIN_EXCEPTIONLOG_PK" PRIMARY KEY ("EXCEPTIONLOGID")
                                USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                                STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                                TABLESPACE "SYSTEM" ENABLE
                                ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                                STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                                TABLESPACE "SYSTEM" ;

                                CREATE OR REPLACE TRIGGER "SYSTEM"."ADMIN_EXCEPTIONLOG_TRG"
                                BEFORE INSERT ON ADMIN_EXCEPTIONLOG

                                FOR EACH ROW

                                BEGIN

                                SELECT ADMIN_EXCEPTIONLOG_EXCEPTIONLOGID_SEQ.nextVal INTO :new.EXCEPTIONLOGID FROm Dual;

                                END;
                                /
                                ALTER TRIGGER "SYSTEM"."ADMIN_EXCEPTIONLOG_TRG" ENABLE;
                                • 13. Re: ORA-1858: a non-numeric character was found where a numeric was expecte
                                  372016
                                  It looks to me as if you are checking to see if a row exists. In your exception you catch no_data_found and set a variable to 0.

                                  You then say if this varaible is null then insert, but it won't be null, you just set it to zero, so no insert. The else logic kicks in and does an update whcih does not error, but updates nothing as presumably there is no wrow with an id of 0.

                                  Possible cures;

                                  set L_exceptionlogid := null; in your catch of no_data_found

                                  or

                                  move all the insert logic into the exception handler and get rid of the if statement so it looks like

                                  begin
                                  select L_exceptionlogid where ... etc etc
                                  -- we must have a result as we didn't get an exception so update
                                  update ADMIN_EXCEPTIONLOG AE where etc etc
                                  capture row count

                                  exception
                                  when no_data_found then
                                  insert into .... etc
                                  set rowcount
                                  end;
                                  return
                                  • 14. Re: ORA-1858: a non-numeric character was found where a numeric was expecte
                                    MarkHoxey
                                    It appears that you have correctly modified the first SELECT...INTO statement to include an exception handler that tests for NO_DATA_FOUND. However, in the exception handler you are setting L_exceptionlogid to 0 (in response to your commented question, no, this isn't required as you initialise this variable to 0 when you declare it and you don't modified before this)... yet just before the INSERT statement you test for L_exceptionlogid IS NULL.

                                    You should modifiy the IF logical test to be L_exceptionlogid = 0.

                                    As for the MERGE command, I think the following is what you might need (I haven't actually tested this SQL out so it might have one or two syntax errors):

                                    MERGE INTO ADMIN_EXCEPTIONLOG a
                                    USING (SELECT V_HELPLINK,
                                    V_HTTP_EMAIL,
                                    V_HTTP_HOST,
                                    V_HTTP_SM_AUTHENTIC,
                                    V_HTTP_COSTCENTER,
                                    V_HTTP_SM_SDOMAIN,
                                    V_CUSTOMERRORMESSAGE,
                                    V_CURRENTMETHOD,
                                    V_HTTP_SM_AUTHORIZED,
                                    V_HTTP_STANDARDID,
                                    V_INNEREXCEPTION,
                                    V_EXCEPTIONMESSAGE,
                                    V_LOGSTATUS,
                                    V_PAGEPATH,
                                    V_REFERRER,
                                    V_EXCEPTIONSOURCE,
                                    V_STACKTRACE,
                                    V_TARGETSITE,
                                    V_SQUERY
                                    FROM dual) v
                                    ON ( a.CUSTOMERRORMESSAGE = v.V_CUSTOMERRORMESSAGE
                                    AND a.LOGSTATUS = v.V_LOGSTATUS
                                    AND a.PAGEPATH = v.V_PAGEPATH)
                                    WHEN MATCHED THEN
                                    -- specifiy what to update for the rows that matched
                                    update
                                    set a.DATELOGGED = Sysdate
                                    , a.OCCURRENCES = a.OCCURRENCES + 1
                                    WHEN NOT MATCHED THEN
                                    -- specify what to insert when no match was made
                                    insert (
                                    CURRENTMETHOD,
                                    CUSTOMERRORMESSAGE,
                                    DATELOGGED,
                                    HELPLINK,
                                    HTTP_COSTCENTER,
                                    HTTP_EMAIL,
                                    HTTP_HOST,
                                    HTTP_SM_AUTHENTIC,
                                    HTTP_SM_AUTHORIZED,
                                    HTTP_SM_SDOMAIN,
                                    HTTP_STANDARDID,
                                    INNEREXCEPTION,
                                    LOGSTATUS,
                                    EXCEPTIONMESSAGE,
                                    PAGEPATH,
                                    REFERRER,
                                    EXCEPTIONSOURCE,
                                    STACKTRACE,
                                    TARGETSITE,
                                    SQUERY,
                                    OCCURRENCES)
                                    values (
                                    v.V_CURRENTMETHOD,
                                    v.V_CUSTOMERRORMESSAGE,
                                    L_datevariable,
                                    v.V_HELPLINK,
                                    v.V_HTTP_COSTCENTER,
                                    v.V_HTTP_EMAIL,
                                    v.V_HTTP_HOST,
                                    v.V_HTTP_SM_AUTHENTIC,
                                    v.V_HTTP_SM_AUTHORIZED,
                                    v.V_HTTP_SM_SDOMAIN,
                                    v.V_HTTP_STANDARDID,
                                    v.V_INNEREXCEPTION,
                                    v.V_LOGSTATUS,
                                    v.V_EXCEPTIONMESSAGE,
                                    v.V_PAGEPATH,
                                    v.V_REFERRER,
                                    v.V_EXCEPTIONSOURCE,
                                    v.V_STACKTRACE,
                                    v.V_TARGETSITE,
                                    v.V_SQUERY,
                                    1
                                    );


                                    Mark
                                    1 2 Previous Next