Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Sunday, October 14, 2007

Using Named Parameters within SQL

One of the Oracle 11g new feature that I like is ability to use Named Parameter syntax in functions calls inside SQL. For example it is possible to run such query:
select DBMS_METADATA.get_xml(
object_type => object_type,
name => object_name)
from user_objects
where object_type in ('TABLE', 'VIEW');
It was not possible in previous Oracle versions (before 11g). It was only possible to specify parameters by position:
select DBMS_METADATA.get_xml(object_type, object_name) from user_objects
where object_type in ('TABLE', 'VIEW');
This is great feature as now SQL is more compatible with PL/SQL. And Named Parameters syntax is my favorite method of calling functions, as it gives me direct knowledge what value is assigned to parameters. Specifying parameters by position requires to remember sequence of parameters in function definition. Additionally named parameters syntax allows programmer to skip any of parameter that has default value, or to change order of parameters in function call. It is also possible to mix those 2 notations in single statement:
select DBMS_METADATA.get_xml(object_type, name => object_name) from user_objects
where object_type in ('TABLE', 'VIEW');
Hope you like it

Cheers, Paweł

4 comments:

Laurent Schneider said...

unfortunately, I cannot use the mixed notation in standard functions

select regexp_substr('978-0-9776715-8-8','(...)-(.)-(.......)-(.)-(.)',subexpression=>1) from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis

however, a full qualified call to the function does work

select sys.standard.regexp_substr('978-0-9776715-8-8','(...)-(.)-(.......)-(.)-(.)',subexpression=>1) from dual;
SYS
---
978

but it is not equivalent. SQL*Plus for example can guess the maximum length of a resulting string when using standard functions

Paweł Barut said...

Hi, Laurent

Package "Standard" is special one, so we can expect different behavior here.

Anonymous said...

I find a lot wrong with this post!

Named parameters were available before 11g. I use them all the time in 10g. I don't know if they exist in 9 and before.

Also, the comment about leaving out default parameters is a bit misleading. You can do that with positional parameters. That's the whole point of default parameters! That's why, after you've defined a default parameter in a function, you can't define a non-default one again.

Paweł Barut said...

Hi Anonymous,

I think you mean PL/SQL - that is right, it was there already in Oracle 8.
But it was not possible in pure SQL. This feature was introduced in Oracle 11g.

Best Regards,
Paweł

 

Copyright © Paweł Barut
Printing from DOS to USB Printer