02-26-04, 16:26 #1Registered User
- Join Date
- Feb 2004
Unanswered: Calling external routines from PL/SQL - strings!
I have a simple C dll function which takes for input two strings, and outputs the concatenation of those two strings:
char* mycfunc(char *x, char *y)
I've tested using this dll from another C program, and it works fine.
I created the Oracle library that references this dll. I also created the PL/SQL function that calls the external dll function. However, when I try to use this new PL/SQL function, I get back garbage:
select func('Hi','There') from dual;
However, I can get this to work if my dll does NOT return anything, and I use 3 parameters (one for the output). Then I create a PL/SQL procedure instead of function. Then I can say this:
-- print out NEWSTRING, which is the concatenation
BUT, I really need this to run as a function. I think the problem may stem from the fact that oracle has no varchar2 of a certain length to store the output to?
Anyway, this explanation may be a little sparse, but any ideas are appreciated. BTW, I can get PL/SQL functions to work with integers fine.
02-26-04, 17:12 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
here are a couple of possible solutions
1) use data type CHAR
2) write results to a file & suck the results in via UTL_FILE
02-26-04, 20:16 #3Drunkard
- Join Date
- Nov 2002
- Desk, slightly south of keyboard
You need to ensure your DLL returns the correct type. A 'C' definition of a string, or indeed of a char* is entirely different to an Oracle definition of a string (ie, how it is stored in memory). Check the Oracle docs for how it expects a 'C' string to be returned, and your particular compiler to ensure there is no under the hood 'trickery' going on.
Delphi code within the DLL.
function GetConfig: PChar; cdecl;
PChar, means pointer to a null terminated string, CDecl, means 'C' calling convention. There are other directives in the code to disable Delphi memory/string management etc, ie to make it conform to standard C conventions.
On the Oracle side, the EXTPROC call is
FUNCTION GetCfg RETURN VARCHAR2 IS
EXTERNAL LIBRARY REGISTER_PCK
Check the Oracle docs and your compiler docs to ensure you get an exact match on calling convention.
Ensure Oracle isn't trying to a free the memory already free'd by your DLL and that your DLL doesn't expect Oracle to free it while Oracle expects the DLL to free it.
I'm afraid it's a case of pouring over the Oracle docs on one side, and your compiler docs on the other. Once you have it right though, it is very rewarding. You have the power of any imaginable 'C' code on the host, running within your PL/SQL. Admittedly, you also have the power to completely screw up the server hosting Oracle, but that can be fun too :-)
BillPlease don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.