Some Help Please: ORA-01722: invalid number

0 views
Skip to first unread message

kingpoop

unread,
Oct 21, 2005, 11:52:25 AM10/21/05
to PLSQL
I am an error while updating a database table.
I think the problem is when I am doing the insert and converting the
data in the values section:

Can anyone shed some light on this:
Thanks, code below:

PROCEDURE frm12139Write(
i_patno IN NUMBER,
i_studyid IN VARCHAR2,
i_regtype IN VARCHAR2,
i_cysdt IN VARCHAR2,
i_formn IN VARCHAR2,
o_results OUT VARCHAR2
)
IS

vStageRow STAGE.TX0412%rowtype;

BEGIN
o_results := 'OK';
SELECT * INTO vStageRow
FROM STAGE.TX0412
WHERE PATNO = i_patno
AND STUDYID = i_studyid
AND REGTYPE = i_regtype
AND CYSDT = i_cysdt
AND FORMN = i_formn
AND SUBNO IN (SELECT MAX (SUBNO)
FROM STAGE.TX0412
WHERE PATNO = i_patno
AND STUDYID = i_studyid
AND REGTYPE = i_regtype
AND CYSDT = i_cysdt
AND FORMN = i_formn
AND PROMOTE='Y');

INSERT INTO DBADMIN.TX0412
(
PATNO,
STUDYID,
REGTYPE,
CYCLENO,
LCDATE,
STATUS,
CYSDT,
WT,
RXSTOPDT,
BSA,
ADDADJOM,
IFN,
BAY43,
BAY43DAYS,
GCSF,
EXPID,
TIME_STAMP,
SUBMUSER
)
VALUES
(
TO_NUMBER(vStageRow.PATNO),
vStageRow.STUDYID,
vStageRow.REGTYPE,
TO_NUMBER(vStageRow.CYCLENO),
TO_DATE(TO_CHAR(vStageRow.LCDATEM || '/' || vStageRow.LCDATED || '/' ||
vStageRow.LCDATEY,'mm/dd/yyyy'),'mm/dd/yyyy'),
vStageRow.STATUS,
TO_DATE(TO_CHAR(vStageRow.CYSDT,'mm/dd/yyyy'),'mm/dd/yyyy'),
TO_NUMBER(TO_CHAR(vStageRow.WTWHOLE || '.' || vStageRow.WTDECIMAL)),
TO_DATE(TO_CHAR(vStageRow.RXSTOPDTM || '/' || vStageRow.RXSTOPDTD ||
'/' || vStageRow.RXSTOPDTY,'mm/dd/yyyy'),'mm/dd/yyyy'),
TO_NUMBER(vStageRow.BSAWHOLE || '.' || vStageRow.BSADECIMAL),
vStageRow.ADDADJOM,
TO_NUMBER(vStageRow.IFN),
TO_NUMBER(vStageRow.BAY43),
TO_NUMBER(vStageRow.BAY43DAYS),
vStageRow.GCSF,
'TREATMNT',
TO_DATE(TO_CHAR(vStageRow.SUBMDT,'mm/dd/yyyy'),'mm/dd/yyyy'),
vStageRow.SUBMUSER
);

Reply all
Reply to author
Forward
0 new messages