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

From: barry <bbulsara23_at_hotmail.com>
Date: 24 Aug 2003 04:22:39 -0700
Message-ID: <747f1dec.0308240322.2879fbc8@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 Sun Aug 24 2003 - 06:22:39 CDT