Solved

Working with External Oracle Tables

Posted on 2013-05-22
11
420 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
 

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

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 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

13 Experts available now in Live!

Get 1:1 Help Now