OraFAQ Forum: SQL & PL/SQL » Calling EXE from PL/SQL(urgent)

Home » SQL & PL/SQL » SQL & PL/SQL » Calling EXE from PL/SQL(urgent)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calling EXE from PL/SQL(urgent) [message #376255]

Tue, 16 December 2008 12:42

nahian
Messages: 7
Registered: June 2007
Location: Bangladesh

Junior Member

Hello everyone

I am nahian and a newcomer when it comes to calling java from pl/sql.I hv to write a procedure that will call any EXE(notepad,CMD).I hv written the following code taking from this forum

=======================================================

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED Command
AS
import java.io.*;
import java.util.*;
public class Command{
public static void run(String cmdText)
throws IOException, InterruptedException
{
int rtn;
Runtime rt = Runtime.getRuntime();
Process prcs = rt.exec(cmdText);
rtn = prcs.waitFor();
}
}

------------------------------------------

CREATE OR REPLACE PROCEDURE runoscommand(cmd IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Command.run(java.lang.String)'

=========================================================
Both Java and procedure were created.But when I execute the procedure or a batch file

-- exec runoscommand('C:\WINDOWS\SYSTEM32\cmd.exe');
OR
--exec runoscommand('C:\Documents and Settings\w.arefin\Desktop\test.bat');

It hangs up saying nothing.no messages,nothing in SQL*PLUS.Please respond quickly anybody.I am in urgent need & I will really be grateful.

Regards
Waliul Arefin Nahian

Report message to a moderator

Re: Calling EXE from PL/SQL(urgent) [message #376262 is a reply to message #376255]

Tue, 16 December 2008 13:26

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Of course everything started by PL/SQL will be started on the server, so when you look at it from the client's point of view nothing seems to happen beside the hang.

Even when the server runs on your local machine, you most likely see nothing happening since whatever the server process starts isn't happening in your Windows session.

And if it's urgent, get paid help and don't ask in a forum.

Report message to a moderator

Re: Calling EXE from PL/SQL(urgent) [message #376263 is a reply to message #376255]

Tue, 16 December 2008 13:31

andrew again
Messages: 2577
Registered: March 2000

Senior Member

Be sure to understand that the command is executed on the DB server, not client. Your example requires Oracle DB to be running on Windows.

In 10g, it's easier to just use external jobs (assuming you don't want to pass in paramaters).

-- grant execute on DBMS_SCHEDULER to scott;
-- grant create job to scott;
-- grant CREATE EXTERNAL JOB to scott;
-- On Windows ensure OracleJobSchedulerXE started in services

-- DBMS_SCHEDULER.create_job
-- (job_name => 'testjob',
-- job_type => 'EXECUTABLE',
-- job_action => 'c:\windows\system32\cmd.exe /c c:\my_dir\my_script.bat',
-- enabled => false);

DBMS_SCHEDULER.run_job (job_name             => 'testjob',
                        use_current_session  => TRUE);

For the Java solution, be sure to grant the Java permissions.
select * from dba_java_policy where grantee = 'SCOTT';

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241

You can retrieve STDOUT for the Java aproach using exec dbms_java.set_output(...). Unsure if external job supports getting STDOUT.

Report message to a moderator

Re: Calling EXE from PL/SQL [message #376659 is a reply to message #376255]

Thu, 18 December 2008 03:34

nahian
Messages: 7
Registered: June 2007
Location: Bangladesh

Junior Member

Hello everyone

Thomas & andrew - Thanks for such a prompt reply.Actually I was late as I was carrying out the tests.Andrew - I hv done it on 10G by your method of creating jobs in local database(at home).Many thanks.

I hv gone through the link - asktom,u hv provided,I am truly confused to see so many things.My clients database is on 9.2.0.5.0,I have a client access (I will test at home though),the DB is on Sun Solaris, now I want to do the same by invoking a batch file.can I create the same scheduler with the same syntax?

If not,could u please suggest a way.

** The ultimate goal of me is to prompt up a HTML file from a pl/sql procedure.I hv tried HTP.PRINT & its not working.So in the batch file I wrote necessary commands to invoke the HTML.

Andrew,I apologize for writing urgent coz thats the case with everyone.

please anybody,suggest me a way.

Thanks & Regards
Waliul Arefin Nahian

Report message to a moderator

Re: Calling EXE from PL/SQL [message #376666 is a reply to message #376659]

Thu, 18 December 2008 03:53

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Quote:

The ultimate goal of me is to prompt up a HTML file from a pl/sql procedure

Which CAN NOT be done, since PL/SQL runs on the server. You have do to do it with something that runs on the client.

Report message to a moderator

Re: Calling EXE from PL/SQL [message #376737 is a reply to message #376255]

Thu, 18 December 2008 07:12

nahian
Messages: 7
Registered: June 2007
Location: Bangladesh

Junior Member

Hello everyone

Thanks Thomas - thanks again for a prompt reply.U know,
I hv to give up to the last try.We are implementing Oracle Apps
for our client & I am very novice to it(everything though Razz

).

We r implementing it in joint venture,and the other team said to do it.Can I put the the html file in the Application server
and when the user calls up the procedure from the client
end,it will show up the HTML file in the client end.

Can u please suggest possible solutions of how to do it?creating schedulers like this in 9i would be best though Smile

.looking forward to your & everybody's reply

Thanks & Regards
Waliul Arefin Nahian

Report message to a moderator

Previous Topic:	select query
Next Topic:	[Problem] Stored Procedure and Scheduler

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 17 19:07:00 CST 2025