RE: Are There any way of calling NT OS Commands like print, del e
Shiva,
Attached is an email from Ankur Shah from another posting - have not tried it
myself but this may help.
Lewis Bishop
---
Barclays Enable - ISS - E-NTRUST/Bexleyheath NT
Oracle Database Consultant
Watling Street, Bexleyheath, Kent, DA6 7RR (Mail Van R)
Phone : 020 8298 3418
Mobile: 07950 380857
Email : [EMAIL PROTECTED]
Enabling Competitive Advantage for Barclays in IT and
Business Processing
-Original
Message-
From: Baswannappa, Shiva
[mailto:[EMAIL PROTECTED]]
Sent: 05 December 2002 15:10
To: Multiple recipients of list
ORACLE-L
Subject: Are There any way of
calling NT OS Commands like print, del etc.
This
header confirms that this email message has been swept for the
presence of computer viruses.
Corporate IT
THE WOOLWICH
--
Hi Gurus
I am looking for easy of calling OS commands like
print, del, etc. from PL/SQL on a NT4 system running Oracle 8.1.7. I tried the
Java approach from oracle web site, it works for few commands and not all.
Any insight, direction and help to achieve is very
much appreciated.
Thanks in advance
Regards
Shiva
Baswannappa
Senior Developer
Digital Consulting and Software Services
Phone: 281.243.2658
Fax: 281.243.2504
Web: http://www.dcss.com
If the reader of this e-mail is not an intended
recipient, you have received this e-mail in error and any review,
dissemination, distribution or copying is strictly prohibited. If you have
received this e-mail in error, please notify the sender immediately by return
e-mail and permanently delete the copy you received. Thank you.
Sent: 30 September 2002 13:46
To: LazyDBA.com Discussion
This header confirms that this email message has been swept for the presence of
computer viruses.
Corporate IT
THE WOOLWICH
--
Many tasks have been automated using shell scripting languages. Frequently,
these shell scripts need to be called from an Oracle stored procedure, but no
direct interface to call shell scripts is provided. Oracle does, however,
provide a method for calling C and Java programs via external procedures. Since
many shell programmers have never programmed in C or Java, converting shell
scripts or writing new tasks in either of these languages is difficult and time
consuming.
This solution provides a C routine that will make OS calls, and these calls can
be commands, shell scripts, or other application programs. Using this method,
no C programming experience is required, but does allow existing and new tasks
to be written as shell scripts.
This article does not provide information to setup the database or
environment for external procedures.
Refer to How to Configure a UNIX Oracle Server to Use External Procedures for
information on setting up the Oracle Server machine.
The external procedure is written in C and compiled with the Oracle
makefile, and hence, an Oracle supported compiler for the specific UNIX
platform is required. See Certified
Compilers
From the Oracle server machine:
1. Create a file for the external procedure code:
shell.c
===
#include stdio.h
#include stdlib.h
#include string.h
void sh(char *);
void sh( char *cmd )
{
int num;
num = system(cmd);
}
2. Compile and link the C code into a shared library:
make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_nocallback \
SHARED_LIBNAME=shell.so OBJS=shell.o
Log into SQL*Plus to perform the remaining steps.
3. Define the shared library in Oracle:
CREATE LIBRARY shell_lib IS 'full path to shared lib/shell.so'; /
4. Create the PL/SQL wrapper procedure:
CREATE OR REPLACE PROCEDURE shell (cmd IN CHAR)
AS EXTERNAL
NAME sh
LIBRARY shell_lib
LANGUAGE C
PARAMETERS (
cmd STRING);
/
5. Call a shell script:
SQL exec shell('sh myscript.sh');
PL/SQL procedure successfully completed.
===
HTHU
Ankur Shah
Oracle DBA
DHR-GA
- Original Message -
To: LazyDBA.com Discussion [EMAIL PROTECTED]
Sent: Monday, September 30, 2002 3:08 AM
But i want to call the shell script or the file from a stored procedure.
-Original Message-
Sent: Monday, September 30, 2002 12:33 PM
To: Dibyendu Kole-VP
there are cron jobs in unix, just like schedulers e.g. dbms_jobs in oracle. hope this
would solve your problem.
Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to [EMAIL PROTECTED]
To subscribe: send a blank email to [EMAIL PROTECTED]
Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
RE: Are There any way of calling NT OS Commands like print, del e
What commands aren't working for you. Some of the commands are not external executables but rather part of cmd, i.e. internal commands. So you need to invoke cmd with the command. HTH Richard Ji -Original Message-From: Baswannappa, Shiva [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 05, 2002 10:10 AMTo: Multiple recipients of list ORACLE-LSubject: Are There any way of calling NT OS Commands like print, del etc. Hi Gurus I am looking for easy of calling OS commands like print, del, etc. from PL/SQL on a NT4 system running Oracle 8.1.7. I tried the Java approach from oracle web site, it works for few commands and not all. Any insight, direction and help to achieve is very much appreciated. Thanks in advance Regards Shiva Baswannappa Senior Developer Digital Consulting and Software Services Phone: 281.243.2658 Fax: 281.243.2504 Web: http://www.dcss.com If the reader of this e-mail is not an intended recipient, you have received this e-mail in error and any review, dissemination, distribution or copying is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by return e-mail and permanently delete the copy you received. Thank you.
RE: Are There any way of calling NT OS Commands like print, del e
On Thu, 5 Dec 2002, Bishop Lewis wrote:
Shiva,
Attached is an email from Ankur Shah from another posting - have not tried
it myself but this may help.
Lewis Bishop
---
The email was instructional but it's only good for Unix. There is
no equivalent for 'system()' in the Win32 world unless you write
your own. The code below has been tested and it works. ;)
WARNING *** WARNING *
The code below will workbut it is not secure in that it only
does what it is told to do. If somebody sends the proc a disk
'format' command it will run it...you have been warned. Make
sure the PL/SQL wrapper procedure that calls the external is
SECURE. Also think about running the external procedure
listener under a different user id that can't do any
damage.
The following code can be placed in a DLL
--- begin code --
ORACMD_API void RunCommand(char *cmd)
{
STARTUPINFO lpStartupInfo;
PROCESS_INFORMATION lpProcessInfo;
DWORD Derr;
BOOL pid;
DWORD Stat;
LPDWORD lpStat = Stat;
char script[1024];
char str[1024];
char msg[1024];
char buf[1024];
char cwd[256];
strcpy(script,cmd);
getcwd(cwd,255);
cwd[255] = '\0';
//
// run the script
// Set up the STARTUPINFO Structure...this structure
// determines what the window is going to look like
lpProcessInfo.dwProcessId = -1;
lpProcessInfo.dwProcessId = -1;
lpStartupInfo.cb = sizeof(STARTUPINFO); // struct size
lpStartupInfo.lpReserved = NULL; // Reserved for Bill
lpStartupInfo.lpDesktop = NULL; // WinNT Desktop
lpStartupInfo.lpTitle = NULL; // for GUI processes
lpStartupInfo.dwX = 0;
lpStartupInfo.dwY = 0;
lpStartupInfo.dwXSize = 800;
lpStartupInfo.dwYSize = 600;
lpStartupInfo.wShowWindow = SW_SHOW;
lpStartupInfo.cb = 0; // Reserved must be zero
lpStartupInfo.lpReserved2 = NULL; // Reserved must be NULL
// Call ::CreateProcess to create a new threadInfo about
// the new thread will be returned in the PROCESS_INFORMATION
// structure
lpStartupInfo.dwFlags = STARTF_USESHOWWINDOW | STARTF_USEPOSITION
| STARTF_USESIZE;
//MessageBox(NULL,script,NULL,MB_OK);
if((pid = ::CreateProcess(NULL, // Application Name (executable
or bat file)
(char *) script, // command Line
NULL, // Process Security Attributes
NULL, // Thread Security Attributes
TRUE, // Handle Inheritance Flag
CREATE_SUSPENDED,// Creation Flags
NULL, // Process Environment (use Parent's)
cwd, // Current directory (use DLL's dir)
lpStartupInfo, // Start up information
lpProcessInfo)) == 0)
{
Derr = GetLastError();
sprintf(buf, Unable to start process %s Error
Code = %d,
str,Derr);
//MessageBox(NULL,buf,ERROR,MB_OK);
}
else
{
sprintf(msg,Created Pid
%08x,lpProcessInfo.dwProcessId);
//MessageBox(NULL,msg,NULL,MB_OK);
//
// The Thread was created suspended...now we
// activiate it.
::ResumeThread(lpProcessInfo.hThread);
}
}
-- end code -
Notes...
1) The external procedure listener must be configured and
running...you should be able to 'tnsping EXTPROC_CONNECTION_DATA'
and get a reply. See Metalink or the manuals for setup
2) Create a Visual C++ project named 'oracmd' and then the type
entered above will work. Use the Win32 DLL template with the
option that exports functions
3) disable name-mangling (name decoration) for the exported
function by putting
extern C ORACMD_API void RunCommand(char *);
in the exported function declarion section of the oracmd.h
Also make sure you include direct.h (for getcwd()) and good
old stdio.h for the strcpy()/sprintf() calls
4) Compile/build the .dll and copy it to a location that is in
the server's PATH (%SYSTEMROOT%\System32 or %ORACLE_HOME%\bin)
5) Issue the CREATE LIBRARY command pointing at the DLL location
and issue the CREATE PROCEDURE AS EXTERNAL command using the
name from the code above i.e. RunCommand
6) Test execution of the procedure.
The process runs in the current directory of the DLL The only
way to make sure that it is working is to create a test '.bat'
file which will do something fairly harmless and that also
logs information to disk. Then you can look for the logfile. Do not
put a 'pause' statement in the .bat file or you will be left
with orphaned CMD.EXE processes...I know this from
