select DBMS_METADATA.get_xml(It was not possible in previous Oracle versions (before 11g). It was only possible to specify parameters by position:
object_type => object_type,
name => object_name)
from user_objects
where object_type in ('TABLE', 'VIEW');
select DBMS_METADATA.get_xml(object_type, object_name) from user_objectsThis 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:
where object_type in ('TABLE', 'VIEW');
select DBMS_METADATA.get_xml(object_type, name => object_name) from user_objectsHope you like it
where object_type in ('TABLE', 'VIEW');
Cheers, Paweł
4 comments:
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
Hi, Laurent
Package "Standard" is special one, so we can expect different behavior here.
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.
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ł
Post a Comment