Packages and NULL parameters

[jpickup]

I'm new to DOA and and having a look at the classes generated by the package wizard.

How do I pass NULL values as parameters to packages procedures?

I imagine the answer will be "you can't because there is no NULL support in the Delphi types, you have to use a TOracleQuery....".

If so then at least I can cut and paste from the generated code

Thanks,
John.

[This message has been edited by jpickup (edited 07 September 2000).]

 

[Marco Kalter]

The Package Wizard has an "Always use variants as parameters" option. If you use this option, you can pass NULL values for parameters by using the Null or Unassigned variant value.

------------------
Marco Kalter
Allround Automations

 

[colinmillar]

Why not modify the package wizard so that nullable fields are passed as variants, and required fields are passed using native Delphi types? This would produce type-safe code and allow nulls to be set in the database without the mess of using variants for every parameter.

 

[Marco Kalter]

What exactly do you mean by 'nullable fields' and 'required fields' in the context of the package wizard?

------------------
Marco Kalter
Allround Automations

 

[colinmillar]

I mean that where the stored procedures' parameters are in the form:

procedure MyUpdateProc(ID in MYTABLE.ID%TYPE,
AREQUIREDFIELD in MYTABLE.REQUIREDFIELD%TYPE,
ANULLABLEFIELD in MYTABLE.NULLABLEFIELD%TYPE)

for a table defined by:

ID - number(8) - not null
AREQUIREDFIELD - varchar2(40) - not null
ANULLABLEFIELD - varchar2(40) - null

should import into Delphi as

procedure MyUpdateProc(ID : integer; const ARequiredField : string; const ANullableField : Variant);

This means that fields that (a) you can set null values in columns, (b) you get a degree of compile time type checking from Delphi, and (c) you avoid the mess and (albeit small) performance penalty of using variants everywhere.

I appreciate that this might not be useful in every situation, but having this as an option would be very useful.

Cheers,

Colin

 

[Marco Kalter]

Like you say, it's not useful in every situation. Even if the underlying column is required, this does not necessarily imply that the parameter cannot be null. It also requires a considerable amount of parsing of the PL/SQL code of the package specification (but we already do that).

I have added your suggestion to the enhancement requests though.

------------------
Marco Kalter
Allround Automations