Home » SQL & PL/SQL » SQL & PL/SQL » ora-01847 error, can't figure out why (oracle 10g)
ora-01847 error, can't figure out why [message #323517] Wed, 28 May 2008 11:34 Go to next message
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 #323518 is a reply to message #323517] Wed, 28 May 2008 11:41 Go to previous messageGo to next message
helsafi
Messages: 1
Registered: May 2008
Location: Bahrian
Junior Member

Enter a valid day value for the specified month
Re: ora-01847 error, can't figure out why [message #323519 is a reply to message #323517] Wed, 28 May 2008 11:42 Go to previous messageGo to next message
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 #323520 is a reply to message #323518] Wed, 28 May 2008 11:45 Go to previous messageGo to next message
clacey2
Messages: 6
Registered: May 2008
Junior Member
I know that is what the ora-01847 message means, day of month be between 1 and last day of month...but I can't figure out where the code is getting an invalid date from. Everything is partitioned and based upon the sysdate
Re: ora-01847 error, can't figure out why [message #323521 is a reply to message #323520] Wed, 28 May 2008 11:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you cut and paste a DESC of the tabe qry_TES_RollingCurEdits for us.
Re: ora-01847 error, can't figure out why [message #323523 is a reply to message #323521] Wed, 28 May 2008 11:51 Go to previous messageGo to next message
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
Re: ora-01847 error, can't figure out why [message #323524 is a reply to message #323517] Wed, 28 May 2008 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>select to_char(MAX( part_name ))
Why the TO_CHAR on CHAR variable?
Why CHAR & not VARCHAR2?
Re: ora-01847 error, can't figure out why [message #323525 is a reply to message #323524] Wed, 28 May 2008 12:09 Go to previous messageGo to next message
clacey2
Messages: 6
Registered: May 2008
Junior Member
no real reason in particular, can change to_varchar2 if that would help with the problem?
Re: ora-01847 error, can't figure out why [message #323528 is a reply to message #323525] Wed, 28 May 2008 12:22 Go to previous messageGo to next message
clacey2
Messages: 6
Registered: May 2008
Junior Member
Thank you guys for the help, I think I solved the problem. After looking at the table desc, somehow the last two columns were switched around and the data types were getting messed up.
Re: ora-01847 error, can't figure out why [message #323550 is a reply to message #323528] Wed, 28 May 2008 15:30 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So error wasn't produced in this piece of code, but the one you didn't post:
Quote:
/* after this it is just an insert statement*/

I suspect that it was either
INSERT INTO this_table SELECT * FROM another_table
while tables' description wasn't identical, or - if you included column names in the INSERT INTO and SELECT statements - you switched these two columns you've mentioned.

Whichever it is, always specify all column names; never use wildcard (*).
Re: ora-01847 error, can't figure out why [message #323555 is a reply to message #323550] Wed, 28 May 2008 16:11 Go to previous message
clacey2
Messages: 6
Registered: May 2008
Junior Member
Thank you for the reply. I did specify each column individually in the insert statement, did not use a wildcard. Selected individual columns.
Previous Topic: creating an array out of several rows of data.
Next Topic: nvl in where clause
Goto Forum:
  


Current Time: Tue Dec 06 10:41:10 CST 2016

Total time taken to generate the page: 1.43809 seconds