Re: Reading registry from Stored Procedure using DLL?

In article <86aaf91f.0106190524.42abdff6_at_posting.google.com>, pim.spierenburg_at_cmg.nl says...
>
>Hi y'all,
>
>This is my first post in this group and I hope if someone can come up
>with a sollution.
>
>I am trying to read some string values out of my registry (NT4) using
>a own developed DLL (in Visual Basic) which uses the ADVAPI32.DLL.
>
>The DLL works fine. Only when I try to use it from a stored procedure
>it doesn't work and generates the following error:
>"ERROR at line 1:
>ORA-01405: fetched column value is NULL"
>
>Somehow this doesn't work. Is is possible to retrieve values from the
>registry in a stored procedure?
>
>Cheers
>
>Pim

without seeing the code, I'll have to guess that you are not using an indicator variable. Without it -- an OUT parameter will throw a NULL fetched error.

Here is a small example in C showing what I mean:

#include <oci.h>

_declspec (dllexport)
int test(char * p_in)
{

    return 0;
}

_declspec (dllexport)
int test_with_indicator(char * p_in, short * p_in_indicator ) {

    *p_in_indicator = OCI_IND_NULL;
    return 0;
}

Now, when I run the following in sqlplus:

tkyte_at_TKYTE816> create or replace

  2      procedure test( p_in in out varchar2 )
  3      as
  4      language C
  5      name "test"
  6      library demoPassing
  7      parameters ( p_in  STRING );

Procedure created.

tkyte_at_TKYTE816>
tkyte_at_TKYTE816>
tkyte_at_TKYTE816> create or replace
  2      procedure test_with_indicator( p_in in out varchar2 )
  3      as
  4      language C
  5      name "test"
  6      library demoPassing
  7      parameters ( p_in  STRING, p_in INDICATOR short );

Procedure created.

tkyte_at_TKYTE816>
tkyte_at_TKYTE816> variable s varchar2(25)
tkyte_at_TKYTE816> exec test( :s );

*
ERROR at line 1:
ORA-01405: fetched column value is NULL

tkyte_at_TKYTE816> exec test_with_indicator( :s );

PL/SQL procedure successfully completed.

you can see the routine that does not provide a NULL indicator does not work -- you have no way to tell oracle whether the data being returned is NULL or NOT NULL. Add a null indicator (pointer to short int) to your routine.

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp