• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4015
  • Last Modified:

COM Error Code 80040e4b (ORA-03147) in OraOLEDB The Accessor is not a Parameteraccessor.

A C++ program runs under Windows XP Pro SP2 and queries an Oracle 9.2.0.7
database on an Oracle server. For many days it runs without problems and then
it starts complaining that a trasanction produced ORA-03147 error. At first some
of the queries are successful at 2nd, 3rd or even 10th try until eventually no
more successful transactions occur. Stopping and restarting the program solves
the problem.

There are few similar problems reported, 2 in Experts Exchange, but none of
them seems to be applicable to my case. In one of EE threads
http://search.experts-exchange.com/Database/Miscellaneous/Q_21718487.html?sfQueryTermInfo=1+accessor+odbc+oracl+paramet+strang+vb
there is a dead hyperlink in an oleggold's posting to
http://cerg.csse.monash.edu.au/feedback/showPostings.php?discussionname=CPE3002&first=1&last=161
Does anyone know, what info should have been there ?
Perhaps the same as in the second posting of oleggold:
http://discuss.develop.com/archives/wa.exe?A2=ind0308&L=oledb_dev&T=0&F=&S=&P=193

Help!
0
dkrnic
Asked:
dkrnic
  • 17
  • 11
  • 3
  • +3
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi dkrnic,

That's a really wierd message code.

I don't recall ever seeing it and can't find it documented anywhere.


Kent
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you already look in the oracle metalink?
do you have access to metalink?
0
 
dkrnicAuthor Commented:
Yes. I have access to metalink, but I haven't found anything appropriate.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
JonveeCommented:
There's a reference to "OraOLEDB error '80040e4b' : Accessor is not a parameter" in this forum which may help.  
It seems it's been an ongoing problem with the last posting in Nov 2007.  
You may want to consider OLEDB version 10.2.

http://forums.oracle.com/forums/thread.jspa?threadID=375592&tstart=1
0
 
dkrnicAuthor Commented:
I've seen that discussion, a long time ago.
I've applied 9.2.0.7 and thought that the problem was gone,
but it wasn't. I couldn't reproduce it with stress tests but it
occurs in production as regularly as before.

Updating the client SW to 10.2 was on my mind, but it is a
can of worms. I may fix this problem and cause several
others to crop up.

The server is 9.2.0.7 on a Linux box.
I'd rather keep client SW in sync with server.
0
 
dkrnicAuthor Commented:
It was a difficult question.
Too difficult.
0
 
JonveeCommented:
dkrnic,
There are a couple of options you may wish to consider, *if* you get no further recommendations...

First, it's now unlikely many experts will find this question and respond because it's now slipped far down the list of unanswered questions!  Many questions are asked daily in this XP Topic Area.  Therefore may i suggest you create a new (20 point) question which is linked to this question http:Q_23145665.html 
Be sure to quote the address of this question in your new one, and advise everyone to only reply in this original question.  

Then, if you get no useful suggestions, scroll down to the title "How do I close a question?" you'll see a sub-heading "No solution has been given to your problem".
http://www.experts-exchange.com/help.jsp#hs5

Here you can post a question in >>>
http://www.experts-exchange.com/Community_Support/General/   
asking the Moderators to delete your question.

Hope that helps.  Please post back if you require more assistance.
0
 
Eric AKA NetminderCommented:
dkrnic,

I happened to see your post in the CA zone, and did a little poking around because it seems like I used to see that error a lot in a different context. I'm not any kind of Oracle whiz, so you can take this for what it's worth -- but it seems like while this isn't an empidemic problem, it would be cool to see if there's a solution that will work for others.

