[Oracle][ODBC]Numeric value out of range.

Hi. I'm new to the list. I have a perl script that was working fine until I upgraded my Oracle
client 9i to 10g (instant client). I'm inserting data into an Oracle database via DBD-ODBC. The
insert will succeed if I do not bind the variables ($rec->{'YEAR APPROVED SES'} and
$rec->{'YEAR APPLIED'}), putting them directly in the query (replacing the question marks). Why
is the query suddenly failing after upgrading 9i to 10g? The trace is pasted below.

Thanks,
Paul

The table
-----------------------------
CREATE TABLE PROVIDER (
PSEQ NUMBER(10) NOT NULL,
PID VARCHAR2(10) NOT NULL UNIQUE,
ORGNAME VARCHAR2(255) NOT NULL,
STATUS VARCHAR2(15) NOT NULL,
APPLICATION VARCHAR2(15),
DATE_APPRVD DATE,
DATE_APPLYD DATE,
CREATED DATE NOT NULL,
PRIMARY KEY (PSEQ)
);

The section of code dying
-----------------------------
my $sql = < INSERT INTO PROVIDER (PSEQ,PID,ORGNAME,STATUS,APPLICATION,DATE_APPRVD,DATE_APPLYD ,CREATED)
VALUES (?, ?, ?, ?, ?, TO_DATE(?,'MM/YYYY'), TO_DATE(?,'MM/YYYY'),SYSDATE);
ENDSQL

my $sth1 = $dbh_ora->prepare($sql);
$sth1->trace(9);
$sth1->execute($pseq, $rec->{'PROVIDERNUM'}, $rec->{'ORGNAME'},
'APPROVED', '', $rec->{'YEAR APPROVED SES'}, $rec->{'YEAR APPLIED'});


The trace
-----------------------------
bind 6 <== '6/2007' (attribs: ), type 0
bind 6 <== '6/2007' (size 6/8/0, ptype 5, otype 1, sqltype 0)
bind 6 <== '6/2007' (len 6/7, null 0)
SQLDescribeParam idx = 6.
SQLDescribeParam 6: SqlType=TIMESTAMP, ColDef=19
bind 6: CTy=1, STy=TIMESTAMP, CD=6, Sc=0, VM=6.
SQLBindParameter: idx = 6: fParamType=1, name=6, fCtype=1, SQL_Type = 93, cbColDef=6,
scale=0, rgbValue =
af21cfc, cbValueMax=6, cbValue = 6
Param value = 6/2007
bind 7 <== '6/2007' (attribs: ), type 0
bind 7 <== '6/2007' (size 6/8/0, ptype 5, otype 1, sqltype 0)
bind 7 <== '6/2007' (len 6/7, null 0)
SQLDescribeParam idx = 7.
SQLDescribeParam 7: SqlType=TIMESTAMP, ColDef=19
bind 7: CTy=1, STy=TIMESTAMP, CD=6, Sc=0, VM=6.
SQLBindParameter: idx = 7: fParamType=1, name=7, fCtype=1, SQL_Type = 93, cbColDef=6,
scale=0, rgbValue =
af21f14, cbValueMax=6, cbValue = 6
Param value = 6/2007
dbd_st_execute (outparams = 0)...
dbd_st_execute (for hstmt 28450064 before)...
dbd_st_execute (for hstmt 28450064 after, rc = -1)...
dbd_error: err_rc=-1 rc=0 s/d/e: 28450064/28449240/28447392
dbd_error: SQL-22003 (native 0): [Oracle][ODBC]Numeric value out of range. (SQL-22003)
dbd_error: SQL-HY000 (native 0): [Oracle][ODBC]Numeric value out of range. (SQL-22003)
[Oracle][ODBC]General error. (SQL-HY000)
dbd_error: err_rc=-1 rc=0 s/d/e: 0/28449240/28447392
dbd_error: err_rc=-1 rc=0 s/d/e: 0/0/28447392
st_execute/SQLExecute error -1 recorded: [Oracle][ODBC]Numeric value out of range. (SQL-22003)
[Oracle][ODBC]General error. (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)
!! ERROR: -1 '[Oracle][ODBC]Numeric value out of range. (SQL-22003)
[Oracle][ODBC]General error. (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)' (err#0)
<- execute= undef at mdb-to-ora.pl line 315 via at mdb-to-ora.pl line 115
DBD::ODBC::st execute failed: [Oracle][ODBC]Numeric value out of range. (SQL-22003)
[Oracle][ODBC]General error. (SQL-HY000)(DBD: st_execute/SQLExecute err=-1) at mdb-to-ora.pl
line 315.
DBD::ODBC::st execute failed: [Oracle][ODBC]Numeric value out of range. (SQL-22003)
[Oracle][ODBC]General error. (SQL-HY000)(DBD: st_execute/SQLExecute err=-1) at mdb-to-ora.pl
line 315.
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0xaf1579c)~INNER) thr#2240c4
SQLFreeStmt called, returned 0.
ERROR: -1 '[Oracle][ODBC]Numeric value out of range. (SQL-22003)
[Oracle][ODBC]General error. (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)' (err#0)
<- DESTROY= undef

