oracle错误代码 1858,ORA-1858: a non-numeric character was found where a numeric was expected — oracle-te...

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;