As you are probably aware, the 80040e4b error is from the OS, not Oracle. Microsoft does a really lousy job of documenting its error messages, but it's shown on one list as:
DB_E_BADACCESSORTYPE: The specified accessor was not a parameter accessor (ref: http://megos.ch/support/doserrors_e.txt).

Of course, we knew that. Essentially, it means there is a problem with the C++ program's connection to the database; there's a big part of me that says that it isn't the database itself, or possibly even the program, but in the part of the database system used for the connection. When people use Access, for example, as a web database, one of the problems is always whether to use OLE or ODBC; one seems easier to set up, while the other is more reliable.

http://www.tech-archive.net/Archive/Data/microsoft.public.data.oledb/2006-03/msg00040.html implies that using an ODBC connection could solve the problem although it doesn't mention Oracle specifically. http://support.microsoft.com/kb/327557 mentions Oracle, but not your specific problem.

I did find a link to one article at gridview.org that looked promising, but the site is a mess -- they can't find the article, even if you search from the menu. Its title was "When you use the Microsoft Remote provider to run a parameterized query against a remote database, the Requery method of the ADODB.Recordset object fails with the following error message: Unknown Error (80040e4b)" -- and it is listed under articles having to do with MDAC 2.7 and 2.8. Have you tried updating the MDAC? Or even reinstalling it (that used to solve a lot of problems back when it was MDAC 2.1 and 2.2).

Sorry that this is all guesswork -- but maybe something will spark an idea for you.

ep
0
 
dkrnicAuthor Commented:
The MDAC is up-to-date - 2.8, but thanks anyway.
I've tracked down the article you mentioned.
It's MS Article Q268972.
Not relevant.
0
 
Eric AKA NetminderCommented:
Just out of curiousity -- is the problem you describe in your initial question something that started happening recently? Or is it one of those things that has always been there but has become enough of an annoyance that you're trying to deal with it?

I know that when MDAC 2.2 came out, it actually broke a few things (notably some ASP systems) that worked fine under MDAC 2.1 -- so I'm thinking that if you had upgraded it, that could be the source of the problem.

The other thing I'd ask you about -- and if you've already tried this, I apolgize for asking -- is if you can change the connection string in the C++ program from OLE to ODBC, just for giggles and grins. I understand if you can't -- but it's just a thought.

ep
0
 
dkrnicAuthor Commented:
The current connection string is
"Provider=OraOLEDB.Oracle;"

What would be the string for ODBC ?
"Provider=MSDAORA;" ?
0
 
Eric AKA NetminderCommented:
From everything I've found, the current connection is ODBC.

http://www.connectionstrings.com/?carrier=oracle shows the ODBC connection for Oracle as being

Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;
or
Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;

And the Microsoft ODBC driver for Oracle (which would replace the bracketed phrase) is shown at http://www.adp-gmbh.ch/ora/network/odbc_connection_string.html as being

Provider=OraOLEDB.Oracle;Data Source=ORA10;User ID=fred;Password=freds_secret;

Also, if you look a little further down the page at connectionstrings.com, the OLE connection string for an Oracle database using the Microsoft driver is

Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

So it might just be that the C++ program doesn't like the Oracle driver; would it be a lot of work for you to change it to the Microsoft driver to see what happens?
0
 
dkrnicAuthor Commented:
Thank you, ericpete.

I'll try the MS driver. It's not easy to verify if that worked, because the error comes only
after longer period of time, about a week at least, but sometimes after a month. If it works
for 2 months without the error. than you earned yourself the bounty. Should the error
re-occur I'll report immediately.
0
 
dkrnicAuthor Commented:
On the other hand,
the capabilities of OraOLEDB.Oracle and MSDAORA are not really compatible.
Switching over to MSDAORA causes many different problems.
It's not a single adjustment of the connection string, it's much much more.
0
 
Eric AKA NetminderCommented:
I'm mostly guessing here, so I won't be at all offended if you request deletion for this. I'll also see if I can get the Mods to find you a couple of more knowledgeable experts. My "expertise" is Access (and a little SQL) and web pages, not Oracle and a C++ program -- so at best my suggestions are lucky stabs in the dark.

The other option you have is to see if there's an update to the driver that might have cured the illness...

ep
0
 
dkrnicAuthor Commented:
Educated stabbing around is OK.
That's how I find my way out of problems when I'm in the dark.

Thank you for trying.

My working hypothesis at the moment is that there is something wrong in the way
DLLs are language-dependent. There might be some residual matching impedance
when a language-dependent layer communicates with a generalized driver.
0
 
Eric AKA NetminderCommented:
Well, I had a conversation last night with the top C++ Expert on the site (jkr), and his comments were a) he doesn't do databases, and b) that if it were something in the C++ programming, that it would fail right away, and would consistently fail.

That got me thinking about the phrasing of your original question: That it will complete transactions for a good period of time, but will then throw an error, and that the frequency of the errors will start increasing until it gets to the point where your only solution is to restart the program.

Can you explain a little more about what is actually happening? Because the inevitability of the failures implies (to me) some kind of buffering issue -- kind of like deleting emails in Outlook that actually just go to the deleted items folder and don't really get deleted. That will gradually slow your email program down, because it considers the Deleted folder just like the rest.

So I'm wondering if it starts happening after X number of transactions -- if there is some threshhold it reaches at which point the programs starts saying "wait a minute -- I can't handle this much information, so I'm going to throw an error." It sulks for a transaction, then processes a few more, then sulks again for a couple of transactions, processes a few more, sulks a little longer...

