Solved

Working with External Oracle Tables

Posted on 2013-05-22
11
426 Views
Last Modified: 2013-05-22
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.
0
Comment
Question by:Seaghost
  • 6
  • 5
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39187583
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
 

Author Comment

by:Seaghost
ID: 39187616
Here is the attachment as requested.
WebLearners.txt
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39187692
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
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.

 

Author Comment

by:Seaghost
ID: 39187727
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
 

Author Comment

by:Seaghost
ID: 39187757
Never mind; I see why it did not work. I was crashing on one of my constraints.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39187775
>> 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
 

Author Comment

by:Seaghost
ID: 39187865
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39188338
>> 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
 

Author Comment

by:Seaghost
ID: 39188811
Wonderful. I really appreciate your assistance with this issue.
0
 

Author Closing Comment

by:Seaghost
ID: 39188818
This Expert has been fantastic and has provided me with additional insight. Thank You Again!!!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39188866
Glad to help!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

813 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

17 Experts available now in Live!

Get 1:1 Help Now