return Oracle stored function value back to Access VBA

[colmkav]

Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.

Set Cmd = New Command
With Cmd
Set .ActiveConnection = get_XE_Conn 'makes a connection
Oracle XE
.CommandType = adCmdStoredProc
.CommandText = "pkg_EQRISK.get_num_dates_varposfile"
'returns the number of
distinct dates in tmp_varposition table using Oracle proc
.Execute , , adExecuteNoRecords

End With
Set Cmd = Nothing

Private m_conn As ADODB.Connection

Public Function get_XE_Conn() As ADODB.Connection

If m_conn Is Nothing Then
Set m_conn = New ADODB.Connection
End If
If m_conn.State <adStateOpen Then
m_conn.Open "PROVIDER=MSDAORA.1;USER
ID=EQRISK;PASSWORD=eq;DATA SOURCE=XE;"
'//m_conn.Open "Driver={Oracle in
XE};Server=XE;Uid=EQRISK;Pwd=eq;"
'//m_conn.Open "ODBC;DSN=EQRISK;DBQ=XE;UID=EQRISK;PWD=eq"
End If
Set get_XE_Conn = m_conn

End Function

[Tim Marshall]

colmkav wrote:

Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.

I didn't look closely at your code as I stick exclusively to DAO when
writing mdbs against Oracle back ends (which is what all my work
comprises), but have you tried running:

Select get_num_dates_varposfile from dual

Or whatever SQL statement is required to get the function to run in SQL
Plus via a pass through query with returns records set to true?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Gord]

On Jul 12, 10:10 am, Tim Marshall
<[e-mail]:

colmkav wrote:

Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.

I didn't look closely at your code as I stick exclusively to DAO when
writing mdbs against Oracle back ends (which is what all my work
comprises), but have you tried running:

Select get_num_dates_varposfile from dual

Or whatever SQL statement is required to get the function to run in SQL
Plus via a pass through query with returns records set to true?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

FWIW, this works for me (VBScript example):

Option Explicit

Dim con, rst

Set con = CreateObject("ADODB.Connection")
con.CursorLocation = 3 ' adUseClient
con.Properties("Prompt") = 2 ' adPromptComplete
con.Open "DSN=Oracle ODBC local GDT;"

Set rst = CreateObject("ADODB.Recordset")
rst.Open _
"SELECT MYTESTFUNC FROM DUAL", _
con, 3, 1

Wscript.Echo rst(0).Value

rst.Close
Set rst = Nothing
con.Close
Set con = Nothing

[colmkav]

On 12 Jul, 19:47, Gord <[e-mail]:

On Jul 12, 10:10 am, Tim Marshall

<[e-mail]:

colmkav wrote:

Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.

I didn't look closely at your code as I stick exclusively to DAO when
writing mdbs against Oracle back ends (which is what all my work
comprises), but have you tried running:

Select get_num_dates_varposfile from dual

Or whatever SQL statement is required to get the function to run in SQL
Plus via a pass through query with returns records set to true?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

FWIW, this works for me (VBScript example):

Option Explicit

Dim con, rst

Set con = CreateObject("ADODB.Connection")
con.CursorLocation = 3 ' adUseClient
con.Properties("Prompt") = 2 ' adPromptComplete
con.Open "DSN=Oracle ODBC local GDT;"

Set rst = CreateObject("ADODB.Recordset")
rst.Open _
"SELECT MYTESTFUNC FROM DUAL", _
con, 3, 1

Wscript.Echo rst(0).Value

rst.Close
Set rst = Nothing
con.Close
Set con = Nothing- Hide quoted text -

- Show quoted text -

thanks. I guess this would work.

Do you know if its possible to return the return value of an Oracle
function in cases where a recordset? I know how to do this via a
stored proc but not a function.

[Gord]

On Jul 16, 5:21 am, colmkav <[e-mail]:

On 12 Jul, 19:47, Gord <[e-mail]:

On Jul 12, 10:10 am, Tim Marshall

<[e-mail]:

colmkav wrote:
Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.

I didn't look closely at your code as I stick exclusively to DAO when
writing mdbs against Oracle back ends (which is what all my work
comprises), but have you tried running:

Select get_num_dates_varposfile from dual

Or whatever SQL statement is required to get the function to run in SQL
Plus via a pass through query with returns records set to true?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

FWIW, this works for me (VBScript example):

Option Explicit

Dim con, rst

Set con = CreateObject("ADODB.Connection")
con.CursorLocation = 3 ' adUseClient
con.Properties("Prompt") = 2 ' adPromptComplete
con.Open "DSN=Oracle ODBC local GDT;"

Set rst = CreateObject("ADODB.Recordset")
rst.Open _
"SELECT MYTESTFUNC FROM DUAL", _
con, 3, 1

Wscript.Echo rst(0).Value

rst.Close
Set rst = Nothing
con.Close
Set con = Nothing- Hide quoted text -

- Show quoted text -

thanks. I guess this would work.

Do you know if its possible to return the return value of an Oracle
function in cases where a recordset? I know how to do this via a
stored proc but not a function.