ep
0
 
dkrnicAuthor Commented:
Yes, you paraphrased it well.
It does feel like some resource getting exhausted but it doesn't show.
The process does not grow, does not accumulate handles, semaphores or the like.
It doesn't start after X transacation. It starts after X, Y, or Z transactions,
which might be a couple of days a week or a month.
0
 
Eric AKA NetminderCommented:
Okay... this is where musing might be about as appropriate as anything... *grin*

There's no pattern (X number of transactions) between zero-day and the first failure. Is there any pattern between failure 1 and failure 2, and between failure 2 and 3? Or can it be X transactions between f1 and f2, and then sometimes it goes straight to f10, where you have to completely restart the program?

We've discussed transactions; are there subtransactions that could be the issue? In other words, if you're processing invoices, you may have more or less the same number of transactions over a period of time, but the number of line items that would need to be processed could vary wildly. Could a subsystem be the actual problem?

Is there any chance that the failures are related to "dirty" records -- records that have been partially finished but not fully committed?

0
 
dkrnicAuthor Commented:
You might be on to something, ericpete.

I have a log of the errors the last time they occurred.
(I could dig out all others too, they're all archived, but this one will suffice.)

I've already made a sort of diagram plotting the time differential between
successive transactions. I've learned a little more about the transactions
(and about plotting software). I'll post a revised chart as soon as I can,
so that you can try squinting at it from different angles in search of a pattern ;-)
0
 
Eric AKA NetminderCommented:
Even the log itself might be enough. We can always import it into a spreadsheet and fiddle with it.
0
 
dkrnicAuthor Commented:
OK. I found it. I have attached file "ora-3147.log" as semi-colon-separated values.
What you see is a part of the log an hour before the first #3147 error until the
programm was restarted. First 20 lines are at about 3 minutes apart logging a
new feature of the program. It's a triplet of push-start-push actions which I
collapsed into one line. There are other actions at about 30 seconds apart
which only get logged if they run into a problem so we don't see them.

At 12:59:09 the error ora-3147 occurs for the first time after almost 3 days of
error-free operation. It ruins the push-start-push which started at 12:58:54
but 3 minutes later another triplet gets completed. Then there are
2 errors 1 success, then
3 errors 1 success,
1 error   1 success
5 errors 1 success
2 errors 1 success
8 errors 1 success
1 erorr   2 successes
2 errors 1 success
4 errors 1 success
52 errors 1 success
13 errors 1 success
and since then at 17:40:14 the error is permanent.

I fail to see any pattern here, but 2 pairs of eyes can see better than one.
ora-3147.log
0
 
Eric AKA NetminderCommented:
German, huh... *L*... It figures it would be one of those languages I can't even guess at.

Okay... I assume that PFDB-ProzeƟ-IDs is the database, correct?

What I have done is taken your log, and then color-coded it to show each group of Push-Start-Push sequences as they are successful. Most of the time, the problem seems to start when the first Push times out (if I'm reading the log correctly). Can you increase the time it allows to do that sequence?

You'll note that it ALWAYS fails on the first Push of the three step sequence. However, I have no idea what the log is saying... If you can help me there, maybe I'll be able to find something.

ep
23145665-ora-3147.xls
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for the "non-germans":
the error message:
Der Accessor ist kein Parameteraccessor
in english applications would be:
The Accessor is not a Parameter accessor

I think we need to see the (SQL) code of the DoZyklus method :)

what I found here:
http://forums.oracle.com/forums/thread.jspa?threadID=375592&tstart=0
http://support.microsoft.com/kb/327557
which is what confirms what I found recently in one of my own applications, is the MS is sometimes picky about data types that are "bigger" than what it expects. while I had the problem in a linked server from SQL Server to Oracle, the error was the same, as was the solution.

hope this helps
0
 
dkrnicAuthor Commented:
You have interpreted it correctly,ericpete. It is the symptom. The triple push-start-push
can't be completed before the start-stage is announced. The way I look at it to visualize
the flow is I use "vim", position the cursor at the string "PushPFDBBerichtAuftrag" and
press the pound sign "#", which causes all instances of that string to be displayed in an
enhanced mode of one's own choice. On a large enough screen you can see all 3 steps
in one line and it is clear that the error has nothing to do with any of the causes
discussed in other threads like those quoted by "angellll". Each time "push" starts the
sequence "push-start-push" it places the same query to the database (PFDB) and
gets exactly the same answer - I can easily see that the system was quiescent at
the time. If any of the returned values were out of limited range acceptable to MS ADO
it would log the error every time. It would not be able to complete the sequence at all.
But we see that it occasionally "comes through" with a successful cycle.

The other possibility that there is some mismatch in the accessor variables, like
sending an empty string for an OUT var can also be excluded. It doesn't happen.
There is a stored routine which gets called under exceptional circumstances with
a single IN var (setter, not getter) and it never makes any problems. At the time
of failure it was not the case.

I am really impressed by the community's willingness to discuss this thread.
I would like to solve this problem and I will. Your help means much to me.
0
 
Eric AKA NetminderCommented:
Is there any possibility of posting the SQL code of the DoZyklus method, as angelIII suggested?

You can also upload a screen shot that will give us an idea of what you're seeing...
0
 
dkrnicAuthor Commented:
Sorry I was away abroad since Friday last.

I'll post a tkprof of the transactions asap.
0
 
schwertnerCommented:
I have some indication that old versions VS (for instance 3 work better as 6 with Oracle).

Also please check if there is not a default firewall turned on on the XP.
Also check for hidden firewalls like TrendMicro, AlarmZone and antiviruseses like McAffey.
 
Some of them (not all!) have to be uninstalled ...
0
 
dkrnicAuthor Commented:
Thank you for the suggestions schwertner.

Since it is a closed network we regularly disable built-in firewall and
do not install any virus protection programs on those machines.
0
 
dkrnicAuthor Commented:
I have done some tracing of the queries involved in push-start-push sequence
which is where the error manifests itself. The enclosed file contains a tar of
all trace files at 00:07 am today. The biggest file carries the bulk of push-start-push
which is done every 2:30 minutes. The other smaller files are mostly part of a
different cycle which is done every 30 seconds. The tar is compressed by
bzip2 but I had to add one of the acceptable extensions in order to upload it.
00-07.tbz2.log
0
 
schwertnerCommented:
Try to upgrade the Oracle DB to 9.2.0.8 as last chance.
In some sources this is reported as bug (and it is bug).
Also changing drivers can help.
0
 
dkrnicAuthor Commented:
It's a valuable tip shwertner. I didn't know there was 9.2.0.8.

As it often happens when a program meets irresistible force, it is necessary
to restart it, which is a differente way of upgrading to 9.2.0.8, without
changing anything and invoking God knows what other bugs are skunking in
9.2.0.8. Ever since we scheduled a task of restarting the offending program
on each Sunday afternoon, the program has not been stuck with ORA-03147.

I am very grateful to ericpete and schwertner for the useful stabbin in the dark.
I wish I could spend at least a part of the war chest for the thoughtfull suggestions
and perseverance, even though the problem remains a mistery. Please let me
know if that's possible before I close this thread.
0
 
Eric AKA NetminderCommented:
Well, that's up to you. I'm obviously neither a programmer nor an Oracle expert, but I do know where a lot of bodies are buried around here, so I can keep trying to find people to look at this question.

It seems that if restarting the program every week eliminates the problem, then the problem isn't with Oracle -- it's with the program (maybe a memory leak of some kind?). That being the case, if you have the ability to post code (in response to Expert requests for it) I can see if there are a few people who will take a look.

On the other hand, if you're satisfied with your workaround, I have no issue with your accepting your comment as the "answer" and closing the question.

ep
0
 
schwertnerCommented:
I am not COM expert and possibly also not Oracle expert.
But the fact that you get the error ORA 3147 means that
(possibly!) this is Oracle error. Seems it is an ancient error
from Oracle 7 times:

Error:  ORA 3147
Text:   failed to generate MBR
-------------------------------------------------------------------------------
Cause:  This is an internal error.
Action: Document messages and contact Oracle Worldwide Support.
        For more information about the Spatial Data option, see <Oracle7
        Spatial Data Option Reference and Administrator's Guide>.

I will leave the following recommendation:
1. Try to migrate to Oracle 9.2.0.8
2. Check if the upgrade is done correctly.  This means that not only the software is upgraded, but also
the Catalog is upgraded. There are different methods to check. The first one is run Full Export. If it fails -
you run into this DBA error.
The second method:
set linesize 10000
SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10)
        version, SUBSTR(comp_name,1,30) comp_name
        FROM dba_registry ORDER BY 1;

If the version of the components CATALOG, CATPROC, XDB (among others) is lower than the patch-set version that you have applied, re-run the script catpatch.sql after a startup migrate. See the readme file of the patch-set for all these mandatory post-installation instructions.


0
 
dkrnicAuthor Commented:
Thank you all for bearing with me.
0
 
Eric AKA NetminderCommented:
Thanks for the points; wish we could have helped more.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 17
  • 11
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now