Category Archives: Uncategorized

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.

How to resolve ORA-24247: network access denied by access control list (ACL)

If you are trying to use oracle to invoke a web service or to connect to a smtp service and you get the following error:

ORA-24247: network access denied by access control list (ACL)

The cause according to oracle is that

“No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list.”

This error is common after an upgrade to Oracle 11. before oracle 11, using network resources via packages like utl_tcp, utl_smtp, utl_mail, utl_http, and utl_inaddr exposed the database to a serious security threat because once the user is granted with permission to use those packages there was no other limitation to connect to any computer.

Since Oracle 11, oracle introduced a fine grained access to network services using access control lists (ACL).

This new feature gave the DBA a better control on which user can connect to which computer

In order to solve ORA-24247 you will need to:

1) Create an acl (if it is not already created)

2) Add privileges to the user using the network resources

3) Assign the acl to a specific address

1) run the following query to check if an ACL exists

SELECT *

FROM dba_network_acls;

If the computer you are trying to connect to is not listed under host, you will need to create an acl:


begin

dbms_network_acl_admin.create_acl (

acl => 'http_permissions.xml', -- or any other name

description => 'HTTP Access',

principal => 'SCOTT', -- the user name trying to access the network resource

is_grant => TRUE,

privilege => 'connect',

start_date => null,

end_date => null

);

end;

/

commit;

This will create the acl and grant SCOTT the connect privilege.

2) IF the acl exists run the following query to verify the user is granted with the appropriate privilege

SELECT *

FROM dba_network_acl_privileges

where principal='SCOTT';

In order to use UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL the user will need the connect privilege

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',

principal => 'SCOTT',

is_grant => true,

privilege => 'connect');

end;

/

commit;

If you need to resolve a host name from a host IP you will need the resolve grant as well.

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',

principal => 'SCOTT',

is_grant => true,

privilege => 'resolve');

end;

/

commit;

3) The final step is to assign the acl to a specific target

BEGIN

dbms_network_acl_admin.assign_acl (

acl => 'http_permissions.xml',

host => 'NETWORK ADDRESS', /*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*/

lower_port => 80,

upper_port => 80

);

END;

It is important to note that only one ACL can be assigned to any host computer. If you assign a new acl to a target the old acl gets unassigned.

However, the old acl is not dropped. So, this could cause confusion because even if the acl was already assigned, it is possible that a new assignment overrode it.

What to do when Oracle XE is not starting after a reboot

When you install Oracle Xe on a windows machine (mainly server OS’s like Windows 2003) you may notice that the database is not always starting after a reboot. This ussualy happenes when the Oracle XE was installed under a different user than the one you are currently logged in as.

It is easy to start the database by going to Start-> All Programs -> Oracle Database 10g Express Edition – Run SQL Command Line and simply type “STARTUP”.


However, if you want to automate this process you can use the StartDB.bat.

1) Navigate to your Oracle XE directory (typically under C:\oraclexe\app\oracle\product\10.2.0\server\BIN)
2) Edit the StartDB.bat file – If the Oracle Service andthe listener is started you can delete the first two lines (net start OracleXETNSListener and net start OracleServiceXE) and save the file.
3) Go to Start > All Programs > Accessories > System Tools > Scheduled Tasks and Use the Windows Scheduler to execute the batch file “When my computer Starts”.

Oradim messages are not displayed in the command prompt (you can still find the results in oradim.log under C:\oraclexe\app\oracle\product\10.2.0\server\database) so it might be wise to try the command first using the command prompt.

If you get ORA-01031: insufficient privileges when you trying to start the db (you probably will if you are logged on as different user than the user who installed Oracle XE) all you need to do is add the user to the ORA_DBA windows group.

1) Right click on My Computer and click Manage.
2) Navigate to Local Users and Groups and click on Groups.
3) Double click on the ORA_DB group and add the user you are currently logged in as.
4) Re run the oradim -startup -sid XE -starttype inst > nul 2>&1 to verify that it works.