Search workaround for DBD:Oracle problem producing "ORA-01722"

Hi everybody,

I have an Oracle DBD problem demonstrated in the following code:

• start code ------------------------------------- create table base(x varchar(255)); create table derived as (select x from base where x >= 1);
  -- ok
  

drop table derived;

insert into base values('alpha');

create table derived as (select x from base where x >= 1);
-- DBD makes wrong guess leading to:

• ORA-01722: invalid number (DBD ERROR: OCIStmtExecute)

drop table base;

---------------- end code ---------------------------------------

The error happens when I execude the code using the Perl DBI, it does not occur under the Oracle SQL Worksheet.

As far as I understand, the following happens: Oracle accepts the wrongly typed condition "where x >= 1" but the DBI make the false assumption that x is a number. It creates "derived(x number)" and Oracle does not accept an alpha value for "x".

This behavor is quite annoying since the error depends upon the data in the base table: as long as it just contains numbers (stored as chars) nothing happens. The error occurs after entering the first real string value.

Any ideas for fixing the problem except "write correct code" (sometimes I do err) and "use prepared statements"?

Please CC to my e-mail adress, since I do not read this group regularly.

Ciao Jens Received on Sat Jul 21 2001 - 18:52:55 CDT