Home » SQL & PL/SQL » SQL & PL/SQL » Procedure out of statements
Procedure out of statements [message #659837] Wed, 01 February 2017 00:31 Go to next message
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 #659839 is a reply to message #659837] Wed, 01 February 2017 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

1/ Why do you want to drop and recreate each time the same external table?
2/ Where do you think the result of "SELECT *" from a job will go?

Explain the business need instead of the solution you envisage and is obviously inadequate.

Re: Procedure out of statements [message #659841 is a reply to message #659839] Wed, 01 February 2017 00:49 Go to previous messageGo to next message
m_r_a
Messages: 7
Registered: February 2017
Junior Member
Thank you michel,

I have to get text file data inserted to a table in the database repeatedly each 15 minutes
Re: Procedure out of statements [message #659842 is a reply to message #659841] Wed, 01 February 2017 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You insert the same file in a table every 15 minutes?
What is your Oracle version (4 decimals like 11.2.0.4)?

Re: Procedure out of statements [message #659843 is a reply to message #659842] Wed, 01 February 2017 00:54 Go to previous messageGo to next message
Littlefoot
Messages: 21328
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that you have to schedule the INSERT statement, not the whole process.
Re: Procedure out of statements [message #659844 is a reply to message #659843] Wed, 01 February 2017 00:57 Go to previous messageGo to next message
m_r_a
Messages: 7
Registered: February 2017
Junior Member
No it won't be the same file, the file will be overwritten within this period.
the version is 12.1.0.2
Re: Procedure out of statements [message #659845 is a reply to message #659844] Wed, 01 February 2017 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But keep the same name?
How do you know it is a new file and not the old one?
If it is the old one you want to load it anyway?


Re: Procedure out of statements [message #659846 is a reply to message #659845] Wed, 01 February 2017 01:02 Go to previous messageGo to next message
m_r_a
Messages: 7
Registered: February 2017
Junior Member
No I want to load new one each time,
Re: Procedure out of statements [message #659847 is a reply to message #659846] Wed, 01 February 2017 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I see, I should post only one question in each post as it seems you are unable to answer all of them.
See, there are 3 question marks so this mean there 3 questions and so your answer should show 3 points.

Re: Procedure out of statements [message #659848 is a reply to message #659847] Wed, 01 February 2017 01:08 Go to previous messageGo to next message
m_r_a
Messages: 7
Registered: February 2017
Junior Member
* Yes I want to keep it the same name but it is overwritten each 15 minutes.
*I know it is the new file since I know that there is a script that overwrites it each time.
*If it is the old one I don't need to insert it.

Re: Procedure out of statements [message #659849 is a reply to message #659848] Wed, 01 February 2017 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Points 2 and 3 are inconsistent.
If 2 is true then 3 should be "irrelevant as it can't happen".
If 3 is correct then you have to answer my previous question "How do you know it is a new file and not the old one?".

Anyway this your problem and Litllefoot already told you what to do: schedule the INSERT statement every 15 minutes.
But a better solution would be: "(launch a script to) execute the INSERT statement by the script that overwrites the file".

Re: Procedure out of statements [message #659850 is a reply to message #659849] Wed, 01 February 2017 01:15 Go to previous messageGo to next message
m_r_a
Messages: 7
Registered: February 2017
Junior Member
How could I write an insert statement to an external table ?
Re: Procedure out of statements [message #659851 is a reply to message #659850] Wed, 01 February 2017 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does not it what you already write?

Quote:
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 #659852 is a reply to message #659851] Wed, 01 February 2017 01:29 Go to previous messageGo to next message
m_r_a
Messages: 7
Registered: February 2017
Junior Member
No It's not, this statement copies data from external table to a normal table.
I followed the process in this page
http://docs.oracle.com/cd/B13789_01/server.101/b10825/et_concepts.htm
Re: Procedure out of statements [message #659853 is a reply to message #659852] Wed, 01 February 2017 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read again the page, you do NOT insert into an external table, an external table is a view into a file.

And as you are in 12c why don't read 12c documentation instead of 10.1 very old one?

Re: Procedure out of statements [message #659854 is a reply to message #659837] Wed, 01 February 2017 02:03 Go to previous messageGo to next message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
When you query u_system.emp_load, you will see whatever rows happen to be in the emp2.dat file at the time. You could check whether a new version of the file has been delivered, rather than hoping it is every fifteen minutes. I would use the Scheduler to create a File Watcher
http://docs.oracle.com/database/121/ADMIN/scheduse.htm#GUID-B17B0307-45B6-4251-B554-923785745E78
that will launch your job to copy the rows whenever the file is delivered.
Re: Procedure out of statements [message #659856 is a reply to message #659854] Wed, 01 February 2017 03:29 Go to previous message
cookiemonster
Messages: 12989
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

Previous Topic: ORA-01722 for bind variable in dbms_sql
Next Topic: Help with update statement
Goto Forum:
  


Current Time: Wed Jan 17 06:02:10 CST 2018

Total time taken to generate the page: 0.06152 seconds