This is a list of all the table types I’ve found in Oracle-supplied packages, e.g. OWA_UTIL in 10g supplies the type:TYPE datetype IS TABLE OF varchar2(10) INDEX BY binary_integer;
I find them handy for quick one-off scripts.
An interesting column comment encountered:
MOOD_SWINGS_IND VARCHAR2(1) (Y/N) “indicates that the person was in a swinging mood at the time of the episode”
Another enjoyable conference. I’ve attended it every year since 2000 and this year’s was packed with presentations. Over 300 attendees spread over up to six presentations meant less crowding, I think; although as always there were a few packed sessions.
Highlights for me were:
Thanks to the AUSOUG committee for another well-organised event.
Looking at the column comments on this table, I can sympathise with the poor soul who after painstakingly describing 100 columns just decided that it just wasn’t worth it…
table audit_history (column comments in quotes):
id number(18) “Unique identifier”
aud_table_name varchar2(30) “audit record table”
audit_action varchar2(50) “audit action e.g. update”
aud_timestamp date “timestamp of the audit”
aud_user_id varchar2(100) “user performing the action”
aud_col_1 varchar2(4000) “audit column 1”
aud_col_2 varchar2(4000) “audit column 2”
aud_col_3 varchar2(4000) “audit column 3”
… (etc.) …
aud_col_99 varchar2(4000) “audit column 99”
aud_col_100 varchar2(4000) “audit column 100”
aud_col_101 varchar2(4000)
aud_col_102 varchar2(4000)
… (etc.) …
aud_col_139 varchar2(4000)
aud_col_140 varchar2(4000)
Tip: Don’t let anyone even think about using this kind of design for their change history auditing requirements. It might look elegant to them, but it is not good. Just, don’t.
I’m starting to come around to Oracle SQL Developer. At home I only use free software so that’s obviously a big plus, but at work I’m still using PL/SQL Developer (Allround Automations) and SQL*Plus.
These are the features I like best about these products:
SQL Developer:
PL/SQL Developer:
I’d like to see Oracle work on some of these features before I switch over at work. I’d also like to see SQL Developer able to export LOBs when exporting tables to XML.
I upgraded Oracle SQL Developer from 1.0.14.67 to 1.0.15.27, just for the heck of it. Unfortunately, all my saved connections had disappeared! After a text search I found the connections are stored in a file called IDEConnections.xml under the folder sqldeveloper\jdev\system\oracle.onlinedb.11.0.0.37.25. I copied this across to a new folder that had been created (oracle.onlinedb.11.0.0.37.36) and bingo they’re back again.
Bonus – now I know what file to back up if I want to restore my connections later on.
This tip provides your users with the option of choosing a refresh interval for the page. The following steps were tested with APEX version 2.2 but should work on earlier versions of APEX or HTMLDB.
F100_REFRESH_INTERVAL)F100_REFRESH_INTERVAL_COMP):
F100_REFRESH_INTERVALREFRESH_INTERVAL):Display – Return
1 sec – 1
5 sec – 5
10 sec – 10
30 sec – 30
1 min – 60
5 min – 300
(you can change this list how you like, as long as the return values are positive integers)
(it is probably not a good idea to provide zero seconds as an option, as this will cause the page to continually refresh without giving the user much chance to intervene)
Now, for each page you wish to have auto-refreshed, edit the Page Properties:
<meta http-equiv="refresh" content="&F100_REFRESH_INTERVAL.">Somewhere in your application you will want to provide the user with a way of modifying the refresh rate. I prefer to provide this on the same page that is auto-refreshed.
Create an Item (e.g. P1_REFRESH):
F100_REFRESH_INTERVALREFRESH_INTERVALI also like to show the date/time when the page was last refreshed. To do this, I just add a display-only text item to the page with:
TO_CHAR(SYSDATE,'Dy DD Mon HH24:MI:SS')Now, the page will be auto-refreshed according to the default interval, and the user can change the refresh interval using the select list.
You can add the item and the auto-refresh header to any page you like. If you need a different refresh interval on different pages, you will need to create additional Application Items, along with their own Application Computations to initialise them.
If the user hits the “Stop” button in IE, the page seems to stop auto-refreshing. I don’t know if this feature works the same in other browsers, however.
Noticed that the final conference programme is now available. Unfortunately I don’t have a laptop so I can’t attend some of the hands-on sessions.
I’m planning to at least attend the following:
Unfortunately, the schedule precludes me from Systematic Oracle Performance Tuning (Guy Harrison).
From the Delegate Program:
“…a smattering of such features and/or techniques Connor has encountered over the years, that he has either created himself, read about, or simply stolen from someone smarter.” (Odds and Ends, McDonald)
– plagiarism is the sincerest form of flattery… 🙂
“Oracle recently announced the future obsolescence of SQL*Plus GUI version… [SQL Developer] allows easier access for less technical users… The session will include basic SQL syntax training so that End Users can utilise the product to access their data.” (SQL Developer Hands-on Workshop, Kate Marshall)
– I personally have never come across any end-users who currently use SQL*Plus; I have, however, worked with some business analysts who can customise simple SQL using a helper like Discoverer; perhaps they’d be the kind of person who would benefit from this session. Looks like Chris Muir is giving a similar talk in Melbourne, although for some reason it’s in the “Applications” stream.
“Since Oracle introduced the shared pool in the Oracle kernel, it has been causing problems… these problems are because of Oracle bugs, …This presentation will have a closer look at what application developers do wrong.” (The Life of a Cursor and Its Impact on the Shared Pool, Anjo Kolk)
– hey, why should we expect presenters to sugar coat their topics?
Most interesting presentation title: Guaranteed Project Failure
Runner-up: Make Money Fast and Improve Your Love Life with Oracle…
Most boring presentation title: Off shoring Oracle Support: Alcoa’s journey …but the presentation itself will be interesting I’m sure 🙂
I’ve been enjoying learning Oracle APEX the last few weeks, and have started to appreciate the depth of the product – there’s more than meets the eye. At first I was dependent on the wizards (of which there are many) for just about everything except SQL and PL/SQL. When I came across problems I’d just delete and re-create the entire page – but now I’m more often able to find the source of a problem and fix it. I’ve found that having a good knowledge of SQL, PL/SQL, HTML and Cascading Style Sheets is very helpful when working with APEX.
Just read the latest draft timetable, looks like there’s going to be some very interesting presentations this year. It looks a lot bigger than before, mainly because of the merger with Apps. I’m particularly looking forward to: