Category Archives: Uncategorized

Short Reminder – why you get ORA-00942: table or view does not exist inside a fuction while you can select from it in SQLPlus

This short and quick reminder explains why you can select from a a table or a view in SQLPlus but you get a ORA-00942: table or view does not exist when you try to compile a function using the same query.

This mostly happens when you try to use v$ views (v$session ,v$sesstat, v$statname etc.) inside a function.
in this case, even if the user is granted with dba role he can query the view but he can not use it inside plsql.

A user with the DBA role can access the v$ tables, because the select access is included in the DBA role. According to Oracle, privileges granted via roles are not inherited by stored objects. so, packages, procedures, views, and triggers need explicit grants, not grants via roles.

The solution is to grant access to object directly to the user. log in to the DB as sys and grant the privileges on a specific object (sys.v$instance ,for example ) to the user itself,
grant select on sys.v$instance to some_user;

ORA-02069: global_names parameter must be set to TRUE for this operation

the Oracle documentation description for “ORA-02069: global_names parameter must be set to TRUE for this operation” is:

“You tried to execute an operation that requires a remote mapping. This can not be done because the parameter called GLOBAL_NAMES is not set to TRUE. ”

This error message is one of the less understandable error messages even in the Oracle documentation standards.

Oracle resolution offer is to try setting the GLOBAL_NAMES parameter to TRUE with the following statement:

ALTER SESSION SET GLOBAL_NAMES = TRUE;

shortly, what this message means is that you are trying to perform an operation (update or insert, for example) on a remote database using a db link while trying to use a local function.
for example:
update remote_table@remote_data_base
set col1= local_function(value)

to resolve this, you can:

alter the session as oracle suggested:

ALTER SESSION SET GLOBAL_NAMES = TRUE;
or simply assign the value of the local function into a variable and use it in the update/insert command:

v_variable :=local_function(value);

update remote_table@remote_data_base
set col1= v_variable;

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

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.

similar SQL/XML queries do not work the same way across oracle versions

If you just upgraded your Oracle version and you see inconsistency in behavior of your old SQL/XML (SQLX) queries, you might want to check if the actual XML structure is still the same.

Specifically, there is a problem with XMLAgg alias. The fact is that if you run the same code on Oracle 9 or 10 (up to 10.2.0.3)   and later versions, the same query returns different results.   If you used aliasing to name an aggregated collection you will notice that the alias disappeared in later versions.

For example, if you run the following code on Oracle 9


with t as (SELECT 'Emp 1' name FROM dual
UNION ALL
SELECT 'Emp 2' name FROM dual
UNION ALL
SELECT 'Emp 3' name FROM dual)
SELECT xmlelement("EMPLOYEES_DATA",
(SELECT xmlagg(
XMLELEMENT("EMPLOYEE_NAME", t.name)
)
FROM t) as "EMPLOYEES").extract('/')
from dual

the result will be :


<EMPLOYEES_DATA>
<EMPLOYEES>
<EMPLOYEE_NAME>Emp 1</EMPLOYEE_NAME>
<EMPLOYEE_NAME>Emp 2</EMPLOYEE_NAME>
<EMPLOYEE_NAME>Emp 3</EMPLOYEE_NAME>
</EMPLOYEES>
</EMPLOYEES_DATA>

However, if you run the same query on oracle 11, the result will be


<EMPLOYEES_DATA>
<EMPLOYEE_NAME>Emp 1</EMPLOYEE_NAME>
<EMPLOYEE_NAME>Emp 2</EMPLOYEE_NAME>
<EMPLOYEE_NAME>Emp 3</EMPLOYEE_NAME>
</EMPLOYEES_DATA>

Notice that the   <EMPLOYEES> tag is missing. Oracle simply ignores the alias.

Clearly, similar queries do not work the same way across oracle versions.   The problem is that it is dropping the alias silently. The query is not failing it just produce a different xml.

Unfortunately, to overcome this, a change in the code is required. You can easily add an additional XMLELEMENT.


with t as    (SELECT 'Emp 1' name FROM dual
UNION ALL
SELECT 'Emp 2' name FROM dual
UNION ALL
SELECT 'Emp 3' name FROM dual)
SELECT xmlelement("EMPLOYEES_DATA",
xmlelement ("EMPLOYEES",
(SELECT xmlagg(
XMLELEMENT("EMPLOYEE_NAME", t.name)
)
FROM   t)) as "WILL_BE_IGNORED_ORACLE_11").extract('/')
from dual;

This will produce the pre upgrade results:


<EMPLOYEES_DATA>
<EMPLOYEES>
<EMPLOYEE_NAME>Emp 1</EMPLOYEE_NAME>
<EMPLOYEE_NAME>Emp 2</EMPLOYEE_NAME>
<EMPLOYEE_NAME>Emp 3</EMPLOYEE_NAME>
</EMPLOYEES>
</EMPLOYEES_DATA>

Another way to use SYS_XMLAGG together with XMLFormat  but this will require code change anyway  so I believe that the best alternative will be to add an additional xmlelement.