Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Question 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.

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    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

  3. #3
    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
    NAME "GetConfig"

    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 :-)

    Please 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts