search

oracle / dotnet-db-samples

.NET code samples for Oracle database developers #OracleDotNet
http://otn.oracle.com/dotnet
MIT License
413 stars 191 forks source link

Long project file paths cause ORA-06502 #290

Closed sccunningham7 closed 1 year ago

sccunningham7 commented 1 year ago

Replicated on

When a project is attempted be run from a very long file path, ODP.NET will generate a connection string sent over the wire which causes the following to be returned, on any connection attempt:

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 15

If the file path is reduced by renaming directories/dlls etc., the connection succeeds without issue.

Using the connection traces it appears the generated CID PROGRAM is the issue:

Does not work:

(CID=(PROGRAM=C:\MyTestWebAppWithAReallyLongName\MyTestWebAppWithAReallyLongName.WebApi\bin\Debug\netcoreapp3.1\MyTestWebAppWithAReallyLongName.WebApi.dll)(HOST=hostname)(USER=sccunningham7))

Works:

(CID=(PROGRAM=C:\test\MyTestWebApp.WebApi\bin\Debug\netcoreapp3.1\testweb.dll)(HOST=hostname)(USER=sccunningham7))

I cannot find a way to modify that CID. Ideally, it should create something else or truncate the value. At a minimum, it should give better error feedback, as this was a pain to track down.

alexkeh commented 1 year ago

I'm sorry about your experience. When Oracle 23c goes production (not the dev release), you will see significant improvements in the error messages. They will be clearer and more actionable. It was a point of focus for improvement in the release.

The ORA-06502 error info indicates there's some Oracle PL/SQL code that isn't expecting a CID value so large. If this is PL/SQL that your team did not create, then it's a DB server bug.

An ODP.NET trace can point to which DB server module has the error. However, a DB server trace will be able to get more root cause details.

Does this error also occur if you use ODP.NET Core without EF Core.?

sccunningham7 commented 1 year ago

I'm stuck with at most Oracle 12c (12.1) for the foreseeable future. Unfortunately outside my control.

When I discovered this bug, it was on 11g. Anytime the filepath was too long, the CID generated by ODP.NET would cause an OR-06502. Shorter names and the issue goes away.

When testing this against another 11g database, identical version, there was no issue. Also tested against a 12c server and had no issue. So I probably jumped the gun here: The issue is likely with the configuration of this specific 11g server. I have no idea what that configuration would be, but that is the only thing that would be different here.

Further context from the ODP.NET trace: TTCAuthenticate.ReceiveOAuthResponse() is when we get the error back, so something in the auth process goes awry.

I doubt there is any way to resolve this from your side, so I won't bother testing with just ODP.NET (I don't have anything directly using that handy anyway). Thanks for the response.