When invoking a PL/SQL procedure or function, you can specify the value of its parameters using either positional, named, or mixed notation. Let’s review what each notation means and then demonstrate the best way to add new parameters to an existing subprogram without breaking existing code.
Consider this simple package as an example:
CREATE OR REPLACE PACKAGE ed_test_pkg
AS
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param1_out OUT NUMBER
);
END ed_test_pkg;
/
CREATE OR REPLACE PACKAGE BODY ed_test_pkg
AS
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param1_out OUT NUMBER
)
AS
BEGIN
param1_out := param1_in + param2_in;
END proc1;
END ed_test_pkg;
/
Positional Notation:
When you invoke a procedure using positional notation, you specify the parameters in the same order as the procedure declares them. For example:
DECLARE
l_param1_out NUMBER;
BEGIN
ed_test_pkg.proc1 (1, 2, l_param1_out);
DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
END;
Named Notation:
When you invoke a procedure using named notation, you specify the name and value of each parameter, using the association operator =>. The order of parameters is irrelevant. For example:
DECLARE
l_param1_out NUMBER;
BEGIN
ed_test_pkg.proc1 (param2_in => 2,
param1_out => l_param1_out,
param1_in => 1
);
DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
END;
Mixed Notation:
When you invoke a procedure using mixed notation, you start with positional notation, then use named notation for the remaining parameters. For example:
DECLARE
l_param1_out NUMBER;
BEGIN
ed_test_pkg.proc1 (1, param2_in => 2, param1_out => l_param1_out);
DBMS_OUTPUT.put_line ('param1_out = ' || l_param1_out);
END;
Which one to use?
After this quick notations refresher, the question is which one to choose as your preferred style when invoking PL/SQL programs? For me, I prefer, and almost always use, the named notation, and you should too. OK, I admit there is more typing involved in named notation, but it is easier to read and maintain than the other two. Moreover, if the program’s parameter list changes, you avoid changing the code that invokes the program using named notation.
Adding a new parameter
Speaking of changing the parameter list, let’s say you have to add a new param3_in IN parameter to the procedure ed_test_pkg.proc1. Which one of the following two approaches do you use?
Approach 1:
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param3_in IN NUMBER DEFAULT 1,
param1_out OUT NUMBER
);
Approach 2:
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param1_out OUT NUMBER,
param3_in IN NUMBER DEFAULT 1
);
As you know, adding DEFAULT to the IN parameter means that you do not have to pass in a value to this parameter, and if you don’t provide a value, the default value is used.
Now, you may be tempted to keep all the IN parameters together, however, the second approach is better. Adding the parameter to the end of the parameter list is safer. Let me show you why.
Let’s say you have a program that calls ed_test_pkg.proc1 using the positional notation. When you add a new IN parameter using the first approach, and if you do not make any further modifications to the code, you’ll get an error:
SQL> DECLARE
2 l_param1_out NUMBER;
3 BEGIN
4 ed_test_pkg.proc1 (1, 2, l_param1_out);
5 DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
6 END;
7 /
ed_test_pkg.proc1 (1, 2, l_param1_out);
*
ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00306: wrong number or types of arguments in call to 'PROC1'
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored
But, you will not get this error if you use approach 2 or if you use the named notation when calling ed_test_pkg.proc1.
Conclusion
So, in general, as a best practice, always use named notation when invoking PL/SQL procedures and functions. To avoid breaking existing code, add new parameters with DEFAULT values to the end of the parameter list of existing procedures or functions.
Source and Resources:
Do you think there is a point in arranging an Obfuscated SQL Contest, modeled after the infamous Obfuscated C Contest? Chen Shapira asked. I replied: Great idea! So, welcome to the first ever Oracle Community contest.
To learn more, or to participate, proceed to this page on OracleCommunity.net.
Filed in Oracle with Comments Off | Tags: contest, oraclecommunity, sqlIn the SQL Developer group on OracleCommunity.net, both Chris and Chen complained about the new SQL Developer’s automatic code completion and how it could freeze the entire editor while waiting for the popup to appear.
I have experienced this behavior too. It is really frustrating when it happens. The solution, however, is really simple. In SQL Developer, go to Tools > Preferences > Code Insight and uncheck both “Enable Auto-Popup” check boxes as seen in the following screenshot:

Now, instead of letting it load automatically, you control when the context sensitive code completion insight popup appears. Simply press Ctrl+Space and voila.
By the way, Vadim Tropashko has a great article about getting most of code completion in SQL Developer.
Filed in Oracle with Comments Off | Tags: sql-developerTab Overload And Deluge

Pop quiz: How many tabs are there in a Toad?
Filed in Oracle with 6 Comments | Tags: toadThe Business Technology blog of the Wall Street Journal hates the kind of gibberish in this Oracle press release about Oracle Communications IP Service and Network Management that simplify Lifecycle Management of complex IP-Based services.
The release is a string of bewildering tech terms and vague verbiage… Technology that businesses use is marginalized enough without buzz terms that humans can’t understand. We believe that one reason few business people care about information technology is that talking to a techie requires mastering a whole new language.
To be honest, I do not understand the press release either, maybe because I’m not a network “techie”. How about you?
Filed in Oracle with 8 Comments | Tags: pressrelease, wsjAfter the huge success of their first video, Mogens and Morten demonstrate an Oracle database installation, again wearing a straitjacket:
(via Kevin)
Filed in Oracle with 2 Comments | Tags: funny, videoAs mentioned on OracleCommunity.net, Oracle SQL Developer 1.5 Early Adopter 1 (EA1) is now available for download. As with any new release, there are new features and bug fixes. But, they are not yet detailed in the release notes.
However, I know that the one feature that I have been eagerly waiting for is finally here, it is the brand new and enhanced SQL Formatter (screenshots below). Not bad at all!
I’ll be using SQL Developer 1.5 as my main SQL and PL/SQL development tool for the next few days and weeks. Let’s see if this release will convince me to ditch Quest’s SQL Navigator.




