Browse > Home / Oracle / Battling ORA-01841: (full) year must be between -4713 and +9999

Battling ORA-01841: (full) year must be between -4713 and +9999

September 19, 2007 by Mark Marucot 

I’m currently doing a custom development on an existing customized Oracle Retail batch programs for a large retailer in US. The batch program is uploading Return to Vendor transaction, initiated from an external system, to Oracle Retail Merchandizing System.The modification is to pickup records from the new staging tables. The modification is to include the new staging tables to the driving cursor of the batch program. The pseudo code for the driving cursor is:

PLAIN TEXT
CODE:
  1. SELECT '1', -- indicator
  2. ROWIDTOCHAR(rowid),
  3. location,
  4. supplier, –- varchar2
  5. create_date, –- date
  6. completed_date, –- date
  7. tran_type –- varchar2
  8. FROM table1
  9. union ALL
  10. SELECT '2', –- indicator
  11. ROWIDTOCHAR(rowid),
  12. location,
  13. supplier, -– varchar2
  14. create_date, –- date
  15. NULL, –- date
  16. NULL –- varchar2
  17. FROM table2;

I used NULL to match the columns in the first select statement because table 2 has no equivalent columns. I’m confident to use the above statement because it works well in TOAD or in SQL Plus. I placed this changes to the driving cursor and perform some testing.
When I executed the batch program, I encountered “core dump”. The cause of the core dump is my struct in Pro *C can’t handle null values which is the given SQL statement there are 3 NULL values. Even though I placed NULL indicator terminator, I encountered core dump.

Struct Definition

PLAIN TEXT
CODE:
  1. <code style="margin: 0px" dir="ltr">struct fetch_record
  2. {
  3. char (*rec_ind) [NULL_IND];
  4. char (*row_id) [NULL_ROWID];
  5. char (*loc) [NULL_LOC];
  6. char (*supplier) [NULL_SUPPLIER];
  7. char (*create_date) [NULL_DATE];
  8. char (*completed_date) [NULL_DATE];
  9. char (*tran_type) [NULL_TRANTYPE];
  10. short *i_rec_ind;
  11. short *i_row_ind;
  12. short *i_loc_ind;
  13. short *i_supplier_ind;
  14. short *i_create_date_ind;
  15. short *i_completed_date_ind;
  16. short *i_tran_type_ind;
  17. }pt_fetch_rec;</code>

Fetching Records to Struct

PLAIN TEXT
CODE:
  1. EXEC SQL FOR :pl_commit_max_ctr
  2. FETCH c_get_tran INTO :pt_fetch_rec.rec_ind:pt_rtv_fetch.i_rec_ind,
  3. :pt_fetch_rec.row_id:pt_rtv_fetch.i_row_id_ind,
  4. :pt_fetch_rec.loc:pt_rtv_fetch.i_ship_loc_ind,
  5. :pt_fetch_rec.supplier:pt_rtv_fetch.i_supplier_ind,
  6. :pt_fetch_rec.create_date:pt_rtv_fetch.i_create_date_ind,
  7. :pt_fetch_rec.completed_date:pt_rtv_fetch.i_completed_date_ind,
  8. :pt_fetch_rec.tran_type:pt_rtv_fetch.i_tran_type_ind;

The next thing I do is to modify the driving cursor and replace NULL with ‘ ‘(with space between the single quote). For the number field

PLAIN TEXT
SQL:
  1. SELECT '1', -- indicator
  2. ROWIDTOCHAR(rowid),
  3. location,
  4. supplier, –- varchar2
  5. create_date, –- date
  6. completed_date, –- date
  7. tran_type –- varchar2
  8. FROM table1
  9. union ALL
  10. SELECT '2', –- indicator
  11. ROWIDTOCHAR(rowid),
  12. location,
  13. supplier, -– varchar2
  14. create_date, –- date
  15. ' ', –- date
  16. ' ' –- varchar2
  17. FROM table2;

After compiling the changes, I run the batch program. The batch program runs and up to the point of inserting records to the RTV tables. The program stopped and when I looked into the error log, I see the error.

ORA-01841: (full) year must be between -4713 and +9999, and not be 0.

After doing some investigation, using ' ' (with space between the single quote) in the driving cursor indicates that the value is a string. When I insert records to a date column in the table, the error above is encountered. For the date field, I used ” (without space between the single quote). See the new driving cursor below.

PLAIN TEXT
SQL:
  1. SELECT '1', -- indicator
  2. ROWIDTOCHAR(rowid),
  3. location,
  4. supplier, –- varchar2
  5. create_date, –- date
  6. completed_date, –- date
  7. tran_type –- varchar2
  8. FROM table1
  9. union ALL
  10. SELECT '2', –- indicator
  11. ROWIDTOCHAR(rowid),
  12. location,
  13. supplier, -– varchar2
  14. create_date, –- date
  15. '', –- date
  16. ' ' –- varchar2
  17. FROM table2;

Related Posts

Filed Under: Oracle
Tagged: ,

Comments

Feel free to leave a comment...
and oh, if you want a pic to show with your comment, go get a gravatar!