Toad World® Forums

Calling Sqlplus.exe inside stored procedure..


#1

Hi Gurus,
Any advise on how can i call sqlplus.exe inside the stored procedure, my main
purpose is to
excute all our fully function sql script, so we dont need to re-write this, we
can just call
this in our new packages/stored procedure.
Thanks for tips in advance.

Regards,
Romeo Romano


#2

a very inefficient idea as you are shelling out to an external process which
itself consumes resources and memory and diskspace
HOST “sqlplus.exe username/login NameOfSQLStatement.sql”

keeping all sql statements in one procedure or one script is more efficient for
the invoking binary as well as the OS
forcing the OS to track 2 processes to perform functionality which could easily
be easily accomplished by one
places unnecessary load on the OS process-manager and resource-manager

Martin Gainty


Verzicht und Vertraulichkeitanmerkung

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.


#3

Afternoon all,

HOST “sqlplus.exe username/login NameOfSQLStatement.sql”

I’m not sure that this will work as the OP wanted to know “how can i
call sqlplus.exe inside the stored procedure” - I took stored procedure
to mean PL/SQL.

The only way that I can think of doing this is to set up a call out
listener (security problem coming up!) and set up an EXTPROC system. Up
to 9i anyway.

10g and 11g might allow this, I think the DBMS_SCHEDULER stuff (you
can tell I have a good grip of the technical terms can’t you?) allows
external programs to be called.

HTH (Slightly)

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#4

Why not create a toad action that runs the script and then schedule it with
windows scheduler ???