APEX and temporary file table

Is saving files in www_flow_files  a good idea?

Where to store your temporary APEX files and www_flow_files issue

How many times did you have the need to store you files temporary in your APEX application? How many times have I heard try not to use www_flow_files table for storing your temporary files. 

There are two reasons: one being security thing and second one is files in local table are easier to manage. 

But does this mean that I have learned my lesson and listen to these tips? Of course not.

Situation: In the company we have an application that project managers use to retrieve some BI Publisher data.  How this can be achieved is a separate subject and I will not go into to many details. 

Bottom line was all of a sudden after two years in production only one combination of parameters for a single client was causing reports to fail. My first suspicion was that it must be data related. 

As everything led me to believe that is to be the only reason. Fortunately I always implement application that have a log capability and this one was no exception. This proved yet again to be crucial part in my analysis while I was trying to convince myself that it had to be data related error.

 

As odd as it may sound initially this unique constraint error didn't ring a bell but after a while it came to me that it must be related to famous www_flow_files view and ability to write data in there. 

Problem turned to be that some filenames and files we not being properly cleaned up in a run time and for this reason were left in this table which at the end caused my reporting procedure to fail due to unique constraint on FILENAME column.Thankfully this time workaround was easy. 

Log in as SYS and run this query:  

select * from  wwv_flow_file_objects$

As a result you will get all files that were left at some stage in temporary table but never were deleted. Once I deleted these there were no other problems with my reporting. 

Lesson to learn:

0. Always implement logging methods as they will save you heaps of time

1. Avoid temporary tables

2. If you have to use them - make sure you have mechanisms in place to properly manage deletion of the files at the run time

3. Make sure that you have a unique filenames no matter how many time reports are being called

 Hope this helps.

Cheers,

SL

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.

More information can be found on Oracle OTN http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

Notes about this patch: http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-425-patch-set-notes-2299532.html

Cheers

SL

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. 

-- within APEX SQL WORKSHOP
BEGIN
    APEX_UTIL.RESET_PW(
        p_user => 'XXXXXX',
        p_msg => 'Your password has been reset by administrator.');
END;

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: 

C:\Users\oracle>d:

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.

Thanks,

SL

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', 'IP_ADDRESS') FROM DUAL;

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

SELECT sys_context('USERENV', 'DB_NAME') 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. 

For more details check http://docs.oracle.com/cd/E12839_01/portal.1111/e12042/psutil.htm 

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

begin
OWA_UTIL.print_cgi_env;
end;

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.

Regards,

Lino

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. 
Thanks,
SL