Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Parameters
icon5.gif  Procedure Parameters [message #265234] Wed, 05 September 2007 17:45 Go to next message
kham2k
Messages: 34
Registered: May 2007
Member
Hello

I am new pl/sql. Can you please help me with procedures. I got a basic question;

I have a procedure with number of parameters, in, out and in/out. In the list of parameters i have some which can be null and some are not null-able. If I get a null value in those which are not null-ables I want to return error as parameter can't have NULL value. will the procedure take care of itself or do i have to specific it with some settings.

Thanks
y2k
Re: Procedure Parameters [message #265239 is a reply to message #265234] Wed, 05 September 2007 21:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Here is one way:

create or replace package notnulltest as
   subtype notnullint IS INTEGER NOT NULL;
end;
/

create or replace
procedure notnulltestproc (p IN notnulltest.notnullint) IS
begin
    dbms_output.put_line(p);
end;
/

begin
    notnulltestproc(null);
end;
/

    notnulltestproc(null);
                    *
ERROR at line 2:
ORA-06550: line 2, column 21:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored


Ross Leishman
Re: Procedure Parameters [message #265271 is a reply to message #265239] Wed, 05 September 2007 23:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Although it is a really nice way of doing it, it might be over your head if you are new to pl/sql, so I will provide you with an alternative way of doing it.
Note that the way Ross showed is superior to mine; it's just that this is easier to understand (because the use of subtypes is quite rare)

create or replace procedure test_proc
( p_not_nullable in varchar2
, p_nullable     in varchar2 default null
) is
begin
  if p_not_nullable is null
  then
    raise_application_error(-20000, 'Parameter p_not_nullable should not be null');
  end if;
  --<do your stuff>
end;


In here I did two things:
1) I provided a default value of null for the second parameter. This makes it possible to call the procedure with either one or two parameters
2) I explicitly test for p_not_nullable being not null.
icon14.gif  Re: Procedure Parameters [message #265580 is a reply to message #265271] Thu, 06 September 2007 12:06 Go to previous message
kham2k
Messages: 34
Registered: May 2007
Member
Thanks allot. This is great, highly appreciated!!
Previous Topic: ORA-29275 partial multibyte character
Next Topic: GL Accounting Segment Hierarcy query
Goto Forum:
  


Current Time: Sat Dec 10 05:22:06 CST 2016

Total time taken to generate the page: 0.26972 seconds