Friday 26 September 2014

APEX 4.2.6 patch available

Oracle Application Express Release 4.2.6

Latest APEX patch released by Oracle

On 24th of September Oracle released latest patch for Application Express which is presumably last patch before the first 5.0 version. These are great news.


Thursday 25 September 2014

APEX user account locked?

How to unlock your user accounts in Oracle APEX

Resetting your APEX user & Workspace admin passwords  

An easy way how you can reset your APEX user account lock flags directly from database.

So many times it happened to me that my user accounts got locked when entering the wrong passwords for more than three times. This is the the way I unlock my users. You need to have access to you sys accounts or you can be assisted by your DBA to do these steps.

Log into your database as sys user and run.

select * from APEX_040200.WWV_FLOW_FND_USER
where account_locked = 'Y'

and default_schema = 'XXXX'

As a result you will get all APEX accounts that are currently locked. What I must admit is I do knot know if this is a 'best way' to do it but it worked every time so far for me without any problems. All you need to do now is create an update statement to unlock your accounts.

update APEX_040200.WWV_FLOW_FND_USER
set account_locked = 'N'
where User_name in ('ADMIN')
and default_schema = 'XXXXX'

After this you should be able to login using same username and password as before.

In case you are using different APEX version all you need to do is change APEX_xxxxxx and it should work.

Apex user password reset

In case when you want to reset password for one of your APEX accounts you can use standard APEX_UTIL function. 

        p_user => 'XXXXXX',
        p_msg => 'Your password has been reset by administrator.');

In ideal world this function should send an email to this user and notify them of their new temporary password which will be asked to be changed on their first log in. Unfortunately it doesn't work every time, sometime users do not receive an email and sometime they are not asked to changed they passwords. I am really not sure why this happens but you have been warned. :)

APEX Workspace admin password reset

Log into your DB server as oracle user. The key thing now is to go to directory where your APEX installation folders are. In my case it was as simple as: 

D:\>cd apex_4.2\apex

If you list all of your files in this folder you should be able to find apxxepwd.sql file which we will use to change our workspace admin password. 

22/10/2012  05:47 PM             1,853 apxrelod.sql
22/10/2012  05:47 PM             5,862 apxremov.sql
22/10/2012  05:47 PM             4,350 apxrtins.sql
22/10/2012  05:47 PM                44 apxsqler.sql
22/10/2012  05:47 PM             9,121 apxxemig.sql
22/10/2012  05:47 PM             3,271 apxxepwd.sql
22/10/2012  05:51 PM    <DIR>          builder

All you have to do now log in as sys to your database and run that script.
D:\apex_4.2\apex>sqlplus "/ as sysdba"
SQL> @apxxepwd

Once you run it you will be asked to enter your new password and that is all. Open again your http:\\ .......\apex\apex_admin link and enter your new password. Well done!!!!

If you think this is to complicated you can always stick with Apex admin interface under Administration tab and do it from there.  Of course you will need to have access with APEX administration privilege.


Friday 19 September 2014

APEX 4.2 and workspace info

APEX system variables

Host address, user IP, SID and OWA_UTIL package

Few days ago we had a bizarre case at my client site that no one knew the workspace admin password and people were asking about what type of configuration and details we can get about the APEX instance currently in use. Sounds familiar? 

So what can we do when we don't have access to workspace Admin passwords. Here are few useful functions that known to help in different situations. 

select utl_inaddr.get_host_address(sys_context('userenv','server_host')) from dual;

select sys_context('USERENV', 'OS_USER') from dual;


select SYS_CONTEXT('USERENV', 'HOST', 25) from dual;

On the other side if you have admin to any of workspace instances you can use always helpful OWA_UTIL package. 

All possible details about your APEX environment can be retrieved by running:

Of course to get around all of this you can reset your passwords. If you are wondering how check my next post.

Hope this helps.


Sunday 14 September 2014

APEX report text wrap - Theme 25 page with container template

How to wrap columns and words in your report?

Another alternative

I know what you are all thinking I have done and seen this before. Exactly what I thought, there are so many post of doing this and I have done it so many times. Well I was wrong. 
the first thing that came to my mind was apply css formatting ans you should be fine.

Standard CSS:
width:500px; display:block; white-space:normal; }

But for some reason this did not work on report in Theme 25 with container page template. Report text was still getting out of column borders and it would not be wrapped.

Solution that I implemented:

1. Change report CSS column properties to include new class name

2. Created CSS on Page level
.wrap_text {
    word-break: break-all !important;
3. Added Javascript on Page Load

//LOOP through each element that needs wrapping and add <p> element to make this work
$(".wrap_text").each(function(index) {
    $(this).html("<p>" + $(this).text() + "</p>");

And that was it.