Thanks for the question, Mudassar.

Asked: January 12, 2002 - 12:19 pm UTC

Last updated: December 02, 2003 - 8:39 am UTC

Version: 8.1.5

Viewed 1000+ times

You Asked

This is what I am trying to do.

SQL> create or replace library lib1
2 as 'g:\winnt\system32\kernel32.dll' ;
3 /

Library created.

SQL> create or replace function func1
2 (v1 in varchar2, v2 in number)
3 return number
4 as
5 language c
6 library lib1
7 name "WinExec" ;
8 /

Function created.

SQL> select func1('notepad.exe',1) from dual ;
select func1('notepad.exe',1) from dual
*
ERROR at line 1:
ORA-28576: lost RPC connection to external procedure agent

Although Oracle gives me an error, but it does start the application notepad.exe in the background. Any idea how to prevent the error and start the program in the foreground.

Thanks and Best Regards,
Mudassar Shahzad

and Tom said...

The database cannot do things GUI. Its not running on a console. It has no display to put anything on.

The database is running as a service, services cannot interact with the end user. Besides -- even if this works -- the notepad app would tend to show up on the server wouldn't it, what then? The server isn't "logged into" by anyone typically.

There is no place for this notepad to goto.

Ok, to make this "work" (eg: not crash) you need to use a binary integer. Your procedure as coded would need a C subroutine that has this prototype:

OCINumber *WinExec
(
char *, /* 1 : V1 */
OCINumber * /* 2 : V2 */
);

I seriously doubt if MS uses an OCINumber structure, you need a plsql function that would map to a C function this this prototype:

int WinExec
(
char *, /* 1 : V1 */
int /* 2 : V2 */
);

So, that would be:

tkyte@TKYTE816> create or replace function func1
2 (v1 in varchar2, v2 in binary_integer)
3 return binary_integer
4 as
5 language c
6 library lib1
7 name "WinExec" ;
8 /

Function created.

but no you'll find:

tkyte@TKYTE816> select func1( 'C:\WINNT\system32\notepad.EXE', 1 ) from dual;

FUNC1('C:\WINNT\SYSTEM32\NOTEPAD.EXE',1)
----------------------------------------
33

Now, I have no idea what 33 is for their return codes but basically NOTEPAD is actually running -- you just cannot see it. Using the resource toolkit I can show you this. I'm on the server when I do this:

tkyte@TKYTE816> host tlist notepad

tkyte@TKYTE816> select func1( 'C:\WINNT\system32\notepad.EXE', 1 ) from dual;

FUNC1('C:\WINNT\SYSTEM32\NOTEPAD.EXE',1)
----------------------------------------
33

tkyte@TKYTE816> host tlist notepad
1404 notepad.exe
CWD: C:\WINNT\system32\
CmdLine: C:\WINNT\system32\notepad.EXE
VirtualSize: 11764 KB PeakVirtualSize: 11764 KB
WorkingSetSize: 996 KB PeakWorkingSetSize: 996 KB
NumberOfThreads: 1
0 Win32StartAddr:0x00000000 LastErr:0x00000000 State:Initialized
5.0.2140.1 shp 0x01000000 notepad.EXE
5.0.2163.1 shp 0x77f80000 ntdll.dll
5.0.2920.0 shp 0x76b30000 comdlg32.dll
5.0.2920.0 shp 0x77c70000 SHLWAPI.DLL
5.0.2180.1 shp 0x77f40000 GDI32.DLL
5.0.2191.1 shp 0x77e80000 KERNEL32.DLL
5.0.2180.1 shp 0x77e10000 USER32.DLL
5.0.2191.1 shp 0x77db0000 ADVAPI32.DLL
5.0.2193.1 shp 0x77d40000 RPCRT4.DLL
5.81.2920.0 shp 0x77b50000 COMCTL32.DLL
5.0.2920.0 shp 0x775a0000 SHELL32.DLL
6.1.8637.0 shp 0x78000000 MSVCRT.DLL
5.0.2167.1 shp 0x77800000 WINSPOOL.DRV

The old "now you don't see it, now you do". Notepad is running but I don't see it anywhere.

You can use this technique to run things that do not demand a console safely (after you run this notepad thing, its up to you to figure out how to KILL all of the notepads that are now running on your server, i used the nt resource toolkit kill command).

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Problem solved

Mudassar Shahzad, January 13, 2002 - 9:55 am UTC

Hello Tom,

First of all thanks for your solution. It did work. But it only works for Oracle 8.1.5; which I had asked for. I tried the same solution on Oracle 9i, but it doesnot work for Oracle 9i.

what dll???

Senthil, March 09, 2002 - 8:21 pm UTC

i am writing a script for hot backup. As my environment is, Oracle 8.0.5 on Windows NT, I am thinking of writing script on database side, That is, writing a stored procedure that sets/resets tablespace to backup mode and copies datafiles to a backup directory. I guess i will have to use Oracle's "ocopy" utility. And also, if i'd have to write script on database side, have to use external library to invoke "ocopy" utility. If its a simple copy command, i would have used kernel32.dll. But, to invoke ocopy, is there any dll available. i dont want to write my own library/dll to call the utility. Could you please provide some idea.
thanks,
senthil.

March 10, 2002 - 6:27 pm UTC

