We help IT Professionals succeed at work.

Call SQR from Oracle Forms?

dermanj
dermanj asked
on
I've been searching high and low for specific information on how to use the SQR API to call an SQR report from within an Oracle Form (version 6i).

I believe the process involves taking the SQRW.DLL file that is delivered with the SQR for Windows product and referencing the functions of that DLL from within a form (e.g., the sqr() function runs the SQR report).  Perhaps it also involves creating a PLL (to go with the DLL) and attaching it to the form.

Can anyone please help me with the specifics of this process?  I need to know exactly what I have to do in order to call an SQR report from within an Oracle form, using SQR API function calls.  I've been unable to find it documented anywhere.

Perhaps my old friend Darryl knows how this is done?  ;-)
Comment
Watch Question

jtriftsMI and Automation
CERTIFIED EXPERT

Commented:
If SQR is an Oracle supported product, the you might be able to use the RUN_PRODUCT command.  However if it is not (as I suspect) you can run any external product using the HOST command.

Here are some notes on HOST command from Oracle Help:

Description

Executes an indicated operating system command.

Syntax

PROCEDURE HOST
  (system_command_string  VARCHAR2);  
PROCEDURE HOST
  (system_command_string  VARCHAR2,
   screen_action          NUMBER);

Built-in Type  unrestricted procedure
Enter Query Mode  yes

Parameters

system_command_ string     Specifies the system command you want to pass to your particular operating system.

screen_action     Specifies one of the following constants:

no parameter  Specifies that Form Builder will:

n     clear the screen

n     prompt the operator to return from the command

NO_PROMPT  Specifies that Form Builder will:

n     clear the screen (does not prompt the operator to return from the command)

NO_SCREEN  Specifies that Form Builder will:

n     not clear the screen

n     not prompt the operator to return from the system command

(The HOST command should not send output to the screen when using the NO_SCREEN parameter.)

Usage notes

?     Thescreen_action parameter is only relevant to applications running in character mode, where the output of the Host command is displayed in the same window as the form.  In GUI applications, the output of the Host command is displayed in a separate window.

?     On Microsoft Windows NT, when using HOST to execute a 16-bit application, the FORM_SUCCESS built-in will return TRUE whether the application succeeds or fails.  This is a Microsoft a Win32 issue.  32-bit applications and OS commands will correctly return TRUE if executed sucessfully and FALSE if failed.  Invalid commands will return FALSE.

?     On Windows 95 platforms the FORM_SUCCESS built-in will always return TRUE for HOST commands which fail.  This includes calls to command.com or OS functions, any 16-bit DOS or GUI application, or an invalid command.  32-bit applications will correctly return TRUE if executed sucessfully and FALSE if failed.

Regards,

JT
CERTIFIED EXPERT
Top Expert 2008

Commented:
How to call Oracle SQR Reports in Unix from Forms 6.0 in Win 98?
 
You can use the DBMS_PIPE package to do this. The following note explains it :

Problem Description:
====================
 
How can I execute commands on the server from an Oracle Forms application
running on a client machine?
 
 
Problem Explanation:
====================
 
For example:
 
o You want to execute a UNIX shell script from an Oracle Forms application
  running on a Microsoft Windows client.
 
o You want to initiate server commands from the client machine without logging
  onto the server.
 
 
[ Search Words: executing, dbms_pipe package, dbms_pipes, operating system,
                dbmspipe.sql, messages, message, starting, initiating, VMS,
                remote host, run, access, start programs, running, sql,
                client-server, scripts, rsh, telnet, pctcp , tcp/ip, command,
                based, dynapipe ]

Solution: CALL RSH (REMOTE SHELL ) USING A FORMS HOST COMMAND


Solution Description:
=====================
 
Most TCP/IP network packages contain a remote shell command, commonly called
'rsh'.  The rsh command will execute UNIX commands on a server from your PC.
The 'rsh' executable would be installed on the client PC.
 
You could call the Oracle Forms HOST() built-in to execute the rsh command
from the Oracle Forms application, which will then execute a UNIX command on
the server.
 
