Re: EXTPROC query : HOWTO return a string/VARCHAR2 from a C/C++ EXTPROC into a PL/SQL function

Barry, I do not now how this is done with OCIString and have often mused over this. Someone more capable can post in this thread with an example. But if all you want is a VARCHAR2, try

extern "C" __declspec(dllexport)char* testFunction() {
  return (char*)"test char*";
}

CREATE OR REPLACE FUNCTION testf1
 RETURN VARCHAR2 AS
  EXTERNAL LIBRARY test0
  NAME "testFunction"
  LANGUAGE C;
/

Kind regards
Martin

bbulsara23_at_hotmail.com (barry) wrote in message news:<747f1dec.0308240322.2879fbc8_at_posting.google.com>...
> And here is my hand (SQL and C). I have cobbled together bits from the
> Oracle documentation, and the web, and can't get it going.
>
> I know the library 'test0' registration/use, no function name mangling
> etc is all okay as the first function (pls_max), copied from the
> Oracle docs, works. But the second function, returning a VARCHAR2,
> does not. This is the function I wrote. Do I know C? Well I'm learning
> it now so might be making a silly mistake.
> Barry
>
>
>
>
> SQL>
> SQL>
> SQL> CONN scott/tiger;
> Connected.
> SQL>
> SQL> DROP LIBRARY test0;
>
> Library dropped.
>
> SQL> CREATE LIBRARY test0 AS 'C:\oracle\plib\test0.dll';
> 2 /
>
> Library created.
>
> SQL>
> SQL> CREATE OR REPLACE FUNCTION pls_max(
> 2 x BINARY_INTEGER,
> 3 y BINARY_INTEGER)
> 4 RETURN BINARY_INTEGER AS
> 5 EXTERNAL LIBRARY test0
> 6 NAME "find_max" -- Name of function call. Quotes preserve
> lower case.
> 7 LANGUAGE C
> 8 PARAMETERS (
> 9 x long, -- stores value of x
> 10 x INDICATOR short, -- this is used to determine if x is a
> NULL value
> 11 y long, -- stores value of y
> 12 y INDICATOR short, -- this is used to determine if y is a
> NULL value
> 13 RETURN INDICATOR short ); -- need to pass pointer to return
> value's
> 14 -- indicator variable to determine if NULL.
> 15 --This means that my function will be defined as:
> 16 --long max(long x, short x_indicator,
> 17 -- long y, short y_indicator, short * ret_indicator)
> 18 /
>
> Function created.
>
> SQL>
> SQL> SELECT pls_max(1,2) FROM DUAL;
>
> PLS_MAX(1,2)
> ------------
> 2
>
> SQL>
> SQL>
> SQL> CREATE OR REPLACE FUNCTION testf1
> 2 RETURN VARCHAR2 AS
> 3 EXTERNAL LIBRARY test0
> 4 NAME "testFunction"
> 5 LANGUAGE C
> 6 PARAMETERS(RETURN INDICATOR,RETURN OCIString);
> 7 /
>
> Function created.
>
> SQL>
> SQL> SELECT testf1 FROM DUAL;
> SELECT testf1 FROM DUAL
> *
> ERROR at line 1:
> ORA-28576: lost RPC connection to external procedure agent
>
>
> SQL>
>
>
>
>
>
> #ifndef OCI_ORACLE
> # include <oci.h>
> #endif
> #ifndef ODCI_ORACLE
> # include <odci.h>
> #endif
>
> #include "test.h"
> #include "string.h"
> #include "stdio.h"
>
>
> extern "C"
> OCIString* __declspec(dllexport) testFunction(short *ret_indicator)
> {
> OCIEnv *envhp=(OCIEnv *) 0; /* env. handle */
> OCIError *errhp=(OCIError *) 0; /* err. handle */
>
> OCIString *objectName=(OCIString*)0;
> OCIStringAssignText(envhp,errhp,(CONST
> text*)"test",(ub2)4,&objectName);
> ret_indicator = (short*)OCI_IND_NOTNULL;
> return objectName;
> }
>
>
> #define NullValue -1
>
> extern "C"
> long __declspec(dllexport) find_max(long x,
> short x_indicator,
> long y,
> short y_indicator,
> short *ret_indicator)
> {
> /* It can be tricky to debug DLL's that are being called by a
> process
> that is spawned only when needed, as in this case.
> Therefore try using the DebugBreak(); command.
> This will start your debugger. Uncomment the following line and
> you can step right into your code.
> */
> /* DebugBreak(); */
>
> /* first check to see if you have any nulls */
> /* Just return a null if either x or y is null */
>
> if ( x_indicator==NullValue || y_indicator==NullValue) {
> *ret_indicator = NullValue;
> return(0);
> } else {
> *ret_indicator = 0; /* Signify that return value is not
> null */
> if (x >= y) return x;
> else return y;
> }
> }
Received on Mon Aug 25 2003 - 12:58:31 CDT