Solved

ORA-01830

Posted on 2013-05-29
6
788 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
6 Comments
 
LVL 76

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 31

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now