Wednesday, February 27, 2008

ORA-06572: Function %s has out arguments

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error is returned when you are (typically) trying to execute a user-defined function inside a SQL statement and the function contains some parameter defined either as OUT or as IN/OUT.
ORA-06572: Function CLOB_TO_BLOB has out arguments
Most built-in functions do not use OUT parameters however it is perfectly acceptable to build packaged functions where such type of parameters are present, if not even required, to increase performance and reduce temporary memory allocation.

Although in the specific case of this CLOB_TO_BLOB function, the p_clob parameter has been declared as IN OUT but there isn't a NOCOPY declaration following it, which looks odd to me, normally the IN OUT declaration is a requirement for NOCOPY.

The trade-off is in that any attempt of using such functions in a SQL statement fails with the aforementioned error.

So, what if you need to iterate the function for each row of a given set?

There are two possibilities:
  • one is to convert the implicit cursor (the SQL statement) into an explicit cursor (FOR cursor LOOP).
  • the second one is to write a wrapper function;

implicit cursor, raising the error:
create table my_blobs(id number, tgt_blob blob)
/
create table my_clobs(id number, src_clob clob)
/

insert into my_blobs(id, tgt_blob)
select id, wwv_flow_utilities.clob_to_blob(src_clob, 'AL32UTF8')
FROM my_clobs;

ORA-06572: Function CLOB_TO_BLOB has out arguments

explicit cursor:
DECLARE
myblob blob;
BEGIN
FOR cur_clob in (SELECT id, src_clob FROM my_clobs) LOOP
myblob := wwv_flow_utilities.clob_to_blob(cur_clob.src_clob, 'AL32UTF8');
insert into my_blobs(id, tgt_blob) values(cur_clob.id, myblob);
END LOOP;
END;
or a wrapper function:
create or replace
function wrap_c2b (
p_clob in clob,
p_charset in varchar2)
return blob
as
tmp_clob clob := p_clob;
begin
return wwv_flow_utilities.CLOB_TO_BLOB(tmp_clob, p_charset);
end;

insert into my_blobs(id, tgt_blob)
select id, wrap_c2b(src_clob, 'AL32UTF8')
FROM my_clobs;

The wrapper function approach may cause performance degradation owing to the extra copies of the CLOBs causes by the passing of the source CLOB by value, which results in a plethora of temporary LOBs, so use it only if necessary (i.e. in your development environment, not on a production server...).

Usage of the explicit cursor seems to me a much more sensible way of using database resources, especially if the relevant parameter(s) of the function have been declared as NOCOPY.

See message translations for ORA-06572 and search additional resources

PLS-00103: Encountered the symbol "CLOB" when expecting one of the following

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may encounter this compilation error in a situation like this:

create or replace
function fn_c2b (
p_clob in NOCOPY clob,
p_charset in varchar2)
return blob
as
begin
...
end;


PLS-00103: Encountered the symbol "CLOB" when expecting one of the following:
:= . ) , @ % default character
The symbol ":=" was substituted for "CLOB" to continue.

The problem is in the combination of IN with NOCOPY.
NOCOPY requires that the parameter is of type OUT or IN OUT, not IN.

NOCOPY is a useful hint that allows PL/SQL procedure to access parameters by reference, instead of by value (the default).
Accessing parameters like LOBs and PL/SQL tables or varrays by reference significantly reduces memory consumption and CPU time and therefore increases performance.

Read more about the NOCOPY hint optimization (from Oracle 10G documentation).

Wednesday, February 20, 2008

ORA-12728: invalid range in regular expression

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error can be seen when working with regular expressions and character lists.
ORA-12728: invalid range in regular expression
For a live simulation of this error, see Yocoya's Regular Expression Workbench.

While in this case it's easy to see that the character range is incorrect because letter c precedes letter a, whereas it should be written as [a-c], it can be harder to understand when multicharacter elements are present:

for instance, range [a-[.ch.]] is legitimate when NLS_SORT is set to XSPANISH, however range [[.ch.]-[.ll.]] is not.
For further details about NLS_SORT settings, see a previous posting.

Note also that you can put multiple character ranges inside lists as in [0-9a-z], however accented characters will not match.
If you need to include normal letters as well as their accented counterparts, use built-in character classes like [[:alnum:]] or [[:alpha:]].

See message translations for ORA-12728 and search additional resources

Tuesday, February 19, 2008

ORA-12731: invalid collation class in regular expression

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error can be seen when working with regular expressions and character lists containing collation classes.
ORA-12731: invalid collation class in regular expression
For a live simulation of this error, see Yocoya's Regular Expression Workbench.

