Posts

Showing posts from 2012

Tabular Form Validations And Processes Beyond APEX 4.0

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 substitutio

Accessing Google Data

Image
First I want to point out, the code posted here is in my no means complete, but I believe it provides a good foundation to extend on. To point out some of what is lacking - I am not fetching the refresh token to easily get a new access token without user intervention. I am not handling when the access token is no longer valid. Etc. Also, since a lot of the responses result in JSON, I used the PL/JSON package throughout. I also unfortunately cannot set up a demo on apex.oracle.com due to the obvious limitation of the wallet, and using utl_http. You may know, google has a series of API's that allow you to access your data programatically, to create third party apps. Originally when I looked at the docs, it was using OAuth 1, and I never ended up mastering it. Oleg made a post about the differences here:  http://dbswh.webhop.net/htmldb/f?p=BLOG:READ:0::::ARTICLE:889800346602035 The google docs are here, and they are quite detailed with what to do:  https://developers.google.com/ac

Redirect to tab from LOV

Image
For this basic example, let me use it to re-direct to one of my tabs in my example application. I have Plugins, Google, Comments. Their submit values are T_PLUGINS, T_GOOGLE, T_COMMENTS respectively. Of course, this example was designed with the fact of apex set up with the tabs infrastructure and not lists - and to get to a specific tab, you submit the page with the tab name. Normally, I would use a LOV from an SQL query, however for this basic example, let me just create a static LOV query. Create the LOV in the shared components - you can find out more information about this in the builders guide - see:  http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/bldapp_lov.htm#HTMDB25450 Specify name as PAGE_TABS and type as Static. From here we want to set the display value as something that the user will see, and the return value as something we can use to. Se specify the display value as the tab display value, and the return value as the tab name. Once done, on your page

Two Apex 4.2 Noteworthy APIs

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 => 2);

Enhancing gedit for PL/SQL Development

Most text editors allow customisation of syntax highlighting and the like, and having recently moved to using gedit, thought i'd enhance it a bit. In a fresh installation, it already has reasonable support for oracle based keywords, but could do with a bit of an update. The object that does the syntax highlighting is gtksourceview - depending on the version of gedit, either version 2 or 3.  By going to the project page on gnome, you will see the howto for adding a new language:  https://live.gnome.org/Gedit/NewLanguage . (Linux only) Since package source code is typically in files with the extension pks and pkb, it is first necessary to update the system to recognise that those files are of the mime type text/x-sql (or a custom one if you really want). So, create an xml file with the following: <mime-info xmlns="http://www.freedesktop.org/standards/shared-mime-info"> <mime-type type="text/x-sql"> <comment>SQL Source including P

Saving Files Client Side

A couple of html5 interfaces that allow you to download files from the client side are: FileSaver BlobBuilder These haven't quite made it into the browsers, but there are projects on GitHub for both of these interfaces, whilst we wait for them to become main stream. The basic examples on the FileSaver project README are saving both a text file based on an input string, and saving a canvas as an image file (you can already redirect the user to the data url of the canvas object, but this would actually download the file). See:  https://github.com/eligrey/FileSaver.js  and  https://github.com/eligrey/BlobBuilder.js . Anyways, i'll give a little focus on saving a file.  I have a basic page with a text field to be used as the file name (:P20_FILE_NAME) and a text area with the data that I want to save to a file (:P20_EXPORT_CONTENT). First I need to add a reference to the two interfaces (I just added these into the page header - obviously better to import these files into

Error Handling API

Originally heard about this from Patrick's blog months back. See  http://www.inside-oracle-apex.com/apex-4-1-error-handling-improvements-part-1/  and  http://www.inside-oracle-apex.com/apex-4-1-error-handling-improvements-part-2/ . Then a couple of weeks back I was looking at the new features on the Apex builder guide, where they mention about the enhanced error handling. See  http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/what_new.htm#CEGIEDHE . So I thought I may as well set up a basic example to see how it is. The only sections in the documentation that really talk about it are: Application Definition Page Definition Which is basically about setting the error handling (PL/SQL) function. The main source of information is on the API docs for apex_error . This page talks about data types, and available functions/procedures you can use with this package, which will allow customising the displayed error message. Whilst the API docs do provide a good example, I ju

Oracle HTTPS Requests: Set up the Wallet

In addition to setting up the ACL, if you wish to make HTTPS requests you will need to set up a wallet - and more than likely, import trusted certificates. On the oracle server, open the wallet manager. In linux this is with the command: owm. Assuming no current wallet exists, create a new one by going to File --> New. At this point you will need to give the wallet a password. Save the wallet and take note of the directory it installed to (this is what you need to pass to utl_http, and no single file). The default directory this is saved to is generally: $ORACLE_HOME/owm/wallets/oracle. The next step is to import trusted certificates into the wallet. The process varies between OS/Browser. First, go to the website you are intending on making requests to and view the certificate information which is normally done by clicking a button in the address bar. Linux Chrome: Click the button the left of the URL Click certificate information Click Details Click Export Specify t

Oracle HTTP Requests: Set up the ACL

In order to use utl_http requests from Oracle 11g onward (or any network services for that matter), you need to set up an ACL. Two generic permissions to grant are connect and resolve, where * can be used as a wild card. A good template I commonly use, just replacing the file name and schema name. This should be executed as a user with dba privileges. DECLARE l_filename varchar2(200) := 'file_test_http.xml'; l_schema varchar2(200) := 'FILE_TEST'; BEGIN BEGIN DBMS_NETWORK_ACL_ADMIN.DROP_ACL( acl => l_filename ); EXCEPTION WHEN OTHERS THEN NULL; -- ACL does not exist yet END; DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( acl => l_filename , description => 'All requests to test utl_http' , principal => l_schema -- schema name , is_grant => TRUE , privilege => 'connect' );

End User's Guide

Just noticed this on the documentation index. Apex now has an end users guide:  http://docs.oracle.com/cd/E23903_01/doc/doc.41/e26811/toc.htm I'm not yet certain it's a good idea linking users to this documentation in any user guides you create for your apps, given Oracle seems to like changing the structure of their links every couple of years, leaving documentation links that no longer work. Even so, it's worth knowing :-) Current sections include: About Oracle Application Express Using Websheets Using Interactive Reports About Uploading Data