| Using "IS NULL" on variables of type Record [message #154964] |
Wed, 11 January 2006 09:12  |
agsharad
Messages: 10
Registered: August 2005
|
Junior Member |
|
|
Hi,
I want to author a procedure that takes a record as parameter. As part of the processing, I need to check if a NULL was passed in. This seems to be something that PL/SQL does not allow:
PROCEDURE test (
p_some_rec IN type_some_rec
)
AS
BEGIN
IF p_some_rec IS NULL
THEN
RETURN;
END IF;
END;
It throws the following error:
PLS-00306: wrong number or types of arguments in call to 'IS NULL'
It does allow me to check "IS NULL" for an attribute/field of the record:
PROCEDURE test (
p_some_rec IN type_some_rec
)
AS
BEGIN
IF p_some_rec.first_attr IS NULL
THEN
RETURN;
END IF;
END;
But this is not desirable as it assumes that the attribute of choice (first_attr in this case) cannot be NULL for a valid record.
Please advise on how this check can be achieved.
Thanks.
|
|
|
|
| Re: Using "IS NULL" on variables of type Record [message #154966 is a reply to message #154964] |
Wed, 11 January 2006 09:25  |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
PL/SQL record variable is instantiated at the moment of declaration and shouldn't be compared with null. You can use object type declaration as an alternative:
SQL> declare
2 type rc is record (
3 file_attr number,
4 file_name varchar2(10)
5 );
6 rc_instance rc;
7 begin
8 if rc_instance is null then
9 dbms_output.put_line('NULL');
10 else
11 dbms_output.put_line('NOT NULL');
12 end if;
13 end;
14 /
if rc_instance is null then
*
ERROR at line 8:
ORA-06550: line 8, column 5:
PLS-00306: wrong number or types of arguments in call to 'IS NULL'
ORA-06550: line 8, column 2:
PL/SQL: Statement ignored
SQL> create type rc is object (file_attr number, file_name varchar2(10));
2 /
Type created.
SQL> declare
2 rc_instance rc;
3 begin
4 if rc_instance is null then
5 dbms_output.put_line('NULL');
6 else
7 dbms_output.put_line('NOT NULL');
8 end if;
9 end;
10 /
NULL
PL/SQL procedure successfully completed.
SQL> declare
2 rc_instance rc := rc(1,'Nothing');
3 begin
4 if rc_instance is null then
5 dbms_output.put_line('NULL');
6 else
7 dbms_output.put_line('NOT NULL');
8 end if;
9 end;
10 /
NOT NULL
PL/SQL procedure successfully completed.
Rgds.
[Updated on: Wed, 11 January 2006 09:30] Report message to a moderator |
|
|
|
| Re: Using "IS NULL" on variables of type Record [message #154968 is a reply to message #154966] |
Wed, 11 January 2006 09:38 |
agsharad
Messages: 10
Registered: August 2005
|
Junior Member |
|
|
I see. That is a neat solution. Thanks.
Only thing is that the record in question is used at a number of other places and leads to a ripple effect of changes. Leads to a question that might be a matter of preference - is there any guidance on choosing between a SQL Object Type and a PL/SQL Record Type?
Also, coming back to my original question. The procedure was:
PROCEDURE test (
p_some_rec IN type_some_rec
)
AS
BEGIN
IF p_some_rec IS NULL
THEN
RETURN;
END IF;
END;
In some cases I was intending to call the procedure like so:
So, the idea was never to declare the record at all (in some cases). If such call were to be made, I was hoping for the procedure's code to discern that a NULL was passed and handle it differently. Is that possible to do?
[Updated on: Wed, 11 January 2006 09:46] Report message to a moderator |
|
|
|
| Re: Using "IS NULL" on variables of type Record [message #154976 is a reply to message #154968] |
Wed, 11 January 2006 09:48 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
http://docs.nojabrsk.ru/sol10/B12037_01/appdev.101/b10807/05_colls.htm#i20716
| Quote: |
Records cannot be tested for nullity, or compared for equality, or inequality.
If you want to make such comparisons, write your own function that accepts two records as parameters and does the appropriate checks or comparisons on the corresponding fields.
|
One of the alternative is in the use of overloaded procedures:
SQL> create or replace package my_pkg
2 is
3 type rec_type is record (file_attr number, file_name varchar2(10));
4 procedure get_rec(rec rec_type);
5 procedure get_rec;
6 end;
7 /
Package created.
SQL> create or replace package body my_pkg
2 is
3 procedure get_rec(rec rec_type)
4 is
5 begin
6 dbms_output.put_line(rec.file_name);
7 end;
8
9 procedure get_rec
10 is
11 begin
12 dbms_output.put_line('nothing');
13 end;
14
15 end;
16 /
Package body created.
SQL> declare
2 r1 my_pkg.rec_type;
3 begin
4 r1.file_name := 'Name';
5 my_pkg.get_rec(r1);
6 end;
7 /
Name
PL/SQL procedure successfully completed.
SQL> exec my_pkg.get_rec;
nothing
PL/SQL procedure successfully completed.
Rgds.
|
|
|
|
| Re: Using "IS NULL" on variables of type Record [message #154984 is a reply to message #154976] |
Wed, 11 January 2006 10:12 |
agsharad
Messages: 10
Registered: August 2005
|
Junior Member |
|
|
Thanks for the link to the excellent reference guide. Appreciate your taking the time to spell out yet another option.
As far as I can tell, a SQL Object Type is generally preferable to a PL/SQL Record Type. The trade-off is some convenience - Object Types have to be declared outside PL/SQL and stored on the Oracle server. Does that make sense?
Thanks again!
Sharad
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
