ORA-01012 Not Logged On Error Code

[Bee Vang]

Has anyone try trapping logon errors for Oracle 8i? Specifically, I
want to trap for the following errors:

ORA-01017: Invalid username/password
ORA-28000: Account Locked
ORA-28001: Password expired
ORA-28002: Password about to expire

When a user enters a wrong username or password, I should be getting
error ORA-01017. If the user tries too many times with incorrect
username/password, I should be getting the ORA-28000 after the account
locks up. I should be getting the other errors when the password is
about to expire or has expired.

The problem is that I always get back the "ORA-01012: Not Logged On"
error when entering a wrong username or password, account locked or
password expires.

This is a snipplet of my code:

dbTmp := TDatabase.Create(Session);
try
dbTmp.DatabaseName := 'DB1';
dbTmp.Params.Add('USER NAME='+editUserName.Text);
dbTmp.Params.Add('PASSWORD='+editPassword.Text);
dbTmp.LoginPrompt := FALSE;
if not dbTmp.Connected then
dbTmp.Connected := TRUE;
bLoginSuccess := TRUE;
except on E:EDBEngineError do
begin
bLoginSuccess := FALSE;
if Pos('ORA-01017',E.Message) > 0 then
begin
ShowMessage('Invalid username or password. Please try again.');

editPassword.SetFocus;
editPassword.SelectAll;
end else if Pos('ORA-28000',E.Message) > 0 then
begin
ShowMessage('You''re account is now locked because the number
'+#13+#10+
'of logins has been exceeded. Please contact
'+#13+#10+
'your Database Administrator or PCF support staff
'+#13+#10+
'to unlock your account.');
end else if Pos('ORA-28001',E.Message) > 0 then
begin
ShowMessage('You''re password has expired. Please use
the'+#13+#10+
'password button to change your password.');
end else if Pos('ORA-28002',E.Message) > 0 then
begin
ShowMessage('You''re password is about to expire. Please use
the'+#13+#10+
'password button to change your password.');
end else
ShowMessage(E.Message);
end;
end;
if dbTmp <> nil then
dbTmp.Free;

In the main form OnCreate event, I have the following code:

slTmp := TStringList.Create;
try
slTmp.Add('SERVER NAME='+Trim(editDatabase.Text));
if not Session.IsAlias('DB1Alias') then
Session.AddAlias('DB1Alias','ORACLE',slTmp)
else
Session.ModifyAlias('DB1Alias',slTmp);
finally
if slTmp <> nil then
slTmp.Free;
end;

I'm using D5 sp1 and Oracle 8i.

Any help is greatly appreciated.

[John Pierce]

Haven't tried it for this, specifically, but you should probably look at
DbiGetErrorEntry (see Delphi Tools help).

John PIerce

[Bee Vang]

John,

Thanks for the info. However, it still doesn't work. I have my code inside
a try except block. This will catch the error and I believe once it does
that, the error stack is clear. Therefore, this function returns that there
are no error in the stack. If I don't have it in a try except block then
when it errored out, it'll stop processing the rest of the code.

I decided to test this using the DbiOpenDatabase function. This works
without giving me an error and without a try except block. Right after
that, I call the DbiGetErrorEntry and to my surprise.... I get the same
error code "ORA-01012: Not Logged On". So using DBI functions don't work
either...

Do you have any other ideas... besides going to a 3rd party product?

[John Pierce]

John Pierce wrote:

> Also, I wonder what would happen if you put the following in a TQuery:
> begin
> connect user/password@service;
> end;
>
> ExecSql;
>

I must have been brain-dead when I wrote that. I've been working with a lot of
SQL scripts and I must have missed a gear.

John Pierce

[Bee Vang]

I thought about that too. I did give a try, but end up in a catch 22 situation.
In order to use TQuery, you must be log on to the database first. But, what I'm
doing is trying to log onto the database. Any other suggestions...

[John Pierce]

Bee Vang wrote:

> I thought about that too. I did give a try, but end up in a catch 22 situation.
> In order to use TQuery, you must be log on to the database first. But, what I'm
> doing is trying to log onto the database. Any other suggestions...

Exactly! I'm afraid I'm out of ideas, short of switching to DOA, which you ought to
do anyway :)

John PIerce

[Alexei Belous]

Hello.

I have the same problem.

You'll get this problem in case of using latest DBE native driver when
connecting
to Oracle8i. If you'll try to use BDE-ODBC connection everything is OK.

But under BDE-ODBC connection you can get other problems, for example,
floating
point datatypes problems in case of using different regional settings,
for example,
German.

I think, at the moment the best solution is to use older version of DBE
native
driver DLL - SQLORA32.DLL instead of using SQLORA8.DLL.

At the moment I didn't find any problems when using this configuration.

Good luck,
Alexei Belous