execute shell script from stored procedure
In Oracle8i, release 8.1, we could use java to run a system command with an
"&" after it (assuming unix) or perhaps "start xxx.cmd" if using NT.
o In Oracle8.0 and up, we can write an external procedure in C that runs host
commands with system() and the "&". (see attached for an external procedure
example)
o In Oracle7.0 and up, we can use dbms_pipes to talk to a daemon running outside
the database. Here is a simple example that uses sqlplus to be the daemon:
A quick and dirty way to do this is with a csh script and sqlplus as such (cut
and pasted from another email)
Ok, so can you do this without C? Yes. Here is a PL/SQL subroutine you can
install in your schema:
create or replace procedure host( cmd in varchar2 )
as
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
/
Here is a C-Shell script you can run in the background, it should be named
host.csh. host.csh will be run by you after the db is up. it will create
temporary scripts "tmp.csh" that it will run. the last thing these tmp.csh
scripts do is re-run host.csh to get the next request....
-------------------- bof ----------------------------
#!/bin/csh -f
sqlplus tkyte/tkyte < tmp.csh
set serveroutput on
declare
status number;
command varchar2(255);
begin
status := dbms_pipe.receive_message( 'HOST_PIPE' );
if ( status <> 0 ) then
dbms_output.put_line( '#exit' );
else
dbms_pipe.unpack_message( command );
dbms_output.put_line( '##!/bin/csh -f' );
dbms_output.put_line( '#' || command );
dbms_output.put_line( '#exec host.csh' );
end if;
end;
/
spool off
"EOF"
chmod +x tmp.csh
exec tmp.csh
----------------------- EOF ---------------------------------
If you run this in the background (The script), you'll be able to have it
execute any host command you want. Run this in one window for example and in
anther window go into sql*plus and try:
SQL> exec host( 'ls -l' );
SQL> exec host( 'uptime' );
SQL> exec host( 'echo Hello World' );
SQL> exec host( 'exit' );
You'll see the output of ls -l, uptime, and echo happen on the other window
where the shell script is running (shows you a way to debug pl/sql routines, use
"host( echo some string )" and you'll get real time feedback from your pl/sql
procedure).....
Make sure you understand the ramifications of the above. It does absolutely no
checking anywhere that only valid commands are executed. If you run this as the
oracle account and someone sends "rm -rf *" -- watch out. This is an example --
it needs to be more robust.
[@more@]
"&" after it (assuming unix) or perhaps "start xxx.cmd" if using NT.
o In Oracle8.0 and up, we can write an external procedure in C that runs host
commands with system() and the "&". (see attached for an external procedure
example)
o In Oracle7.0 and up, we can use dbms_pipes to talk to a daemon running outside
the database. Here is a simple example that uses sqlplus to be the daemon:
A quick and dirty way to do this is with a csh script and sqlplus as such (cut
and pasted from another email)
Ok, so can you do this without C? Yes. Here is a PL/SQL subroutine you can
install in your schema:
create or replace procedure host( cmd in varchar2 )
as
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
/
Here is a C-Shell script you can run in the background, it should be named
host.csh. host.csh will be run by you after the db is up. it will create
temporary scripts "tmp.csh" that it will run. the last thing these tmp.csh
scripts do is re-run host.csh to get the next request....
-------------------- bof ----------------------------
#!/bin/csh -f
sqlplus tkyte/tkyte < tmp.csh
set serveroutput on
declare
status number;
command varchar2(255);
begin
status := dbms_pipe.receive_message( 'HOST_PIPE' );
if ( status <> 0 ) then
dbms_output.put_line( '#exit' );
else
dbms_pipe.unpack_message( command );
dbms_output.put_line( '##!/bin/csh -f' );
dbms_output.put_line( '#' || command );
dbms_output.put_line( '#exec host.csh' );
end if;
end;
/
spool off
"EOF"
chmod +x tmp.csh
exec tmp.csh
----------------------- EOF ---------------------------------
If you run this in the background (The script), you'll be able to have it
execute any host command you want. Run this in one window for example and in
anther window go into sql*plus and try:
SQL> exec host( 'ls -l' );
SQL> exec host( 'uptime' );
SQL> exec host( 'echo Hello World' );
SQL> exec host( 'exit' );
You'll see the output of ls -l, uptime, and echo happen on the other window
where the shell script is running (shows you a way to debug pl/sql routines, use
"host( echo some string )" and you'll get real time feedback from your pl/sql
procedure).....
Make sure you understand the ramifications of the above. It does absolutely no
checking anywhere that only valid commands are executed. If you run this as the
oracle account and someone sends "rm -rf *" -- watch out. This is an example --
it needs to be more robust.
[@more@]
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/18921899/viewspace-1017651/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- How to rename an Oracle stored procedureOracle
- Oracle stored procedure to send emailOracleAI
- stored procedure 收集session wait 資訊SessionAI
- EXECUTE IMMEDIATE dynamic sql in procedureSQL
- stored procedure 收集session wait 資訊(轉)SessionAI
- Entity Framework Tutorial Basics(29):Stored Procedure in Entity FrameworkFramework
- OWI --用 stored procedure 收集session wait 資訊SessionAI
- Calling Dbms_metadata.Get_ddl From Stored Procedure Results Ora-31603_463483.1
- [shell] execute remote Script自動生成oracle awr report並mail出來REMOracleAI
- Result Sets from Stored Procedures In Oracle (轉)Oracle
- Shell Script
- shell script
- Entity Framework 6.0 Tutorials(9):Stored Procedure MappingFrameworkAPP
- SQL error 2812: Could not find stored procedure.docSQLError
- Spring 呼叫 Stored Procedure 並獲取返回值Spring
- Shell Script(轉)
- How to use sql result stored on shell variable?SQL
- revoke EXECUTE privileges on powerful packages from PUBLICPackage
- shell script to rename directory
- DEPRECATED: Use of this script to execute hdfs command is deprecated.
- Orale的struct,array,cursor,stored procedure,function,package的用法例子StructFunctionPackage
- Shell Script(bash)--用於自動備份的Shell Script(轉)
- Send Email with Attachments from PL/SQL using Java Stored ProceduresAISQLJava
- Get your Windows product key from a scriptWindows
- 【RMAN】Run Allocate Channel For Maintenance From Script FailsAINaN
- Script of Narrative Writing from different point of viewView
- shell script的簡單使用
- Stored Procedure(儲存過程)編寫經驗和最佳化措施 (轉)儲存過程
- Powershell script to install Windows Updates (msu) from folderWindows
- Script to generate AWR report from remote sql clientREMSQLclient
- Bourne shell script中的quoting
- Shell Script(bash)--教學例(轉)
- Can one execute an operating system command from PL/SQL?SQL
- 20天學會bash shell script (二)
- 20天會學bash shell script(一)
- 20 天學會bash shell script (三)
- 透過shell指令碼檢視procedure的資訊指令碼
- 通過shell指令碼檢視procedure的資訊指令碼