SQL Developer insight

When using SQL Developer, there is a feature called Completion Insight which help you write SQL and PL/SQL in the SQL Worksheets. It will auto-pop helpful things, like object name when typing a SELECT statement, column names if it is aware of the object which you are accessing and things like that.

The pop-up seems to pop-up inconsistently, appearing when I don’t want it and not appearing when I do. I finally snapped and decided to work out (look up) exactly how this feature works. And it’s like this.

1. Make sure your SQL is correctly formed. If you have multiple bits of SQL in the Worksheet, and they are not terminated with a “;” or “/“, then it can get confused about where you are contextually within a statement and not pop-up.

2. The Auto-popup has a delay of either 0.3s (SQL) or 0.6s (PL/SQL). This can be changed in Tools -> Preferences -> Code Editor -> Completion Insight. I have now set a 1.0s delay so it’s not quite so eager to appear.

3. You want the pop-up to appear? Press CTRL + SPACE. If it still does not appear, then it cannot determine your SQL Context (see point 1) or there’s nothing to pop.

Hope this helps you. It’s certainly helped me.

Oracle Join Syntax for ANSI people

I was talking with a non-Oracle DBA the other day about Oracle SQL syntax for joins, as I had written some SQL which he didn’t understand, using the old form of Oracle notation to signify outer joins – the (+) operator. It got me thinking; I have been using Oracle for a very long time, before ANSI Join syntax was allowed. I therefore tend to use the Oracle-specific format for joins as my brain it wired to simply understand the notation. However, I understand the standard ANSI syntax as I do use and administer other RDBMS systems (maily SQL Server, but I am also familiar with the *= syntax used by Sybase.)

I was wondering, which syntax do you use? If you use the ANSI standard joins with Oracle, have you come across any drawbacks or problems; I seem to recall some anecdotes but can’t put my finger on any?

Examples of the Syntax:

--Inner Join ANSI
select def.username,usr.username,usr.account_status
from dba_users_with_defpwd def join dba_users usr on (def.username = usr.username)
order by 2
--Inter Join Oracle
select def.username,usr.username,usr.account_status
from dba_users_with_defpwd def, dba_users usr
where def.username = usr.username
order by 2

--Right outer join ANSI
select def.username,usr.username,usr.account_status
from dba_users_with_defpwd def right outer join dba_users usr on (def.username = usr.username)
order by 2

-- Right outer join oracle
-- put the (+) where you want to say "match regardless" [on the "wrong" side]
select def.username,usr.username,usr.account_status
from dba_users_with_defpwd def, dba_users usr
where def.username(+) = usr.username
order by 2
%d bloggers like this: