CODEWITHSUNDEEP
c++sqloracle-databaseplsql
John
John

Reputation:

PL/SQL - setting null value

I have seen that there is a NVL function for PL/SQL that substitutes a value when null is encountered.
But what if I want to set a field to NULL, e.g. 

EXEC SQL UPDATE mytable SET myfield=NULL WHERE otherValue=1;

When I run this with C++ on HPUX, 0L is used for null while on Linux the statement fails with illegal value.

Is there a generic Oracle null value/method I can use?

Upvotes: 2

Views: 1229

Answers (5)

EvilTeach
EvilTeach

Reputation: 28882

The fact you are using an EXECUTE SQL statement, says that you are using Oracles Pro*C in a C or C++ program.

If the preprocessor is doing some translation on the NULL, then you are missing a piece of the compile procedure.

Prior to compilation, you need to run the source code though the Pro*C preprocessor. It translates the EXEC SQL statements, into a series of structures and function calls, that the c/c++ compiler can compile.

This link should help with some of the concepts.

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48151

Oh, now I realize what must be happening. The C++ system headers have #define NULL 0L (in HPUX) and so the hardcoded NULL is being replaced with that value by the preprocessor.

Several ideas:

  • Simply change the case of NULL in the SQL statement. Oracle doesn't care about the case, and if it doesn't match the defined version exactly it won't get replaced. Of course they might have defined NULL, Null, null, etc. all to the same value.
  • Find a way to disable the definition of NULL around that line of code. I assume there's a way to do this but I don't really know much about preprocessor directives
  • Try using an empty string instead of the literal NULL. Oracle should convert it to a NULL.
  • Store the text of your statement in a configuration file and read it into a string. This way it won't actually be in the code so the preprocessor won't modify it.
  • Write a function in Oracle that simply returns NULL, and use that function in your statement instead of the NULL literal.

Upvotes: 4

angus
angus

Reputation: 2367

NULL should work as-is, but you could try with an indicator variable as explained here: http://download.oracle.com/docs/cd/B10500_01/appdev.920/a97269/pc_06sql.htm#424

Upvotes: 1

John
John

Reputation:

Yes - the problem is the way NULL is expanded in C++. For informix there is rsetnull that sets the varialble to the appropriate value. Is there anything like this for oracle?

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48151

NULL is the correct value in Oracle. I have no experience in calling Oracle from C++ so I don't know what the cause of your issue is, but it must be an issue on the client side. If you were to run that statement through Oracle's SQLPlus it should do what you want.

Upvotes: 1

Related Questions