| Procedure out of statements [message #659837] |
Wed, 01 February 2017 00:31  |
 |
m_r_a
Messages: 7
Registered: February 2017
|
Junior Member |
|
|
Hello;
I have some statements that I need to schedule in a job and I want to write them into a procedure.
The statements:
Drop Table u_system.emp_load;
CREATE TABLE u_system.emp_load
(employee_number CHAR(5),
employee_atten_time TIMESTAMP,
machine_number CHAR(3),
in_out CHAR(2)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY user_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS (employee_number CHAR(4),
employee_atten_time CHAR(20) date_format TIMESTAMP mask "dd/mm/yyyy hh24:mi:ss",
machine_number CHAR(4),
in_out CHAR(1)
)
)
LOCATION ('emp2.dat')
);
insert into u_system.emp_attend(employee_number,employee_atten_time,machine_number,in_out)
select * from u_system.emp_load;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Procedure out of statements [message #659856 is a reply to message #659854] |
Wed, 01 February 2017 03:29  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
And just to be absolutely clear: Since the external table is just a view on a file, if the file structure and name don't change each time it's replaced then there is absolutely no point in modifying the external table in any way, never mind dropping it and recreating it.
As Michel said, the process that replaces the file should kick off the process to insert the data from the external table into the normal table. Then you don't have to worry if something went wrong in the file replacement process leaving the old file in place.
Or just use the file watcher as John said. Should have read that post properly before answering.
[Updated on: Wed, 01 February 2017 03:30] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
