Search

Top 60 Oracle Blogs

Recent comments

April 2010

Hotsos2010 - Training Day Tanel Poder

Media: 
See video

ASH

No, not active session history – but the volcanic stuff that’s drifting through the skies and keeping me in Denmark after Miracle Open World 2010. I’m feeling a little lucky at present (compared to the other visiting speakers – especially the American contingent). My flight home on Friday was postponed then cancelled, but my diary [...]

What I learned today :)

That I grew my beard for a reason.

I am apparently more trustworthy looking simply because of it.

And here I thought I was doing it to save money on razors. Good side effects are always nice.

Row count

Here’s a quick and dirty script I wrote some time ago when looking at a problem relating to “buffer busy waits”. We were seeing a large number of waits on a particular table (which we could identify easily from the “Segments by …” part of the AWR report , and I wanted to be able to [...]

When column name matters

There’s a thread on the OTN forum with a case of amusing behavior of the 11.2.0.1 JDBC driver: it fails to detect properly all bind variables in an SQL statement referencing column named NORDER. WTF?! PS. 1) Oracle’s SSO is actually Multi Sign-On because I have two accounts (one for OTN forums and one for [...]

Michigan OakTable Symposium (MOTS) 2010

The Michigan members of the OakTable Network are organizing a two day Oracle training event on the Thursday and Friday (September 16th and 17th, 2010) just before Oracle OpenWorld. OakTable Network members from around the world will be providing sessions at the training event in Ann Arbor, Michigan (not far from the University of Michigan) at the Four Points by Sheraton hotel. Presented sessions will appeal to both DBAs and developers. Seating at the event will be limited to 300 people.

For more information (speakers, agenda, pricing, etc.) visit the home page of MOTS.

I will be giving refined versions of my "Understanding System Statistics" and "FIRST_ROWS(N)" sessions.

The early bird registration ends April 30. Hope to see you there.

Never Imagined...

I never imagined that I would have to cancel a seminar, because of....

A volcano.

I was supposed to be in Hungary for a two day seminar Monday and Tuesday - which I had to cancel since the airlines told me the earliest I would be able to leave for Europe would be .... Tuesday night!

I also had to cancel a planned in person appearance at the Bulgaria Oracle User Group - we are going to try to make it a 'virtual' appearance for some technical sessions if possible. That'll take place next Friday the 23rd.

So, to anyone that was signed up for the seminar in Budapest - I apologize, but there is no way to get there from here right now. We'll be looking for a reschedule date now...

Evaluating an expression, like a calculator...

Today, I learned a new 11g Release 1 and above 'trick' that I wasn't aware of. This is pretty cool.

A frequently asked question in the past has been:

I have a string, with some calculation in it - like "1+2/3". I would like to evaluate that string and get the result. How do I do that.

Historically - the answer has been "dynamic SQL, but please be careful to not flood the shared pool with tons of literal SQL and be really careful about SQL Injection!"

Now, I learned a new way. It came from this post, thanks to the frequent asktom poster "Sokrates"


ops$tkyte%ORA11GR1> select * from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

ops$tkyte%ORA11GR1> variable x varchar2(40)
ops$tkyte%ORA11GR1> exec :x := '55+42*123/3'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select xmlquery(replace( :x, '/', ' div ' )
returning content ) .getNumberVal()
2 from dual
3 /

XMLQUERY(REPLACE(:X,'/','DIV')RETURNINGCONTENT).GETNUMBERVAL()
--------------------------------------------------------------
1777

It works for very very simple expressions - XQuery arithmetic expressions are very simple. They support +, -, *, div, idiv (integer division), and mod


ops$tkyte%ORA11GR1> exec :x := '(55+42-124) idiv 3 div 2 mod 5*2'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> /

XMLQUERY(REPLACE(:X,'/','DIV')RETURNINGCONTENT).GETNUMBERVAL()
--------------------------------------------------------------
-9

See http://www.xquery.com/tutorials/guided-tour/xquery-operators.html for a guided tour of what you can do.

MOW Opening Video

Media: 
See video