Using Union together with Cast and Multiset can cause your function to simply hang (even if the query works great in SQL Plus)

Many times, when an outside client is calling an oracle function it requires the results in a table format (a collection or an array or data set). The most common way to do so is using Cast and Multiset.
CAST tells Oracle the required datatype for the result of an expression, and MULTISET tells Oracle to put the multiple rows returned by the SELECT statement into a single collection object.

A few days ago I changed a query inside a function using Cast and Multiset. I had a working query with reasonable performance. However, once I compiled the function and tried to use it. The same query that worked great in SQLPlus and PL/SQL developer became painfully slow when using it inside a function.

I checked all the usual suspects like wrong binding of mixed types that could make the optimizer choose a wrong plan and make the query slow. Still, I could not find the reason for the slow performance of the function. After a while, I came to the conclusion that the slow performance is related to the fact that the query is using Union (after all that was the actual change in the function) only then I remembered that using Union together with Multiset will cause your function to simply hang.

one workaround is to use multiset union [all] command.
for example:

create type rec1 as object (rec_id number, text varchar2(20));
create type rec_collect as table of rec1;

SELECT
CAST(MULTISET(
select 1, 'first' from dual
union all
select 2, 'second' from dual
) AS rec_collect)
FROM dual;

ERROR at line 1:
ORA-03113: end-of-file on communication channel
or "Unable to send Break messsage"

Using Multiset Union:

select cast (multiset (select 1, 'first' from dual) as rec_collect)
multiset union -- ALL or distinct
cast (multiset (select 2, 'second' from dual) as rec_collect) rec_collect
from dual

The only problem is that this Multiset Union works only from Oracle 10. In addition it works only with nested table collections and not with varrays.

If your function is pre oracle 10 or you simply need a quick workaround all you need to do is wrap the query with an extra inline view and your function will perform just like the query in SQL Plus.

SELECT
CAST(MULTISET(
select * from
(
select 1, 'first' from dual union all
select 2, 'second' from dual
)
) AS rec_collect)
FROM dual;

UPDATE: This happens with MINUS as well

A simple workaround for bug 13477790 – XML/SQL query hangs after Oracle 11

IF you will run the following query on an oracle 11 db

select xmlelement("root",
(xmlelement("sub_root",
xmlforest('value' as "some_tag",
(select xmlagg(xmlelement("test",
xmlforest('code_value' as
"Code")))
from dual) as "SOME_AGG_CODES"))))

from dual

you might find that the sq/xml query simply hangs for many minutes after the upgrade. Many times, the following message will be displayed ” end-of-file on communication channel” . In other cases, it is possible that your session will be disconnected or might not be able to send a break message because you client will simply freeze.

This is even more problematic if this query was written on earlier versions of oracle like oracle 9. The same query will work without a problem on Oracle 9 or oracle and this can be very challenging if you encounter the problem after the actual upgrade to oracle 11.

Oracle identified this problem as a known issue bug #13477790 and released a patch with the same number. It appears that this patch solves this issue but applying a patch is not always easy on large production systems.
Another option for solving this issue is rewriting the the query using an additional xmlelement instead of the xmlforest but this can be time consuming as well.

A quick workaround for overcoming this bug without applying the 13477790 patch is adding a simple hint to the query. Adding the /*+NO_XML_QUERY_REWRITE*/ hint before the first element in the query. Will solve this issue.

select /*+NO_XML_QUERY_REWRITE*/ xmlelement("root",
(xmlelement("sub_root",
xmlforest('value' as "some_tag",
(select xmlagg(xmlelement("test",
xmlforest('code_value' as
"Code")))
from dual) as "SOME_AGG_CODES"))))

from dual

OCI-21700: object does not exist or is marked for delete (a quick reminder)

While many reasons can cause the OCI-21700: object does not exist or is marked for delete message, many times the OCI-21700 is caused by difficulty to access resources that are checked only at runtime.

The most common reason for this error is granting execute privileges on a function without granting privileges on referenced object like types for example.

Giving the same grants to the referenced objects will solve the problem.

