1 person found this helpful
why not create a stored procedure from your sql file and execute this instead?
Because for not all files that I have is possible to create a stored procedure...
1 person found this helpful
So your users place a file on a different linux server, and you want to execute it?
If your application server is also running on linux it would be easy to create a share (nfs or smb) on your source server, and permanently mount it on your application server and thus making it accessible on your application server. Then you'd simply have to use +host+ to run SQL*Plus against your database with the given script. Or you could use +text_io+ to read it and execute it with +exec_sql+ which would also allow you to do some basic checks (like there is no +drop+ keyword in your script etc.) and also would allow you to verify if the script was run successful. Of course if you have SQL*Plus script elements (format, spool, ...) in your scripts running them via +exec_sql+ isn't an option.
However my this-is-a-huge-security-hole alarm light is flashing at a very high frequency....
What operations do your users execute with this kind of interface? Maybe it is an option to provide them a standard set of scripts which is commonly used?
Typically they load data into DB or they execute a sql script that makes some reports...
Allright. For loading data into the database (which I guess they have in some sort of CSV format etc.) why not provide them a somehow generic external table you could use to load their data? You'll have to be careful with overwriting your files, but this is nothing different then with storing SQL files somewhere (this could also be done using SQL*Loader, but you'd have to install clients).
Or another idea would be to create a webservice like interface to do so; we have used mod_plsql (or it's open source version mod_owa: https://oss.oracle.com/projects/mod_owa/dist/documentation/modowa.htm)
to provide a generic service interface to our database; the customer would simply make a post request to send their data to the database.
For ad-hoc reporting there are several tools out there; some are from oracle (discoverer, OBIEE) for which you'll have to pay through the nose for using them. But there are other tools out there; at the moment we are verifying https://www.jaspersoft.com/ for our ad-hoc reporting. Very nice (open source) tool which is also very intuitive.
Both would require more work (external tables, service) and some money and work (jaspersoft, or any other ad-hoc reporting tool), but at least for generating reports I definitely wouldn't go the SQL*Plus report way, as it's capabilities are very limited. How to load data into the database - well, external tables or webservices are just one way, there might be some other and better ways.
The problem is that not all programs we have can be trasformed into PL/SQL procedure....
So what I want is to execute one of this program, that is on the server, using webutil or not (at the moment this is not fundamental)...
As said, the host built-in is what you are looking for; it could be used to invoke SQL*Plus with a given script on your application server. There would be several ways to transfer your file to the server; maybe by webutil (simply open webutil.pll; the procedure you are looking for should be named client_to_as or something), or maybe provide your users a multiline text-field where they can enter a SQL script which would be written on the AS using text_io, or maybe by standard windows samba shares (just let them place their scripts in a given directory on your AS).
This however still wouldn't solve your problem with reporting SQL scripts if your users do things like:
spool some_output.txt select some_column from some_table / spool off
so you'd need to interpret the file and transfer the spooled file back to your client. Very cumbersome indeed. So I'd say for ad-hoc reporting this isn't a very good solution. At least you'd need to break up the processes in different types like
- load data
- execute SQL script (with no return)
- execute SQL script (reporting thingie)
which in essence would take a SQL script and do different actions with it (like transfer a output file back to the client etc.).
However I still think that a step back is in order and look at the base requirements (that is: loading data, ad-hoc reporting and doing some other work say via a PL/SQL procedure as far as I understood), and see how they are best solved. Giving users the possibility to execute SQL Scripts is a very general solution for a set of specific problems IMO.
Granted, you'd have the greatest flexibility with a SQL*Plus and SQL scripts, but I'd think you can expect from people to whom the common tools are not sophisticated enough that they install a SQL*Plus themselves and run their SQL scripts as they please if they have access to the database and know what they are doing