gram77
asked on
Identifying dataset belongs to which file in external table.
I have an external table that reads data from 2 files.
Both files have the same structure
The contents of the external table is read by a proc.
How do i identify which dataset belongs to file1 and which dataset belongs to file2 in the external table.
Both files have the same structure
The contents of the external table is read by a proc.
How do i identify which dataset belongs to file1 and which dataset belongs to file2 in the external table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you could put a view on top of the 2 external tables so you only need to read one object.
or, in your procedure, issue an ALTER TABLE on the external table to change the location so that it only points to a single file at any one time, read the first file, change the location to point to the second file, read that one.
my preference would be to go with the 2 table method and a view.
or, in your procedure, issue an ALTER TABLE on the external table to change the location so that it only points to a single file at any one time, read the first file, change the location to point to the second file, read that one.
my preference would be to go with the 2 table method and a view.
ASKER
There is a way, but it is tedious. should i implement this in production?
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3015912000346648463
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3015912000346648463
if you have 11.2 and have access to execute a preprocess instruction, then why not do it?
ASKER
don't have 11.2!
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
A preprocessor clause was accepted in my env!
ACCESS PARAMETERS
16 ( RECORDS DELIMITED BY NEWLINE
17 preprocessor exec_dir:'run_gunzip.sh'
18 FIELDS TERMINATED BY "|" LDRTRIM
However, i dont think such a thing will be allowed in production.
Also, will i need to do this each time data is refreshed in the external table everyday?
!file emp?.dat.gz
!cat run_gunzip.sh
This does not seem to be a solution but a workaround the issue.
ACCESS PARAMETERS
16 ( RECORDS DELIMITED BY NEWLINE
17 preprocessor exec_dir:'run_gunzip.sh'
18 FIELDS TERMINATED BY "|" LDRTRIM
However, i dont think such a thing will be allowed in production.
Also, will i need to do this each time data is refreshed in the external table everyday?
!file emp?.dat.gz
!cat run_gunzip.sh
This does not seem to be a solution but a workaround the issue.
It's an undocumented feature for 11.1 so it might not be supported.
I'm not sure what you mean by doing it each time.
"you" don't have to do anything, the directive will run the script for you as it reads the files.
also I'm not sure why you're doing the run_gunzip script, what does compression have to do with this process?
I'm not sure what you mean by doing it each time.
"you" don't have to do anything, the directive will run the script for you as it reads the files.
also I'm not sure why you're doing the run_gunzip script, what does compression have to do with this process?
ASKER
As external table is like a view that dosen't consume any space, keeping one ET per file is not a bad idea.
As discussed i can create a composite view for each ET that contains the filename.
This design looks ok.
As discussed i can create a composite view for each ET that contains the filename.
This design looks ok.
ASKER
(
col1 VARCHAR2(4000 BYTE),
col2 VARCHAR2(400 BYTE),
col3 VARCHAR2(400 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY mydir
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
BADFILE mydir:'myfile.out.bad'
DISCARDFILE mydir:'myfile.out.dsc'
LOGFILE mydir:'myfile.out.log'
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
(
col1,
col2,
col3
)
)
LOCATION (mydir:'file1.out', mydir:'file2.out')
)
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;
create or replace procedure myproc(v_filename varchar2)
begin
delete from mytable
where file_name = v_filename
and mdate <= sysdate; <--before inserting data, i need to delete the data that was added --if there is a rerun.
insert into mytable
select col1, col2, col3, v_filename from my_exttable;
commit;
end;
begin
myproc('file1');
end;