CALL UNIX SHELL SCRIPT FILE FROM ORACLE FORM

you can use host  function of oracle from and also you can use parameter to control behaviour of new window. I mean if you want to hide new window, you can easily do by passing integer value as 2nd parameter of host function.

Which version of Oracle Forms do you have?   Where are the forms running (on a UNIX-based application server, or somewhere else)?  The "host" command can only interact with the host O/S on the machine where the Forms executable is running.  In the client/server days, this was usually on a Windows desktop.  These days, it is usually on an application server, unless you still have Forms6i or earlier running in client/server mode.

virdi dis,  I want to use the host function of oracle.  I have tried the below but for some reason, it acts like it can't find my path.  


host(emis/bin/products.sh XX)

You have to  to enclose the command in single quotes:

host('emis/bin/products.sh XX')

try to pass additional argument of host function like
host ('execution_filename Arg1 Arg2', 3);
This will hide dos-prompt window.

Oracle Forms does support the "host" command, but these commands then execute on the machine where Oracle Forms is running.  This is usually *NOT* on the database server.  Where (which machine)do your Oracle Forms run on?

Also, the host command will execute from the context (path) that Oracle Forms executes from, so this command: "emis/bin/products.sh" may not be found, since this is not a complete path in a UNIX or Linux system.

The Oracle Form resides on a windows desktop.  The form is version 4.5.  How can I get the host command to  find the unix shell script file..  Please give me an example of what a complete path should look like.

OK, that helps.  Oracle Forms for Windows, version 4.5 can *ONLY* execute host commands on the Windows desktop where it is running, so the only "host"commands it can execute are those that are valid DOS/Windows commands.  Oracle Forms for Windows CANNOT execute commands on a UNIX (or Linux) database server, at least not unless you have a utility installed that supports UNIX/Linux commands from DOS.

Later versions of Oracle Forms that run in three-tier (web-enabled) mode can execute "host" commands on the application server they run on, but this is usually not the database server either.

Oracle Forms4.5 is (or at least was) available for Linux, where it could run as a "character mode" client/server application.  Valid Linux "host" commands can include "ssh" or "telnet" to get to another Linux or UNIX machine, so for Oracle Forms4.5 on Linux, it may be possible to execute a "host" command that actually runs on a UNIX database server.

What is the business problem you hoped to solve via a "host" command?  There may be another option, if you can describe the problem to us.

If this is possible also depends on the database version you're using.

If you are running Oracle 10 or higher, you can use dbms_scheduler to run a job on the db machine.

Actually, I just happened to recognise you use Forms 4.5,
so it is not very likely you that a late version of the database.

Okay, you want to execute Unix script from window platform using oracle form. It is not possible directly, but we can do something to achive this target. Before proceed further, you must have the knowledge of ftp as well as cron schedular. My plan is something like this:
You must have a copy of unix script file MyProg.sh in your window platform.
In end of your unix script file MyProg.sh, add code echo > /pathOfFile/ MyProg.sh to remove the content so that file should not execute again.
Transfer a copy of Unix shell script file (say MyProg.sh) from window machine to Unix using ftp (code should be written in your Form 6i window machine)
Schedule to execute MyProg.sh in crontab daily after 5 hours.

Sorry I dont have Unix right now, otherwise I will try to send the code.

Good Luck

In the below example, the products.sh file has the parameter  XX  and calls the the detail.sql file which needs the series of parameters to execute.  

The host command is in a pl/sql program unit in the oracle form.   The form is version 4.5, the database is oracle 8.  The .sh   and  .sql  files  are shell script that reside on the unix server.  I have windows XP desk top.  The form currently reside in a folder on my c:drive.

In the form,  when the user selects a particular product, the .sh file and .sql file executes and produces a report.  I'm trying to execute the .sh  and  .sql  in the same host command if possible.  So far, I haven't been able to get it to work.  

===========================================================================
HOST( 'nohup 'emis/bin/products.sh  XX'     'emis/bin/detail.sql  PRINTER ' ||
                  t_printer_queue || ' ' || product_ID || ' ' ||
                  t_product_code || ' ' || TO_CHAR(t_product_nbr) ||
                  ' > /dev/null &', NO_SCREEN );

This is incorrect syntax used in Host command. First try to execute your detail.sql file with parameter using your shell script. Note how I am writing this code in command prompt to execute that file
Below filename is cmdFile.sh

echo << EOF "
sqlplus / as sysdba
conn username/password
@emis/bin/detail.sql $1 $2 $3 $4 $5 $6 $7
EOF " >> MyTempFile