Re: [Oracle][ODBC]Numeric value out of range.

--- Paul Simon wrote:

> Hi. I'm new to the list. I have a perl script that was working fine until I upgraded my
> Oracle
> client 9i to 10g (instant client). I'm inserting data into an Oracle database via DBD-ODBC.
> The
> insert will succeed if I do not bind the variables ($rec->{'YEAR APPROVED SES'} and
> $rec->{'YEAR APPLIED'}), putting them directly in the query (replacing the question marks).
> Why
> is the query suddenly failing after upgrading 9i to 10g? The trace is pasted below.
>
> Thanks,
> Paul
>
> The table
> -----------------------------
> CREATE TABLE PROVIDER (
> PSEQ NUMBER(10) NOT NULL,
> PID VARCHAR2(10) NOT NULL UNIQUE,
> ORGNAME VARCHAR2(255) NOT NULL,
> STATUS VARCHAR2(15) NOT NULL,
> APPLICATION VARCHAR2(15),
> DATE_APPRVD DATE,
> DATE_APPLYD DATE,
> CREATED DATE NOT NULL,
> PRIMARY KEY (PSEQ)
> );
>
> The section of code dying
> -----------------------------
> my $sql = < > INSERT INTO PROVIDER (PSEQ,PID,ORGNAME,STATUS,APPLICATION,DATE_APPRVD,DATE_APPLYD ,CREATED)
> VALUES (?, ?, ?, ?, ?, TO_DATE(?,'MM/YYYY'), TO_DATE(?,'MM/YYYY'),SYSDATE);
> ENDSQL
>
> my $sth1 = $dbh_ora->prepare($sql);
> $sth1->trace(9);
> $sth1->execute($pseq, $rec->{'PROVIDERNUM'}, $rec->{'ORGNAME'},
> 'APPROVED', '', $rec->{'YEAR APPROVED SES'}, $rec->{'YEAR APPLIED'});
>
>
> The trace
> -----------------------------
> bind 7 <== '6/2007' (attribs: ), type 0
> bind 7 <== '6/2007' (size 6/8/0, ptype 5, otype 1, sqltype 0)
> bind 7 <== '6/2007' (len 6/7, null 0)
> SQLDescribeParam idx = 7.
> SQLDescribeParam 7: SqlType=TIMESTAMP, ColDef=19
> bind 7: CTy=1, STy=TIMESTAMP, CD=6, Sc=0, VM=6.
> SQLBindParameter: idx = 7: fParamType=1, name=7, fCtype=1, SQL_Type = 93, cbColDef=6,
> scale=0, rgbValue =
> af21f14, cbValueMax=6, cbValue = 6
> Param value = 6/2007
> dbd_st_execute (outparams = 0)...
> dbd_st_execute (for hstmt 28450064 before)...
> dbd_st_execute (for hstmt 28450064 after, rc = -1)...
> dbd_error: err_rc=-1 rc=0 s/d/e: 28450064/28449240/28447392
> dbd_error: SQL-22003 (native 0): [Oracle][ODBC]Numeric value out of range. (SQL-22003)
> dbd_error: SQL-HY000 (native 0): [Oracle][ODBC]Numeric value out of range. (SQL-22003)
> [Oracle][ODBC]General error. (SQL-HY000)

I finally got this working again by using bind_param() and explicitly setting the sql type as
SQL_VARCHAR for Oracle's TO_DATE function:

use DBI qw(:sql_types);
...
my $sth1 = $dbh_ora->prepare($sql);
$sth1->bind_param(1, $pseq);
$sth1->bind_param(2, $rec->{'PROVIDERNUM'});
$sth1->bind_param(3, $rec->{'ORGNAME'});
$sth1->bind_param(4, 'APPROVED');
$sth1->bind_param(5, '');
$sth1->bind_param(6, $rec->{'YEAR APPROVED SES'},SQL_VARCHAR);
$sth1->bind_param(7, $rec->{'YEAR APPLIED'},SQL_VARCHAR );

