Skip navigation.

Gary Myers

Syndicate content
I am a proud Oracle developer and this is my blog.
My website is here
This blog is OPINION, SUGGESTION and DEBATE. Please correct anything I write that is misleading.Gary Myersnoreply@blogger.comBlogger261125
Updated: 4 hours 14 min ago

Client support for WITH using PL/SQL

Mon, 2016-02-29 03:00
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 PlusThe 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/SQLCLIf 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 ASWITH 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 ASWITH FUNCTION r123 RETURN NUMBER IS BEGIN   RETURN 123; END;SELECT r123  val  FROM dual/SQL DeveloperAs 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 PlusTOAD 11While 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.
Just for completeness, these are the test statements I used
CLEAR SCREEN
SET SQLTERMINATOR OFF
DROP TABLE t123/DROP VIEW v123/DROP MATERIALIZED VIEW mv123/
PROMPT SELECT WITH FUNCTION r123 RETURN NUMBER IS BEGIN   RETURN 123; END;SELECT r123 val  FROM dual/
PROMPT CREATES
CREATE TABLE t123 ASWITH FUNCTION r123 RETURN NUMBER IS BEGIN   RETURN 123; END;SELECT r123  val  FROM dual/
CREATE VIEW v123 ASWITH FUNCTION r123 RETURN NUMBER IS BEGIN   RETURN 123; END;SELECT r123 val  FROM dual/
CREATE MATERIALIZED VIEW mv123 ASWITH FUNCTION r123 RETURN NUMBER IS BEGIN   RETURN 123; END;SELECT r123 val  FROM dual/
PROMPT INSERT/DELETE/MERGE
INSERT /*+WITH_PLSQL */ INTO t123 WITH FUNCTION r123 RETURN NUMBER IS BEGIN   RETURN 123; END;SELECT r123  FROM dual/
DELETE /*+WITH_PLSQL */FROM t123WHERE val =  (WITH     FUNCTION r123 RETURN NUMBER IS     BEGIN       RETURN 123;     END;    SELECT r123      FROM dual)/
MERGE /*+WITH_PLSQL */ INTO  t123 D   USING (WITH             FUNCTION r123 RETURN NUMBER IS             BEGIN               RETURN 123;             END;            SELECT r123 val              FROM dual) s   ON (d.val = s.val )   WHEN NOT MATCHED THEN INSERT (val) VALUES (s.val)/
PROMPT UPDATES
UPDATE /*+WITH_PLSQL */  (WITH     FUNCTION r123 RETURN NUMBER IS     BEGIN       RETURN 123;     END;    SELECT val, r123      FROM t123)SET val = r123/
UPDATE /*+WITH_PLSQL */ t123SET val =  (WITH     FUNCTION r123 RETURN NUMBER IS     BEGIN       RETURN 123;     END;    SELECT r123      FROM dual)/      
CREATE /*+WITH_PLSQL */ TABLE t123 ASWITH FUNCTION r123 RETURN NUMBER IS BEGIN   RETURN 123; END;SELECT r123  val  FROM dual/
CREATE /*+WITH_PLSQL */ VIEW v123 ASWITH FUNCTION r123 RETURN NUMBER IS BEGIN   RETURN 123; END;SELECT r123 val  FROM dual/
CREATE /*+WITH_PLSQL */ MATERIALIZED VIEW mv123 ASWITH FUNCTION r123 RETURN NUMBER IS BEGIN   RETURN 123; END;SELECT r123 val  FROM dual/