Now execute cmdFile.sh Printer t_printer_queue Product ...... This way you can execute your shell file.
Therefore in host command you have to call like this
host ('emis/bin/detail.sql printer ' || ......)

Syntax of host is host('first argument', second_argument), but you used as
host ('first argument' 'second argument)

Thanks!   I will try your examples as soon as I get to my job tomorrow morning.  I appreciate your help.

I'm still skeptical.  Are you able to execute UNIX commands from your Windows PC at a command prompt in WIndows?  If not, you certainly will not be able to from "host" commands in Oracle Forms either.  Oracle Forms does *NOT* give us a magic way to do things that are not supported by the O/S where Forms is running.

But, if if you have (or find)  a way to do this from a Windows command prompt, then it should be possible to do it using an Oracle Forms "host" command as well.  Just be aware that if there is a problem or error in the program called by the "host" command, you will *NOT* see the error in Forms.

From window execute batch file using host command of Oracle. This batch file will transfer unix script file to Unix platform in particular position. Update crontab to execute that file after 5 min. Every 5 min, crontab will execute the content in Unix machine. Its obvious, if content of shell script is empty, there will be no error, when you transfer your script file from window to unix, crontab will execute it. Therefore from window you can use host command.

OK, now I understand what you mean.  I was confused by your earlier comment where you said:
"Schedule to execute MyProg.sh in crontab daily after 5 hours."

But now you say "Every 5 min".  That is better, but it is still is not "real time".  The cron job could be scheduled to every 1 minute, that would be closer to "real time".

We will have to wait for a response from ratliffjm to see if he/she knows how to copy a file from Windows to UNIX, and if the file created on Windows is executable, or if it has extra end-of-line characters that UNIX doesn't like.

Sorry My english is poor.
 Intially I would like to set 5 min so that if user wrote something wrong, he/she has at least 4 min after transfer that file. Right now I dont have unix, otherwise I will write command.

Virdi ds

I am able to execute   cmdFile.sh   from the unix prompt  with the parameters.  How can I execute
host ('emis/bin/detail.sql printer ' || ......).  I don't know how to incorporate the 7 parameters that I need for the host command ?

@emis/bin/detail.sql $1 $2 $3 $4 $5 $6 $7

Being able to execute cmdFile.sh from the command prompt in UNIX is only one (small) part of the picture here!  Are you able to copy that file to UNIX from a command window on a WIndows machine?  Are you also able to re-write that file on a Windows machine, copy it to UNIX and have UNIX execute it successfully without getting errors due to the different end-of-line characters on WIndows files than UNIX files?

Are you also satisfied with this file getiing executed via a cron job in UNIX some time after you copy it from Windows, and *NOT* in real-time when the "host" command is executed?

If yes, and if you can do the things I mentioned in the first paragraph, then you should be able to automate the same steps in an Oracle Forms "host" command, or in a *.bat file that you call from a "host" command.

if I could exedute the sql file with the host command, I'd be happy.  Such as below.

@emis/bin/detail.sql $1 $2 $3 $4 $5 $6 $7

But are you able to do the other things that are required to get a "host" command from Oracle Forms on a WIndows PC to interact with a UNIX server?  That is: can you copy a file from a command prompt on the WIndows machine to the UNIX server?  Is the file in a format the UNIX server can process without errors?

Suppose you have declared 7 variables, now you want to send it as parameters. Do the following
V1 varchar(100);
V1 varchar(100);
V2 varchar(100);
V3 varchar(100);
V4 varchar(100);
V5 varchar(100);
V6 varchar(100);
V7 varchar(100);

V1 := 'V1';
V2 := 'V2';
V3 := 'V3';
V4 := 'V4';
V5 := 'V5';
V6 := 'V6';
V7 := 'V7';

host ('emis/bin/detail.sql printer ' || v1 || ' ' || v2 || ' ' || v3 || ' ' || v4 || ' ' || v5 || ' ' || v6 || ' ' || v7);  

To virdi_ds:

A very imporatant step is still missing though!  The "host" command you suggested will *NOT* work from an Oracle Form running on a Windows PC, since that is not a valid DOS/Windows command.  The "host" command in Oracle Forms does *NOT* give us a magic way to execute O/S commands on a database server somewhere!  It only allows Oracle Forms to execute a valid O/S command ON THE MACHINE where Oracle Forms is running, which is a Windows PC in this case.

That's why I've been asking ratliffjm, can you do the command(s) from a command window in Windows  that you want Oracle Forms to be able to do?  If not, you will *NOT* be able to get the "host" command in Oracle Forms on Windows to do it either.