Wednesday 29 February 2012

jQuery vs ApexLib... FIGHT!

Recently I was working in an environment where they were running Apex 3.2. While I didn't hold that against them, I wanted to use some jQuery functionality. There was a problem with that because they had also configured ApexLib within their Apex instance.

For those unfamiliar with ApexLib, it's a development framework that improved the out-of-the-box features of Apex. In part thanks for Patrick Wolf moving to Oracle, we now find a lot of these features incorporated into the Apex 4.x product, such as the Apex Advisor.

Back to the business at hand, I must first state I'm no JQuery expert. I know how to copy & adapt, the rest I'm working on. What I do know is that having two JavaScript frameworks on the same website may cause the universe your website to explode with obscenely generic errors such as:

Message: Object doesn't support this property or method

The long and short of it is we need to use jQuery.noConflict()

This is documented specifically at the JQuery website, with further documentation about using jQuery with other libraries.

Here is all I needed to do - add the jQuery.noConflict line and modify the prefixes to use $j.

If you are currently running Apex 3.2 and plan to upgrade to Apex 4.x, this OTN forum entry may also be of interest.

As for the post title, it's funny the random things that make you think of something from a long time ago... I digress.


Friday 24 February 2012

Thursday Thoughts

See the trick is, you're quite possibly reading this somewhere tomorrow where from your perspective you presume it was en-titled, then written accordingly. So while it may not be Thursday somewhere (I gave up after a while and a few Friday loudmouth soups), I'm close enough.

That constitutes what may regularly occur in what I aspire to be a semi-regular blog event at grassroots-oracle - Thursday Thought. Anyone who'se seen or read any of my presentations possibly recognise my admiration of alliteration - that's why I chose Thursdays ;-)

As with my occasional Friday Funny, They may be about something going on in the Oracle world, probably not technical, potentially something completely random, could be a little science-ish, let's see how it develops - but I'll attempt to keep it brief.

Something regular, something different, hopefully interesting to pause your occasional day.

Today I thought I'd mention Steven Feuerstein on a topic neighboring ethics.

Thanks to what I'm sure must be that wonderful phenomena that is the bell curve - interactive, competitive websites such as the PL/SQL Challenge, must weed out those people that make it a less than scientific reflection of the informative and interesting data that is PL/SQL skill, awareness & use.

à la - having separate accounts to pre-read an calculate the true answer to achieve unrealistic answer times.

Every quarter a collection of the top competitors in those three months face off in a timed competition of a number of questions. I think Steven's installing a very interesting net in an attempt to catch those who perhaps don't deserve to be there, and make more room for those that do. Eliminate those outliers...

A simple rule
If the performance on these qualifier quizzes is substantially worse than the player's performance during the quarter  (... then eligibility kicks in)
As I mentioned in my response to his announcement, I support the move and think it will also improve the general integrity of the quiz. I'm sure Steven's had a good time to practice the techniques to allow them to evolve into criteria intelligent enough to catch the right ones.

I wonder what other techniques website administrators use to discard the relative undesirables, in the varying technologies and genre?

Here endeth today's Thursday Thought.

Wednesday 22 February 2012

Executing CLOBs as DDL

How to kill your session

11g> declare
  2  v_clob clob :=  EMPTY_CLOB(); -- initialize clob
  3  begin
  4  execute immediate v_clob;
  5  end;
  6  /
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8540
Session ID: 196 Serial number: 364

How to execute it properly

11g> declare
  2  v_clob clob :=  EMPTY_CLOB();
  3  begin
  4  v_clob := to_clob('begin null; end;'); -- assign ddl value to clob
  5  execute immediate v_clob;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Something you couldn't do in 10g

10g> declare
  2  v_clob clob :=  EMPTY_CLOB();
  3  begin
  4  v_clob := to_clob('begin null; end;');
  5  execute immediate v_clob;
  6  end;
  7  /
execute immediate v_clob;
ERROR at line 5:
ORA-06550: line 5, column 19:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

Related OTN post.

Documentation here.

Related to an idea regarding lifecycle management.

Friday 17 February 2012

AUSOUG event - See Connor bend time

Attention AUSOUG members - come by for a nice breakfast and listen to one of Perth's slickest presenters, Ace Director Connor McDonald!

