Running a sql script from a pl/sql block

[tmnath]

I am trying to run a sql script inside an anonymos pl/sql block. I have tried using the HOST command and a lot of others, but none of them have worked.
I was wondering if anyone has worked on anything like this and if so, it would be great if you could help me out on this. I am working on Windows and not Unix and on Oracle 8

[pwoneill]

I think your options might be limited to re-coding the sql script into pl/sql (which would involve utl_file instead of spooling), or using external procedures. The external procedure route would require you to have a compiler, probably C or C++. You can find out how if you search on Metalink. Try notes 68061.1 or 130799.1, there are a few others if you dig for them.

It might also be possible using Java stored procedures to execute the system call but you would have to look into it to find out for sure.

[tmnath]

Thanks O'Neil. But the problem in detail is something like this. I am trying to create something like a menu option and on choosing one option, I have to run the corresponding sql
So, the way I am trying to do is have menu.sql (with all option numbers and all) which passes the option chosen to an anonymous pl/sql block, where I have a IF THEN ELSE condition and have to call the corresponding sql.
I accept a few values inside the sql, so I can't club all sqls into a procedure or a package as '&' doesn't work inside a procedure.
I can do it in C++ or Pro*C, but I don't have a ProC compiler on my machine. If any one has a ProC compiler that runs on Windows, could you please send it across to me at mayur_nath@hotmail.com

[pwoneill]

I see your idea now, and agree something like that should be a lot easier to implement than it really is.

Could you try makin your menu.sql some other type of program, say a batch file (or even Perl which is available on NT), which prompts users for options? Executing the sqlplus system calls through the batch file or perl script or anything else is much easier than pl/sql, I don't know what your requirements are though.

[tmnath]

Well, PERL is not a bad idea, except that I will have to learn it. Basically, what I am trying to do is a kind of an administrative tool and the requirement is as follows.

menu - You can choose
1. to view the stats on a statement run by a user (sql query -> disk reads, buffer gets, optimiser etc) --> statement_stats.sql (Needs the NT user ID, else fetches for all)
2. View tablespace free, occupied sizes, the largest fragment available. --> free_tablespace.sql
3. View the storage used by each object -> tables, procedures, triggers etc... --> table_size.sql (Needs the object Name, else fetches for all)
4. To view the stats on tables, like when was it last analyzed etc... --> table_stats (needs table name or fetches for all)

On choosing the option, I pass the value to an anonymos block as I need to do a SWITCH - CASE, and choose the appropriate sql (shown after -->).
Inside each sql, I may need a few other parameters to be entered, like the NT user id etc... so I can't have them in a package or procedure as I can't have the & there (if I do, I have to set the scan off while compiling and hence can't make use of the &).
Well, as you had suggested PERL or Pro*C would be the best idea. I hope I have made myself clear now.