I'm guessing that DBI is making incorrect assumptions about SQL Types somewhere... Is this
what's happening?

Re: [Oracle][ODBC]Numeric value out of range.

------=_Part_27383_25979343.1184778290678
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/16/07, Paul Simon wrote:
>
>
> I'm guessing that DBI is making incorrect assumptions about SQL Types
> somewhere... Is this
> what's happening?


It could be differences between versions, as you apparently upgraded the
client only.

You didn't mention the database version.

Try running your script without bind_param against a different version of
the database.



--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

------=_Part_27383_25979343.1184778290678--

Re: [Oracle][ODBC]Numeric value out of range.

> > I'm guessing that DBI is making incorrect assumptions about SQL Types
> > somewhere... Is this
> > what's happening?
>
>
> It could be differences between versions, as you apparently upgraded the
> client only.
>
> You didn't mention the database version.
>
> Try running your script without bind_param against a different version of
> the database.

Ah, I didn't think about the version match between client and server. Your assumption is
correct - I only upgraded the client to 10g. The database server is 9i (9.2.0.6.0). So, that
could be causing the problem. (That's not good because I have a php script using the 10g client
to hit 9i too - hope there aren't any hidden problems there...)

I do have access to a 10g server, but It's going to take a little while to get what I need in
place. I'll run my script against that database then report back.

Thanks!

Re: [Oracle][ODBC]Numeric value out of range.

> > > I'm guessing that DBI is making incorrect assumptions about SQL Types
> > > somewhere... Is this
> > > what's happening?
> >
> >
> > It could be differences between versions, as you apparently upgraded the
> > client only.
> >
> > You didn't mention the database version.
> >
> > Try running your script without bind_param against a different version of
> > the database.
>
> Ah, I didn't think about the version match between client and server. Your assumption is
> correct - I only upgraded the client to 10g. The database server is 9i (9.2.0.6.0). So, that
> could be causing the problem. (That's not good because I have a php script using the 10g
> client
> to hit 9i too - hope there aren't any hidden problems there...)
>
> I do have access to a 10g server, but It's going to take a little while to get what I need in
> place. I'll run my script against that database then report back.
>
> Thanks!
>

I ran the script against oracle 10g server and got the same error.

DBD::ODBC::st execute failed: [Oracle][ODBC]Numeric value out of range. (SQL-22003)
[Oracle][ODBC]General error. (SQL-HY000)(DBD: st_execute/SQLExecute err=-1) at mdb-to-ora.pl
line 326.

Again, setting the sql type to SQL_VARCHAR via bind_param solves the error...

Looking back, the problem started when I upgraded the oracle client (including oracle odbc
driver) from 9i to 10g. There's something not right here - even though the data type in schema
is DATE, the function, TO_DATE(?,'MM/YYYY'), requires a string.

Re: [Oracle][ODBC]Numeric value out of range.

On Fri, 2007-07-20 at 12:34 -0700, Paul Simon wrote:

> Again, setting the sql type to SQL_VARCHAR via bind_param solves the error...
>
> Looking back, the problem started when I upgraded the oracle client (including oracle odbc
> driver) from 9i to 10g. There's something not right here - even though the data type in schema
> is DATE, the function, TO_DATE(?,'MM/YYYY'), requires a string.

Your message seems to imply that to_date() should not require a string.

If that is so, it is incorrect. to_date() does require string.

Are you trying to reformat a date for display? Then use to_char().

Re: [Oracle][ODBC]Numeric value out of range.

--- Jared Still wrote:

> On Fri, 2007-07-20 at 12:34 -0700, Paul Simon wrote:
>
> > Again, setting the sql type to SQL_VARCHAR via bind_param solves the error...
> >
> > Looking back, the problem started when I upgraded the oracle client (including oracle odbc
> > driver) from 9i to 10g. There's something not right here - even though the data type in
> schema
> > is DATE, the function, TO_DATE(?,'MM/YYYY'), requires a string.
>
> Your message seems to imply that to_date() should not require a string.
>
> If that is so, it is incorrect. to_date() does require string.
>
> Are you trying to reformat a date for display? Then use to_char().
>
No, I'm using to_date() to insert a string like '06/2006' into a DATE field. I agree with you
that to_date() requires a string, but evidently the software does not. I'd like to know where
the disagreement is. I initially thought it was DBI or ODBC, but now I'm not sure.