If you're on our mailing list, you check your e-mail - if not, please contact us about joining, particularly if you think your corporation has group membership.

Where: Oracle Australia: Level 2, 66 Kings Park Road, West Perth

When: Wednesday, February 29, 2012. Breakfast starts 7:30am. Connor will speak from 8-9am, question time afterwards for those who can stick around.

RSVP: By RSVP Sunday 26th February 5pm by emailing

Cost: Free to all AUSOUG members. Ticket parking on Walker St, just off Kings Park Road, or catch the Red CAT, with a short walk from corner of Outram/Ord.

Abstract - Flashback:
We have all heard the term "adrenalin rush".  It comes about 3 seconds after you press the Commit button and you realise that you probably needed to have a WHERE clause on that "delete all rows from the SALES table" SQL statement.  Or it might come just after you run "drop table" on the Production database, when you were just so sure that you were logged onto the Test system.  This session will discuss how you can use the FLASHBACK features in 10g and 11g to overcome this uniquely human condition

Connor's Bio:
Connor has been working with Oracle software for 17 years for clients in Australia and the UK. He is a member of the OakTable, is an Oracle Ace Director, and has co-authored three Oracle books. He has been a popular speaker at Oracle conferences around the world for the decade, specialising in topics regarding the database engine and PL/SQL.

Find his blog

I hope to see you there,


ps- Another Oracle Ace & fellow blogger, Yury Velikanov, will be visiting sometime in March - details to come.

And don't forget next Wednesday!

Thursday 16 February 2012

February Oracle Event in Perth

I've just received details of a "beer & pizza" session on Oracle Data Integration to be held in the Oracle offices in Perth. See below for details.

Unfortunately I won't be able to make it - but I will soon release details of an AUSOUG event on 29th February involving Connor McDonald - keep breakfast free!


Invitation to join Oracle Perth for an Oracle Technology update on Oracle Data Integration
Data integration is an essential component of many critical IT projects, including business intelligence, platform migration/upgrade, data warehousing, data quality, consolidation/modernization, service-oriented architecture, and cloud computing.
Oracle data integration products offer  real-time data integration, and transactional data replication across heterogeneous system. Key capabilities include moving changed data and distribute these transactions to different targets in real time to achieve the following…
  • By synchronizing old and new systems it allows zero downtime for system migration and/or system upgrade projects
  • Bidirectional replication enables Active to Active DB configurations with seamless transitions during unplanned outages
  • Can improve system performance by distributing data for query offloading
  • Real time operational reporting
  • Feeding data warehouses with real time data
  • Distributing data across systems…and so much more.
Please join product specialist Robert Spinks and the Perth Oracle team to learn more about our high-performance data integration solutions and then join us for refreshments and pizza.

Event Details:
Date:                Wednesday, February 22nd
Time:                4pm to 5pm, then stay for refreshments & Pizza
Location:          Oracle’s Offices, 66 Kings Park Road

RSVP:  Please accept by return email to
or phone Vicky on 9324 7213

Wednesday 15 February 2012

Using format attributes in Apex

Recently I encountered some SQL in a classic Apex report that needed some attention.

To protect the innocent, let's just say the query looked like the following:
select order_id
              ,'fm$999,999,990.00') order_total 
from demo_orders;
Sure, output looks great with the currency and all, but what happens when we try to sort using the column heading?
Works great if there is the same number of digits, but as soon as we get into lower figures, our sorting appears askew.

So how should we be doing this? With attributes defined specifically for this very purpose.
So remove any formatting out of your SQL - there should be an app attribute for that.

Here endeth the simple lesson.


Wednesday 8 February 2012

APEX variables in SQL

When I was first learning Oracle Application Express, I found one of the trickiest things was deciphering when to use which substitution string. Martin Giffy D'Souza succinctly describes variable reference options here.

Oracle supplies a number of built-in substitution strings. The APEX documentation provides a number of examples of here, for example generating links and referring to the session number:
(from the Oracle Documentation)
Trouble is, each syntax has it's place - trouble is if you use the wrong one you could impact the performance of your application - as I described here.

9 times out of 10, I reckon you should be using the bind variable syntax of :SESSION

