UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error Calling Oracle Procedure From Access    
 
   
dpineau
post Aug 21 2013, 02:30 PM
Post#1



Posts: 78
Joined: 9-April 07



Hello All,
This is a bit of a head-scratcher and I would welcome any insight from those of you who have been through the wars getting Access/VBA to work with Oracle database (11g).
We CAN successfully call and execute some Oracle packages and stored procedures from our Access database (2010). The issue is that SOME Oracle stored procedures raise ERRORS – and these errors ONLY occur when called from Access. The same procedure runs fine when called from Oracle SQL Plus or a db tool like Toad. (So the issue is NOT the stored procedure by itself).
THere is the summary:
1) We have two Stored Procedures
a. One simple test SP – HELLO_WORLD3 (pasted below) to verify that our methodology for executing Oracle packages & procedures works (it DOES). It also tests the ability to run an SP with DBMS_OUTPUT because that may be the problem with the other SP.
b. One complex SP - BZ_WRAPPER (pasted below). This is actually a “wrapper” procedure that calls a different procedure (F.bz_test_pkg.bz) in the same database. This is a pretty complex sp that does a significant amount of processing. It also produces some DBMS_OUTPUT which may be the culprit.
2) BOTH Stored Procedures DO run successfully when called from SQL Plus (or a db Tool). So the problem is NOT that the procedure is bad. The issue seems to be related to the VBA-Oracle connection.
3) The process in VBA that calls the sp DOES successfully call an Oracle SP. We proved this be executing HELLO_WORLD3 from VBA.
4) When we call the complex SP from VBA, we get an error. The error appears to be related to DBMS_OUTPUT???

This a mature Access Front-End App linked to Oracle database. It has co-existed with the backend for several years now. If anyone can shed some light on this or if someone has experience in the VBA/Oracle stored procedure world, I would welcome your insights.
Are there properties or settings that we need to modify to better run stored procedures in this way? Are there proven best practices on the Oracle side to follow if an SP is being called by Access? Are there better ways to call an SP from Access? Etc?
Much of the code is pasted below. I have redacted a few parts for the sake of security (so it may have a tiny syntax error which you can ignore). It has also been somewhat simplified for testing. The procedure code is written by two very experienced Oracle developers, so we are not looking for insight PLSQL development – this stuff runs.
THANK you for any insight!
David
---Simple Access Sub to call Oracle SP -----
Public Sub subTestOraclePKGCall_Simple()
Dim Oracon As ADODB.Connection
Dim recset As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim objErr As ADODB.Error
On Error GoTo err_test
Set Oracon = CreateObject("ADODB.Connection")
Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=XXX;" & _
"User ID=YYY;" & _
"Password=ZZZZZZZ;"
Oracon.Open
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Oracon
' Enable PLSQLRSet property
cmd.Properties("PLSQLRSet") = False
' this WORKS :
'cmd.CommandText = "{CALL TRANSFER_CANDIDATE_PKG.HELLO_WORLD3() }"
‘this does NOT Work – even though the same call DOES work via SQLPlus or DB Tool
cmd.CommandText = "{CALL TRANSFER_CANDIDATE_PKG.BZ_WRAPPER() }"
cmd.Execute
runB = "OK"
Oracon.Close
Exit Sub
err_test:
MsgBox Error$

For Each objErr In Oracon.Errors
MsgBox objErr.Description
Next
Oracon.Errors.Clear
Resume Next
End Sub
---Oracle Stored Procedures-----
PROCEDURE HELLO_WORLD3
as
begin
dbms_output.put_line('hello world33333333333333333333333333333');
dbms_output.put_line('DBMS Output Line 2');
insert into testing_data(process_name, STRING_VALUE) values ('hello_world3fromvba', to_char(sysdate,'MM/DD/YYYY hh:MI'));
commit;
end;
PROCEDURE BZ_WRAPPER
AS
BEGIN
F.bz_test_pkg.bz(
p_client_STR => 'TEST'
p_account_STR => '733,734',
p_case_STR => '2599'
);
END;
Go to the top of the page
 
BananaRepublic
post Aug 21 2013, 02:41 PM
Post#2


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


What is the error text you get back, both in Err.Description and in OraCon.Errors?
Go to the top of the page
 
dpineau
post Aug 21 2013, 03:35 PM
Post#3



Posts: 78
Joined: 9-April 07



Hi,
I am not sure that the Access Error messages will help you. I think they are specific to our code. I can't get the Oracle errors until tomorrow.
THANKS!
Access Error Message:
---------------------------
Microsoft Access
---------------------------
ORA-20001: LOGGER FORCED FATAL ERROR EV=169225 bz(strings) tag=dci.1|dci(1)|lc012.1|lc011|lc01|LOAD1|IF9|IF2|IF1|upevstr|bz_init1|bz_init0|
ORA-06512: at "LOGGER.ERROR_LOG_UTIL_PKG", line 376
ORA-06512: at "LOGGER.SHARED_LOG_UTIL_PKG", line 23
ORA-06512: at "F.BZ_TEST_PKG", line 2816
ORA-06512: at "F.TRANSFER_CANDIDATE_PKG", line 129
ORA-06512: at line 1
---------------------------
OK
---------------------------
Go to the top of the page
 
BananaRepublic
post Aug 21 2013, 03:41 PM
Post#4


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Aha, it's taking in the informational output as a error.
If you read this article, it says you can differentiate between a real error from a warning/informational message by testing whether the Error number (in the ADO's Errors collection, not in Err.Number) by whether the number is positive. So you would probably adjust the error handler to ignore positive numbers in the Errors collection and only give up if it's negative.
Does that help?
Go to the top of the page
 
dpineau
post Aug 21 2013, 04:46 PM
Post#5



Posts: 78
Joined: 9-April 07



Thanks. We will investigate this and see if that resolves the issue.
appreciate the response. Thank you very much.
David
Go to the top of the page
 
jsitraining
post Aug 28 2013, 04:34 AM
Post#6


UtterAccess VIP
Posts: 5,241
Joined: 30-June 03
From: Scotland (Sunny Glasgow)


The error at the top of your stack is
ORA-20001
This is a user defined error number all errors between -20000 and -20999 are user defined. In the initial stages, I'd comment out your exception handler in your Oracle procedure so that the real error can propagate to your calling environment.
TA
Also, the other errors are 'catchalls' i.e. all ORA-06512 is saying is that there was an error, not what it was.
TBH, that's kinda what's been done by raising -20001 too. i.e. it's saying that there was an error. It says roughly where the error occurred (the act of converting the exception to a custom error has most likely obfuscated the true location of the problem), but it doesn't say what the problem actually is. I would say that the exception handling routines on the Oracle package(s) require some refinement.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 12:10 PM