Home » SQL & PL/SQL » SQL & PL/SQL » Pass default values to procedure
Pass default values to procedure [message #36545] Tue, 04 December 2001 05:36 Go to next message
Poonamb
Messages: 16
Registered: October 2001
Junior Member
I have a procedure as below :

PROCEDURE SENDTODOC(
v_DOC_ID IN VARCHAR2 DEFAULT NULL,
v_SPIDER_DT IN DATE DEFAULT NULL,
v_CONCEPT_N IN VARCHAR2 DEFAULT NULL,
v_DOC_DT IN DATE DEFAULT NULL,
v_DOC_HEADLINE IN VARCHAR2 DEFAULT NULL,
v_OUT_STRING OUT VARCHAR2
) AS

BEGIN
-- Main Logic.
END;

Like this it works.
SENDTODOC(MyID, MyDate,NULL,MyDocDt,NULL,MyOutString);

But I have to call this as below, It does not work.
SENDTODOC(MyID, MyDate,,MyDocDt,,MyOutString);

What could be the problem? When I have mentioned default null, shouldn't a comma be enough to imply that the parameter is null?

Please help! Thanks in advance.
PB

----------------------------------------------------------------------
Re: Pass default values to procedure [message #36549 is a reply to message #36545] Tue, 04 December 2001 06:12 Go to previous messageGo to next message
tinel
Messages: 42
Registered: November 2001
Member
Hi
you can call the sendtodoc procedure like this:
SENDTODOC(v_DOC_ID => MyId, v_SPIDER_DT => MyDate, v_DOC_DT => MyDocDt, v_OUT_STRING => MyOutString)
Bye

----------------------------------------------------------------------
Re: Pass default values to procedure [message #36552 is a reply to message #36549] Tue, 04 December 2001 06:29 Go to previous messageGo to next message
Poonamb
Messages: 16
Registered: October 2001
Junior Member
I know that will work. My problem is that if the input field is null, I want it to come by as a comma. The front-end we use (dotnet/VB) has problems sending NULLs and when I do the manipulation of the stuff I receive, my procedure slows down.

Logically, the following should work. They know its null on the front-end and if they can only pass a comma, our lives become simpler and much faster! No one has to massage the data to send dummy values and for the procedure to parse these dummy values.

If anyone has any ideas having worked in conjunction with VB, please advise.

TIA.

----------------------------------------------------------------------
Re: Pass default values to procedure [message #36554 is a reply to message #36552] Tue, 04 December 2001 07:06 Go to previous messageGo to next message
Rob Baillie
Messages: 33
Registered: November 2001
Member
Try re-ordering the parameters:

create or replace PROCEDURE SENDTODOC(
v_OUT_STRING OUT VARCHAR2,
v_DOC_ID IN VARCHAR2 DEFAULT NULL,
v_SPIDER_DT IN DATE DEFAULT NULL,
v_CONCEPT_N IN VARCHAR2 DEFAULT NULL,
v_DOC_DT IN DATE DEFAULT NULL,
v_DOC_HEADLINE IN VARCHAR2 DEFAULT NULL
) AS

Or whatever, putting the likely NULL procedure calls at the end (most likely last, and then back)

This way, when you build you procedure call string you can just omit the last parameter (not forgetting to not put on the comma)

Though I think you misunderstand the previous post, which is as good as (if not better.... no, lets be honest, much better) than my own suggestion... you just don't put in the v_PARAMETERNAME=> for the parameter that's NULL.

There's no massaging of data there... if you're building the string anyway then surely you can build it however you want!

----------------------------------------------------------------------
Re: Pass default values to procedure [message #36570 is a reply to message #36552] Wed, 05 December 2001 00:06 Go to previous message
Rob Baillie
Messages: 33
Registered: November 2001
Member
Not knowing VB... maybe this is another dead-end

I suppose you could decide on an impossible (or highly unlikely) input value for each of your parameters (for example 01/01/0001 for your DATEs, '^^^^^^^invalid_string^^^^^^^' for you VARCHAR2s, etc). Then detect if you have zero length strings in any of your parameters in VB (it does have zero length strings doesn't it?), and if so then replace it with your impossible value relating to your parameter.

Then within the procedure you would replace any of your impossible values with NULL for your processing.

Easy way of changing IN parameter values inside is a procedure is to simply replace all internal references to the parameter with a local variable, and set that local variable to your input value or default value on entry.

Overall, it's not nice... but it might just work?

----------------------------------------------------------------------
Previous Topic: oracle resource not being released -2
Next Topic: DBMS_SQL.EXECUTE
Goto Forum:
  


Current Time: Wed Feb 26 04:44:48 CST 2020