NUMERIC DATATYPE

[NITIN27]

HI
i am new to microsoft access so kindly bear with me

I have create a ODBC CONNECTION named "TEST" TO MICROSOFT ACCESS WITH ORACLE now i have opened sqlplus

connect A/A@ODBC:TEST

I HAVE WRITTEN A CREATE TABLE STATEMENT

CREATE TABLE NN(
NAME CHAR(30),
AUTO_NO NUMBER(10,2));

ERROR IS COMING INVALID CREATE TABLE STATEMENT

HOW TO WRITE NUMERIC DATATYPE WITH 2 DECIMAL PLACES

PLS. HELP ME
THANKS IN ADVANCE

 

[jrbarnett]

Hello

I think that you are trying to use SQL Plus to connect to your Access database? If so, try using Access instead.
Go to the table section, then click New and Design view, then put the information in there.
If you want to create it through SQL, go to the query section, click New, then press escape to open the SQL Window and paste the SQL in.
Remember however that your code is written in Oracle PL/SQL. To do it in Access, you need to use Jet SQL (Jet is the database engine that underlies MS Access) which is different to Oracle.

John

 

[NITIN27]

Yes, u are right and i want to create a table in sqlplus by connecting Ms access database so pls tell me how to do this
and which datatype will store number(14,2) field like we do in oracle

eg
123456789234.23

i have tried float,double,number but they only store
number(7,2)

pls. answer me as i have to do it by today afternoon

thanks

 

[jrbarnett]

Use the following SQL code:

CREATE TABLE NN(NAME TEXT(30), AUTO_NO double);

If you want to set one of the fields as a primary key, put "PRIMARY KEY" after the field data type, to set required to true put "NOT NULL" there.

John

 

[NITIN27]

Thanks for ur reply.it seems to be working but when i try to insert a row in sqlplus it only stores number(7,2) but in microsoft access it stores full value but doesn't show decimal in between instead it shows comma in access datasheet
eg
1234567893,34

could u tell me why it show like this?

actually i have to transfer data from oracle to ms access so i am bit conerned that will some numeric data will be lost

pls. help me
thanks in advance

 

[jrbarnett]

Hello,

Copied straight out of the Access help file, these are the definitions of the Single, Double and Decimal data types, all large capacity for storing real values:

Single
(single-precision floating-point) 4 bytes -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values

Double
(double-precision floating-point) 8 bytes -1.79769313486231E308 to
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values

Decimal
14 bytes +/-79,228,162,514,264,337,593,543,950,335 with no decimal point;
+/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is
+/-0.0000000000000000000000000001

If "Decimal" isn't big enough, you can specify "Variant" which allows 16 bytes, but this can also be used for storing text data, and to store null values.

John