Monday, February 29, 2016

Client support for WITH using PL/SQL

My employer has been using 12c for about a year now, migrating away from 11gR2. It's fun working out the new functionality, including wider use of PL/SQL.

In the 'old' world, you had SQL statements that had to include PL/SQL, such as CREATE TRIGGER, PROCEDURE etc). And you had statements that could never include PL/SQL, such as CREATE SYNONYM, CREATE SEQUENCE. DML (SELECT, INSERT, UPDATE, DELETE and MERGE) were in the latter category.

One of the snazzy new 12c features is the use of PL/SQL in SELECTs, so we have a new category of statements which may include PL/SQL. In some cases that confuses clients that try to interpret the semi-colons in PL/SQL as SQL statement terminators.

SQL Plus

The good news is the the 12c SQL Plus client works great (or at least I haven't got it confused yet), so gets a grade A pass. However, if you're stuck with an older 11g client, you have to make accommodations to use this 12 stuff.

Fortunately, even the older sqlplus clients have a SET SQLTERMINATOR statement. By setting the value to OFF, the client will ignore the semi-colons. That means you'll be using the slash character on a new line to execute your SQL statements. Given the necessary workaround, I'll give it a B grade, but that's not bad for a superseded version of the client.

SET SQLTERMINATOR OFF

WITH
 FUNCTION r123 RETURN NUMBER IS
 BEGIN
   RETURN 123;
 END;
SELECT r123 val
  FROM dual
/

SQLCL

If you grab the latest version of SQLcl (mentioned by Jeff Smith here) you'll be fine with the WITH...SELECT option. It also seemed to work fine for the other DML statements. Note that, as per the docs, "If the top-level statement is a DELETEMERGEINSERT, or UPDATE statement, then it must have the WITH_PLSQL hint." 

INSERT /*+WITH_PLSQL */ INTO t123 
WITH
 FUNCTION r123 RETURN NUMBER IS
 BEGIN
   RETURN 123;
 END;
SELECT r123
  FROM dual
/

It does fall down on the CREATE statements. The CREATE TABLE, CREATE VIEW and CREATE MATERIALIZED VIEW statements all allow WITH PL/SQL, and do not require the hint. The following works fine in SQL Plus (or if you send it straight to the SQL engine via JDBC or OCI, or through dynamic SQL).

CREATE TABLE t123 AS
WITH
 FUNCTION r123 RETURN NUMBER IS
 BEGIN
   RETURN 123;
 END;
SELECT r123  val
  FROM dual
/

Again, there's a workaround, and sqlcl will process the statement if it does contain the WITH_PLSQL hint. However that hint isn't genuine as far as the database is concerned (ie not in the data dictionary and won't be pulled out via a DBMS_METADATA.GET_DDL). Also sqlcl doesn't support the SQL Plus SET SQLTERMINATOR command, so we can't use that workaround. Still, I'll give it a B grade.

CREATE /*+WITH_PLSQL */ TABLE t123 AS
WITH
 FUNCTION r123 RETURN NUMBER IS
 BEGIN
   RETURN 123;
 END;
SELECT r123  val
  FROM dual
/

SQL Developer

As of 4.1.3, SQL Developer offers the weakest support for this 12c functionality. 
[Note: Scott in Perth noted the problems back in 2014.]

Currently the plain WITH...SELECT works correctly, but DML and CREATE statements all fail when it hits the semi-colon and it tries to run the statement as two or more separate SQLs. The only work around is to execute the statement as dynamic SQL through PL/SQL.

Since it seems to share most of the parsing logic with sqlcl, I'd expect it to catch up with its younger sibling on the next release. Hopefully they'll be quicker supporting any 12cR2 enhancements.

I'll give it a 'D' until the next release. In the meantime, pair it up with SQL Plus

TOAD 11

While I very rarely use it, I do have access to TOAD at work. TOAD recognizes blank lines as the separator between statements, so doesn't have an issue with semi-colons in the middle of SQL statements. Grade A for this functionality.