[ODBC Oracle Driver]Invalid scale value 
Author Message
 [ODBC Oracle Driver]Invalid scale value

I am trying to query an Oracle database using ODBC and Microsoft Excel. I
want to pass the query a date so that I can produce statistics for the
specified month. When I try to use a cell value as a parameter (formatted as
an Excel date) I get an [Microsoft][ODBC Oracle Driver]Invalid scale value
error.

What am I doing wrong?



Thu, 08 Jul 2004 19:19:48 GMT  
 [ODBC Oracle Driver]Invalid scale value

Quote:

> I am trying to query an Oracle database using ODBC and Microsoft Excel. I
> want to pass the query a date so that I can produce statistics for the
> specified month. When I try to use a cell value as a parameter (formatted as
> an Excel date) I get an [Microsoft][ODBC Oracle Driver]Invalid scale value
> error.

Dates are a pain.

I don't know the Oracle flavour of SQL, but I believe that the standard
representation of a date in SQL is #<date>#.  I would try making your
parameter a string like ="#" & TEXT(TheDate,"d-mmm-yyyy") & "#"

Let us know if you find a solution

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup



Mon, 12 Jul 2004 08:44:21 GMT  
 [ODBC Oracle Driver]Invalid scale value
Alan

In Oracle you have a "default" date format associated with your session.  That
means that if you pass it a date in that format it will understand it.  If you
pass a date in any other format you must include a format string to tell the
Oracle server what you mean.  In theory the ODBC driver may convert dates for you,
but in practice this doesn't always work and can slow things down considerably.

In practice the only really safe thing to do is always include a format string in
your SQL statement.

So, for example, this is how I would use a date in a SQL statement

select
<columns list...>
from
<table list.....>
where
<other predicates...>
and <date_field> = to_date('28-jan-2002','dd-mon-yyyy')

The 'to_date' is an Oracle function that takes a character string containing a
date (or a date and time) and a second character string which gives the format.
Its fairly similar to the VB/VBA format command but the picture strings are
different.  Also both strings should be in single quotes.

If you are returning dates from Oracle then its often a good idea to do the
reverse of this procedure.  Using the Oracle to_char function to convert a date
into a string that will be unambiguous when it gets back to VB/VBA.  Otherwise you
can get wrong interpretations of dates in your results.

Hope this helps.

Jim Markham

Quote:


> > I am trying to query an Oracle database using ODBC and Microsoft Excel. I
> > want to pass the query a date so that I can produce statistics for the
> > specified month. When I try to use a cell value as a parameter (formatted as
> > an Excel date) I get an [Microsoft][ODBC Oracle Driver]Invalid scale value
> > error.

> Dates are a pain.

> I don't know the Oracle flavour of SQL, but I believe that the standard
> representation of a date in SQL is #<date>#.  I would try making your
> parameter a string like ="#" & TEXT(TheDate,"d-mmm-yyyy") & "#"

> Let us know if you find a solution

> Bill Manville
> MVP - Microsoft Excel, Oxford, England
> No email replies please - reply in newsgroup



Sun, 18 Jul 2004 06:16:25 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. error [Microsoft]ODBC driver for Oracle]Invalid scale value

2. [ODBC DRIVER FOR ORACLE] INVALID SCALE VALUE

3. ODBC-Error: [Oracle] [ODBC Oracle Driver] Invalid date, time or timestamp value (#0)

4. Oracle ODBC driver / Microsoft ODBC driver for Oracle

5. ODBC SQL Server Driver Invalid charater value for cast specification (0#)

6. ODBC error - Invalid Precision Value - Access 2000 front with Oracle 8 table

7. Microsoft ODBC 3.0 and Oracle ODBC Drivers on NT 4.0

8. ODBC DRIVER MSACCESS97 AND ODBC DRIVER MSACCESS2000 ON SAME PC

9. Excel and Oracle 8.1 ODBC Driver

10. Microsoft ODBC for Oracle drivers


 
Powered by phpBB® Forum Software © phpBB Group