Working with External Oracle Tables

I have a basic external table that contains two basic columns.

Column A:  UserID  VARCHAR2(25),
Column B: COMP_DATE  DATE

--Script that I used to create my External Table
CREATE TABLE APP_REGISTRAR.T_EXTERNAL_VIRTUAL_ORIENT
(
  NETID      VARCHAR2(25 CHAR),
  COMP_DATE  DATE
)
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(35)  date_format  DATE mask "YYYY-MM-DD HH:MI:SS"
 )
              )
     LOCATION (EXTERNAL_DATA:'weblearners.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;


When I try to populate my base table with the data in the external table, I see that nothing ever gets inserted. My Base table structure is as the following:

ID  NUMBER,
USER VARCHAR2(25) BYTE,
COMP_DATE    DATE,
ADMIT_TERM  VARCHAR2 10

I have a procedure that performs the transaction of data from the External table to my base table. This is reference in the following:

PROCEDURE P_INSERT_WEBDATA IS

v_maxdate VARCHAR2(20);
v_pidm NUMBER;
N_COUNT NUMBER := 0;


CURSOR c_WEBREC_CUR IS
--New Solution according to redefined specs.
SELECT NETID, COMP_DATE
FROM APP_REGISTRAR.T_EXTERNAL_VIRTUAL_ORIENT
WHERE COMP_DATE < (SELECT (SYSDATE) FROM DUAL);

BEGIN

FOR NEWWEB_REC IN c_WEBREC_CUR LOOP

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

INSERT INTO APP_REGISTRAR.T_TESTVIRTUAL_ORIENT
(PIDM
,NETID
,COMP_DATE
,ADMIT_TERM)

VALUES
(v_pidm
,NEWWEB_REC.NETID
,NEWWEB_REC.COMP_DATE
,APP_REGISTRAR.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

I am currently running on Oracle 11g and not sure why this happening. I seem to have issues pulling data from an external table that has a Date has one of the columns.
SeaghostAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Check out your format mask in the external table definition and the format in the file.

Change:
COMP_DATE  CHAR(35)  date_format  DATE mask "YYYY-MM-DD HH:MI:SS"

To:
COMP_DATE  CHAR(35)  date_format  DATE mask "MM/DD/YYYY HH:MI:SS AM"


Now to the cursor and loop.


Any reason you just don't do something like this (untested but you get the idea):

INSERT INTO APP_REGISTRAR.T_TESTVIRTUAL_ORIENT
(PIDM
,NETID
,COMP_DATE
,ADMIT_TERM)
(
SELECT
APP_REGISTRAR.PKG_VIRTUAL_UPDATE.F_GETPIDM_BY_NETID(NETID),
COMP_DATE
FROM APP_REGISTRAR.T_EXTERNAL_VIRTUAL_ORIENT
WHERE COMP_DATE < (SELECT (SYSDATE) FROM DUAL)
);
0
 
slightwv (䄆 Netminder) Commented:
I really don't think you need to loop but that's another thing.

Can you post a couple of sample records from weblearners.txt?
0
 
SeaghostAuthor Commented:
Here is the attachment as requested.
WebLearners.txt
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
SeaghostAuthor Commented:
Ok, I have made the change in the External table definitions, however; I still don't have the data being translated into my base table. Any reason why this happens? Also to answer your second concern of the Loop; this Procedure will kick off on the top of the hour every hour and I may have up to 100 individuals that I need to go through each time. I felt that a loop should suffice in this situation.
0
 
SeaghostAuthor Commented:
Never mind; I see why it did not work. I was crashing on one of my constraints.
0
 
slightwv (䄆 Netminder) Commented:
>> I felt that a loop should suffice in this situation.

It will but its just not the most efficient.  For 100 records you likely won't see any performance issues but if it ever jumps into the thousands, keep it in mind.
0
 
SeaghostAuthor Commented:
Awesome you have definitely have solved my problem. I wanted to ask that I may have one more issue that may come across. The file that I am bringing data into my External table will always be appended to throughout the day. However; I only want the records that I have not already imported. So what I have done is modify my Cursor to perform this check. I wasn't sure if this would work exactly. Could you test out this scenario since I am dependent on four other departments to generate the test file for me.

Modified Cursor in the Procedure:

CURSOR c_WEBREC_CUR IS
SELECT NETID, COMP_DATE
FROM APP_REGISTRAR.T_EXTERNAL_VIRTUAL_ORIENT
WHERE COMP_DATE > (SELECT MAX(b.COMP_DATE) FROM APP_REGISTRAR.T_TESTVIRTUAL_ORIENT b);
0
 
slightwv (䄆 Netminder) Commented:
>> I wasn't sure if this would work exactly.

I can envision a possibility where the load kicks off at the exact same second that a new row is added to the file.  The new row might be missed.

I'm not sure how to set up a test case to 'prove' the theory but I'm thinking it is a possibility.

Other than that edge/fringe case, I think it should work.
0
 
SeaghostAuthor Commented:
Wonderful. I really appreciate your assistance with this issue.
0
 
SeaghostAuthor Commented:
This Expert has been fantastic and has provided me with additional insight. Thank You Again!!!
0
 
slightwv (䄆 Netminder) Commented:
Glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.