Home » SQL & PL/SQL » SQL & PL/SQL » Call HOST Command in Pl/Sql (merged)
Call HOST Command in Pl/Sql (merged) [message #408660] Wed, 17 June 2009 04:42 Go to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi

I would request you people to please help me out the issue i am facing.
I want to call Host command in pl/sql procedure.

Description of requirement:

1. I want to call a batch file (abc.bat) file from Pl/Sql package. For that i want to call Host command within the block.

2. In this call of procedure i want to pass a parameter which then goes in the procedure and execute the Host command for that parameter (as we did through SQL command simply) e.g.

SQL> HOST C:\abc.bat

Please guide me on that!

Regards
Rajat
Re: Call HOST Command in Pl/Sql [message #408661 is a reply to message #408660] Wed, 17 June 2009 04:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
As PL/SQL is server side, you'd be executing commands on the server. Are you sure that's what you want?

MHE
Re: Call HOST Command in Pl/Sql [message #408681 is a reply to message #408660] Wed, 17 June 2009 05:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
There's no need to repeat yourself. I believe that you'd need a Java stored procedure to get that working.

MHE
Re: Call HOST Command in Pl/Sql [message #408690 is a reply to message #408681] Wed, 17 June 2009 05:51 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Thanks Maaher for quick response.
Earlier i was in the dilema that the query was not submmitted thats why paste the same again.

By the way coming back to the point, do you mean to say that for that purpose I need to create the Java Procedure to achieve that.

Can you provide me the link to get that kind of code?

Regards
Rajat
Re: Call HOST Command in Pl/Sql [message #408692 is a reply to message #408690] Wed, 17 June 2009 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please search before posting, it is a FAQ.

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

Regards
Michel
Re: Call HOST Command in Pl/Sql [message #408728 is a reply to message #408692] Wed, 17 June 2009 07:27 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

I searched before posting the question but i posted it just to check that is there any other way to achieve it instead of creating Java Code.

Is there any other way to achieve it?
Re: Call HOST Command in Pl/Sql [message #408732 is a reply to message #408728] Wed, 17 June 2009 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, external procedure, pipeline to a server process, http request to a web service...

But Java procedure is the easiest and safest one.

Regards
Michel

[Updated on: Wed, 17 June 2009 07:32]

Report message to a moderator

Re: Call HOST Command in Pl/Sql [message #408818 is a reply to message #408732] Wed, 17 June 2009 23:29 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Another way, using only PL/SQL is here.

regards,
Delna
Re: Call HOST Command in Pl/Sql [message #408858 is a reply to message #408818] Thu, 18 June 2009 01:54 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi delna

Thanks for timely reply!
I tried to follow the process which you have mentioned but it ends with an error:

BEGIN
dbms_scheduler.create_job(job_name => 'SEO_PING',
  job_type => 'EXECUTABLE',
  job_action => 'C:\WINDOWS\SYSTEM32\CMD.EXE /C D:\Execute_by_batch_file\test.bat',
  auto_drop => true,
  enabled => true);
dbms_scheduler.run_job('SEO_PING');
END;
/

SQL> /
BEGIN
*
ERROR at line 1:
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing execution agent failed with status: 2
ORA-27301: OS failure message: The system cannot find the file specified.
ORA-27302: failure occurred at: sjsec 6a
ORA-27303: additional information: The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 7


Please help to resolve it.
Re: Call HOST Command in Pl/Sql [message #408860 is a reply to message #408858] Thu, 18 June 2009 02:00 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
rajat_chaudhary wrote on Thu, 18 June 2009 08:54

Please help to resolve it.
Oracle solved it for us:
rajat_chaudhary wrote on Thu, 18 June 2009 08:54
ORA-27301: OS failure message: The system cannot find the file specified.



So, the database server cannot find the file you've given. Note that this file should be on the server, not the client. Does your database server have the files you've entered on the path you've entered?

MHE

[Updated on: Thu, 18 June 2009 02:01]

Report message to a moderator

Re: Call HOST Command in Pl/Sql [message #408871 is a reply to message #408860] Thu, 18 June 2009 02:25 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

I tried to execute it on my local matchine and file is very well placed at the specified location.
Re: Call HOST Command in Pl/Sql [message #408873 is a reply to message #408871] Thu, 18 June 2009 02:31 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can't execute it "on your local machine", unless "your local machine" is also the database server.

PL/SQL is always executed on the server.
Re: Call HOST Command in Pl/Sql [message #408880 is a reply to message #408873] Thu, 18 June 2009 02:50 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

I executed it on my local becasue Oracle DB is installed on my local. So i think in that case i can execute it on my local also.
Re: Call HOST Command in Pl/Sql [message #408890 is a reply to message #408880] Thu, 18 June 2009 03:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
OK, I don't have Oracle on Windows to really test it, but this should work then :

BEGIN

dbms_scheduler.create_job(job_name => 'SEO_PING',
  job_type => 'EXECUTABLE',
  job_action => 'C:\WINDOWS\SYSTEM32\CMD.EXE',
  number_of_arguments => 2,
  auto_drop => true,
  enabled => false);

dbms_scheduler.set_job_argument_value ('SEO_PING', 1, '/c');

dbms_scheduler.set_job_argument_value ('SEO_PING', 2, 'D:\Execute_by_batch_file\test.bat');

dbms_scheduler.enable('SEO_PING');

dbms_scheduler.run_job('SEO_PING');

END;
/

Re: Call HOST Command in Pl/Sql [message #409049 is a reply to message #408890] Fri, 19 June 2009 00:12 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Thanks Thomas!

THe problem which i am facing now is :

If I try to execute that command as SYS user then it works

BEGIN
dbms_scheduler.create_job(job_name => 'SEO_PING',
job_type => 'EXECUTABLE',
job_action => 'C:\WINDOWS\SYSTEM32\CMD.EXE',
number_of_arguments => 2,
auto_drop => true,
enabled => false);
dbms_scheduler.set_job_argument_value ('SEO_PING', 1, '\c');
dbms_scheduler.set_job_argument_value ('SEO_PING', 2, 'D:\Execute_by_batch_file\test.bat');
dbms_scheduler.enable('SEO_PING');
dbms_scheduler.run_job('SEO_PING');
END;
/

PL/SQL procedure successfully completed.



But the same code is I try to execute after connecting with the normal user then it throws the error:

ERROR at line 1:
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing execution agent failed with
status: 2
ORA-27301: OS failure message: The system cannot find the file specified.
ORA-27302: failure occurred at: sjsec 6a
ORA-27303: additional information: The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 11


Is it something related to permissions. but i already gave the required permission to the user.
Re: Call HOST Command in Pl/Sql [message #409089 is a reply to message #409049] Fri, 19 June 2009 05:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Connect to the database via SQL*Net, not locally.

See metalink Note 604641.1 for details.

Re: Call HOST Command in Pl/Sql [message #409139 is a reply to message #409089] Fri, 19 June 2009 10:50 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Not to be a Nay-Sayer, Before you spend a lot of time trying to do this, you should contact your Architecture, DBA and Security teams in your company. They will have something to say about it I am sure.

Most organizations do not like the idea of their developers writing code that has the Oracle database executing applications external to itself. They usually have an approval process for what programs are allowed, in what situations, etc.

In more than one instance I have seen development teams create a solution where they do a callout to an external procedure only either have it killed during a review process, or have their deployments delayed pending approvals. If you want to see managers get mad just get into one of these situations.

Personally I always enjoyed seeing which Chiefs of the company feel they had enough clought to say NO, you can't do that, find another way; or NO your delivery date must slip indefinitely until you meet our requirements. Its fun watching the Titans throw their thunderbolts about.

Good luck, Kevin
Previous Topic: Help in Creating a Text File using UTL_FILE
Next Topic: How to manipulate data read from file using utl_file package - 10g - xp sp3
Goto Forum:
  


Current Time: Mon Dec 05 10:50:38 CST 2016

Total time taken to generate the page: 0.11360 seconds