?
Solved

Calling exe not using nor dll neither PRO*  - xp_cmdshell with oracle

Posted on 2003-03-25
13
Medium Priority
?
1,774 Views
Last Modified: 2013-12-11
Hi,
Coming from SQL Server I used to call programs using XP_CMDSHELL, is there a way to call exe files as simply
with oracle? Maybe with some DBMS function?
0
Comment
Question by:JCguerin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
13 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 8208705
0
 

Author Comment

by:JCguerin
ID: 8208713
Does it works on Win2000 or Linux only?
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 8208851
HOST command works also on Windows OS.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:JCguerin
ID: 8208929
Thanks a lot, I'll try this
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8225664
Are you wanting to execute a command from a PL/SQL program OR from the SQL*PLUS command line?

Both places are possible but use different methods.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8296027
Just a follow-up ... are you wanting to issue the command from SQL*PLUS or from a PL/SQL program?

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram
0
 

Author Comment

by:JCguerin
ID: 8297537
I should use it from a PL/SQL program, with oracle, on a Win2k workstation (from a stored procedure called by a trigger most probably).

Sorry for the late answer, but I did had a lot of job beside this...
Thanks
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8303533
There is NO DBMS_* function to call a command from a PL/SQL program.  There is the ability to call an external procedure (call a DLL written in C, which can perform the function you desire).

You could easily write this function to accept from the PL/SQL program a shell command and have it execute it.

External Program development using C or another supported language is a documented Oracle feature.

You can find information in the \oracle\ora92\rdbms\extproc subdirectory.  (This is for Oracle 9i).  If you are running a different version the location will be slightly different (e.g. 8i => \oracle\ora81\rdbms\extproc).

These instructions are for Oracle 9i but other versions have similar abilities/instructions.

Please note:  After you compile the c program you need to put the DLL in the \oracle\ora92\bin subdirectory.  I spent about 20 minutes discovering this "hidden" requirement.

The example in the \oracle\ora92\rdbms\extrpoc works fine and I've successfully called scripts (e.g. system(xxx); from PL/SQL using this method.

The example will show you how to accept input from the PL/SQL (say trigger based) procedure and return a value.

I successfully called shell commands from a DLL without the ability to pass the command via a PL/SQL parameter.  I used Microsoft VC++ 6.0.  It does work.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

External Procedure Supplement

This document supplements the information in the Applications Development chapter of the Getting Started for Windows NT manual.  It explains how to run the sample and gives some trouble-shooting advice.

Running the Sample

1.  Install Microsoft Visual C++ 5.0.

2.  Run the MAKE.BAT file in the RDBMS\EXTPROC directory.  This creates the EXTERN.DLL.

3.  Run SVRMGRL and run the EXTERN.SQL script.  It prompts for the database's internal password.
     D:\ORANT\RDBMS\EXTPROC\>svrmgrl @extern.sql

This SQL script does the rest, including calling the PLSQL function "UseIt" that makes a call to the C function contained in the EXTERN.DLL.


Trouble-Shooting


PROBLEM:
You get the following error running the EXTERN.SQL script:
ORA-28575: unable to open RPC connection to external procedure agent
ORA-06512: at "SAMPLE.PLS_MAX", line 0
ORA-06512: at "SAMPLE.USEIT", line 8
ORA-06512: at line 2

SOLUTION:
This is most likely a SQL*Net configuration problem.  Make sure that your listener service is started.  Make sure that your TNSNAMES.ORA and LISTENER.ORA are properly updated.  If you make changes to your LISTENER.ORA, be sure to stop and re-start your listener.  The correct entry in your TNSNAMES.ORA is:
extproc_connection_data.world =
 (DESCRIPTION =
   (ADDRESS =
         (PROTOCOL = IPC)
         (KEY = ORCL)
   )
   (CONNECT_DATA = (SID = extproc)
   )
 )

