Oracle/PHP problem

[Geoff Muldoon]

Cross-posted to comp.databases. oracle.misc and comp.lang.php ...

Using PHP 4.3.11 on Linux and ADODB data abstraction library.

Oracle client version is 10.1.

Can successfully connect to remote database (version 9.2.0.6 on Linux
NLS_LANG=AL32UT F8).

When doing a simple select from a view:
select RTS_LOAD from VW_R_PROJECTED_ LOAD_OUTPUT
I get the error message:
ociexecute(): OCIStmtExecute: ORA-01858: a non-numeric character was found
where a numeric was expected in adodb/461/drivers/adodb-oci8.inc.php on
line 942
1858: ORA-01858: a non-numeric character was found where a numeric was
expected

This happens even if I just select count(*) from the view.

Using SQLPlus from the web server I don't have any problems at all
selecting from the view, so it is definitely valid.

If in my PHP script I directly query the underlying table that this Oracle
view is based on, I have no problem.

Any clues?

Geoff M

[Erwin Moller]

Re: Oracle/PHP problem

Geoff Muldoon wrote:
[color=blue]
> Cross-posted to comp.databases. oracle.misc and comp.lang.php ...
>
> Using PHP 4.3.11 on Linux and ADODB data abstraction library.
>
> Oracle client version is 10.1.
>
> Can successfully connect to remote database (version 9.2.0.6 on Linux
> NLS_LANG=AL32UT F8).
>
> When doing a simple select from a view:
> select RTS_LOAD from VW_R_PROJECTED_ LOAD_OUTPUT
> I get the error message:
> ociexecute(): OCIStmtExecute: ORA-01858: a non-numeric character was found
> where a numeric was expected in adodb/461/drivers/adodb-oci8.inc.php on
> line 942
> 1858: ORA-01858: a non-numeric character was found where a numeric was
> expected
>
> This happens even if I just select count(*) from the view.
>
> Using SQLPlus from the web server I don't have any problems at all
> selecting from the view, so it is definitely valid.
>
> If in my PHP script I directly query the underlying table that this Oracle
> view is based on, I have no problem.
>
> Any clues?
>
> Geoff M[/color]

Hi Geoff,

Assuming you set up the whole thing right, and you had some experience with
adodb... well, in that case it sounds like a firstclass bug to me. :-(

I would contact John Lim (jlim#natsoft.c om, replace# with @) and ask him he
has a clue, and maybe he can fix it for you right away if you ask nicely.
:-)

Regards,
Erwin Moller

[Andy Hassall]

Re: Oracle/PHP problem

On Tue, 28 Feb 2006 14:37:33 +1100, Geoff Muldoon
<geoff.muldoon@ trap.gmail.com> wrote:
[color=blue]
>Cross-posted to comp.databases. oracle.misc and comp.lang.php ...
>
>Using PHP 4.3.11 on Linux and ADODB data abstraction library.
>
>Oracle client version is 10.1.
>
>Can successfully connect to remote database (version 9.2.0.6 on Linux
>NLS_LANG=AL32U TF8).
>
>When doing a simple select from a view:
>select RTS_LOAD from VW_R_PROJECTED_ LOAD_OUTPUT
>I get the error message:
>ociexecute() : OCIStmtExecute: ORA-01858: a non-numeric character was found
>where a numeric was expected in adodb/461/drivers/adodb-oci8.inc.php on
>line 942
>1858: ORA-01858: a non-numeric character was found where a numeric was
>expected
>
>This happens even if I just select count(*) from the view.
>
>Using SQLPlus from the web server I don't have any problems at all
>selecting from the view, so it is definitely valid.
>
>If in my PHP script I directly query the underlying table that this Oracle
>view is based on, I have no problem.
>
>Any clues?[/color]

Try enabling debug on the ADOdb connection so you can see what it is actually
tring to execute (rather than what you expect it is executing) - just to make
sure.

My initial thought is NLS issues - implicit conversions somewhere in the view?

ORA-01858 is thrown by date conversions - don't think anything else throws
that, IIRC.

What's NLS_DATE_FORMAT set to?

What's the definition of the view?

When you queried the table directly, did you use *exactly* the same SQL as the
view definition?

--
Andy Hassall :: andy@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Geoff Muldoon]

Re: Oracle/PHP problem

andy@andyh.co.u k says...
[color=blue]
> My initial thought is NLS issues - implicit conversions somewhere in the view?[/color]

Bingo.

Although in my PHP code I always use explicit (to_date) date conversion in
any SQL, it appears that querying a view that has been successfully
compiled using implicit date conversion causes the problem.

Odd that PHP and/or AdoDB and/or OCI appears to be attempting to
"revalidate " the view when simply selecting from it. Don't know if this
is version specific or even at what stage (PHP/AdoDb/OCI/Oracle Client)
it's kicking in.

Just recompiled the view with explicit date conversion and moving on, no
time to chase further.

Thanks Andy and Erwin for your pointers, owe you one.

Geoff
[color=blue]
> ORA-01858 is thrown by date conversions - don't think anything else throws
> that, IIRC.
>
> What's NLS_DATE_FORMAT set to?
>
> What's the definition of the view?
>
> When you queried the table directly, did you use *exactly* the same SQL as the
> view definition?
>
>[/color]