Home » SQL & PL/SQL » SQL & PL/SQL » Execute an external application from PL/SQL
Execute an external application from PL/SQL [message #432316] Mon, 23 November 2009 09:49 Go to next message
serfraile
Messages: 18
Registered: November 2009
Junior Member
Hello,

I need some help to know if this is possible.

I want to know if you can call an external application from a PL/SQL procedure.

I've seen that command HOST allows to execute any external application, but from SQL Plus, not from a PL/SQL editor like TOAD. I want to create a procedure that makes that call and executes an external application.

Do you know if it is possible?

Thanks in advance.

Sergio.

Report message to a moderator

Re: Execute an external application from PL/SQL [message #432317 is a reply to message #432316] Mon, 23 November 2009 09:53 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Basically: Yes.

Big but:

PL/SQL runs on the server. So you can only execute an external application on the server, not on the client, which is what the "host" command of SQL*Plus does.

There are a couple of ways, for example using dbms_scheduler, or a Java stored procedure, depending on what kind of application for what purpose.

Report message to a moderator

Re: Execute an external application from PL/SQL [message #432319 is a reply to message #432316] Mon, 23 November 2009 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In addition this is most often a VERY VERY BAD idea, just the indication that you have a big flaw in your design.
If you need that just ask yourself how can I do without that.

Regards
Michel

[Updated on: Mon, 23 November 2009 09:56]

Report message to a moderator

Re: Execute an external application from PL/SQL [message #432324 is a reply to message #432319] Mon, 23 November 2009 10:08 Go to previous messageGo to next message
serfraile
Messages: 18
Registered: November 2009
Junior Member
Thanks both of you.

I knew it's a very bad idea, but we have to do it.

I think we will use a Java procedure, which was our first idea, but i wanted to know if was possible by PL/SQL code.

Have a good day.

Sergio.

Report message to a moderator

Re: Execute an external application from PL/SQL [message #432354 is a reply to message #432324] Mon, 23 November 2009 16:04 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
EXECUTE IMMEDIATE might be used to do this.
You might call anonymous blocks from external application (ex. JAVA, perl, sqlplus).
There are many other ways to make it working.

Things that you should be aware:
- The query/plsql might need to be re-compiled on every execution and it takes time.
- Oracle is (can be) using pre-compiled statements... but only if it can.
- Oracle is keeping the dependencies. Without knowing the statement it cannot keep them. It won't know if everything is OK. It would need to check this on the execution. Expect significant(?) performance issue.

Report message to a moderator

Re: Execute an external application from PL/SQL [message #432355 is a reply to message #432324] Mon, 23 November 2009 16:09 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
dbms_scheduler is much easier - no Java stored proc required. Also - using the DBMS_PIPE approach you can run the command wherever you like, and under a different ID (but there are more moving pieces to consider). DBMS_PIPE solution pre-dates Java stored proc & dbms_scheduler.

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

[Updated on: Mon, 23 November 2009 16:11]

Report message to a moderator

Re: Execute an external application from PL/SQL [message #432360 is a reply to message #432319] Mon, 23 November 2009 17:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not always a bad idea - I've never found a way to interface with the QAS address mapping software other than writing a set of java wrappers for their library, and if you've ever found a way to interface with Experian's credit checking system in the uk that didn't involve writing the interface in C and then calling that via Extproc then I'd love to know how you did it.

Sometimes there are things that your application needs to talk to that Oracle's set of utilities just won't handle.

Report message to a moderator

Re: Execute an external application from PL/SQL [message #432394 is a reply to message #432360] Mon, 23 November 2009 23:29 Go to previous message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
OK, I should add to my signatures:
- ALWAYS = always unless you have a good reason to not to so
- NEVER = never unless you have a good reason to do so.

But writing so in a forum that is read by beginners will "always" be read at "so we cannot (or can) do it".

Regards
Michel

Report message to a moderator

Previous Topic: reconfigure a col by sql command
Next Topic: how to get outer table value in the nested subquery
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 14 22:35:52 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.