Calling SQL*Plus from SQL*Forms (v2.3)

Hi,

        Once again my inexperience forces me to turn to the superior knowledge and experience of the net. I only hope that you guys (and gals) will be able to solve this problem as effectively as you've been able to solve my previous ones.

        Problem is this. I've written a piece of SQL*Plus code and stored it in a file (so I can run it with _at_myfile) This works great for me when I'm testing the system, but the users are going to need a nicer interface.

        Ideally I'd like them to be able to select an option from a menu which runs this SQL*Plus file without them having to log into SQL*Plus. At present my best thought (as yet untested) would be to have a #HOST SQLPLUS command in a trigger in a Menu Form. To say the least, this is ugly. Also the user would be forced to login twice. (once into the form, then again into SQL*Plus)

        I don't really want to go into the User Exits arena. I have enough problems writing SQL code as it is, but if that's what's needed then I'll do it.

        The other option I've though of would be to move all the code into a trigger (big trigger!) I think this could be tricky though for this reason. The SQL*Plus file uses SPOOL then SELECT to create a temporary file made up of more SQL commands (GRANTs in fact) and then runs that file.

        I would have used PL/SQL except that it doesn't allow me to use GRANTs. The reason for all this complexity is that I want to maintain a Table with a list of users, tables and privileges and then use the SQL*Plus command file to enforce them.

        Well, there you go, a fairly detailed break down of the problem. Tackle it at what ever level you like. If you think I need to re-engineer my entire database, say so, it may be possible (but be quick.)

        Many thanks,

                        David McCann Received on Tue Jul 27 1993 - 14:07:41 CEST