Trent's Tech Tips

For this, I set up an authentication plugin and behind the scenes it's using a web service to check if the client is valid or not. To automate the login process, you need to make use of the sentry function that gets called every time you access a page in the application. This is what the item help says: Enter the name of the PL/SQL function the plug-in can use to perform the session sentry verification. It can reference a function of the anonymous PL/SQL code block, a package function or a stand alone function in the database. For example: check_ldap_session_sentry When referencing a database PL/SQL package or stand alone function, you can use the #OWNER# substitution string to reference the parsing schema of the current application. For example: #OWNER#.check_ldap_session_sentry There is however one caveat with this. It doesn't run on whatever you have defined as the login page (User interface attributes --> Desktop --> Login URL) - which is reasonable enough. ...

Recently I've been working on a project that had some views that needed to reference some session state information, which uses the ever too familiar v function: select * from some_table where some_item = v('P1_SOME_ITEM') Since I work extensively in SQL Developer, when I'm debugging, it becomes a bit more difficult, because we are outside the context of your apex session, our views data comes back empty. One solution I've come up with to help with this is using some un-documented procedures to create an apex session outside the apex scope. Actually, I can't take the credit, I stole the code from  @martindsouza 's blog  with a few minor adjustments. create or replace package apex_session_utl as procedure re_init_session( p_session_id in apex_workspace_sessions.apex_session_id%type); function get_session_username( p_session_id in apex_workspace_sessions.apex_session_id%type) return apex_workspace_sessions.user_name...

These past couple of weeks, I have been doing some work with some external APIs, so have had to do some time stamp manipulation. Here are some tips I've learnt along the way. A quick way to get UTC time, is with the function: sys_extract_utc . With that, we can quickly get the UTC timestamp. Here is an example to return the UTC time in RFC3399/ISO8601 format: to_char( sys_extract_utc(systimestamp) , 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"' ) To return that back into local time, you want to declare a variable with time zone support. So just say the string you have is: 2014-04-17T02:46:16.607Z, you'll want to declare it with a time zone attribute. l_time timestamp with time zone; Since Z refers +00:00, you need to set the time zone. If you call to_timezone or to_timezone_tz , without specifying the timezone, it will create the time in the local time zone, so we need to specifically set it with from_tz . Either: l_time := to_timestamp_t...

Say for example you have a generic build status of 'Exclude' - so you can temporarily disable components without touching the conditions (it doesn't really seem in the nature of build options, but still can be done). There will undoubtedly come a time where you need to locate all said components to either remove them from the application, or remove the build option so they are re-included. First we can query the apex_dictionary view to find any views that contain the build option column: select * from apex_dictionary where column_name = 'BUILD_OPTION' As at ApEx 4.2, this gives us the following list: APEX_APPL_USER_INTERFACES APEX_APPLICATION_BC_ENTRIES APEX_APPLICATION_COMPUTATIONS APEX_APPLICATION_ITEMS APEX_APPLICATION_LISTS APEX_APPLICATION_LIST_ENTRIES APEX_APPLICATION_LOV_ENTRIES APEX_APPLICATION_NAV_BAR APEX_APPLICATION_PAGES APEX_APPLICATION_PAGE_BRANCHES APEX_APPLICATION_PAGE_COMP APEX_APPLICATION_PAGE_DA APEX_APPLICATION_PAGE_PROC A...

Quite often then not, I'm adding a list region onto the global page (page 0), and specifying the condition: Current Page Is Contained Within Expression 1 (comma delimited list of pages). The trouble with this is that each time you add a new list entry, you then need to go back to page 0, edit the region conditions, and update the list of pages. Then if you delete an entry from the list, you need to remember to go and update it. It's just occured to me there has to be a simpler way where you can just query the data dictionary to get the pages list, and check if it exists in that. When you specify the target type as 'Page in this application', you will notice that the column 'entry_target' returns the URL (or similar): f?p=&APP_ID.:14:&SESSION.::&DEBUG.:::: where 14 is the page you specified. So, we can get a list of pages the list relates to with a query similar to the following: select regexp_substr(apex_application_list_entries.entry_targe...

tl;dr: if you are going to have a tabular form and an sql report on the same page with a hidden item, don't hide it using column attributes. I've set up the following page to document the behaviour:  http://apex.oracle.com/pls/apex/f?p=45448:23:11484095074702::::: Basically, when you have a tabular form, elements are given a name attribute such as f01. Then, any processes can reference these elements.  The trouble is, I have a SQL report, and I want to have the name column a link, which uses the ID column. I also don't want to leave the ID column showing, so I set it to hidden in column attributes. Now if I try to delete a row in the tabular form, I will run into the following issue: Now if I look at the source of the people report, I will see the hidden item has also named that field the same way as the tabular form, which will be causing a conflict for the MRU,MRD processes, and any custom process that uses the apex_application.g_f0x array....

Before APEX 4.1, if you wanted to add more complex validation using PL/SQL, you would have to do a loop through the apex_application.g_f0x array and act accordingly - as per:  http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_app.htm#autoId2 Aside from validations, you may want some process to do something with values before deleting/updating/creating. Apex 4.1 introduces some new substitution strings: http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/concept_sub.htm#BEIIBAJD APEX$ROW_NUM - the row number being processed APEX$ROW_SELECTOR - If a check has been marked in the checkbox (value will be 'X') APEX$ROW_STATUS - C if created; D if deleted; U if updated I wasn't really sure how to work with these, and nor did I really investigate, when I first saw them in the builder guide. A recent thread in the forums, and it all makes sense! With these changes, you no longer need to deal with the array, but instead can just refer to the above subs...

Well, yesterday oracle released the first early adopter of application express, having had a chance to have a little play around, these are the ones that caught my attention: APEX_IR See:  http://apex.oracle.com/pls/apex/f?p=38997:1:0::NO:RP,1:P1_MARQUEE_FEATURE:Interactive%20Report%20Enhancements There are currently some interactive reports utility functions/procedures as part of the apex_util package. These will now be deprecated, and functionality will be added to a package named APEX_IR. The most exciting addition is the ability to get the derived IR query (from added filters/sorts). Without any published API docs, I can only assume this is through the function get_report. Further, I set up an example on page 2 of the sample database application. Created a new popup page with a dynamic region with the following source: declare l_report apex_ir.t_report; begin l_report := apex_ir.get_report( p_region_id => 6506762112827941367, p_page_id =>...