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: 9 hours 2 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/

Multisessioning with Python

Sun, 2016-01-31 00:27
I'll admit that I pretty constantly have at least one window either open into SQL*Plus or at the command line ready to run a deployment script through it. But there's time when it is worth taking a step beyond.

One problem with the architecture of most SQL clients is they connect to a database, send off a SQL statement and do nothing until the database responds back with an answer. That's a great model when it takes no more than a second or two to get the response. It is cumbersome when the statement can take minutes to complete. Complex clients, like SQL Developer, allow the user to have multiple sessions open, even against a single schema if you use "unshared" worksheets. But they don't co-ordinate those sessions in any way.

Recently I needed to run a task in a number of schemas. We're all nicely packaged up and all I needed to do was execute a procedure in each of the schemas and we can do that from a master schema with appropriate grants. However the tasks would take several minutes for each schema, and we had dozens of schemas to process. Running them consecutively in a single stream would have taken many hours and we also didn't want to set them all off at once through the job scheduler due to the workload. Ideally we wanted a few running concurrently, and when one finished another would start. I haven't found an easy way to do that in the database scheduler.

Python, on the other hand, makes it so darn simple.
[Credit to Stackoverflow, of course]

proc connects to the database, executes the procedure (in this demo just setting the client info with a delay so you can see it), and returns.
Strs is a collection of parameters.
pool tells it how many concurrent operation to run. And then it maps the strings to the pool, so A, B and C will start, then as they finish D,E,F and G will be processed as threads become available.

I could my collection was a list of the schema names, and the statement was more like 'begin ' + arg + '.task; end;'

#!/usr/bin/python

"""
Global variables
"""

db    = 'host:port/service'
user  = 'scott'
pwd   = 'tiger'

def proc(arg):
   con = cx_Oracle.connect(user + '/' + pwd + '@' + db)
   cur = con.cursor()
   cur.execute('begin sys.dbms_application_info.set_client_info(:info); end;',{'info':arg})
   time.sleep(10)   
   cur.close()
   con.close()
   return
   
import cx_Oracle, time
from multiprocessing.dummy import Pool as ThreadPool 

strs = [
  'A',  'B',  'C',  'D',  'E',  'F',  'G'
  ]

# Make the Pool of workers
pool = ThreadPool(3) 
# Pass the elements of the array to the procedure using the pool 
#  In this case no values are returned so the results is a dummy
results = pool.map(proc, strs)
#close the pool and wait for the work to finish 
pool.close() 
pool.join() 

PS. In this case, I used cx_Oracle as the glue between Python and the database.
The pyOraGeek blog is a good starting point for that.

If/when I get around to blogging again, I'll discuss jaydebeapi / jpype as an alternative. In short, cx_Oracle goes through the OCI client (eg Instant Client) and jaydebeapi takes the JVM / JDBC route.