using Oracle 10g (maybe others, I don't know)
Writing Stored procs which have default values.

I had a bunch of code that had parameter definitions that looked like:

p1 in varchar2 := null,
p2 in number := null

These worked fine when the parameter was a varchar but for anything else it
would not work. I'm not sure what was being passed.
However if I coded

p1 in varchar2 DEFAULT null,
p2 in number DEFAULT null

everything worked fine. The documentation states that := should be use for
defining default numerics. something like

p2 in number := 7

which would seem to imply that null in place of 7 should be ok. But it's
not.

Is this an error in the documentation?

thanks

kjs

**** Post for FREE via your newsreader at post.usenet.com ****

Hi Kevin

> These worked fine when the parameter was a varchar but for anything else
it
> would not work.

What does this mean? The default value was not used, you get an error or
something else....

> everything worked fine. The documentation states that := should be use for
> defining default numerics. something like
>
> p2 in number := 7
>
> which would seem to imply that null in place of 7 should be ok. But it's
> not.
>
> Is this an error in the documentation?

According to the documentation both can be used.
http://download-west.oracle.com/docs...htm#sthref1788

Chris



-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Maybe using CONSTANT would help your situation......
Its unclear whether you meant parameter(can change) or constant (doesnt change)


cn_item_text_length CONSTANT NUMBER(2) := 69;
cn_item_amount_length CONSTANT NUMBER(2) := 19;

good luck

george


"Christian Antognini" <christian.antognini@trivadis.com> wrote in message news:<4111e4f3$1@post.usenet.com>...
> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Hi Kevin
>
> > These worked fine when the parameter was a varchar but for anything else
> it
> > would not work.
>
> What does this mean? The default value was not used, you get an error or
> something else....
>
> > everything worked fine. The documentation states that := should be use for
> > defining default numerics. something like
> >
> > p2 in number := 7
> >
> > which would seem to imply that null in place of 7 should be ok. But it's
> > not.
> >
> > Is this an error in the documentation?
>
> According to the documentation both can be used.
> http://download-west.oracle.com/docs...htm#sthref1788
>
> Chris
>
>
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.usenet.com
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

I can only presume that I was getting something else. The default value was
null and no value was passed in from the c# .Net program or the value was
explicitly set to null. However the condition failed when it should not
have. we generally code things like

create or replace procedure
( param in number := null
param2 in number DEFAULT null)
as
SELECT col1,col2
FROM sometable
WHERE ((param is null) OR (param = col1))
AND ((param is null) OR (param = col2))

In this case the version that had the param coded with the := behaved
differently than the one coded with DEFAULT.


"Christian Antognini" <christian.antognini@trivadis.com> wrote in message
news:4111e4f3$1@post.usenet.com...
> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Hi Kevin
>
> > These worked fine when the parameter was a varchar but for anything else
> it
> > would not work.
>
> What does this mean? The default value was not used, you get an error or
> something else....
>
> > everything worked fine. The documentation states that := should be use
for
> > defining default numerics. something like
> >
> > p2 in number := 7
> >
> > which would seem to imply that null in place of 7 should be ok. But it's
> > not.
> >
> > Is this an error in the documentation?
>
> According to the documentation both can be used.
>
http://download-west.oracle.com/docs...htm#sthref1788
>
> Chris
>
>
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.usenet.com
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=