Sorry, I don't understand your question.

[colmkav]

On 16 Jul, 17:53, Gord <[e-mail]:

On Jul 16, 5:21 am, colmkav <[e-mail]:

On 12 Jul, 19:47, Gord <[e-mail]:

On Jul 12, 10:10 am, Tim Marshall

<[e-mail]:
colmkav wrote:
Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.

I didn't look closely at your code as I stick exclusively to DAO when
writing mdbs against Oracle back ends (which is what all my work
comprises), but have you tried running:

Select get_num_dates_varposfile from dual

Or whatever SQL statement is required to get the function to run in SQL
Plus via a pass through query with returns records set to true?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

FWIW, this works for me (VBScript example):

Option Explicit

Dim con, rst

Set con = CreateObject("ADODB.Connection")
con.CursorLocation = 3 ' adUseClient
con.Properties("Prompt") = 2 ' adPromptComplete
con.Open "DSN=Oracle ODBC local GDT;"

Set rst = CreateObject("ADODB.Recordset")
rst.Open _
"SELECT MYTESTFUNC FROM DUAL", _
con, 3, 1

Wscript.Echo rst(0).Value

rst.Close
Set rst = Nothing
con.Close
Set con = Nothing- Hide quoted text -

- Show quoted text -

thanks. I guess this would work.

Do you know if its possible to return the return value of an Oracle
function in cases where a recordset? I know how to do this via a
stored proc but not a function.

Sorry, I don't understand your question.- Hide quoted text -

- Show quoted text -

What I mean is if I run a stored procedure I know how to return a
value through one of the parameters

eg

Dim UpdateCmd As ADODB.Command

Set Cmd = New Command
With Cmd
Set .ActiveConnection = get_XE_Conn 'makes a connection
Oracle XE
.CommandType = adCmdStoredProc
.CommandText = "pkg_EQRISK.get_num_dates_Varposfile"
'returns the no. of distinct dates
in tmp_varposition using Oracle proc
.Execute , , adExecuteNoRecords
End With
debug.print Cmd(1)
however, if I want to write it as a function I am not sure how to do
this. (Cmd(1) returns the value in the 2nd parameter)

[Gord]

On Jul 17, 3:33 am, colmkav <[e-mail]:

On 16 Jul, 17:53, Gord <[e-mail]:

On Jul 16, 5:21 am, colmkav <[e-mail]:

On 12 Jul, 19:47, Gord <[e-mail]:

On Jul 12, 10:10 am, Tim Marshall

<[e-mail]:
colmkav wrote:
Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.

I didn't look closely at your code as I stick exclusively to DAO when
writing mdbs against Oracle back ends (which is what all my work
comprises), but have you tried running:

Select get_num_dates_varposfile from dual

Or whatever SQL statement is required to get the function to run in SQL
Plus via a pass through query with returns records set to true?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

FWIW, this works for me (VBScript example):

Option Explicit

Dim con, rst

Set con = CreateObject("ADODB.Connection")
con.CursorLocation = 3 ' adUseClient
con.Properties("Prompt") = 2 ' adPromptComplete
con.Open "DSN=Oracle ODBC local GDT;"

Set rst = CreateObject("ADODB.Recordset")
rst.Open _
"SELECT MYTESTFUNC FROM DUAL", _
con, 3, 1

Wscript.Echo rst(0).Value

rst.Close
Set rst = Nothing
con.Close
Set con = Nothing- Hide quoted text -

- Show quoted text -

thanks. I guess this would work.

Do you know if its possible to return the return value of an Oracle
function in cases where a recordset? I know how to do this via a
stored proc but not a function.

Sorry, I don't understand your question.- Hide quoted text -

- Show quoted text -

What I mean is if I run a stored procedure I know how to return a
value through one of the parameters

eg

Dim UpdateCmd As ADODB.Command

Set Cmd = New Command
With Cmd
Set .ActiveConnection = get_XE_Conn 'makes a connection
Oracle XE
.CommandType = adCmdStoredProc
.CommandText = "pkg_EQRISK.get_num_dates_Varposfile"
'returns the no. of distinct dates
in tmp_varposition using Oracle proc
.Execute , , adExecuteNoRecords
End With
debug.print Cmd(1)

however, if I want to write it as a function I am not sure how to do
this. (Cmd(1) returns the value in the 2nd parameter)

Have your function return a value, then call it using an
ADODB.Recordset object as I illustrated previously.

In that example my test function was simply

create or replace function "MYTESTFUNC"
return VARCHAR2
is
begin
RETURN 'Gord was here.';
end;

[colmkav]

On 17 Jul, 12:46, Gord <[e-mail]:

On Jul 17, 3:33 am, colmkav <[e-mail]:

On 16 Jul, 17:53, Gord <[e-mail]:

On Jul 16, 5:21 am, colmkav <[e-mail]:

On 12 Jul, 19:47, Gord <[e-mail]:

On Jul 12, 10:10 am, Tim Marshall

