Unix commands from pl/sql
I want to execute unix commands from a pl/sql script and retrieve the results into a varible for calcultion. Specifically i want to issue the appropriate unix command to get the amount of available disk space on my unix box, then return this value to my program and do calcualtion/comparison to see if there is enough space to increase oracle datafile.
I have done a lot of searching and nothing has turned up as the obvious path I should pursue. So, what i was thinking i would do is write a shell script to do sql query to get relevant rows and do all the calculation on that side through the script. This seems to be the only alternative opposed to writting C external procedure or dbms_pipes which seems less than straigt forward.
If i had my choice, i would want to do this from pl/sql. That way, i can execute the pl/sql from my windows application. Please let feel free to offer any suggesstions. Thanks in advance!
[946 byte] By [
buckey] at [2007-11-20 8:20:34]
# 1 Re: Unix commands from pl/sql
Well have a look at the man pages on popen(). If you notice that it just returns a file pointer. Then you can use fread() to get the data. It invokes the shell, but since you were talking about using a script already, it probably wouldnt hurt.
# 2 Re: Unix commands from pl/sql
I'm not sure i follow...... that is a unix function executed from pl/sql ? the only thing i need from unix is the size of available space. The rest of it I can do in pl/sql. What i want to do is not use a shell script at all. Please elaborate on your suggestion. Mean while, i will look at man pages for your suggestion. Thanks in advance.
buckey at 2007-11-9 13:46:18 >
# 3 Re: Unix commands from pl/sql
Well popen is a C function that allows a program to invoke another program and either pass data to it or receive data from it. It is the simplest way (In C) to pass data between two programs. After you use popen to get a file pointer, you can use fread to get at the results from your request to get hard drive space.
So, what i was thinking i would do is write a shell script to do sql query to get relevant rows and do all the calculation on that side through the script.
# 4 Re: Unix commands from pl/sql
You could also probably ftp or telnet to your UNIX box to get that data. You could do it with Windows Scripting or API calls.
# 5 Re: Unix commands from pl/sql
So, are you suggesting i write an external procedure in c to let me access my OS and return the results to my pl/sql program? I guess what i am trying to get at is, where does the c come in? Can the c function be called from pl/sql or am i calling the c program from pl/sql.
I am trying to understand this, please bear with me for a quick second. Thanks again.
buckey at 2007-11-9 13:49:23 >
# 6 Re: Unix commands from pl/sql
Well in sitting back and looking at your entire post, I at first thought to solve your problem on the UNIX box, however as we corresponded it became evident that your UNIX box is infact to be remotely administrated by your Windows Box that has PL SQL installed correct?
Well this being the case, typically I interface to SQL and such with a Win32 or an MFC application. I myself would do it all with MFC or Win32, the request to the UNIX box for the space, the change to the datafile and everything. I am not sure if PL/SQL will call C Functions...but you could do it that way too in order to:
#1 Win32/MFC Code to telnet (via API calls)to the UNIX box and parse the text to get the resultant free space.
#2 Code to telnet to the UNIX box and call the PL/SQL commands to increase the datafile size.
#3 Possibly provide a user interface via Win32 or MFC or C# or whatever you prefer.
# 7 Re: Unix commands from pl/sql
Oh, i see where you are comming from now...
Yea, this is what i have . I have an c++ winforms app(i am using vc++ express 2005) that i want to add this feature to in the near future. So, for the time being i though i would figure out the unix stuff and at least get the algoritm together to do the work(one way or the other) weather it be a shell script, pl/sql or whatever. Once i got that stuff figured out, i want to segment this into my utility app.
So, my approach has been to figure out how to automate space increases so new staff and other oncall people can handel these issues more easily then add it to my app as an enhancment.
so, in light of that my app is ran on windows machine that accescces the db using oledb. The sql that is contained in the query is to query an oracle database running on a tru64 unix machine.
Sorry, but i didn't realize how much info i left out from my initial posts until i just finished writing this. Anyway, hope this clarifies my situation a little more.
Does this change your thoughts in reguards to this latest post?
Thnaks again!
buckey at 2007-11-9 13:51:16 >
# 8 Re: Unix commands from pl/sql
You can use DBMS_PIPE package available in PL/SQL.
ndarji at 2007-11-9 13:52:19 >