What I find when I visit clients a number of developers - typically those self-taught, use the &SESSION. syntax in queries - which is bad, and this goes for any variable you want to reference.

The reason for this is because you're essentially flooding your shared SQL area in the database with similarly parsed SQL statements. This is bad because Oracle sweats when it has to to a hard-parse on your queries, as opposed to recognising a query you've executed before, and running it again with different bind variables - this is soft-parsing which Oracle can do blindfolded with it's little toe.

To elaborate, I created to basic report pages with slightly different SQL statements.
select org_id, name -- Good query
  ,'f?p='||:app_id||':1:'||:app_session lnk
from organisations;

select org_id, name -- BAD query
  ,'f?p=&APP_ID.:1:&SESSION.' lnk
from organisations;
It's a subtle different, but the highlighted line 6 is where the curry will burn.

I enabled tracing on my application by adding a parameter to the end of my URL
(more information on tracing your Apex application can be found in the documentation)
I opened both pages a number of times, logging out a few times in the process to generate new session numbers

Then I located my trace file, ran tkprof over it, opened up the output and searched for "organisations". Forgetting all the other information for the moment, there was one instance of this:
select org_id, name
  ,'f?p='||:app_id||':1:'||:app_session lnk
from organisations
And a number of instances that all looked very similar
select org_id, name
  ,'f?p=4000:1:1223945495716883' lnk
from organisations

select org_id, name
  ,'f?p=105:1:3914512356591996' lnk
from organisations

select org_id, name
  ,'f?p=105:1:4361599949844983' lnk
from organisations

select org_id, name
  ,'f?p=105:1:8029570771757325' lnk
from organisations
See a concerning trend?

A quick peek in v$sqlarea confirmed the same issue - although I would like to ask the Oracle APEX team (or someone who knows more than me in these matters) why the parse calls is above the execution count for my "good sql" - it doesn't seem right to me.

Looking at it a second time, I notice the fourth result is from the application builder, so I would guess the extra parses come from me defining the page (5 to update the report... really?!)

One would need to obtain finer measurements to determine the hard vs soft parse count difference.

select sql_text,executions, parse_calls
from v$sqlarea 
where sql_text like '%--%from organisations%';

At the end of the day, please keep issues like this in mind when writing your queries.

Monday 6 February 2012

Awesome APEX Developer Addon

Thank you Peter Raganitsch for this fantastic ApexLib APEX Developer Addon

 PL/SQL highlighting was my first attraction

Highlighting for HTML (templates) and JavaScript is a bonus
And I'm sure the shortcuts available from the search box will become useful - especially the "goto page"
I've installed in on Firefox with the Greasemonkey addon, and I will probably do it on Chrome later.

Thanks again, Peter!

Friday 3 February 2012

ASCII table listing from SQL

Today I was chasing the relevant ASCII code for certain characters... I know there are plenty of ASCII tables on the net, but this is an interesting SQL solution, nonetheless.
select rownum "dec"
      ,chr(rownum) "char"
      ,to_char(rownum,'XXXX') "hex"
