Posts

Showing posts from 2014

Logging in without user intervention

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.

Exposing procedures for URL access with ORDS

Image
I had just been looking at exposing some procedure for URL access, using ORDS. ORDS offers the following configuration properties: security.inclusionList security.exclusionList security.disableDefaultExclusionList security.validationFunctionType security.requestValidationFunction Which you can see on the docs: https://docs.oracle.com/cd/E37099_01/doc.20/e25066/config_file.htm#AELIG7204   If you look at the administrator guide:  https://docs.oracle.com/cd/E37097_01/doc.42/e35129/adm_mg_service_set.htm#AEADM209 it suggests pointing the validation function to: wwv_flow_epg_include_modules.authorize, and modifying the function: wwv_flow_epg_include_mod_local, within the APEX schema to return true or false depending if you want your procedure to be accessible or not. This unwrapped function is effectively called at the end of wwv_flow_epg_include_modules.authorize if none of the apex procedures matched. If you leave the value of security.requestValidationFunction emp

Debugging parameterised views outside of apex

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

Getting the instance URL

To get the instance URL of your APEX environment, it is not too uncommon to use OWA_UTIL.GET_CGI_ENV function calls, similar to the below block: declare l_protocol varchar2(5); l_host varchar2(150); l_script varchar2(15); l_instance_url varchar2(200); begin l_protocol := owa_util.get_cgi_env('REQUEST_PROTOCOL'); l_host := owa_util.get_cgi_env('HTTP_HOST'); l_script := owa_util.get_cgi_env('SCRIPT_NAME'); l_instance_url := l_protocol; l_instance_url := l_instance_url || '://'; l_instance_url := l_instance_url || l_host; l_instance_url := l_instance_url || l_script; l_instance_url := l_instance_url || '/'; dbms_output.put_line(l_instance_url); end; Something you might not know, is that there are a couple of helper functions in the APEX API that allow you to do exactly that. Option 1: APEX_UTIL.HOST_URL('SCRIPT') Reference:  http://docs.orac

APEX5 EA APEX_ZIP usage

I recently saw a link to a presentation that made mention of APEX_ZIP for the upcoming release of APEX (Version 5). Currently in early adopter, you can have a play around. So just thought I'd go and have a bit more of a play to see the possiblities. On the presentation, and specifically for extracting files the sample code is: declare l_zip_file BLOB; l_unzipped_file BLOB; l_files apex_zip.t_files; begin select file_content into l_zip_file from my_zip_files where file_name = :P13_file_name; l_files := apex_zip.get_files(p_zipped_blob => l_zip_file); for i in 1..l_files.COUNT LOOP l_unzipped_file := apex_zip.get_file_content(p_zipped_blob => l_zip_file, p_file_name => l_files(i)); insert into my_files (file_name, file_content) values (l_files(i), l_unzipped_file)

Oracle vim config

This page gives a pretty good overview of tweaks you can do, so I just want to re-gurgitate what I have done (from that site). Syntax Highlighting Grab the file:  http://www.datacraft.com/plnet/files/vim/syntax/plsql.vim , and save this in: ~/.vim/syntax/ Then edit your .vimrc file and add the following line (to auto detect the filetype based on the extension): au BufNewFile,BufRead *.fun,*.pks,*.pkb,*.sql,*.pls,*.plsql    set filetype=plsql Auto Indentation Grab the file:  http://www.datacraft.com/plnet/files/vim/indent/plsql.vim , and save this in ~/.vim/indent/ Then edit your .vimrc file and add the following line runtime! indent.vim .. Though, I prefer just to use auto indent - there are some special situations where it wont calculate the indentation correctly using the indentation file above. So this gives me the following in my .vimrc file: au BufNewFile,BufRead *.fun,*.pks,*.pkb,*.sql,*.pls,*.plsql set filetype=plsql au BufNewFile,BufRead *.fun,*.pks

Accessing Google data, part 2

Intro Back in Novemeber 2012, I blogged about accessing Google data from APEX - and provided some sample code to getting started. You can find this post here: http://apextips.blogspot.com.au/2012/11/accessing-google-data.html . Over the past little while, I have been (slowly) working on a more modularised API, that can be easily built upon to add support for additional Google services. I have called it PL/GAPI ( G oogle API ). It can be found on github:  https://github.com/trent-/pl-gapi . I hope the wiki on github is straightforward enough to follow in setting this up in your local environment:  https://github.com/trent-/pl-gapi/wiki Services I've started with the following services: Drive Calendar Usage Without going into too much detail (since it's all on the wiki linked earlier), you link to an authorization URL with:  &OWNER..GAPI_AUTH.BEGIN_AUTH?p_scope=&P0_SCOPE.&p_return_app=&APP_ID.&p_return_page=&APP_PAGE_ID.&p_sessi

Triggering dynamic actions from a dialog button

A typical usage scenario is you create a region, give it a static ID, and set the default display to be hidden - the latter is handled if you select your region template as modal region. A typical JavaScript dynamic action to display the dialog, would be: $('#dialog-region').dialog({ width: 500, title: 'My Dialog', buttons: [ { text: 'Submit', click: function() { $(this).dialog("close"); } }, { text: 'Cancel', click: function() { $(this).dialog("close"); } } ] }) So the question is, how do we get some other dynamic action to fire when the button is clicked? Option 1 On the button, give it an id property, such as: { text: 'Submit', id: 'btn-submitDialog' click: function() { $(this).dialog("close"); } } And then, on your dynami

Times at specific time zones

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

Getting around large exports with RTF report templates

Image
If you are on a slow connection like me, you may find how much of a pain it is deploying apps that house some BI publisher report templates. It only takes a few templates before your app export becomes 10MB+ in size. Perhaps in a future release, report templates will be treated in the same way as application images, in that you can add them as a script to supporting objects so that you don't need to export the templates each and every deployment. If you haven't seen, apex_util has a procedure to download report queries, so by using that procedure coupled with storing your files in your own maintained table, you can avoid the issue of massive application exports. First create a table to store the templates: create table rtf_template( code varchar2(25) PRIMARY KEY , layout BLOB); / Then, add your template file to the table. We will need two application items (or 1 will do if you identify the layout with exactly the same name as the query). So I've created:

Database connectivity with Google Sheets

Image
Overview I happened to be watching some Google I/O videos from 2013 last week and was watching some on apps-script, which I had never really used up until now. They mentioned about having JDBC connector so that you could connect to external data sources (Oracle, MySQL, SQL Server, etc). It's worth noting, there are 2 types scripts that can be container bound to a spreadsheet: 1. Add ons 2. Script gallery Script gallery is not supported in the new version of sheets, in favour of add-ons. There is a market place where you can search for and install add-ons, and if your organisation is using google apps, you can even restrict your add-on to your organisation. Unlike the script gallery where you could view the source, that is not the case for Add-ons (the code is not visible to end users). Excluding if you share the document with the development version of the code attached to the document. Add-ons are currently in developer preview, so whilst you can develop add-ons, only

Finding components with a particular build option

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

SQL Developer Configuration

Can be found here: ~/.sqldeveloper/4.0.0/product.conf