Not all params defined..

[zarkwizard]

I have a case where a Stored Proc doesn't require any params.

The issue is I don't know how I am supposed to generate the sql for the call.. For ones that we use all the params it's not an issue, but in the case of having a set of params, and a return cursor, I only want the return cursor, and I have no params to pass.

an example :

someproc(p1 in number,
p2 in number,
p3 in number,
OutCursor out)

Now the sql block, if I was using all the params would look like the following:

SQL.Add('begin '+
'someproc('+
'p1 => 1,'+
'p2 => 2,'+
'p3 => 3,'+
'OutCursor => :OutCursor'+
'); '+
'end;');

Then I can assign my variables by using the following :

DeclareVariable('p1', otFloat);
SetVariable('p1', 10);
DeclareVariable('p2', otFloat);
SetVariable('p2', 20);
DeclareVariable('p3', otFloat);
SetVariable('p3', 30);
DeclareVariable('OutCursor', otCursor);
SetComplexVariable('OutCursor', nil);

My question is what if I don't want to pass in p1, p2, p3? If I don't do a declare the component complains, if I leave them out of the sql block it complains.

If I set the val to nil for the floats it complains (SetVariable('p1', nil) ) .. I am not sure how I am suppose to omit params I don't require.

Anyone know how to set a param to nil(null)? So it isn't required, or how the SQL block is suppose to look if all I wanted was a cursor and none of the params?

Any help would be great.

Thanks in advance,

Jeff

 

[Marco Kalter]

If you want to omit parameters, you should probably make them optional in the PL/SQL declaration, by assigning a default value. For example:

procedure someproc(p1 in number,
                   p2 in number default null,
                   p3 in number default null,
                   OutCursor out)

Now you can call it like this:

SQL.Add('begin '+
        '  someproc('p1 => :p1,'+
                    'OutCursor => :OutCursor'); '+
        'end;');

Finally, if you want to pass a null value to a parameter, you can do so as follows:

SetVariable('p3', Null);