| Importing data [message #110752] |
Thu, 10 March 2005 00:53  |
kinjal
Messages: 124
Registered: June 2001
|
Senior Member |
|
|
I have created one procedure called host_command('command_name')
to execute the linux command from oracle.
by executing this procedure, lets say for df command, when I execute host_command('df'), its executing df command on linux and storing the output of this command in output.txt.
Now I want to use this output file and want to convert it to a table automatically..
Is it possible?
If it is possible then please tell me how?
|
|
|
|
| Re: Importing data [message #111009 is a reply to message #110752] |
Fri, 11 March 2005 18:51  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
you need to define the table, so saying you want to to become a table magically is not possible. You can search for "external tables" on this site, or use sqlldr (using your host_command ) to load it into your table. It may be easier to just fetch from the dbms_buffer if lines < 255 characters long and max total size 1M.
This is untested code - but will generally do the job.
DECLARE
hdl UTL_FILE.file_type;
line_buf VARCHAR2 (200);
BEGIN
-- read the output
hdl := UTL_FILE.fopen ('/tmp', 'my_file.txt', 'R', 32767);
LOOP
BEGIN
UTL_FILE.get_line (hdl, line_buf);
INSERT INTO my_tab (col1) VALUES (line_buf);
EXCEPTION
WHEN NO_DATA_FOUND
THEN EXIT;
END;
END LOOP;
UTL_FILE.FCLOSE (hdl);
exception
when others then
UTL_FILE.FCLOSE (hdl);
raise_application_error(-20501, 'Whoops', true);
END;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