For example: When-Button-Pressed Trigger
 
  Begin
 
    HOST( 'c:\pctcp\rsh orlsun7 test.sh' );
                         ^^^^^^^ ^^^^^^^
                           |        |
                           |        Unix Command, or script name
                           Unix Host Name or IP Address
  End;
 
Common Restrictions:
--------------------
The following are common rsh restrictions.  Consult your client network
package documentation for restrictions specific to your product.
 
o The rsh command supports execution of a single command on a remote host
 
  If you need to execute multiple commands, put them in a script and execute
  the script.
 
o rsh does not support interactive commands
 
  You must provide all necessary parameters to the command for it to
  execute successfully without operator intervention.
   
 
Solution Explanation:
=====================
 
When you use the HOST command in Forms, you are hosting out to the operating  
system on the client machine, not the server.  A remote shell utility
installed on the client machine will allow you to execute a UNIX command on
the server operating system from an Oracle Forms application running on a
client machine.
 
 
Additional Information:
=======================
 
See your third-party TCP/IP network documentation for information regarding
the remote shell command ( rsh ).
 
Oracle Documentation:
---------------------
Oracle Forms 4.X Reference Manual,
Chapter 3, Built-in Subprograms
HOST

Solution: USE DBMS_PIPES TO SEND MESSAGE FROM FORMS CLIENT TO EXECUTE COMMAND ON SERVER


Solution Description:      
=====================
 
Use the Oracle 7 server DBMS_PIPE package to call a a stored database function
to initiate server jobs from Oracle Forms running on the client that supports
SQL*Net.
 
** IMPORTANT:  The following solution REQUIRES the following Oracle Support
               Bulletin:
 
   105688.158 "Dynamic SQL and System Commands Using DBMS_PIPE"  
 
 
1. SERVER: Create the DBMS_PIPE package:
   -------------------------------------
 
   o Login to SQL*Plus or SQL*DBA as SYS ( NOT SYSTEM )  
   o Run the DBMSPIPE.SQL script to create the package  
   o GRANT EXECUTE ON DBMS_PIPE TO PUBLIC
 
     Grant execute privileges to all users who need to make calls to
     DBMS_PIPE.  This example grants execute privileges to PUBLIC, which
     is all users.
 
   The DBMSPIPE.SQL Oracle7 Server procedure script creates the DBMS_PIPE
   database package.  This script can by run on its own, and is automatically
   run by the CATPROC.SQL script.  It requires that PISTUB.SQL has been run to
   create the PL/SQL packages for the procedural option.  
 
   Consult with your Database Administrator (DBA) for assistance creating the
   DBMS_PIPE package.
 
 
2. SERVER: Create the DBMS_OUTPUT package:  
   ---------------------------------------  
   
   o Login to SQL*Plus or SQL*DBA as SYS ( NOT SYSTEM )    
   o Run the DBMSOTPT.SQL script to create the package  
   o GRANT EXECUTE ON DBMS_OUTPUT TO PUBLIC  
 
     Grant execute privileges to all users who need to make calls to  
     DBMS_OUTPUT.  This example grants execute privileges to PUBLIC, which  
     is all users.  
 
   The DBMSOTPT.SQL Oracle7 Server procedure script creates the DBMS_OUTPUT  
   database package.  This script can by run on its own, and is automatically  
   run by the CATPROC.SQL script.  It requires that PISTUB.SQL has been run to  
   create the PL/SQL packages for the procedural option.    
 
   Consult with your Database Administrator (DBA) for assistance creating the  
   DBMS_OUTPUT package.  DBMS_OUTPUT will be used by the listener process
   you will create in Step 3 below.
 
 
3. SERVER: Create a daemon listener on the server to receive messages:
   -------------------------------------------------------------------
 
   Oracle Support Bulletin 105688.158 "Dynamic SQL and System Commands Using
   DBMS_PIPE" contains the daemon.pc code that you need to compile on
   the Server to create the "daemon" listener.
   
   You must have both the Pro*C precompiler and a C compiler installed to
   compile the daemon.pc code into an executable.
 
 
