asked on

ORA-01830

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alex [***Alex140181***]

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

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

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.

Seaghost

ASKER

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);

slightwv (䄆 Netminder)

>>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.