from dual connect by level < 256;
dec char    hex    
--- ----    ---
1       1
2       2
3       3
4       4
5       5
6       6
7       7
8       8
9 " "   9
10 "
"     A
11       B
12       C
13 "
"     D
14       E
15       F
16      10
17      11
18      12
19      13
20      14
21      15
22      16
23      17
24      18
25      19
26      1A
27      1B
28      1C
29      1D
30      1E
31      1F
32      20
33 !    21
34 "    22
35 #    23
36 $    24
37 %    25
38 &    26
39 '    27
40 (    28
41 )    29
42 *    2A
43 +    2B
44 ,    2C
45 -    2D
46 .    2E
47 /    2F
48 0    30
49 1    31
50 2    32
51 3    33
52 4    34
53 5    35
54 6    36
55 7    37
56 8    38
57 9    39
58 :    3A
59 ;    3B
60 <    3C
61 =    3D
62 >    3E
63 ?    3F
64 @    40
65 A    41
66 B    42
67 C    43
68 D    44
69 E    45
70 F    46
71 G    47
72 H    48
73 I    49
74 J    4A
75 K    4B
76 L    4C
77 M    4D
78 N    4E
79 O    4F
80 P    50
81 Q    51
82 R    52
83 S    53
84 T    54
85 U    55
86 V    56
87 W    57
88 X    58
89 Y    59
90 Z    5A
91 [    5B
92 \    5C
93 ]    5D
94 ^    5E
95 _    5F
96 `    60
97 a    61
98 b    62
99 c    63
100 d    64
101 e    65
102 f    66
103 g    67
104 h    68
105 i    69
106 j    6A
107 k    6B
108 l    6C
109 m    6D
110 n    6E
111 o    6F
112 p    70
113 q    71
114 r    72
115 s    73
116 t    74
117 u    75
118 v    76
119 w    77
120 x    78
121 y    79
122 z    7A
123 {    7B
124 |    7C
125 }    7D
126 ~    7E
127     7F
128 €    80
129     81
130 ‚    82
131 ƒ    83
132 „    84
133 …    85
134 †    86
135 ‡    87
136 ˆ    88
137 ‰    89
138 Š    8A
139 ‹    8B
140 Π   8C
141     8D
142 Ž    8E
143     8F
144     90
145 ‘    91
146 ’    92
147 “    93
148 ”    94
149 •    95
150 –    96
151 —    97
152 ˜    98
153 ™    99
154 š    9A
155 ›    9B
156 œ    9C
157     9D
158 ž    9E
159 Ÿ    9F
160      A0
161 ¡    A1
162 ¢    A2
163 £    A3
164 ¤    A4
165 ¥    A5
166 ¦    A6
167 §    A7
168 ¨    A8
169 ©    A9
170 ª    AA
171 «    AB
172 ¬    AC
173 ­    AD
174 ®    AE
175 ¯    AF
176 °    B0
177 ±    B1
178 ²    B2
179 ³    B3
180 ´    B4
181 µ    B5
182 ¶    B6
183 ·    B7
184 ¸    B8
185 ¹    B9
186 º    BA
187 »    BB
188 ¼    BC
189 ½    BD
190 ¾    BE
191 ¿    BF
192 À    C0
193 Á    C1
194 Â    C2
195 Ã    C3
196 Ä    C4
197 Å    C5
198 Æ    C6
199 Ç    C7
200 È    C8
201 É    C9
202 Ê    CA
203 Ë    CB
204 Ì    CC
205 Í    CD
206 Î    CE
207 Ï    CF
208 Ð    D0
209 Ñ    D1
210 Ò    D2
211 Ó    D3
212 Ô    D4
213 Õ    D5
214 Ö    D6
215 ×    D7
216 Ø    D8
217 Ù    D9
218 Ú    DA
219 Û    DB
220 Ü    DC
221 Ý    DD
222 Þ    DE
223 ß    DF
224 à    E0
225 á    E1
226 â    E2
227 ã    E3
228 ä    E4
229 å    E5
230 æ    E6
231 ç    E7
232 è    E8
233 é    E9
234 ê    EA
235 ë    EB
236 ì    EC
237 í    ED
238 î    EE
239 ï    EF
240 ð    F0
241 ñ    F1
242 ò    F2
243 ó    F3
244 ô    F4
245 õ    F5
246 ö    F6
247 ÷    F7
248 ø    F8
249 ù    F9
250 ú    FA
251 û    FB
252 ü    FC
253 ý    FD
254 þ    FE
255 ÿ    FF
I think I'm glad I don't need to work in a multi-language / character set environment.

Wednesday 1 February 2012

Please practically plan your prezi presentation

At the 2011 AUSOUG Conference I gave a presentation on simply called Oracle Apex 4.1 Security.

The exciting thing was (well, I thought it was exciting) I gave powerpoint the flick and used Prezi instead. I've got oraclenerd to thank for putting me onto it.

  • Plan - it's an easy thing to say, particularly with a new presentation technique, however plan the talk as much as you can. I find it best going big picture first, then keep drilling down to details Fits well with the prezi technology.
  • Map out your list of topics - When I write a powerpoint presentation I do a similar thing. I list out my major points, decide on their order, then expand on each. 
  • Map out your "slide" order - I found this important with the security presentation. I had a map of my infrastructure and I thought this will work really well in this style, but I wasn't sure what order to run with.  In the case of prezi, you need a physical path to follow, and the better planned that is - the better the presentation will look. I'm sure this will come easier over time. Consider it story-boarding.
  • Zoom ideas (instead of slide) come - prezi has a different delivery style to powerpoint. Where powerpoint is slide after slide, and interesting interactions and features can be delivered between slides, zooming lends itself to different techniques, different delivery ideas. And unfortunately, you can add animation (not yet anyway) so bear that in mind because I came across that yearn regularly.
  • Then list zoom locations, just like slide deck - I think when I noted this I meant to make it easier for you to know what's coming up. I wrote one up to remind myself of the general order - but forgot on the day, it was ok though because I practiced!
  • Put in brackets your idea/what you might mention - this will help if it takes you a while to write your presentation. Ideas sound great at the time, you capture the frame, then you forget a month later what the revelation was that you were going to share with the audience. The comments will also be handy for on-line viewers, but maybe not necessary on the day of delivery - although a one-liner around a frame while talking might be valuable to your audience, you'll see it in a few of mine.
  • Place your images/test in each slide - when I first started I found myself shifting things around until near perfection for each frame. I think there is more value in bundling your bits and worry about them later, so
  • Lay them out generally as you'd think you'd need them - finer tuning of image location, added words etc can come later again.
  • I've also been finding myself worrying about frame locations later - perhaps do the major ones first - particularly since there is a slide sorter now, making it even easier to add content where ever you like 
  • When "later" comes - that's when you can do the finer points. A bit like writing a report, get the data right, then think about layout.
The slide sorter is really handy. That came along for my Apex Security presentation, and made a big difference in playing around compared to when I rewrote the Apex Performance from powerpoint to prezi.

Don't worry about the slide sorter when you want to add say, slide 8 out of 120. There are little dots in the lines between frames where you can create a new frame. Check out the prezi tutorials, they answer everything.

You might find this interesting, I kept the notes I made from my first run through. I made a few notes, I found this better than trying to fix things up on the fly. Just do a run through, note down what needs attention and maybe where, then work through your list.

So, some insight into my brain:

-medieval castle - where is word defence in depth
-skips layer 8
-put monitor around browser
-question audience for hands! ff vs ie vs chrome
-skips xss
-zoom out a bit more before apex & db
- zoomed to apex area
-seems to skip something before "protect your items" - just mouse
- changed protect your... to green
-highlight tamper popup name
-is dr evil last before xss
-add pic of trojan horse
-put escape_sc after escaped html
-skips dbms assert
-zoom out to db first, skips out too fast
-my dmz comment after cloud?
-zoom to computation section of apex_dml_lock...
-skips tde heading - nope, was mouse
-zoom out to db after driving_site
-frame vpd redness
-add comment "per page for :APP_USER" - changed mind
add red comment  under added to all queries "user_name = :APP_USER"
remove p_name from p_set_context!!
get trace of policy in use, to prove performance question
-highlight set_context block
-under chart under "fires for every page", add "thanks to the session pool"
-zoom down to equation: pic of lego man with APEX_PUBLIC_USER shirt + pic of
-pool + pic of party
-add "automagically" in green in data encrypted...
-after zoom out to apex + db, zoom to apex
-duplicate zoom on user groups
-add "apis exist" under blue line at "allocate to your apex users"
-remove "authentication scheme definition" from view on custom attributes
-add to custom authentication source "-- this is a documented example" in
-green, then " but it doesn't work" in red somewhere - doesn't it????? - not anymore
-change post auth code to be just "select 'Y'"
-add on same page in green "because it solves a problem" in blue bar
-add 4.1 to evaluation point for authorisation schemes, default changed

save this for blog
count the 4.1s*/

And at the end of the day, it turned out really well - despite the fact I overslept and made it to the venue with 10 minutes to spare before my presentation, and I forgot my dongle for my (vital) clicker.

The delivery style is fresh, probably not for everyone - I can't see someone like Connor adapting this technology. His style is more suited for powerpoint. If you've never seen it, try watch him some time - especially his presentation on optimizer statistics. It's similar to Dick Hardt & Larry Lessig.