select banner
from v$version
;
BANNER
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Solaris: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
With its 12c release, Oracle has added the functionality to allow the declaration of Pl/SQL functions directly at the top of an SQL statement (see https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1)
This can be quite a handy feature, esp. on projects where you need to pull data from DBs with user rights limited to SELECT statements.
The following (obviously simplified) query runs fine in Oracle SQL Developer:
with
function add_string(p_string in varchar2) return varchar2
is
--Function to add a string
l_buffer varchar2(32767);
begin
l_buffer := p_string || ' works!';
--
return l_buffer;
--
end ;
--
select add_string('Yes, it') as outVal
from dual
;
---------
OUTVAL
Yes, it works!
Now, I am trying to load the result set of a query based on the same principle into an ADODB Recordset in MS-Access 2007. I am aware that ADO does not handle queries starting with a WITH clause seamlessly (see, e.g. Why can't I do a "with x as (...)" with ADODB and Oracle?). The way I usually get around this problem is to enclose the query in a SELECT block, like this:
select hey_yo
from (
with sub as (
select 'hey' as hey
from dual
)
select hey || ' yo!' as hey_yo
from sub
)
;
---------
HEY_YO
hey yo!
Unfortunately, this does not seem to be legal syntax in Oracle when dealing with functions in the WITH clause:
select *
from (
with
function add_string(p_string in varchar2) return varchar2
is
--Function to add a string
l_buffer varchar2(32767);
begin
l_buffer := p_string || ' works!';
--
return l_buffer;
--
end ;
--
select add_string('Yes, it') as outVal
from dual
)
;
---------
PLS-00103: Encountered the symbol ")" when expecting one of the following:
. , @ ; for <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus order partition start subpartition union where connect
sample
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
Here's the sub I'm trying to run in VBA:
Sub TestSub()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
'Connection details
Dim strHostName As String
Dim nPortNum As Integer
Dim strUser As String
Dim strPassword As String
Dim strServiceName As String
Dim strConnection As String
Dim strSQL As String
Set conn = New ADODB.Connection
'[... set credentials ...]
'Open Connection
With conn
.ConnectionString = "Provider=MSDAORA;" & _
"Data Source=(DESCRIPTION=(ADDRESS_LIST=" & _
"(ADDRESS=(PROTOCOL=TCP)(HOST=" & strHostName & ")(PORT=" & nPortNum & ")))(CONNECT_DATA=(SERVICE_NAME=" & strServiceName & ")));" & _
"User ID=" & strUser & ";Password=" & strPassword & ";"
.Open
End With
Set rs = New ADODB.Recordset
strSQL = "WITH FUNCTION add_string(p_string IN VARCHAR2) RETURN VARCHAR2 IS l_buffer VARCHAR2(32767); BEGIN l_buffer := p_string || ' works!'; RETURN l_buffer; END ; SELECT add_string('Yes, it') AS outval FROM dual"
rs.Open strSQL, conn, adOpenStatic, adLockReadOnly
'[... do stuff with data ...]
rs.MoveFirst
Debug.Print rs.Fields(0).Value
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub
Any ideas how to get around this problem? (Unfortunately, compiling the function in the DB is not an option for this particular project).
Update: I should mention the error I get when running the VBA code:
Run-Time error 3704: Operation is not allowed when the object is closed.
at rs.MoveFirst
