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
DELETE, MERGE, INSERT, 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.]
[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.