<[e-mail]:
colmkav wrote:
Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.

I didn't look closely at your code as I stick exclusively to DAO when
writing mdbs against Oracle back ends (which is what all my work
comprises), but have you tried running:

Select get_num_dates_varposfile from dual

Or whatever SQL statement is required to get the function to run in SQL
Plus via a pass through query with returns records set to true?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

FWIW, this works for me (VBScript example):

Option Explicit

Dim con, rst

Set con = CreateObject("ADODB.Connection")
con.CursorLocation = 3 ' adUseClient
con.Properties("Prompt") = 2 ' adPromptComplete
con.Open "DSN=Oracle ODBC local GDT;"

Set rst = CreateObject("ADODB.Recordset")
rst.Open _
"SELECT MYTESTFUNC FROM DUAL", _
con, 3, 1

Wscript.Echo rst(0).Value

rst.Close
Set rst = Nothing
con.Close
Set con = Nothing- Hide quoted text -

- Show quoted text -

thanks. I guess this would work.

Do you know if its possible to return the return value of an Oracle
function in cases where a recordset? I know how to do this via a
stored proc but not a function.

Sorry, I don't understand your question.- Hide quoted text -

- Show quoted text -

What I mean is if I run a stored procedure I know how to return a
value through one of the parameters

eg

Dim UpdateCmd As ADODB.Command

Set Cmd = New Command
With Cmd
Set .ActiveConnection = get_XE_Conn 'makes a connection
Oracle XE
.CommandType = adCmdStoredProc
.CommandText = "pkg_EQRISK.get_num_dates_Varposfile"
'returns the no. of distinct dates
in tmp_varposition using Oracle proc
.Execute , , adExecuteNoRecords
End With
debug.print Cmd(1)

however, if I want to write it as a function I am not sure how to do
this. (Cmd(1) returns the value in the 2nd parameter)

Have your function return a value, then call it using an
ADODB.Recordset object as I illustrated previously.

In that example my test function was simply

create or replace function "MYTESTFUNC"
return VARCHAR2
is
begin
RETURN 'Gord was here.';
end;- Hide quoted text -

- Show quoted text -

ok, thanks. I see what you mean now. Is this the only way to return
the value of a function? Seems slightly convoluted.

[Gord]

On Jul 17, 6:59 am, colmkav <[e-mail]:

On 17 Jul, 12:46, Gord <[e-mail]:

On Jul 17, 3:33 am, colmkav <[e-mail]:

On 16 Jul, 17:53, Gord <[e-mail]:

On Jul 16, 5:21 am, colmkav <[e-mail]:

On 12 Jul, 19:47, Gord <[e-mail]:

On Jul 12, 10:10 am, Tim Marshall

<[e-mail]:
colmkav wrote:
Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.

I didn't look closely at your code as I stick exclusively to DAO when
writing mdbs against Oracle back ends (which is what all my work
comprises), but have you tried running:

Select get_num_dates_varposfile from dual

Or whatever SQL statement is required to get the function to run in SQL
Plus via a pass through query with returns records set to true?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

FWIW, this works for me (VBScript example):

Option Explicit

Dim con, rst

Set con = CreateObject("ADODB.Connection")
con.CursorLocation = 3 ' adUseClient
con.Properties("Prompt") = 2 ' adPromptComplete
con.Open "DSN=Oracle ODBC local GDT;"

Set rst = CreateObject("ADODB.Recordset")
rst.Open _
"SELECT MYTESTFUNC FROM DUAL", _
con, 3, 1

Wscript.Echo rst(0).Value

rst.Close
Set rst = Nothing
con.Close
Set con = Nothing- Hide quoted text -

- Show quoted text -

thanks. I guess this would work.

Do you know if its possible to return the return value of an Oracle
function in cases where a recordset? I know how to do this via a
stored proc but not a function.

Sorry, I don't understand your question.- Hide quoted text -

- Show quoted text -

What I mean is if I run a stored procedure I know how to return a
value through one of the parameters

eg

Dim UpdateCmd As ADODB.Command

Set Cmd = New Command
With Cmd
Set .ActiveConnection = get_XE_Conn 'makes a connection
Oracle XE
.CommandType = adCmdStoredProc
.CommandText = "pkg_EQRISK.get_num_dates_Varposfile"
'returns the no. of distinct dates
in tmp_varposition using Oracle proc
.Execute , , adExecuteNoRecords
End With
debug.print Cmd(1)

however, if I want to write it as a function I am not sure how to do
this. (Cmd(1) returns the value in the 2nd parameter)

Have your function return a value, then call it using an
ADODB.Recordset object as I illustrated previously.

In that example my test function was simply

create or replace function "MYTESTFUNC"
return VARCHAR2
is
begin
RETURN 'Gord was here.';
end;- Hide quoted text -

- Show quoted text -

ok, thanks. I see what you mean now. Is this the only way to return
the value of a function? Seems slightly convoluted.

The only way? Perhaps not, but IMO it is no more convoluted than using
an ADODB.Command object to return values from Procedures.