?
Solved

ORA-01830

Posted on 2013-05-29
6
Medium Priority
?
816 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 820 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 820 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

777 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