A reader, November 21, 2001 - 7:38 pm UTC
hi tom
i don't have make.bat in
%oracle_home%\plsql\demo directoty
it's prcmake.bat
i read the text in side and it is telling
-- -----------------
Rem Use this script to create executables for the demo scripts with PL/SQL
Rem blocks embedded in C code. To create all executables, at the prompt type:
if (%1) == () goto usage
Rem MSVCDir is defined by running a batch file, vcvars32.bat,
Rem in Microsoft Visual Studio directory. You can find it using
Rem Windows Explorer.
Rem Environment Check:
if (%MSVCDir%) == () goto msvcdir_error
-- ---------
i don't have visual studio
I am running personal oracle 8.1.7 on windows 98
i want to call executable from pl/sql at my work.
so please respond as soon as possible
November 21, 2001 - 7:47 pm UTC
If you don't have MAKE.BAT you don't have a version of Oracle with extprocs. prcmake.bat doesn't make the extprocs. you need make.bat.
if you
dir ext*
in that directory -- you should see:
Directory of C:\oracle\plsql\demo
02/29/2000 02:03p 17,853 extproc.c
02/29/2000 02:03p 15,779 extproc.sql
if not, you don't have the extproc stuff.
I've never installed Personal Oracle -- I suppose there is a chance its "not there".
try downloading
</code>
http://asktom.oracle.com/~tkyte/extproc.tar <code>
I've tarred up (winzip will extract it) the source and make.bat. Lets start there and see what happens.
A reader, November 21, 2001 - 8:07 pm UTC
Tom
So FAST REPLY!!!!!!!!!!!!!!!!!!!!!!!!!!!
IT REALLY HELPS ME A LOT
CAN'T BELIEVE IT
I REALLY APPRICIATE IT
YOU ARE REALLY BOON TO DEVLOPER COMMUNITY
HAVE NICE THANKSGIVING
A reader, November 21, 2001 - 8:11 pm UTC
Another question.
External procedure in C is better
OR java stored procedure is better
(Consider Resources and perfomance of database)
November 22, 2001 - 8:42 am UTC
It is a double edged sword.
C will be faster, its closer to the machine.
Java will be a little slower but it is approaching the speed of C with regards to performance when NCOMP'ed (natively compiled)
C will run outside the address space of the server -- it will take another process. Java runs inside the database, in the same address space. No extra process.
Calling a java stored procedure for the first time in a session takes a bit of time (jvm startup). Its getting better with each release but its still rather large the first time. A C extproc is not. This mostly counts in a stateless environment where "for the first time in a session" happens very often.
My mantra in general is:
o if you can do it in SQL, do it
o if not, do it in PLSQL, if you cannot do it there (eg: ftp'ing a file, writing a binary file, etc)
o do it in Java in a simple stored procedure
o if Java won't do it (you have a 3rd party api, existing C code) do it in C as an extproc.
make.bat and MS .net
Jeff Yuan, June 18, 2003 - 12:41 am UTC
First of all, thank you very much for your excellent coverings on extproc in your book and here.
I believe the existing make.bat (from Oracle 9.0.1 installation) works with MS Visual Studio. Now in my box, I have only MS Visual Studio .NET installed, including MS Visual C++ .NET. I can not have MS Visaul C++ in same box.(?)
When I try to run make.bat, I doesn't work. I don't know much about .NET. Could you please look into this? Is some modification on the make.bat necessary to find the C++ compiler?
Regards,
Jeff
~~~~~~~~~~~~~~
C:\oracle\plsql\demo>make.bat
.
Environment variable MSVCDIR must be set before running this batch file.
Please run vcvars32.bat from MS Visual Studio directory.
.
C:\>cd program files\microsoft visual studio .net
C:\Program Files\Microsoft Visual Studio .NET>cd vc7\bin
C:\Program Files\Microsoft Visual Studio .NET\Vc7\bin>vcvars32.bat
C:\Program Files\Microsoft Visual Studio .NET\Vc7\bin>"C:\Program Files\Microsof
t Visual Studio .NET\Common7\Tools\"vsvars32.bat
Setting environment for using Microsoft Visual Studio .NET tools.
(If you also have Visual C++ 6.0 installed and wish to use its tools
from the command line, run vcvars32.bat for Visual C++ 6.0.)
C:\>cd oracle\plsql\demo
C:\oracle\plsql\demo>make
Files\Microsoft was unexpected at this time.
June 18, 2003 - 6:04 pm UTC
looks like there might be an issue with spaces in the filenames.
maybe you can use the 8.3 filenames instead. I don't have access to any windows machines, sorry.
vc
Aladdin, September 17, 2003 - 10:44 am UTC
sir how could i use external dll files not made by c language but with visual c ++ or vb
September 17, 2003 - 4:29 pm UTC
you just need to know the "c prototype" they use. then you can map to it.
Need a clarification
Sanjay Jha, October 08, 2003 - 1:03 pm UTC
For using External Proc..do we need any additional piece of software? I am not able to run make.bat. It complains:
C:\oracle\ora81\plsql\demo>make
'cl' is not recognized as an internal or external command,
operable program or batch file.
I referred metalink doc id (70110.1)and it says, I need to run vcvars32.bat before running make.bat:
**********************************************************
3. Use the make.bat available in the %ORACLE_HOME%\rdbms\extproc directory. You need to run vcvars32.bat file before running this batch file. This will create a dll file.
**********************************************************
However, I did not see that step mentioned in your book, nor do I see a mention of the fact that I need any extra piece of software than Oracle RDBMS. Do I need Visual Studio to be installed, since I did not see vcvars32.bat, cl.exe etc. anywhere in my Oracle folder?
October 09, 2003 - 3:42 pm UTC
you need a C compiler, yes. In order to compile C, you need a C compiler.
You do not need a C compiler if you have a DLL already.
You do need a C compiler to turn C into a DLL on windows.
Demo C-Based External Procedure -- Error ORA-03114
Cheema, January 25, 2005 - 10:02 am UTC
Hi Tom,
I followed Chapter 18 of your "One-on_One" and was able to compile and link the demo program. However, when I call it from demopack.demo_procedure I get the following errors:
SQL> @extproc
Package created.
No errors.
Package body created.
No errors.
ERROR:
ORA-03114: not connected to ORACLE
BEGIN demopack.demo_procedure; END;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Is there anything which I may have missed out.
Thanks
January 25, 2005 - 10:22 am UTC
please contact support -- that is the sample that is shipped with the server, if you followed the setup exactly and that happens -- there is a problem.... (the reason I put the shipping example in the book like that is -- in the event of somehting going horribly wrong -- support would be able to work with you, you are using the standard sample....)
ORA-28595: Extproc agent : Invalid DLL Path
rose, October 04, 2006 - 10:29 am UTC
Hi Tom,
I am using a external procedure , but when I execute the function in pl/sql, it returns this error:
"ORA-28595: Extproc agent : Invalid DLL Path"
My listener.ora is:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = ORAR9I.us.oracle.com))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.85)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = OraR9i)
(ORACLE_HOME = C:\Ora9i)
(SID_NAME = OraR9i)
)
(SID_DESC =
(SID_NAME = extproc)
(ENVS=LD_LIBRARY_PATH=E:\Formt\source)
(ORACLE_HOME = C:\Ora9i)
(PROGRAM = extproc)
)
)
-----------------
My tnsnames.ora :
extproc_connection_data =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = orar9i.us.oracle.com))
(CONNECT_DATA = (SID = extproc))
)
-----------
My library is :
create or replace library lib1 as
'E:\Formt\source\testdll.dll';
/
--------------
My function in pl/sql is:
create or replace function vdig (
dig1 in varchar2, dig2 in varchar2) return number as
external library lib1
name "ComparDig"
language c
parameters (dig1 string,dig2 string);
/
----------------
I call the externa procedure this way:
variable ret number;
variable d1 varchar2(100);
variable d2 varchar2(100);
begin
:d1 := '20000000496E59046B58E2036A46B';
:d2 := '20003200498739046B58E2036A46B';
:ret := vdig (:d1, :d2);
end;
/
and this returns
"ORA-28595: Extproc agent : Invalid DLL Path"
In these steps, where are the errors?
Thank you!
October 04, 2006 - 5:15 pm UTC
when you searched for 28595 on this site....
what did you see?
(before you added the 28595 to this message, you would have seen ONE hit, now you'll see two, this one and.......)
28595
rose, October 04, 2006 - 7:11 pm UTC
Tom,
I read the document, altered my listener.ora,
but I didn't yet got success! The error ora-28595 continues!
listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = orar9i.us.oracle.com))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.85)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = OraR9i)
(ORACLE_HOME = C:\Ora9i)
(SID_NAME = OraR9i)
)
(SID_DESC =
(SID_NAME = extproc)
(ENVS="EXTPROC_DLLS=E:\Formt\source\testdll.dll,
LD_LIBRARY_PATH=E:\Formt\source")
(ORACLE_HOME = C:\Ora9i)
(PROGRAM = extproc)
)
)
could you help me please?
October 04, 2006 - 7:40 pm UTC
you did restart the listener to get the new configuration read right?
28595
rose, October 05, 2006 - 8:21 am UTC
-After I modified listener.ora, I restarted listener, I recreate library, recreate the function that calls the .dll, and then I execute the test.
-The .dll isn't mine.
-OracleOra92Agent is running.
-extproc.exe must be running? Listener starts up it?
October 05, 2006 - 8:31 am UTC
listener runs the exproc
ld library path is a unix thing, what's up with that?
AND: most important: e: is a LOCAL (direct attach, not network) disk right?
calling .dll
rose, October 05, 2006 - 10:20 am UTC
- e: is other partition of local disk. Oracle is in c:.
- I deleted ld_library_path from listener.ora
One question:
-in listener.ora, when
a) (ENVS="EXTPROC_DLLS=E:\formt\source"), my test results ora-28595-Invalid DLL Path.
but when
b) (ENVS="EXTPROC_DLLS=E:\formt\source\<filename.dll>"), my test results ORA-28575: unable to open RPC connection to external procedure agent
the a) option is OK, right?
October 05, 2006 - 1:16 pm UTC
you specify the dll name.
if you are getting the 28575, you have solved the dll problem.... type 28575 into search..
Your External Procdure in your first book
CG, December 01, 2006 - 2:35 pm UTC
Tom,
I am currently trying to create a Pro C file on 9i AIX 64 bit. Cant get help from my DBAs on looking for proc.mk so I have used demo_proc.mk to build my file. Well it worked but I get a coredump.
Well I wanted to get more info about what was going on in code so I got out your book and started looking at the code you use to start out your external procs.
I noticed the extproc.ini file referenced in your code. I cant find this file on my AIX server to use your code.
Will your code work for 9i EE AIX 64bit?
BTW I downloaded the 10g companion cd cpio hoping to find proc.mk in it and it was not there. Im waiting for my metalink account to be approved so I can get support to send me the file.
Thanks for your time.
December 01, 2006 - 9:47 pm UTC
just type it in - extproc.ini is just an ini file, small file with "variable=value" pairs. i created it in the book.
Executing external procedure('c' language)" in version 10g
HVN, January 10, 2008 - 1:38 pm UTC
Is there is a different way of executing external procedure in 'C' language from PL/SQL in Oracle10gR2? Or is it still the same way as in version 8i?
January 10, 2008 - 2:47 pm UTC
in 10g you can
a) call an external procedure
b) use java
c) use dbms_scheduler to create and schedule a job that runs an external program
External procedure on a different host?
Rich, February 26, 2009 - 3:10 pm UTC
Hi Tom,
We are running 9.2.0.8 on Windows 2003. Is it possible to call an external procedure located on a different server than the database server?
more precisions...
Rich, February 26, 2009 - 6:31 pm UTC
Hi Tom,
a little more description might help..
--Situation now.
Database 9.2.0.8 running on windows 2000. In our database, there is a procedure which call a Microsoft api running locally on the same server using a database link (in fact, a loopback db link).
--Situation to go.
Database must be migrated on Linux, impossible to find linux api which would do the same trick (as the microsoft api). The client needs a quick fix (he is planning on removing the dependancy, but needs a temporary fix)
My questions:
Can we move the api on the app server and have it remotely called by the database?
Rich
March 03, 2009 - 8:25 am UTC
ah, this is doable.
leave database install on windows - just the database INSTALL, you can take all of the data away (you'd need that anyway to get the extproc code, so this is OK)
put the new database on linux. Create a dblink back to windows instance and call the stored procedure that runs the external process.
Need DEMO to understand extproc
Vitaliy, June 12, 2018 - 8:52 am UTC
Hi Tom! You wrote
-- try downloading
-- http://asktom.oracle.com/~tkyte/extproc.tar
-- I've tarred up (winzip will extract it) the source and
-- make.bat. Lets start there and see what happens.
This link don't work, can You tarred up the sorce for me?
June 13, 2018 - 2:29 am UTC
It's been lost over time, but I'd suggest this one instead. Go to our presentations and search for "Expert One-on-One source code".
That's the code from Tom's book, which contained a whole chapter on external calls.