4. SERVER: Create a stored database function to send messages to listener:
   -----------------------------------------------------------------------
   
   Create a stored database function that will send messages to the daemon
   listener ( that you created using daemon.pc ) on the server from the
   clients.  
 
   Oracle Support Bulletin 105688.158 "Dynamic SQL and System Commands Using  
   DBMS_PIPE" contains the code to create a stored database package called  
   "daemon" which includes the stored database function "execute_system" to do
   this.
 
 
5. CLIENT:  Call stored database function from Forms:
   --------------------------------------------------
     
   The Oracle Forms application running on the client machine will call the
   "execute_system" stored database function in the daemon package from a  
   trigger or program unit, specifying what system command it wants to run on  
   the server.
 
   Your server must be correctly configured to be able to call stored database
   procedures/functions from Oracle Forms.  See Oracle Support Bulletin  
   106573.779 "(V4) Calling Stored Database Procedures from Oracle Forms" for
   further information.
 
   For example:  Oracle Forms When-Button-Pressed-Trigger
 
       DECLARE
 
          server_command_status NUMBER;
 
       BEGIN
 
          /* Request execution of test.sh script on UNIX server,         */
          /* specifying a wait time of 30 seconds ( default is 10 sec. ) */
 
          server_command_status := daemon.execute_system('/dir/test.sh', 30 );
 
          /* Check if 'test.sh' script executed successfully.  This code */
          /* assumes test.sh returns a negative value on failure.        */
 
          IF server_command_status < 0 THEN
             RAISE FORM_TRIGGER_FAILURE;
          END IF;
 
       END;
 
   In the above example, the call to the daemon.execute_system() stored
   database function is a request from the client Oracle Forms application to
   run the 'test.sh' UNIX script on the server.  
 
   The daemon.execute_system() stored function will then send this message to
   the "daemon" listener, and the "daemon" listener will execute the 'test.sh'
   UNIX script on the server.
 
   The "daemon" listener will return the status of the test.sh script  
   execution to the "daemon.execute_system" function, which will pass this  
   outcome of the system command execution back to the form.
 
   The Oracle Forms application can then do whatever processing it wants based
   on the result of the system command.
 
   Forms Application --> daemon.execute_system() --> daemon listener -->
                                                                       |
         ^                          ^                                  |
         |<-------------------------|<---------------------------------V
 
 
Solution Explanation:
=====================
 
A pipe is essentially a queue of messages.  The client first packages a
message and then puts the message into the queue on the server.  The server
then reads the queue and unpacks the message for use.  Since you can call a
PL/SQL package over SQL*Net, any client that supports SQL*Net can use Oracle
Pipes to make calls to the server.  
 
When you use the HOST command in Forms, you are hosting out to the operating  
system on the client machine, not the server.  Oracle7 databases have a
feature called DBMS Pipes, allowing you to send messages between sessions
connected to the same database.  In this example one of the sessions is the
PL/SQL block within Oracle Forms on the client, and the other is the "daemon"
listener 'C' program on the server.
 
 
Additional Information:
=======================
 
Oracle Support Bulletins:
-------------------------
105688.158  
Dynamic SQL and System Commands Using DBMS_PIPE
 
106573.779  
(V4) Calling Stored Database Procedures from Oracle Forms  
 
Oracle Documentation:
---------------------
Oracle7 Server Administrator's Guide
Chapter 2,  Creating a Database
            Creating the Data Dictionary
Appendix B, Data Dictionary Reference
            Data Dictionary SQL Script Files
 
Related GSX Entries:
--------------------
1008366.6
COMMON QUESTIONS ABOUT DBMS_PIPE PACKAGE
CERTIFIED EXPERT
Top Expert 2008

Commented:
SQR API with Oracle Forms

I'm trying to find any information I can on how to use the SQR/Windows API to execute SQR reports from withing Oracle Forms for Windows.

I'd like to know what files (DLLs, etc.) need to be placed where, what function calls need to be made from with the Oracle Form, and any other information that might be applicable. For some strange reason, I can't seem to find any good information on this subject -- not even from the company that sells SQR (i.e., Brio).