No, there is no DLL for ocopy -- and I would urge you to write your OWN C code to do this (invoke ocopy) instead of just calling kernel32.dll. You'll need to verify that the file sizes and such are correct after the copy -- just using the SYSTEM() call is too simplistic for something as important as a backup.

question again?

A reader, March 12, 2002 - 7:59 am UTC

Sorry Tom, to pull back the question again.

i have created a new dll, using Borland C++ compiler. i am able to register the dll on my database, but if i try to call the function am getting "unable to load dll function". Here is the code for easy reference...

#include <fstream.h>

#define BUFLEN 256

#ifdef WIN32COMMON
__declspec(dllexport)
#endif
long getfilesize(const char *name)
{
   ifstream ifs(name);
   if (!ifs)
      return -1;
   ifs.seekg(0, ios::end);
      return ifs.tellg();
// ifstream destructor will close the file
}

In sqlplus...
SQL> get shell
  1  create or replace library mylib_getfilesize as 'd:\users\library\devl.dll';
  2  /
  3  create or replace function mylib_getfilesize_func(cmd in varchar2)
  4  return binary_integer as
  5  external library mylib_getfilesize
  6  name "getfilesize"
  7  language c
  8  parameters (cmd string, return long);
  9  /
 10  var r number;
 11  exec :r := mylib_getfilesize_func('d:\a.log');
 12* print r
 13  .

SQL> @shell

Library created.


Function created.

BEGIN :r := mylib_getfilesize_func('d:\a.log'); END;

*
ERROR at line 1:
ORA-06521: PL/SQL: Error mapping function
ORA-06522: Unable to load symbol from DLL
ORA-06512: at "SCOTT.MYLIB_GETFILESIZE_FUNC", line 0
ORA-06512: at line 1



         R
----------

For testing purpose, i am using my own database, which is Oracle 8.1.7 personal edition.

your comments will definitely help me...

thanks,
senthil.

March 12, 2002 - 8:04 am UTC

You are probably compiling in C++ mode -- the name "getfilesize" is being 'mangled' into something else.

Compile in C mode, not C++. Turn off the name mangling.

RE: Senthil's code

Mark A. Williams, March 12, 2002 - 11:59 pm UTC

Tom:

PMFJI...

I realize you did not write the code for the external library in question, but I wanted to pass along a quick observation about that code...

I believe the following line will cause an unintended result:

ifstream ifs(name);

This code allows the default values for the second and third parameters of the constructor to be taken. The second parameter will default to 'ios::in', causing an empty file to be created if it does not already exist. Therefore, the "if (!ifs)..." will succeed, even though that is probably not desirable in this case.

I believe the code would be better as something like:

ifstream ifs(name, ios::in | ios::nocreate);

It has been awhile since I was coding, but I think I am remembering this correctly - if not sorry, and just tell me to sod off! Plus, I don't have a Borland compiler to verify this with...

- Mark

March 13, 2002 - 7:15 am UTC

could be -- I never really used C++, finding C more then capable of doing whatever needed be done ;)

RE: C

Mark A. Williams, March 13, 2002 - 8:35 am UTC

Tom:

Agree with you in regard to C... The code presented by the poster probably would be better off in straight C anyway. Now he's going to have to wrap "extern C..." around stuff and/or make sure he's not using name mangling as you've already pointed out.

I have found (in the past anyway) people think they are doing OO just because they use C++... anyway, that's another story. I just wanted to let the poster know he may be getting some unexpected results...

Thanks!

- Mark

Senthil, March 13, 2002 - 9:09 pm UTC

Mark-

You could be right. As i am just trying to create library(dll) and use it my procedure. If that started working, then i should be concentrating on the library side. And also, i stole the code from one of the forums, so, might be having a lot of flaws. i should appreciate you for reviewing the code. Thank you.

thanks,
senthil.

external procedure

abeda, December 02, 2003 - 5:31 am UTC

Hi,

While i'm trying to execute external procedure,
Following error has been occured:

SQL> create library libgettz as '/peur/cre/ora0816/gettz.a';   2  /                                                        
                                                             
Library created.                                              
                                                              
SQL> create or replace function gettz                         
  2  return char is external                                  
  3  library libgettz                                         
  4  name "gettz"                                             
  5  language C;                                              
  6  /                                                        
                                                             
Function created.                                             
                                                             
SQL> set serveroutput on                                      
SQL> select gettz from dual;                                  
select gettz from dual                                        
       *                                                      
ERROR at line 1:                                              
ORA-06521: PL/SQL: Error mapping function                     
ORA-06522: dlsym(): symbol not found                          
ORA-06512: at "SYSTEM.GETTZ", line 0                          
ORA-06512: at line 1                                          
--------------------    

Please suggest us.

Thanks in advance!

December 02, 2003 - 8:39 am UTC

you don't have a function gettz in your library according to the error message.

You are using a ".a" (archive), it should be a ".so" (shared object).

If you have my book "Expert one on one Oracle" -- i spent alot of time going over everything about extprocs.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Problem solved

what dll???

question again?

RE: Senthil's code

RE: C

external procedure

More to Explore

PL/SQL demos

PL/SQL docs

Calling External Procedures

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Problem solved

what dll???

question again?

RE: Senthil's code

RE: C

external procedure

More to Explore

PL/SQL demos

PL/SQL docs