By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,583 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,583 IT Pros & Developers. It's quick & easy.

return Oracle stored function value back to Access VBA

P: n/a
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

Jul 12 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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
Jul 12 '07 #2

P: n/a
On Jul 12, 10:10 am, Tim Marshall
<TIM...@PurplePandaChasers.Moertheriumwrote:
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

Jul 12 '07 #3

P: n/a
On 12 Jul, 19:47, Gord <g...@kingston.netwrote:
On Jul 12, 10:10 am, Tim Marshall

<TIM...@PurplePandaChasers.Moertheriumwrote:
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.

Jul 16 '07 #4

P: n/a
On Jul 16, 5:21 am, colmkav <colmj...@yahoo.co.ukwrote:
On 12 Jul, 19:47, Gord <g...@kingston.netwrote:
On Jul 12, 10:10 am, Tim Marshall
<TIM...@PurplePandaChasers.Moertheriumwrote:
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.

Jul 16 '07 #5

P: n/a
On 16 Jul, 17:53, Gord <g...@kingston.netwrote:
On Jul 16, 5:21 am, colmkav <colmj...@yahoo.co.ukwrote:


On 12 Jul, 19:47, Gord <g...@kingston.netwrote:
On Jul 12, 10:10 am, Tim Marshall
<TIM...@PurplePandaChasers.Moertheriumwrote:
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)

Jul 17 '07 #6

P: n/a
On Jul 17, 3:33 am, colmkav <colmj...@yahoo.co.ukwrote:
On 16 Jul, 17:53, Gord <g...@kingston.netwrote:
On Jul 16, 5:21 am, colmkav <colmj...@yahoo.co.ukwrote:
On 12 Jul, 19:47, Gord <g...@kingston.netwrote:
On Jul 12, 10:10 am, Tim Marshall
<TIM...@PurplePandaChasers.Moertheriumwrote:
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;

Jul 17 '07 #7

P: n/a
On 17 Jul, 12:46, Gord <g...@kingston.netwrote:
On Jul 17, 3:33 am, colmkav <colmj...@yahoo.co.ukwrote:


On 16 Jul, 17:53, Gord <g...@kingston.netwrote:
On Jul 16, 5:21 am, colmkav <colmj...@yahoo.co.ukwrote:
On 12 Jul, 19:47, Gord <g...@kingston.netwrote:
On Jul 12, 10:10 am, Tim Marshall
<TIM...@PurplePandaChasers.Moertheriumwrote:
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.
Jul 17 '07 #8

P: n/a
On Jul 17, 6:59 am, colmkav <colmj...@yahoo.co.ukwrote:
On 17 Jul, 12:46, Gord <g...@kingston.netwrote:
On Jul 17, 3:33 am, colmkav <colmj...@yahoo.co.ukwrote:
On 16 Jul, 17:53, Gord <g...@kingston.netwrote:
On Jul 16, 5:21 am, colmkav <colmj...@yahoo.co.ukwrote:
On 12 Jul, 19:47, Gord <g...@kingston.netwrote:
On Jul 12, 10:10 am, Tim Marshall
<TIM...@PurplePandaChasers.Moertheriumwrote:
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.

Jul 17 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.