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

Leave a reply

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)

29 Replies

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

Leave a reply

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.

How to grant privileges on all tables to a user

1 Reply

Sometimes you need to grant privileges to one user’s objects to another user. While granting should be done very carefully, when you have a lot of tables it is nice to have an automated script to do it.

It is important to note that the best way to do that is creating a role, granting the privileges to this role and then grant the role to the user. This way, you can fine tune the privileges and the process will be a lot faster next time.

Another important thing to note is that you should avoid the temptation to use the  “SELECT ANY TABLE” privilege. This will indeed allow the user to select any table but (and this is a big but) since it is a system privilege, it will allow him to select any table from any schema. Depends on the setting, it might even allow to user to select from the dictionary tables themselves. There is no reason to grant this privilege to someone who is not the DBA.

The easiest way to grant select on all of  one account’s object to another user is to use a PL/SQL loop. This script will grant select on tables but it should be easy to adapt it to any other privilege.

 begin

 for i in (select * from user_tables)       

 loop
     execute immediate ‘grant select on ‘||i.table_name||’ to a_role_created_for_this’;
   end loop;
 end;

In case you need a pure SQL solution, the best way to go would be to spool the results of the following query and run the script created in grant-all.txt.

SQL> set lin 150

SQL> spool c:\grant-all.txt

SQL>select ‘grant select  to ‘||table_name||’ to “the new user”;’ from all_tables where owner=’the old user;

SQL> spool off

“A service specific error occurred: 2” when trying to start dbconsole

15 Replies

When logging in to database control after some time you may get a message that some accounts’ passwords need to be changed (sysman, system etc..).  A page will be displayed allowing you to change those password but if something (like timeout) happens during the page processing you will not be able to log in again to the database control and change the passwords.

usually, to solve you should restart the dbconsole by opening the command prompt and typing emctl stop dbconsole. However, you may find that the service is no longer running. Trying to start the dbconsole by typing emctl start dbconsole in the command prompt will result in the following message:

The OracleDBConsole[ServiceName] service could not be started.

A service specific error occurred: 2.

More help is available by typing NET HELPMSG 3547.

as expected, typing NET HELPMSG will not provide usefull information.

To solve this:

1) Delete and recreate the Enterprise Manager agent.

In the command prompt type> emca -deconfig dbcontrol db

even if this fails, type> emca -config dbcontrol db

You will be asked to provide the database SID, the listener port, password for SYS, DBSNMP and SYSMAN accounts, and some optional settings.

This process will run for several minutes.

2) restart the dbconsole

> emctl stop dbconsole

> emctl start dbconsole

This should solve the issue, however, if you are still unable to log in or you get the following error:

ORA-28001: the password has expired (DBD ERROR: OCISessionBegin), make sure that the SYSMAN account is unlocked

SQL> conn / as sysdba
Connected.
SQL> alter user sysman identified by [new password]
2  /

User altered.
SQL> alter user sysman account unlock;

User altered.
SQL> exit

> emctl stop dbconsole

> emctl start dbconsole

if you still have problems logging in to database control, refer to the following post

What to do when Enterprise Manager is not able to connect to the database instance.