Re: Printing SQL*Forms Queries

In article <1992Jul8.171233.1283_at_hermes.dlogics.com>, dlm_at_hermes.dlogics.com wrote:
>
> gavin_at_ewd.dsto.gov.au (Gavin Clark) writes:
> > I have found the system.last_query variable which should be what I need.
> > I would like to assign a trigger(?) to a single function key that would
> > execute the
> > last query made and spool it to a disk file.
> > Am I on the right track?
>
> Yes, you are on the right track. I used this technique:
>
> 1. in ORATERM (or CRT) assign a key to "function key 0" (any of 0-9 will do).
> 2. write a user exit in Pro*Whatever which:
> a. opens a file
> b. copies the value of SYSTEM.LAST_QUERY to a local variable
> c. performs a "type 4" dynamic query using that variable
> d. fetches each row and writes it to the file in any format you like
> e. closes the file
> NOTE: this can become complicated if you don't know how many select columns
> to bind. there's an alternative, if you don't mind the overhead, and have
> enough memory to do it:
> a. opens a file
> b. copies the value of SYSTEM.LAST-QUERY to a local variable
> c. writes the variable to the file
> d. closes the file
> e. calls the o/s to peform a SQLPLUS / _at_filename
> 3. link the user exit into your very own version of IAP
> 4. in SQLFORMS write a KEY-F0 trigger which:
> a. calls the user exit
> --
> Dave Mausner, Senior Consultant / Datalogics Inc / Chicago IL / 312-266-4450
> dlm_at_dlogics.com Motto: Just show me the code

OK, I haven't actually tried this, but it seems like you ought to be able to do this without writing a user-exit, in pure PL/SQL, if you are like me and like to avoid 3GL. If you can't simply pass the entire query as a parameter in a HOST command, which you probably can't, then you should be able to insert the query into a *table*, then execute a SQL*Plus procedure that will spool it into a file, then execute that file. So, there's a couple of invocations of Plus, there, which are probably slow. And, it might be tricky (impossible?) to get the query into a file in an executable form if it's long, or has long strings in it or something - by simply selecting it from a table. But, I'd give this method a shot if I needed to do this. Received on Mon Jul 20 1992 - 18:43:44 CEST