Update: it appears that this error message can be raised when using Weblogic Data Source to run a function as well.
Sometimes, mostly after a network failure/database restart/DB schema changes, the “OCI-21700: object does not exist or is marked for delete” will be raised. The weird thing is that the same function will fail when running it with Weblogic but it can run without a problem when you run it from the sql console using the same credentials.
It appears that some Data Base actions like importing or droping objects or even unexpected network failure can “corrupt the DataSource”. If this is the case, all you have to do to solve the problem is restarting the Data Source.

Update 2: Apparently, there is a bug in Oracle 10 that raises the OCI-21700 error regardless of providing the required permission. A unexplained workaround, if all else fails, is creating the function with the same name in the schema you are connected to and retry. Even weirder, if this solves the problem, it will still work even if you drop the newly created function. Sound like Oracle voodoo but hey, if it solve the problem …

create a database link without changing the TNSNAMES.ORA

The basic syntax of creating a public database link is:

CREATE PUBLIC DATABASE LINK NAME.OF.DATABASE.LINK
CONNECT TO schema_name
IDENTIFIED BY "schema_password"
USING 'tnsnames entry';

However, many times even if you have the database permission to create a db link (CREATE DATABASE LINK system privilege), you have no access to the TNSNAMES.ORA file on the server.

Without adding the entry to the TNSNAMES.ORA file any attempt to select from this database link will result in a ORA-12154: TNS:could not resolve the connect identifier specified error.
The simple solution is to create a database link without changing the TNSNAMES.ORA file. The syntax is:

CREATE PUBLIC DATABASE LINK NAME.OF.DATABASE.LINK

CONNECT TO schema_name IDENTIFIED BY " schema_password"

USING

'(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=hostname-or-IP)

(PORT=1521))

(CONNECT_DATA=

(SID=sid-name)))'

;

Since Oracle 10, it is possible to use the abbreviated version

CREATE DATABASE LINK NAME.OF.DATABASE.LINK
CONNECT TO schema_name IDENTIFIED BY schema_password
USING '//hostname-or-IP:1521/sid';

It is important to note that while this trick might be great as temporary workaround, it is recommended to use the old fashion TNSNAMES method to create a constant database link. This is especially true in a multiple servers environment where a databases can be copied from one environment to another (prod to test for example). In such cases, the copied database might include links to prod machines while the tnsnames.ora file will still point to the correct machine.

ORA-02020 – Too many database links in use (also a workaround for ORA-28267 )

If many of your queries are done over db links, you will probably encounter the ORA-02020
(too many database links in use)
error. The cause for this error according to the Oracle documentation is ” The current session has exceeded the INIT.ORA open_links maximum” . The default value for this parameter is 4 but this can easily be verified by running the following query

select * from v$parameter where name ='open_links'

In plain English, this means that the current session is using more than 4 db links.

The problem is that Oracle keeps the database link open until the session is closed. This is especially problematic when working with Weblogic data sources pools for example. Since the Weblogic server keeps its session open constantly, all queries are using the same session (or sessions). This cause the number of open links to accumulates with every query from a new remote server. The final result is that when the fourth database link is used the “ora-02020-too many database links in use” is raised. The fact that many times this message is raised when running a query that uses only one database link makes it hard to debug.

According to Oracle the solution is to Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases. However, changing the open_links parameter can not be done online and require downtime. In addition, not every DBA will be happy to increase this parameter.

Using the following query to identify the number of open database links will show that rolling back or committing does not always free up the open links.

select db_link from v$dblink;

The solution is to actively close the db link after every query using the following command.

DBMS_SESSION.CLOSE_DATABASE_LINK ('DB LINK NAME')
This is equivalent to
execute immediate 'alter session close database link DB LINK NAME'

However, it is not always feasible to change existing code. A possible solution might be to create a procedure that will close all open db links and run it after or before queries that uses database links.

create or replace procedure rollback_and_close_db_links
authid current_user is
begin
rollback;
for links in (select db_link from v$dblink) loop

DBMS_SESSION.CLOSE_DATABASE_LINK (links.db_link);
end loop;
end;

UPDATE: Apperantly, this procedure can act as a workaround for the ORA-28267 Invalid NameSpace error that could happen when your SOA suite connects to a database using JDBC and try to invoke an object that connect to a remote DB. For example, weblogic connects to a function on one DB and this function uses a db link. if you add a call to “rollback_and_close_db_links ” before returning the result, you can workaround this error.