The correct entry in your LISTENER.ORA should look like this:
LISTENER =
 (ADDRESS_LIST =
       (ADDRESS=
         (PROTOCOL= IPC)
         (KEY= oracle.world)
       )
       (ADDRESS=
         (PROTOCOL= IPC)
         (KEY= ORCL)
       )
       (ADDRESS=
         (COMMUNITY= NMP.world)
         (PROTOCOL= NMP)
         (SERVER= YourServer)
         (PIPE= ORAPIPE)
       )
       (ADDRESS=
         (PROTOCOL= TCP)
         (Host= YourServer)
         (Port= 1521)
       )
       (ADDRESS=
         (PROTOCOL= TCP)
         (Host= YourServer)
         (Port= 1526)
       )
       (ADDRESS=
         (PROTOCOL= TCP)
         (Host= 127.0.0.1)
         (Port= 1521)
       )
       (ADDRESS=
         (PROTOCOL= SPX)
         (Service= YourServer_lsnr)
       )
 )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = 0
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = YourServer)
     (SID_NAME = ORCL)
   )
   (SID_DESC =
     (SID_NAME = extproc)
     (PROGRAM=extproc)
   )
 )
PASSWORDS_LISTENER = (oracle)

PROBLEM:
When you build your DLL, you get the following missing exports:
ociepacm
ocieperr
ociepgoe
ociepmsg

SOLUTION:
Make sure you link with ORAPLS8.LIB in the ORANT\OCI\LIB\ directory.  You will also need to link with the ORACLIENT8.LIB as well.

PROBLEM:
When you execute your external procedure, you get an Application Error in EXTPROC.EXE.

SOLUTION:
Your function prototypes are not correct, you are returning invalid data, or you have messed up the stack in you function.  Try using DebugBreak(); to step into your DLL code to check the make sure you are not messing things up.

PROBLEM:
When you execute your external procedure, you get the following error
ORA-28576: lost RPC connection to external procedure agent
ORA-06512: at "SAMPLE.PLS_MAX", line 0
ORA-06512: at "SAMPLE.USEIT", line 8
ORA-06512: at line 2

SOLUTION:
This is a time-out problem.  Perhaps while you were debugging your external function, you did not let your function return in time.  In that case it is harmless.  However, it could be an indication that you are taking too long in your external callout code.

PROBLEM:
You are having performance problems.

SOLUTION:
Performance is dramatically improved on future calls to external procedures in the same session.  So, try to make as many of your external procedure calls as possible before disconnecting.  Future releases will decrease this initial performance hit.

PROBLEM:
You want to limit the OS permissions of the DLL's that contain the external procedure.

SOLUTION:
Create a new listener service with its own LISTENER.ORA and have that service run under a user with limited permissions.

PROBLEM:
You are getting the following error when trying to execute the external procedure:
ORA-06520: PL/SQL: Error loading external library
ORA-06522: Unable to load DLL
ORA-06512: at "SAMPLE.PLS_MAX", line 0
ORA-06512: at "SAMPLE.USEIT", line 8
ORA-06512: at line 2

SOLUTION:
Make sure that the full path name to your extern.dll is correct and that the DLL exists.
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8303552
Here is another example (again works on Oracle 9i Release 2 but other versions of Oracle supports external functions/procedures).

Here is how to get the Oracle_Home into a PL/SQL varchar2 variable, where you can use it as necessary.  I got this working on Windows/XP Professional with Oracle 9.2.0.1.0.  It should work with Windows 2000.  You will need a C compiler (e.g. Microsoft Visual C++ 6.0).

1)  Compile the C program using make.bat.

make.bat

REM USAGE: just type MAKE
cl -I. /LD -Zi oraclehome.c /link msvcrt.lib /nod:libcmt /DLL

oraclehome.c

/*------------------------------------------------------------------------*\
|                                                                          |
| (c) Copyright 2003 Bertram Glenn Moshier - All Rights Reserved           |
|                                                                          |
|     Program                                                              |
|       Name:     OracleHome.c                                             |
|       Version:  1.00                                                     |
|       Date:     2003-02-24                                               |
|                                                                          |
|       Purpose:  Pass back to an Oracle funcion the Oracle_Home           |
|                 environment variable.                                    |
|                                                                          |
|       Syntax/   variable_name := oracle_home()                           |
|       Setup:                                                             |
|                                                                          |
|       Revision: None                                                     |
|                                                                          |
\*------------------------------------------------------------------------*/

#include <windows.h>
#include <process.h>
#include <stdlib.h>


__declspec(dllexport) char * oracle_home(
                        short       *ret_indicator)
{

  char *OracleHomeVar;
  OracleHomeVar = getenv("Oracle_Home");

  *ret_indicator = 0;
  return OracleHomeVar;


}

2)  Run oraclehome.sql.  NOTE:  As written this sql code uses an username of sample.  You should change or delete this as necessary.

oraclehome.sql

----------------------------------------------------------------------------
--                                                                        --
-- (c) Copyright 2003 Bertram Glenn Moshier - All Right Reserved          --
--                                                                        --
--    Program                                                             --
--      Name:     OracleHome.sql                                          --
--      Version:  1.00                                                    --
--      Date:     2003-02-24                                              --
--                                                                        --
--      Purpose:  Obtain the ORACLE_HOME environment variable and display --
--                it using an external C procedure.                       --
--                                                                        --
--      Syntax/   variable := ORACLE_HOME()                               --
--      Setup:                                                            --
--                                                                        --
--      Revision: None                                                    --
--                                                                        --
----------------------------------------------------------------------------

connect sample/sample;

drop library externProcedures;

create library externProcedures as 'c:\Oracle\Ora92\bin\oraclehome.dll';
/

CREATE OR REPLACE FUNCTION ORACLE_HOME
RETURN VARCHAR2 AS
  EXTERNAL LIBRARY externProcedures
  NAME "oracle_home"  -- Name of function call. Quotes preserve lower case.
  LANGUAGE C
  PARAMETERS (
     RETURN INDICATOR short ); -- need to pass pointer to return value's
/

show error

CREATE OR REPLACE PROCEDURE UseIt AS
oraclehome varchar2(256);
BEGIN
 oraclehome := ORACLE_HOME();
 dbms_output.put_line('Your Oracle_Home environment variable is: ' || oraclehome);
END;
/

show error

set serveroutput on
execute UseIt;
/
 
Comment from bmoshier  02/24/2003 08:13PM PST  
P.S.  Two things I forgot to add:

1)  You need to move the DLL created by the Microsoft compiler to the %Oracle_Home%\bin subdirectory.  For example:  copy *.dll c:\oracle\ora92\bin\*

2)  You may have to enable the external listener extproc or it may already be on.  It was on for my database.

You can get more information on external C/Java/Visual Basic/etc., procedures on Oracle 9i Release 2 at:

http://www.csis.gvsu.edu/GeneralInfo/Oracle/appdev.920/a96590/adg11rtn.htm

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram
0
 
LVL 4

Accepted Solution

by:
bmoshier earned 300 total points
ID: 8303605
Yet, another programming example.  This shows accepting a PL/SQL parameter (environment variable), processing, and returning an answer (environment variable value).

/*------------------------------------------------------------------------*\
|                                                                          |
| (c) Copyright 2003 Bertram Glenn Moshier - All Rights Reserved           |
|                                                                          |
|     Program                                                              |
|       Name:     GetEnv.c                                                 |
|       Version:  1.00                                                     |
|       Date:     2003-02-24                                               |
|                                                                          |
|       Security: Like CGI procedures any program (not just the expected   |
|                 one) can call this routine.  While checking of the       |
|                 input parameter should occur, this routine does not      |
|                 validate the parameter for two reasons:                  |
|                                                                          |
|                 1)  No direct usage of the parameter occurs.  The user's |
|                     parameter goes directly to the getenv call.          |
|                                                                          |
|                 2)  Buffer overflow is not possible as this routine      |
|                     does not directly access the parameter.  It only     |
|                     uses a pointer to the parameter.                     |
|                                                                          |
|                 Should a security problem exist it would be in getenv.   |
|                                                                          |
|       Purpose:  Pass back to an Oracle funcion the requested             |
|                 environment variable.                                    |
|                                                                          |
|       Syntax/   variable_name := oracle_home(EnvVar);                    |
|       Setup:                                                             |
|                 where:                                                   |
|                                                                          |
|                 EnvVar is a varchar2 with the environment variable       |
|                 name to pass back.                                       |
|                                                                          |
|                                                                          |
|       Revision: None                                                     |
|                                                                          |
\*------------------------------------------------------------------------*/

