Link to home
Create AccountLog in
Avatar of zuberpalekar
zuberpalekar

asked on

Execute external command line tools in Oracle

Is it possible to execute an external command line tool (E.g. tshark.exe) through Oracle?
I am ready to use any tool from the oracle suite.
tshark sniffs the packets from the network, and I want to use Oracle TimesTen database to store that information in the database after limited processing of the packets. tshark can output the packets details in a file, or on the standard output. Can we redirect the standard output to the oracle application which can then process the packets and store in the database?
What are the possible performance issues that I may have to encounter?
Avatar of Sean Stuber
Sean Stuber

You can't direct your standard output oracle, but if you can write to a file, you can define an external table in oracle to read the contents of that file and then ship to timesten.
SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
If the output of the invoked program is stored in OS file you have to:
1. Create a logical Oracle directory
2. Define an External table and SELECTING the file as External table to create Oracle regulat table
CREATE TABLE regular_table AS (SELECT * FROM external_table);


To use External tables:

create directory med as 'c:\med' ; -- on main
GRANT READ  ON DIRECTORY med TO lsmedical;
GRANT WRITE ON DIRECTORY med TO lsmedical;



drop table lsmedical.USERMEDBASIC_EXT;
create table lsmedical.USERMEDBASIC_EXT (
                   PZN  VARCHAR2(33),
                   NAME VARCHAR2(111)   )
    organization external  
    (type oracle_loader  
     default directory med
     access parameters (records delimited by newline
     fields terminated by '|')
     location ('med-de-formatted.txt','med-de-insulin-formatted.txt',
               'med-us-formatted.txt','med-us-insulin-formatted.txt'));

Avatar of zuberpalekar

ASKER

Thanks for the help above. But only half the story is now complete.

The output of the 'tshark' (The command line tool) will keep on updating the file with the messages. Does an external table update itself when the file is updated. I really doubt it?
How can I read the  contents of that file real time?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
The only thing missing from sdstuber excellent example above is a "/" at the very end after "AY';".  Otherwise this worked perfectly.  I was able to use this to do things like call 7za to create an archive and add files to it.  Using native Windows OS commands took some tinkering, but figured that out.

Using the example above, and assuming you have the command-line version of 7zip installed, this should work (if you have a C:\temp and some .sql files in there):

select * from table(run_cmd('c:\progra~1\7za\7za.exe a c:\temp\oracle.zip *.sql'));