Solved

Working with External Oracle Tables

Posted on 2013-05-22
11
433 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
[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
  • 5
11 Comments
 
LVL 77

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 77

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 77

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 77

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 77

Expert Comment

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

Featured Post

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!

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

732 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