Category Archives: Uncategorized

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.

“Shrink failed for Database” when attempting to shrink a data file.

Sometimes, when you try to shrink a data file you may get the following message:

TITLE: Microsoft SQL Server Management Studio
——————————
Shrink failed for Database ‘Data base name’.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476


First, you should know that shrinking data files is not recommended and will hurt performance. Shrinking data files will cause index fragmentation. You can recreate the indexes but it will require the freed space. Shrinking will also cause fragmentation in the server’s file system which will slow it even more. In addition, since every page move is logged to the transaction log, chances are that the transaction log will claim the same space.


However, if in case you decide to shrink the database anyway (for example, after a large and permanent delete, or in case it is a test system) a common reason for this error is lack of space.
Since many times the attempt to shrink a database will come after discovering that the drive is running out of space, it would only make sense that there is not enough space for the shrinking process itself.


To verify, open the sql server error log (usually under Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG) and look for Operating system error 112(There is not enough space on the disk.).


To solve, you should clear some space to allow the process to complete. If you can not free some disk space, Try to shrink the transaction log and tempdb.

Adding a Transaction Log at runtime to prevent it from running out of space

sometimes, when running a big transaction, the transaction log can become full or simply grow to a huge size . while you can sometimes free some space using “backup log db-name with truncate_only” and trying to shrink the DB using SQL Management studio.


The process is described in the above link but basically it is: right click on the Database name ->tasks->shrink->files and choosing the log file.
This will shrink the db if there is free space in the transaction log but it can only serve as a temporary solution.

If the transaction is still running and you are afraid that the hard drive will run out of disk space, a better solution would be to create another transaction log on a different drive. This will solve the disk space issue and might even improve performance.
To create another transaction log file  using the SQL Server Management Studio:

    1) Expand the DataBases node and choose your Database
    2) Right Click your Database and click Properties.
    3) Select the Files Page
    4) CLick ADD and Navigate to te Database files grid.
    5) Enter a name for the new log file. This name must be unique within the database.
    6) Select the Log file type
    7) select initial size, growth method (auto Growth, fixed Increments or percent). you can also set a maximum size. Set up the path to the new file and file name and Click OK

In Some cases, the file name column might be disabled when adding a log file using the Sql Server Mangment Studio while the transaction is still runing.
In this case you can add the file manually using a query.
Open a query editor and type:


USE master

GO

ALTER DATABASE db-name

ADD LOG FILE

( NAME = addedlog,

FILENAME = ‘I:addedlog.ldf’,

SIZE = 1000MB,

— MAXSIZE = 10000MB,

FILEGROWTH = 10MB)

GO

There are better ways to avoid the Transaction log is full situation and this should be prevented in the the first place by the code but if you got no access to it, this might be a good solution.