Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

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.
0
Seaghost
Asked:
Seaghost
  • 6
  • 5
1 Solution
 
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
 
slightwv (䄆 Netminder) 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
Technology Partners: 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!

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now