Trent's Tech Tips

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...

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...

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)...

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

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...

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...

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...