Any assistance would be greatly appreciated.

Answer from Oracle:

We do not have any information on calling SQR Reports from within Forms.
We will keep this thread open for sometime, so that, others can share their experiences.
CERTIFIED EXPERT
Top Expert 2008

Commented:
Finally and in short: if SQR has a executable (.exe, .com, etc., possibly with command line parameters) try using HOST() command in Forms to call it.

Author

Commented:
Thank you for the comments so far (jtrifts & schwertner), but I'm afraid those comments don't help me.

Perhaps this might clarify a little, from a more generic point of view:  I have a Windows DLL file (provided with the SQR product), and I believe it is possible to somehow incorporate that DLL into a Windows Oracle Form and call the functions of that DLL from within the form -- but I need to know specifically how to do that (assuming it's possible).

BTW, the comment posted above, titled "SQR API with Oracle Forms" is a cut-and-paste of the question that *I* posted a few days ago on Metalink.
Commented:
Form Builder has a supplied built-in package called ORA_FFI that allows you to interface Forms with Foreign Functions.  I have never used it myself; however, I think you should research this particular package because I think it will give you what you need.  If you look up ORA_FFI on Oracle Forms Help it shows several code examples on how to set up ORA_FFI to access dlls.  
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
I've used earlier versions of SQR (a third-party report writer for Oracle that executes much faster than Oracle Reports) with Oracle Forms4.5 for Windows.  If the SQR report and the SQR executables exist on the client, then you can use a "host" call from a Forms PL\SQL trigger.  The easiest way I have found to do that is to write a batch file that launches SQR and runs the report, then just call that batch file with the "host" command.

If SQR is only installed on the server, then you will need to use the more complex DBMS_PIPE or ORA_FFI/DLL approach.
I don't think it's possible...
CERTIFIED EXPERT
Top Expert 2008

Commented:
Mr. Dermany:
you see that my suggestions are supported by other experts. I gave you the way to work around your problem and it costs me time and eforts.
Try to use HOST command in Forms as the easiest solution. Your second choce will be DBMS_PIPE.

There is no change to find a call to Forms in the DDL supplied with SQR. The reason is obvious - now SQR is part of Peplesoft package and it will be very unusual for every supplier to open doors to competative software (except migrating to him self).

Best Regards

Joseph Schwertner

Author

Commented:
I think I've got this problem figured out -- and now that I've got it working, I'm really surprised that no one seems to have it documented anywhere.  I would think that it's a reasonably common thing that people would want to do (i.e., run an SQR report directly from within an Oracle Form, using the Windows API).

The people at Oracle Metalink had no suggestions whatsoever.  The people at Brio/SQR tech support also had no suggestions.

The comment I received from ser6398 (above) was consistent with what I actually wanted to do (i.e., using functions from a DLL within Oracle Forms).  As suggested by ser6398, I researched the ORA_FFI function, and after much trial-and-error, I finally managed to get some actual working results.

I am not using the HOST command, nor am I using DBMS_PIPE.  I am actually calling the sqr() function found in the SQRW.DLL file -- calling it from within an Oracle Form.

I think this is the first time that I've ever posted a question on EE, so I'm not sure what to do about points in this situation.  I don't feel that my question was answered, but I do feel that the comment made by ser6398 was helpful.  I also do appreciate the attempts of others to help me.

Any advice as to what would appropriate for me to do with regard to rewarding points in this case?
CERTIFIED EXPERT
Top Expert 2008

Commented:
The aim of this site is to help colleagues (when possible). Points are not so important.

Commented:
If your question wasn't completely answered you should give the points to the person who you felt helped you the most toward your answer.  You also get to grade the answer.  If you are still unsure, you can read the EE guidelines on grading answers (follow the link on the bottom of this page).

Author

Commented:
While this comment didn't really answer my question, it did point me in the right direction and helped me to eventually figure out the solution.

Thanks to ser6398 and to everyone else who offered suggestions.