#include <windows.h>
#include <process.h>
#include <stdlib.h>

#define  NullValue -1


__declspec(dllexport) long  oraclegetenv(
        char             *EVarIn,
        short            EVarIn_l,
        short            *ret_indicator)
{

   char *OracleVarVal;
   char *NoParm = "Required parameter (environment variable name) is missing";

   *ret_indicator = 0;

   return EVarIn_l;




   OracleVarVal = getenv(EVarIn);

   *ret_indicator = 0;
   return OracleVarVal;

}

---

----------------------------------------------------------------------------
--                                                                        --
-- (c) Copyright 2003 Bertram Glenn Moshier - All Right Reserved          --
--                                                                        --
--    Program                                                             --
--      Name:     OracleGetEnv.sql                                        --
--      Version:  1.00                                                    --
--      Date:     2003-02-24                                              --
--                                                                        --
--      Purpose:  Obtain the requested environment variable and display   --
--                it using an external C procedure.                       --
--                                                                        --
--      Syntax/   variable := ORACLEGETENV(<environment variable>);       --
--      Setup:                                                            --
--                                                                        --
--      Revision: None                                                    --
--                                                                        --
----------------------------------------------------------------------------

connect sample/sample;

drop library externProcedures;

create library externProcedures as 'c:\Oracle\Ora92\bin\oraclegetenv.dll';
/

CREATE OR REPLACE FUNCTION ORACLEGETENV(
      evarin varchar2)
RETURN BINARY_INTEGER AS
   EXTERNAL LIBRARY externProcedures
   NAME "oraclegetenv"  -- Name of function call. Quotes preserve lower case.
   LANGUAGE C
   PARAMETERS (
      evarin string,
      evarin length short,
      return indicator short); -- need to pass pointer to return value's
/

show error

CREATE OR REPLACE PROCEDURE UseIt AS
environmentVar varchar2(256);
oracleenv      varchar2(10000);
BEGIN
  dbms_output.enable(10000);
  environmentVar := 'Oracle_Home';
  oracleenv      := ORACLEGETENV(environmentVar);
  dbms_output.put_line('Environment variable ' || environmentVar || ' is: ' || oracleenv);

  environmentVar := 'PROCESSOR_IDENTIFIER';
  oracleenv      := ORACLEGETENV(environmentVar);
  dbms_output.put_line('Environment variable ' || environmentVar || ' is: ' || oracleenv);

  environmentVar := '';
  oracleenv      := ORACLEGETENV(environmentVar);
  dbms_output.put_line('Environment variable ' || environmentVar || ' is: ' || oracleenv);

  environmentVar := 'ORACLE_SID';
  oracleenv      := ORACLEGETENV(environmentVar);
  dbms_output.put_line('Environment variable ' || environmentVar || ' is: ' || oracleenv);
END;
/

show error

set serveroutput on
execute UseIt;
/

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram
0
 

Author Comment

by:JCguerin
ID: 8305720
Ok, I guess I have enough with all this to make it work correctly. I think I'll regret my xp_cmdshell :).

Thanks a lot. How to close this topic?

JC Guerin
Oracle certified enemy
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8306575
On the right hand side of each comment is a link:

"Accept Answer"

You need to find this link on the answer IYHO (In YOUR Humble Opinion) helped you the most / is the best answer.  Next, you'll click on this link.  Finally, the system will then ask you to grade the answer (A, B, C, D).  You're done!

Bertram Moshier
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8306601
Just a thought, you might want to open a question on how to do in Oracle what you desire WITHOUT the use of XP_CMDSHELL.  (A different question is the nice way of doing it.)

Normally, I find accomplishing the same function in Oracle is possible - just by looking at and using a different approach.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question