Home » SQL & PL/SQL » SQL & PL/SQL » Default null parameters in stored procedure
Default null parameters in stored procedure [message #399689] Thu, 23 April 2009 08:36 Go to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
Hi

When I declare any of the argument as default null in argument list, can I omit totally when I call the procedure.
I'm using Oracle 10g XE client and if i omit default null argument I get 'Wrong number of parameters in call'

Regards
Re: Default null parameters in stored procedure [message #399690 is a reply to message #399689] Thu, 23 April 2009 08:41 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's not a case of can.
You HAVE to totally omit the parameter for the default to take effect.
Re: Default null parameters in stored procedure [message #399691 is a reply to message #399689] Thu, 23 April 2009 08:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Depends on how you are passing the parameters? by name / by position / mixed.

Further questions?? Post your code.

By
Vamsi
Re: Default null parameters in stored procedure [message #399692 is a reply to message #399691] Thu, 23 April 2009 08:52 Go to previous messageGo to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
Following is my code in vb.net. I'm calling procedure in package which ahs some parameters defined as default null. to pass parameters by name wher shud i make change in vb caall or in oracle procedure?

Dim iCatId As Integer = -1
commandLibrarian = New OleDbCommand("PLIB_LIBRARIAN_PKG.INS_PLIB_CATALOG_REGISTERED", connectLibrarian)
commandLibrarian.CommandType = CommandType.StoredProcedure
Try
commandLibrarian.Parameters.Add("P_CAT_ID", OleDbType.Integer).Value = catId
commandLibrarian.Parameters.Add("P_CATR_REG_COPY", OleDbType.VarChar).Value = regCopy
commandLibrarian.Parameters.Add("P_CATR_REG_LABEL", OleDbType.VarChar).Value = regLabel
commandLibrarian.Parameters.Add("P_LOCN_ID", OleDbType.Integer).Value = locnId
commandLibrarian.Parameters.Add("P_CATR_ACQUIRED_DATE", OleDbType.VarChar).Value = acquiredDate
commandLibrarian.Parameters.Add("P_MTYPE_ID", OleDbType.Integer).Value = mTypeId
commandLibrarian.Parameters.Add("P_CATR_MEDIA_COMMENTS", OleDbType.VarChar).Value = media_comments
commandLibrarian.Parameters.Add("P_CATR_WITH_DIGITAL", OleDbType.Integer).Value = withDigital
commandLibrarian.Parameters.Add("P_CATR_ORIGINAL", OleDbType.VarChar).Value = original
commandLibrarian.Parameters.Add("P_CATR_LINK", OleDbType.VarChar).Value = link
commandLibrarian.Parameters.Add("P_CATR_CREATED_BY", OleDbType.VarChar).Value = createdBy
commandLibrarian.Parameters.Add("P_CATR_CREATED_DATE", OleDbType.Date).Value = createdDate
commandLibrarian.Parameters.Add("P_CATR_CHECKOUT", OleDbType.VarChar).Value = checkout

Dim param As New OleDbParameter("P_CATR_ID", OleDbType.Integer)
param.Direction = ParameterDirection.Output
commandLibrarian.Parameters.Add(param)

connectLibrarian.Open()
commandLibrarian.ExecuteNonQuery()
connectLibrarian.Close()
iCatId = param.Value

Catch oraEx As OleDb.OleDbException
objErrorLog.writeError(oraEx)
iCatId = -1

Catch ex As Exception
MsgBox(ex.Message)
objErrorLog.writeError(ex)
iCatId = -1
End Try

Return iCatId
Re: Default null parameters in stored procedure [message #399693 is a reply to message #399689] Thu, 23 April 2009 08:55 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Based on your code (admittedly I don't know vb) I would assume you are passing the parameters by name.

It would help if you posted the procedure definition (in code tags)
Re: Default null parameters in stored procedure [message #399694 is a reply to message #399693] Thu, 23 April 2009 08:59 Go to previous messageGo to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member

procedure INS_PLIB_CATALOG_REGISTERED (
   P_CAT_ID              in number,
   P_CATR_REG_COPY       in varchar2,
   P_CATR_REG_LABEL      in varchar2,
   P_LOCN_ID             in number,
   P_CATR_ACQUIRED_DATE  in varchar2 default null,
   P_MTYPE_ID            in number,
   P_CATR_MEDIA_COMMENTS in varchar2 default null,
   P_CATR_WITH_DIGITAL   in number,
   P_CATR_ORIGINAL       in number,
   P_CATR_LINK           in varchar2 default null,
   P_CATR_CREATED_BY     in varchar2,
   P_CATR_CREATED_DATE   in varchar2,
   P_CATR_CHECKOUT       in number,
   P_CATR_ID             out number     
) is

begin

  insert into PLIB_CATALOG_REGISTERED (CAT_ID, CATR_REG_COPY, CATR_REG_LABEL, LOCN_ID, CATR_ACQUIRED_DATE, CATR_REG_DATE, MTYPE_ID,
CATR_MEDIA_COMMENTS, CATR_WITH_DIGITAL, CATR_ORIGINAL, CATR_LINK, CATR_CREATED_BY, CATR_CREATED_DATE, CATR_MODIFIED_BY, CATR_MODIFIED_DATE,
CATR_CHECKOUT, U_NAME, CATR_RETURN_DATE) 
values (P_CAT_ID, 
P_CATR_REG_COPY, 
P_CATR_REG_LABEL, 
P_LOCN_ID, 
P_CATR_ACQUIRED_DATE  ,
to_date(sysdate, 'YYYY-MM-DD'), 
P_MTYPE_ID,
P_CATR_MEDIA_COMMENTS, 
P_CATR_WITH_DIGITAL, 
P_CATR_ORIGINAL, 
P_CATR_LINK, 
P_CATR_CREATED_BY, 
to_date(sysdate, 'YYYY-MM-DD'), 
null, 
null, 
P_CATR_CHECKOUT, 
null, 
null);
select max(catr_id) into P_CATR_ID from PLIB_CATALOG_REGISTERED;

end INS_PLIB_CATALOG_REGISTERED;
Re: Default null parameters in stored procedure [message #399695 is a reply to message #399689] Thu, 23 April 2009 09:09 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not all of your datatypes match.
Re: Default null parameters in stored procedure [message #399701 is a reply to message #399689] Thu, 23 April 2009 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>to_date(sysdate, 'YYYY-MM-DD'),
WHY are you using TO_DATE on SYSDATE which is already a DATE datatype?????
Re: Default null parameters in stored procedure [message #399702 is a reply to message #399689] Thu, 23 April 2009 09:48 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should probably be using a RETURNING clause for this:
select max(catr_id) into P_CATR_ID from PLIB_CATALOG_REGISTERED;
Re: Default null parameters in stored procedure [message #399708 is a reply to message #399702] Thu, 23 April 2009 11:04 Go to previous messageGo to next message
somdeep2k9
Messages: 4
Registered: April 2009
Junior Member
Using Function?
Re: Default null parameters in stored procedure [message #399709 is a reply to message #399708] Thu, 23 April 2009 11:06 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
somdeep2k9 wrote on Thu, 23 April 2009 17:04
Using Function?


I have no idea what you mean Confused
Re: Default null parameters in stored procedure [message #399712 is a reply to message #399709] Thu, 23 April 2009 11:14 Go to previous messageGo to next message
somdeep2k9
Messages: 4
Registered: April 2009
Junior Member
You should probably be using a RETURNING clause for this -Did you mean by using OUT parameter?


Also want to know how a procedure returned values can be passed to another procedure?

whaetever result I can get from one procedure ,I want to pass it as a parameter to another procedure B

Re: Default null parameters in stored procedure [message #399714 is a reply to message #399712] Thu, 23 April 2009 11:23 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
somdeep2k9 wrote on Thu, 23 April 2009 17:14
You should probably be using a RETURNING clause for this -Did you mean by using OUT parameter?


I meant use a RETURNING clause to return the value from the insert statement into the out parameter.

Quote:

Also want to know how a procedure returned values can be passed to another procedure?

whaetever result I can get from one procedure ,I want to pass it as a parameter to another procedure B




So just pass it?
If you're having problems doing this, start a new thread, answering multiple peoples problems on the same thread is just going to be confusing.
Re: Default null parameters in stored procedure [message #400094 is a reply to message #399714] Sun, 26 April 2009 00:49 Go to previous messageGo to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
my nls_date_format is RR-MM-DD
So to change date format i'm using to_date for sysdate also.
I tried to change nls_date_format but attempt was unsuccessful
Re: Default null parameters in stored procedure [message #400095 is a reply to message #399689] Sun, 26 April 2009 01:02 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>So to change date format i'm using to_date for sysdate also.
>I tried to change nls_date_format but attempt was unsuccessful


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Do not explain or describe.
Use CUT & PASTE to show what exactly what you are doing.
Re: Default null parameters in stored procedure [message #400096 is a reply to message #400094] Sun, 26 April 2009 02:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
NewToOracle10g wrote on Sun, 26 April 2009 07:49
my nls_date_format is RR-MM-DD
So to change date format i'm using to_date for sysdate also.
I tried to change nls_date_format but attempt was unsuccessful

Dates in the database have no format, as long as they have a datatype DATE. Only when you convert them to strings, whether you do it implicitly by displaying them, or explicitly by using to_char, they get a format.
SYSDATE is a date, and as such has no format. If you do a to_date on it, Oracle expects a string as parameter, so it will first do an implicit conversion from date to string, before applying your to_date.
Re: Default null parameters in stored procedure [message #400097 is a reply to message #400096] Sun, 26 April 2009 02:13 Go to previous message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
thanx i'll check that
Previous Topic: Table Join
Next Topic: query runs fast but record count is very slow
Goto Forum:
  


Current Time: Sat Dec 03 01:23:02 CST 2016

Total time taken to generate the page: 0.30272 seconds