OCILogon problem - Oracle password expiry

Hi all,

Help please.

Environment:
Unix (Tru64) / Apache / PHP4.0.6 / Oracle8i

Establish connection to Oracle:

for ($I=0; $I<$max_attempts; $I++) {
if (!$conn) {
$conn = @OCILogon($username, $password, $db);
}
}
if (!$conn) {
echo "Connection failed";
$subject = "Connection failed";
$body = "Error Message: [" . OCIError($conn) . "]";
mail($me, $subject, $headers, $body, $param);
exit;
} else {
echo "Connected!";
}

Connection appeared to have failed, but no OCIError displaying (Error
Message: []).

Discovered that the Oracle password had expired, but that I was still in
the "14 day grace period". OCI was actually returning
"OCI_SUCCESS_WITH_INFO" (the extra info being the password expiry
alert).

$conn not valid (apparently the "WITH_INFO" somehow scrambled it???),
but
a Oracle session *WAS* actually established, and eventually the DB maxed
out on session limits due to continual script retries.

Any suggestions on how best to handle this?

TIA

Geoff M

re: OCILogon problem - Oracle password expiry

"Geoff Muldoon" a écrit le 12/12/2003 :[color=blue]
> Hi all,[/color]
Hi

[...][color=blue]
> $conn = @OCILogon($username, $password, $db);[/color]

The use of @ prevent from error msg as far as I know, so maybe you
cannot then retreive it via OCIError...

--
Have you read the manual?
http://www.php.net/manual/en/

re: OCILogon problem - Oracle password expiry

jedi121news@free.fr.Removethis says...[color=blue]
> "Geoff Muldoon" a écrit le 12/12/2003 :[color=green]
> > Hi all,[/color]
> Hi
>
> [...][color=green]
> > $conn = @OCILogon($username, $password, $db);[/color]
>
> The use of @ prevent from error msg as far as I know, so maybe you
> cannot then retreive it via OCIError...[/color]

