What should you do if you have an anydata column in a queue table and you don’t have any tool to read from it(sqldeveloper doesn’t support it natively)?
I have written some code to extract all the information from such a column and print it, whatever the content would be.
You can find out more (more…)
Apex: Report Sort Option showing strange behaviour …
A few days ago I was making a basic report page in ApEx, but when I tried to run the page I got the next error:
Although the report was based on a simple select-from-one-table, it took me quite some time to found out what went wrong:
The first thing I did was checking my SQL Query:
My query is running smoothly in my SQL Developer and I didn’t modify anything on the column/report attributes; so I expected my report to display normally.
So why is it producing this “ORA-00911: invalid character” error?
On the Oracle apEx Forum I made a post explaining my problem.
Meanwhile I did some further investigations; I removed my custom header-names and my sort option. I was amazed when all of a sudden my report was displaying normally like nothing ever happened. When enabling the sort function again , the above error showed once more.
When posting this new info on the forum, someone made it clear to me that I had to remove the ‘;’ at the end of my query. I followed this advice and enabled the sort option again; and indeed my report was displaying normally with the sort option on. The explanation is that when you establish a sort via the wizard, Apex is dynamically extending your SQL statement to apply the order by clause and the “;” makes the SQL invalid.
So to cut a long story short: don’t use a ‘;’ at the end of your SQL query in your report source, the query runs fine without it and you have no problems enabling other options.
Apex: Using a Date Format Mask at Application Level
Every Apex application contains a lot of report pages with date columns and form pages with date fields. For all these items you can specify a date format mask. Never asked yourself if it is possible to define that format mask only once at a central place in your application?
With PICK_DATE_FORMAT_MASK you can. This is a user defined substitution string at application level. It is the best way to keep the dates consistent throughout your application.
Creating the PICK_DATE_FORMAT_MASK substitution string is done on the edit application attributes page. So, navigate to “Edit Attributes” (application level) à “Definition” and scroll down to the “Substitutions” region. Enter “PICK_DATE_FORMAT_MASK” in the “Substitution String” column and enter your date format mask (here “DD-MM-YYYY”) in the “Substitution Value” column. Press “Apply Changes”.
Suppose we have a report page presenting table “EMP” with column “HIREDATE”. For that “HIREDATE” column we want to use our application level format mask.
Go to the “Column Attributes” page of the column “HIREDATE” and go the “Column Formatting” region. For “Number/Date Format” enter “&
PICK_DATE_FORMAT_MASK.”. Press “Apply Changes”.
Suppose we have a form page to create or edit a row from table “EMP”, with also the “HIREDATE” column. For that “HIREDATE” column we want to use a date picker with our application level format mask.
Go to the “Edit Page Item” page of the item on “HIREDATE” (here “P2_HIREDATE”) and go the “Name” region. For “Display As” choose “Date Picker (use application format mask)”. Press “Apply Changes”.
This is the result when we run the form page:
Formatting data while selecting it from the database can be performed by including the PICK_DATE_FORMAT_MASK in a TO_CHAR function:
TO_CHAR ( hiredate,
ICK_DATE_FORMAT_MASK )
Converting the text presentation of a date item to a date using the correct format mask can also be done by using the PICK_DATE_FORMAT_MASK in the TO_DATE function:
TO_DATE ( 2_HIREDATE, ICK_DATE_FORMAT_MASK )
Apex: How to Disable a Text Area ?
Every application contains pages where the data should be displayed in read-only mode.
For a normal text item, you can declare a field as “Display as Text Item (does not save state)” and the value is shown as HTML
(see Text 1).
For a multi-line text area there is not a similar option.You can leave it as such by defining it as “Textarea (auto-height)”
(see Text 2).
This can be confusing because the enduser can edit the field and have the impression that he may change the content.
Another possibility is to set on the Element-level for that textarea-field the HTML Form Element Attributes to “disabled“.
The disadvantage of this solution is that the scrollbar disappears; so, when you are dealing with a large textextract, you may not see the complete content (see Text3).
Javascript can help us to resolve this problem. By defining the two following steps, you can scroll within the textarea, but you may not change the text
(see Text 4):
Apex: Caveat PK column name length
It is highly encouraged in Apex to use system generated, single primary key columns whenever possible.
When choosing multi-column, composed primary keys, you quickly get in trouble when dealing with the standard MRU Processes. This is limited to handling tables with at most a 2 column primary key.
So, it is a good practice to work with auto-generated, sequence-based PK’s, on 1 column.
There is one caveat that you should be aware of.
Within Oracle, the maximum length of a column name is 30 characters.
However, when you plan to build tabular forms on your tables, the length of the primary key column name may not exceed 22 characters.
ApEx Sample Application … Great … But …
Creating the sample application by default is a nice idea within Apex.
It is simple. You define a new workspace, and you get if for free … Great …
When you are a newbie, this application gives you a good idea of the possibilities of the tool.
But after a while it gets annoying to remove each time you make a new workspace that application and especially all those default demo_xxx tables, created in your oracle-schema that owns that workspace.
Did you know that you can suppress that automatic creation of this demonstration application ?
You need to login as Apex Administrator. Navigate to Manage Service / Manage Environment Settings In the region “Application Development”, you can switch off the creation of the demonstration objects in new workspaces. 
When you still want to play with this sample application afterwards, you can always use the “Application Creation Wizard”. One of the options there is the creation of the demonstration application.
