Re: SQL*Forms question - Key trigger for spawning report.

2843600_at_jeff-lab_at_queensu.ca (Steve Frampton) writes:

> I have written an Oracle Form (Forms 3.0 I think)
> and I would like the user to have the option to generate a report if
> he/she hit a certain key (say, the F6 key for example). Is this
> possible?

The numeric keypad keys 0 to 9 are usually associated with the key triggers KEY-F0 to KEY-F9 (though you could, of course, associate any otherwise unused function keys with the KEY-F0 to KEY-F9 triggers if you wanted). You may have to add the physical device key mappings to your Ora*Term resource file, since the default mappings distributed by Oracle Corp. don't always include the numeric keypad keys 0 to 9 for all devices.

Also ensure that the device initialisation sequence in your Ora*Term resource file switches the numeric keypad into application mode, if you decide to map KEY-F0 to KEY-F9 to the numeric keypad.

I wouldn't recommend trying to use the top-row F6 key if you're using DEC terminals, since that's normally caught by DCL and interpreted as CTRL/Y. You can get around that if you want to, but it's a hassle.

> I also need to know how to pass the appropriate parameters to the report.
> For example, I would like to pass the contents of :MASTER.LOCATION_CODE
> and :MASTER.REPORTING_MONTH (both of which are char fields in the master
> block of my form). How is this information passed to a RPT file?

Suppose you have a command procedure called REPORT.COM which takes the parameters as input. You could then invoke REPORT.COM from within a PL/SQL block in a form by using the HOST packaged procedure like this:

declare
  dcl_command char(255);
begin
  dcl_command := '_at_report ' || :MASTER.LOCATION_CODE || ' ' ||     :MASTER.REPORTING_MONTH;
  host(dcl_command);
end;

And you would have to construct a driver file for your RPT program within the command procedure REPORT.COM, like this:

$ open/write f tmp.tmp
$ write f 'p1'			! Corresponds to :MASTER.LOCATION_CODE
$ write f 'p2'			! Corresponds to :MASTER.REPORTING_MONTH
$ close f
$ define/user sys$input tmp.tmp	! Get RPT to read from TMP.TMP
$ rpt ...

Within the RPT program you can use the ASK statement to read input text into an RPT variable.

As an alternative to passing the parameters into RPT via a driver file and the ASK statement, you could store the parameters in a database table before invoking RPT via HOST, and use an EXECUTE statement in RPT to retrieve the parameters from the database table into RPT program variables.

I suspect that would be a little slower than the first approach, and perhaps a little messier as well (you might need to cope with multiple users accessing the parameter table, and you'd have to delete old information from the table when no longer needed).

Hope this helps.

David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                      69012 Heidelberg, Germany

Internet: Hazledine_at_EMBL-Heidelberg.DE