ora-01847 error, can't figure out why [message #323517] |
Wed, 28 May 2008 11:34  |
clacey2
Messages: 6 Registered: May 2008
|
Junior Member |
|
|
I have been running the procedure below for a long time now and all of a sudden today, I recieved the ora-01847 error and I can't seem to figure out why, below is the portion of the procedure that I think is giving me the problem:
create or replace PROCEDURE TESCurEdits_Rolling_Update_sp
IS
v_MaxPart varchar2(40);
v_MinDt date;
v_PartitionRange char(6) ;
v_PartitionName varchar2(40) ;
v_ExecuteStatement varchar2(255) ;
BEGIN
/* ***** ***** ***** ***** ***** ***** */
/* */
/* Table is partioned by month (and year) */
/* Do we need to create a new partition? */
/* */
/* ***** ***** ***** ***** ***** ***** */
/* Get the last partition */
select to_char(MAX( part_name ))
into v_MaxPart
from gwprod.qry_TES_RollingCurEdits;
If to_char( sysdate, 'yyyymm' ) > v_MaxPart Then
/* CREATE NEW PARTITION */
v_PartitionName := to_char( sysdate, 'yyyymm' ) ;
v_PartitionRange := to_Char( Add_Months( sysdate, 1 ), 'yyyymm' ) ;
v_ExecuteStatement :=
'ALTER Table gwprod.qry_TES_RollingCurEdits ADD PARTITION "' ||
v_PartitionName || '" Values Less Than ( ''' || v_PartitionRange ||
''')' ;
Execute Immediate v_ExecuteStatement ;
Commit ;
End If ;
/* ***** ***** ***** ***** ***** ***** */
/* */
/* if this rountine run multi times in a day */
/* prevent duplicate rows being added */
/* */
/* ***** ***** ***** ***** ***** ***** */
delete from gwprod.qry_TES_RollingCurEdits
where run_dt = trunc( sysdate );
commit;
/* after this it is just an insert statement*/
any thoughts??
|
|
|
|
Re: ora-01847 error, can't figure out why [message #323519 is a reply to message #323517] |
Wed, 28 May 2008 11:42   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You get this error when converting a String to a date.
The most likely causes are
Either - 1) You are doing a TO_DATE on a string and the day part of the string makes the date invalid
Or - 2) You have a column in a table that stores a date as a Varchar2, and you've got garbage data in there.
|
|
|
|
|
Re: ora-01847 error, can't figure out why [message #323523 is a reply to message #323521] |
Wed, 28 May 2008 11:51   |
clacey2
Messages: 6 Registered: May 2008
|
Junior Member |
|
|
sure....here is the table structure for qry_tes_rollingcuredits:
RUN_DT DATE Yes 1
ENCOUNTER NUMBER(22,0) No 2
TRANSACTION_ZZ NUMBER(22,0) No 3
EDIT_COND NUMBER(22,0) No 4
TES_CREATE_DT DATE Yes 5
CREATE_LAGDAYS NUMBER(22,0) Yes 6
CREATE_LAGCAT VARCHAR2(4000 Bytes) Yes 7
BAR_GROUP_ID NUMBER(22,0) Yes 8
GRP_NAME VARCHAR2(51 Bytes) Yes 9
DIV_ID NUMBER(22,0) Yes 10
DIV_SHORT VARCHAR2(6 Bytes) Yes 11
DIV_NAME VARCHAR2(54 Bytes) Yes 12
BILLAREA_ID NUMBER(22,0) Yes 13
BILLAREA_NUM VARCHAR2(7 Bytes) Yes 14
BILLAREA_NAME VARCHAR2(30 Bytes) Yes 15
LOC_ID NUMBER(22,0) Yes 16
IOC VARCHAR2(4000 Bytes) Yes 17
SOS VARCHAR2(3 Bytes) Yes 18
LOC_NAME VARCHAR2(69 Bytes) Yes 19
LOC_NBR VARCHAR2(6 Bytes) Yes 20
PROV_ID NUMBER(22,0) Yes 21
PROV_AGG VARCHAR2(9 Bytes) Yes 22
PROV_NAME VARCHAR2(30 Bytes) Yes 23
PROV_CAT VARCHAR2(1 Bytes) Yes 24
PROV_MNE VARCHAR2(12 Bytes) Yes 25
PROC_ID NUMBER(22,0) Yes 26
PROC_CAT VARCHAR2(5 Bytes) Yes 27
PROC_CODE VARCHAR2(43 Bytes) Yes 28
PROC_NAME VARCHAR2(79 Bytes) Yes 29
PROC_CODE_NAME VARCHAR2(125 Bytes) Yes 30
PROC_NAME_CODE VARCHAR2(125 Bytes) Yes 31
PROC_RPT_CAT1 VARCHAR2(28 Bytes) Yes 32
EDIT_COND_NAME VARCHAR2(69 Bytes) Yes 33
EDIT_TYPE VARCHAR2(3 Bytes) Yes 34
EDIT_GROUP_ID NUMBER(22,0) Yes 35
EDIT_GROUP_NAME VARCHAR2(31 Bytes) Yes 36
EDIT_GROUP_MNEMONIC VARCHAR2(10 Bytes) Yes 37
CATEGORY_ID VARCHAR2(2 Bytes) Yes 38
CATEGORY_NAME VARCHAR2(19 Bytes) Yes 39
CATEGORY_MNEMONIC VARCHAR2(4 Bytes) Yes 40
CHG_AMT NUMBER(22,0) Yes 41
COUNTER NUMBER(22,0) Yes 42
PART_NAME CHAR(6 Bytes) Yes 43
REF_PHY_NM VARCHAR2(255 Bytes) Yes 44
REF_PHY_NPI VARCHAR2(12 Bytes) Yes 45
REF_PHY_ADD VARCHAR2(255 Bytes) Yes 46
TAX_ID VARCHAR2(11 Bytes) Yes 47
SERV_DT DATE Yes 48
|
|
|
|
|
|
|
|