Why APEX running inside the database is a GOOD thing!

I’ve had quite a few discussions with people regarding whether APEX running inside the database is a good thing or a bad thing. Personally I believe it is definitely a good thing, I see no downsides really. However a recent forum thread

For me, the fact that APEX runs inside the database means that one of the major positives is you are able to take full advantage of each and every database available to you. A recent forum thread illustrates this very nicely. The poster in the thread has deleted some files and has unfortunately performed a commit before realising their mistake.

Here is where the features of the database can help you, for example we are able to take advantage of the Flashback feature to get the database back to the state it was in before the files were deleted.

The user in the forum deleted some files from the apex_application_files table (which is actually a view on an underlying table restricted so that you can only see files associated with your workspace), so first of all we query the apex_application_files view –

jes@DBTEST> select count(*) from apex_application_files;
COUNT(*)
———-
0

The reason this returns zero records is because I’m running the query from SQLPlus and not from the SQL Workspace (i.e. my workspace isn’t defined in SQLPlus), we can fix that in the following way –

jes@DBTEST> select wwv_flow_api.get_security_group_id from dual;
GET_SECURITY_GROUP_ID
———————
0

jes@DBTEST> exec wwv_flow_api.set_security_group_id;
PL/SQL procedure successfully completed.

jes@DBTEST> select wwv_flow_api.get_security_group_id from dual;
GET_SECURITY_GROUP_ID
———————
1.1830E+15

Note that you only need to perform the wwv_flow_api.set_security_group, but for completeness I showed that before the call the SGID was set to 0, but afterwards it was set to the correct value.

Now we can requery the apex_application_files view –

jes@DBTEST> select count(*) from apex_application_files;
COUNT(*)
———-
8

So now we can see that 8 files have been uploaded for this workspace. So, we can simulate what the user did by deleting the files, however before we do that I’m going to check in another SQLPlus session what the current SCN (System Change Number) is, since we’ll need that later –

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
3334372

So, the current SCN is 3334372, now we can delete the records –

jes@DBTEST> delete from apex_application_files;
8 rows deleted.

jes@DBTEST> commit;
Commit complete.

jes@DBTEST> select count(*) from apex_application_files;
COUNT(*)
———-
0

They’re gone…deleted, no way back (well, I could restore from backups of course), however there’s an easier way. Firstly we shut down the database and mount it.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 452984832 bytes
Fixed Size 779648 bytes
Variable Size 133175936 bytes
Database Buffers 318767104 bytes
Redo Buffers 262144 bytes
Database mounted.

Now use Flashback to go back to the state we were in back before we deleted the files (SCN 3334372).

SQL> flashback database to scn 3334372;
Flashback complete.

So now we can open the database, but we also need to remember to RESETLOGS since we have used Flashback (i.e the current logs are no longer needed/valid)

SQL> alter database open resetlogs;
Database altered.

Now we can requery the apex_application_files view –

jes@DBTEST> exec wwv_flow_api.set_security_group_id;
PL/SQL procedure successfully completed.

jes@DBTEST> select count(*) from apex_application_files;
COUNT(*)
———-
8

and we see that the data is back!

Now you could be saying at this point, so what? I could do that if I was using Java instead of APEX, all we’ve done is use Flashback technology to flashback the database.

Ahhh but there’s the great thing about APEX, imagine for a moment that instead of performing a delete on apex_application_files, that you were in the process of a massive enterprise change management task.

Imagine you have just run 394 SQL scripts to upgrade your production application from v3.41 to v3.42 and then you update your APEX application…imagine now that something terrible happens and it doesn’t work in production (every developer has this experience at least once in their life right?)…the question is, how easily can you regress your production environment back to the working environment you had before? Well the answer is, with APEX extremely easily, since you can flashback the database and not only will you go back to the working versions of your schema (the old versions of your objects etc), but because your APEX applications also live inside the database you will automatically go back to the version of the Application as it was at that point (i.e. the working version).

Compare this with say a traditional client-server solution, or a middle-tier solution where not only would you need to regress the database, but I would also need to regress the middle tier applications back to the previous versions, I would also need to remember to revert back to the old versions of any configuration files that the apps might need to and in my experience the fewers things you need to do the less chance there is of forgetting something.

There are a huge number of Oracle database features that make every area of managing production systems easier, from development through to change management, Flashback is just one out of many reasons why I think having APEX live inside the database is *definitely* a good thing!

**Note: The usual caveats apply here, don’t test the above code on your production system and then come back to me if you break it. When it comes to using things like this the best advice I can give is read the docs, read the docs, read the docs. Also please note that I’m not advocating that ‘flashback database’ is your first course of action, you should actually consider using some of the other Flashback technologies, such as flashback table etc where appropriate since flashing back the entire database is only desirable in certain circumstances, however for the purposes of this post it shows how easy it is to use.

4 thoughts on “Why APEX running inside the database is a GOOD thing!

  1. Patrick Wolf

    Hi John,

    I’m running FF 2.0 with 1280×1024.

    Have you already changed it? Because I’m not at home and have the same configuration as above, but this time i’m able to read the SQL statements.

    Patrick

    Like

    Reply
  2. Pingback: DB Life » Flashback Tech

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s