Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.
Creating inline functions within a SQL statement was relatively easy.
WITH FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS BEGIN RETURN p_id; END; SELECT with_function(event_no) FROM events /However a slight adjustment is required for DML. The documentation suggests that
but does not give any examples, which I think is unfortunate - but thanks Tim for getting us started ;-)
UPDATE /*+ WITH_PLSQL */ events e SET e.org_id = (WITH FUNCTION inline_fn(p_id IN NUMBER) RETURN NUMBER IS BEGIN RETURN p_id; END; SELECT inline_fn(e.org_id) FROM dual); /Without the hint Oracle returns
ORA-32034: unsupported use of WITH clausebut I was getting
ORA-00933: SQL command not properly endedWhat clued me in was the brief highlight SQL Developer makes over the statement before it executes. For me this paused at the return statement within the function.
I happened to be using one of the pre-built Oracle Developer VMs to play around, and it turns out the one I'm using has SQL Developer 18.104.22.168 supplied.
That particular version doesn't seem to be aware of this bleeding edge feature. I vaguely recall seeing this mentioned somewhere probably in the vicinity of thatJeffSmith fellow. I tried it in the command line SQL*Plus and it worked fine against the 22.214.171.124 instance.
It does ring a clear bell for once upon a time circa 2006 working on Oracle 9i or 10g when I sent an email to a colleague containing a SQL statement including a WITH clause.
He didn't have success in his Oracle 8i SQL*Plus windows client either... oh how I miss thee.