In order to understand how to fix the problem in a generic situation, let me show you first how to fix it in the workbench, where you have to change the NLS_SORT option, displayed in the sidebar, to the value XSPANISH (extended Spanish).

Acceptable characters within a collation class are determined by the value of initialization parameter NLS_SORT, that can be altered at the session level with:
ALTER SESSION SET NLS_SORT=XSPANISH;
For an in-depth discussion of linguistic sorting, see the relevant chapter of the Database Globalization Support Guide (10g).

So, when you switch the linguistic option in the workbench picking a value from the drop down list, you are causing the program to alter the current session value of NLS_SORT.

However, if after changing NLS_SORT you are still experiencing the same the problem, it could be that you have specified an invalid multicharacter element, in which case you must determine what characters are allowed by Oracle for the desired linguistic sort option.
For instance, in extended Spanish only "ch" and "ll" are allowed as multicharacter elements in collation classes.

But what is the purpose of multicharacter collation elements anyway?

As you can see in workbench live example, i defined a character range within a list, where all characters between a and ch will be replaced by an asterisk. Being considered a single character, ch will be replaced by a single asterisk, not two.
The collation element allows you to define a range of characters according to linguistic rules, where the starting or ending element can be a multicharacter element.

See message translations for ORA-12731 and search additional resources

Thursday, February 14, 2008

Build and use an apex page alias substitution string

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

The seasoned developers certainly know that among the apex built-in substitution strings there isn't any returning a page alias, whereas there is indeed a very handy substitution string returning the application alias for instance.

Page aliases allow you treat apex pages in terms of symbolic names in contrast with less meaningful numeric page IDs and they also may give you an edge when it comes to page indexing by web search engines, thanks to their self-explanatory names and assuming you did use such self-explanatory name of course... :-D

What i'm going to briefly document here is a 20 second set up process that allows you to stuff a brand new APP_PAGE_ALIAS into the built-in collection of substitution strings.

  1. Create an application item called APP_PAGE_ALIAS and define it as restricted if you don't want that anybody messes around it.
  2. Create an application computation for APP_PAGE_ALIAS with computation frequency Before Header, computation type SQL Query, containing the following computation source (based on an apex dictionary view):
SELECT page_alias
FROM apex_application_pages
WHERE application_id = :APP_ID
AND page_id = :APP_PAGE_ID
The computation will run on each and every page automatically updating the value of the application item that you can freely use in the form &APP_PAGE_ALIAS. inside branching, URLs, text and so on.

I find this a nice example of what you can do with application-wide computations and the apex dictionary views in a minimal amount of time.

See more articles about Oracle Application Express or download tools and utilities.

Monday, February 11, 2008

ORA-12541: TNS:no listener

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

ORA-12541: TNS:no listener
This error can be seen when trying to connect to a database instance from a client program and the listener service is down or has not been started automatically after a reboot.
Note however that you can also get this error when trying to reach the listener on the wrong TCP port: by default a listener is configured to listen on port 1521 but a DBA could have changed the default port value in case the server runs multiple oracle instances or different database server versions. So, if the listener is configured to listen, let's say, on port 1522 and the TNSNAMES.ora file located in the ORACLE_HOME/network/admin folder contains port 1521 instead, you'll get ORA-12541, even if the listener is up and running.

If you are experiencing this error in a client/server environment and you are not the database administrator, then you should contact the DBA and ask for help. The oracle utility TNSPING should also help you out in the troubleshooting process as it will display all the relevant information.

On Windows platforms the listener is a Windows service that is usually set to start up automatically.
In Oracle XE for Windows, the service name is OracleXETNSListener.
In other versions of Oracle database for Windows, the name is automatically generated upon creating a listener with the Oracle Net Service configuration console, but usually, for a single instance installation, the default name will be OracleOracleHomeNameTNSListener, i.e. OracleOraHome101TNSListener for a database version 10.1 whose oracle home is named OraHome101. You can also check the listener status by executing LSNRCTL from the command line in much the same way as you'd do it on a Unix platform, as explained below.

On a Unix server, if you hold the required privileges, you can check the listener status by executing the listener configuration utility LSNRCTL and issuing the command STATUS:
if the result is like the following:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserv)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused

then the listener is not running and you can start it with the command START.

If connecting from SQLDeveloper from a client machine, it's very likely that you get instead the following error message:
An error was encountered performing the requested operation:
Io exception: The Network Adapter could not establish the connection
Vendor code 17002


See message translations for ORA-12541 and search additional resources

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles