Solved

ORA-01830

Posted on 2013-05-29
6
806 Views
Last Modified: 2013-05-29
I am having an issue with a procedure of mine where I am receiving data from an External table and inserting that information into an underlying base table. I keep receiving an error code of ORA-01830 and not sure why when the process worked last week. I am running on Oracle 11g.  I don't know what changed. My code to my procedure is listed below:


Procedure:
PROCEDURE P_INSERT_WEBDATA IS


v_pidm NUMBER;
N_COUNT NUMBER := 0;


CURSOR c_WEBREC_CUR IS
--New Solution according to redefined specs.
SELECT NETID, TO_DATE(COMP_DATE,  'YYYY-MM-DD HH24:MI:SS') "COMP_DATE"
FROM WEBAPP.T_EXTERNAL_VIRTUAL_ORIENT
WHERE TO_DATE(COMP_DATE, 'YYYY-MM-DD HH24:MI:SS') > (SELECT MAX(b.COMP_DATE) FROM WEBAPP.T_VIRTUAL_ORIENT b);
--> (SELECT MAX(b.COMP_DATE) FROM WEBAPP.T_VIRTUAL_ORIENT b);



BEGIN

FOR NEWWEB_REC IN c_WEBREC_CUR LOOP

v_pidm := WEBAPP.PKG_VIRTUAL_UPDATE.F_GETPIDM_BY_NETID(NEWWEB_REC.NETID);


INSERT INTO APP_WEBAPP.T_VIRTUAL_ORIENT
(PIDM
,NETID
,COMP_DATE
,ADMIT_TERM)

VALUES
(v_pidm
,NEWWEB_REC.NETID
,NEWWEB_REC.COMP_DATE
,WEBAPP.PKG_VIRTUAL_UPDATE.F_GRAB_ADMITTERM(v_pidm));

N_COUNT := N_COUNT + 1;

END LOOP;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
RAISE;

END P_INSERT_WEBDATA;

My table creation script to the underlying table is:

CREATE TABLE WEBAPP.T_VIRTUAL_ORIENT
(
  PIDM        NUMBER,
  NETID       VARCHAR2(25 BYTE),
  ADMIT_TERM  VARCHAR2(10 BYTE),
  COMP_DATE   DATE
)
TABLESPACE APP_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

I have also attached an data file that is used to import into my External table, in which I have attached. Furthermore; I have provided the creation script for my External table as well, just to further along the process.

External Table Script:
CREATE TABLE APP_REGISTRAR.T_EXTERNAL_VIRTUAL_ORIENT
(
  NETID      VARCHAR2(25 CHAR),
  COMP_DATE  VARCHAR2(25 CHAR)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY EXTERNAL_DATA
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY NEWLINE
   NOLOGFILE
   NODISCARDFILE
   FIELDS TERMINATED BY ","
   MISSING FIELD VALUES ARE NULL
(
 NETID        CHAR(25),
 COMP_DATE CHAR(25)
 )
                )
     LOCATION (EXTERNAL_DATA:'weblearners.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
Newweblearners-txt.txt
0
Comment
Question by:Seaghost
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 205 total points
ID: 39204408
check the format in the external file.

Some of the rows are missing the AM and PM at the end:
rsaud,2013-05-23 14:13:01 PM
rsaud,2013-05-24 10:29:28


The format in the file MUST match the format in the TO_DATE call.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39204456
slightwv is right:
try for yourself:

This will raise ORA
select to_date('2013-05-23 14:13:01 PM', 'YYYY-MM-DD HH24:MI:SS')
  from dual;

Open in new window


This is ok
select to_date('2013-05-24 10:29:28', 'YYYY-MM-DD HH24:MI:SS')
  from dual;

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 205 total points
ID: 39204726
It appears that you have dates with hh24 formats, but some add an additional (invalid) AM or PM. You might try modifying your to_date functions to something like -
TO_DATE(REPLACE(REPLACE(COMP_DATE,' AM',''),' PM',''),'YYYY-MM-DD HH24:MI:SS')
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39204742
Good catch awking00!

For example the following doesn't make sense and could not be converted to a date anyway: 14:13:01 PM

Try it:
select to_date('14:00 PM','HH:MI AM') from dual;

You 'could' do a blanket replace as suggested above but that could EASILY lead to incorrect data down the road.  What happens if you get: 10:00 PM and it actually means 10 PM and you replace PM with nothing and switch to 24 hour time, the data will be loaded as 10 AM.

Instead of trying to jump through hoops to manipulate the data and 'guess' at what is correct, you need to fix the process that generates the input file to ensure the data is correct.
0
 

Author Comment

by:Seaghost
ID: 39204992
Ok; I have modified the file that has been delivered to me from the supplying vendor and it worked the way it should. However; I am faced with yet another small issue. When I try and select from my under lying table (New table with no data) to compare dates ; I of-course will receive a null value in my select statement. In the code snippet below, this works perfectly well if there is data in my base table APP_REGISTRAR.T_VIRTUAL_ORIENT; but if there is no data in this table because it is a new table, how can I compensate for a returned NULL value of a date datatype so that it will still bring across the initial data from my External table?

SELECT NETID, TO_DATE(COMP_DATE,  'YYYY-MM-DD HH24:MI:SS') "COMP_DATE"
FROM APP_REGISTRAR.T_EXTERNAL_VIRTUAL_ORIENT
WHERE TO_DATE(COMP_DATE, 'YYYY-MM-DD HH24:MI:SS') > (SELECT MAX(b.COMP_DATE) FROM APP_REGISTRAR.T_VIRTUAL_ORIENT b);
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39205003
>>but if there is no data in this table because it is a new table, how can I compensate for a returned NULL

This really should be a new question.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question