The @ supresses the injection of error messages into the data stream
output. Removing it made no difference to Error Message: [" . OCIError
($conn) . "].

The disply of the error message is not my main concern, rather that the
$conn session object was not set as valid, but the Oracle client session
was actually established.

Tks,

Geoff M

re: OCILogon problem - Oracle password expiry

On Fri, 12 Dec 2003 11:52:42 +1100, Geoff Muldoon
<gmuldoon@scu.edu.au> wrote:
[color=blue]
>Environment:
>Unix (Tru64) / Apache / PHP4.0.6 / Oracle8i
>
>Establish connection to Oracle:
>
>for ($I=0; $I<$max_attempts; $I++) {
> if (!$conn) {
> $conn = @OCILogon($username, $password, $db);
> }
>}
>if (!$conn) {
> echo "Connection failed";
> $subject = "Connection failed";
> $body = "Error Message: [" . OCIError($conn) . "]";
> mail($me, $subject, $headers, $body, $param);
> exit;
>} else {
> echo "Connected!";
>}
>
>Connection appeared to have failed, but no OCIError displaying (Error
>Message: []).
>
>Discovered that the Oracle password had expired, but that I was still in
>the "14 day grace period". OCI was actually returning
>"OCI_SUCCESS_WITH_INFO" (the extra info being the password expiry
>alert).
>
>$conn not valid (apparently the "WITH_INFO" somehow scrambled it???),
>but
>a Oracle session *WAS* actually established, and eventually the DB maxed
>out on session limits due to continual script retries.
>
>Any suggestions on how best to handle this?[/color]

Look at:

http://bugs.php.net/bug.php?id=17448

By the looks of it, it has been "fixed" in the wrong way.

re: OCILogon problem - Oracle password expiry

andy@andyh.co.uk says...
[color=blue][color=green]
> >Discovered that the Oracle password had expired, but that I was still in
> >the "14 day grace period". OCI was actually returning
> >"OCI_SUCCESS_WITH_INFO" (the extra info being the password expiry
> >alert).
> >
> >$conn not valid (apparently the "WITH_INFO" somehow scrambled it???),
> >but
> >a Oracle session *WAS* actually established, and eventually the DB maxed
> >out on session limits due to continual script retries.
> >
> >Any suggestions on how best to handle this?[/color]
>
> Look at:
>
> http://bugs.php.net/bug.php?id=17448[/color]

Thanks for the info Andy. Unfortunately, I can't raise bugs.php.net at
the moment (web server down?? can get www.php.net). Will try again
later. Any chance you could post the bug note?

Cheers,

Geoff M

re: OCILogon problem - Oracle password expiry

On Mon, 15 Dec 2003 14:58:14 +1100, Geoff Muldoon <gmuldoon@scu.edu.au> wrote:
[color=blue][color=green]
>> Look at:
>>
>> http://bugs.php.net/bug.php?id=17448[/color]
>
>Thanks for the info Andy. Unfortunately, I can't raise bugs.php.net at
>the moment (web server down?? can get www.php.net). Will try again
>later. Any chance you could post the bug note?[/color]

How about the Google cache of it:

http://66.102.11.104/search?q=cache:...hl=en&ie=UTF-8

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

re: OCILogon problem - Oracle password expiry

andy@andyh.co.uk says...
[color=blue][color=green][color=darkred]
> >> http://bugs.php.net/bug.php?id=17448[/color][/color][/color]
[color=blue]
> How about the Google cache of it:
> http://66.102.11.104/search?q=cache:...hl=en&ie=UTF-8[/color]

Tks greatly. Agree that it is unfortunate that OCI_SUCCESS_WITH_INFO is
handled by PHP as an error, particularly as it appears that there is no
way (no valid connection handler) within PHP to terminate the existent
Oracle Client connection.

Hopefully PHP 5.0?

Cheers,

Geoff M

re: OCILogon problem - Oracle password expiry

On Tue, 16 Dec 2003 10:17:23 +1100, Geoff Muldoon <gmuldoon@scu.edu.au> wrote:
[color=blue]
>andy@andyh.co.uk says...
>[color=green][color=darkred]
>> >> http://bugs.php.net/bug.php?id=17448[/color][/color]
>[color=green]
>> How about the Google cache of it:
>> http://66.102.11.104/search?q=cache:...hl=en&ie=UTF-8[/color]
>
>Tks greatly. Agree that it is unfortunate that OCI_SUCCESS_WITH_INFO is
>handled by PHP as an error, particularly as it appears that there is no
>way (no valid connection handler) within PHP to terminate the existent
>Oracle Client connection.
>
>Hopefully PHP 5.0?[/color]

PHP's open source, patch it yourself :-)

I'd be surprised if there were no more maintenance releases of PHP 4.3. Not
sure what the process for submitting a patch to PHP is.

Just from a brief glance, looks like all you'd have to change to get it to at
least connect would be the status checks on oci_session in ext/oci/oci8.c.
There's a whole load of:

if (OCI(error) != OCI_SUCCESS) {

At least one of those - the one that can raise the password expiry warning
(OCISessionBegin?) - needs changing to:

if (OCI(error) != OCI_SUCCESS
&& OCI(error) != OCI_SUCCESS_WITH_INFO ) {

Whilst I've got the file open, may as well give it a go.

andyh@testbox:~$ sqlplus -S expiring/expiring
ERROR:
ORA-28002: the password will expire within 2 days

<pre>
<?php
putenv('ORACLE_HOME=/home/oracle/Ora92');
putenv('ORACLE_SID=dev92lnx');

if (!$oci = OCILogon("expiring", "expiring")) {
$err = OCIError();
var_dump($err);
die();
}

$stmt = OCIParse($oci, "select sysdate from dual");
OCIExecute($stmt);
OCIFetchStatement($stmt, $array);
var_dump($array);

OCILogoff($oci);

?>
</pre>

Before:


Warning: ocilogon(): OCISessionBegin: OCI_SUCCESS_WITH_INFO: ORA-28002: the
password will expire within 2 days
in /home/andyh/public_html/connect.php on line 6
array(4) {
["code"]=>
int(28002)
["message"]=>
string(50) "ORA-28002: the password will expire within 2 days
"
["offset"]=>
int(0)
["sqltext"]=>
string(0) ""
}

After applying patch at end of this message, it outputs:

Warning: ocilogon(): OCISessionBegin: OCI_SUCCESS_WITH_INFO: ORA-28002: the
password will expire within 2 days
in /home/andyh/public_html/connect.php on line 6
array(1) {
["SYSDATE"]=>
array(1) {
[0]=>
string(9) "15-DEC-03"
}
}


It seemed the right thing to do to output the warning; all I've done is
avoided it aborting entirely. Patch follows:

*** oci8.c.orig Mon Dec 15 23:39:42 2003
--- oci8.c Mon Dec 15 23:43:49 2003
***************
*** 2321,2326 ****
--- 2321,2327 ----

if (OCI(error) != OCI_SUCCESS) {
oci_error(OCI(pError), "OCISessionBegin", OCI(error));
+ if (OCI(error) != OCI_SUCCESS_WITH_INFO)
goto CLEANUP;
}

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)