Thread: sql error 1064

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0

    sql error 1064


    i m getting this error when i m trying to test .
    plz help me my table is following

    create table dataset_xhydro
    (ifentification_number number(5),
    titl varchar2(4),
    first_name varchar2(15),
    last_name varchar2(15),
    position varchar2(40),
    alternate_address_flag null(1),
    company_name varchar2(60),
    mailing_address varchar2(40),
    street_address varchar2(40),
    city varchar2(25),
    state varchar2(2),
    zipcode number(10),
    phone varchar2(20),
    hydrostat null(1),
    acetylene null(1),
    ultransonic null(1),
    accoustic null(1),
    other_type varchar2(30),
    dot_specifications varchar2(80),
    class_of_tester null(1),
    inspector varchar(4),
    date_inspected date,
    number_years null(1),
    date_received date,
    revision_date date,
    old_approval_date date,
    approval_date date,
    acetylene_oldat date,
    acetylene_apdat date,
    ultrasonic_oldat date,
    ultrasonic_apdat date,
    accoustic_oldat date,
    accoustic_apdat date,
    cmnts varchar2(70),
    video date,
    date_of_entry date,
    date_of_change date,))
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,607
    Rep Power
    4247
    Are you trying to do this for Microsoft SQL Server?? If so, this where the errors are -- there are no column types called NUMBER, VARCHAR2 or DATE for SQL Server. The equivalents would be INTEGER, VARCHAR and DATETIME (or SMALLDATETIME). Also, NULL is an attribute, not a column type, so it would go something like this:
    alternate_address_flag CHAR(1) NULL,
    or
    alternate_address_flag VARCHAR(1) NULL,
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0
    no actually its oracle
  6. #4
  7. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,607
    Rep Power
    4247
    Moved thread from SQL Server to Oracle forum
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    The following DDL (Data Definition Language) statement works in Oracle 8.1.6:
    I replaced the nulls with varchar2 and fixed the closing parenthesis.

    create table dataset_xhydro
    (ifentification_number number(5),
    titl varchar2(4),
    first_name varchar2(15),
    last_name varchar2(15),
    position varchar2(40),
    alternate_address_flag varchar2(1),
    company_name varchar2(60),
    mailing_address varchar2(40),
    street_address varchar2(40),
    city varchar2(25),
    state varchar2(2),
    zipcode number(10),
    phone varchar2(20),
    hydrostat varchar2(1),
    acetylene varchar2(1),
    ultransonic varchar2(1),
    accoustic varchar2(1),
    other_type varchar2(30),
    dot_specifications varchar2(80),
    class_of_tester varchar2(1),
    inspector varchar(4),
    date_inspected date,
    number_years varchar2(1),
    date_received date,
    revision_date date,
    old_approval_date date,
    approval_date date,
    acetylene_oldat date,
    acetylene_apdat date,
    ultrasonic_oldat date,
    ultrasonic_apdat date,
    accoustic_oldat date,
    accoustic_apdat date,
    cmnts varchar2(70),
    video date,
    date_of_entry date,
    date_of_change date)

    Cheers,
    Dan
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0

    Thumbs up


    thank you so much with the last table i posted .it wored


    i m getting following error on this table when i m testing it ..

    error
    ora - 01727 numeric precision specifier is out of range(1to38)

    this is my new table


    create table dataset_symbol
    (identification_number varchar2(5),
    title varchar2(4),
    first_name varchar2(15),
    last_name varchar2(15),
    position varchar2(40),
    alternate_address varchar2(1),
    company_name varchar2(40),
    mailing_address varchar2(40),
    street_address varchar2(40),
    city varchar2(25),
    state varchar2(2),
    zipcode number(10),
    class_of_tester varchar2(1),
    phone number(20),symbol varchar(35),
    specifications varchar(25),
    cfr_section_no number(<length>,40),
    drum_mfg varchar2(1),
    cylinder_mfg varchar(1),
    pail_mfg null(1),
    other_contrs null(15),
    date_received date,
    approval_date date,
    revision_date date,
    date_of_entry date,
    date_of_change date);
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    The following should work:

    create table dataset_symbol
    (identification_number varchar2(5),
    title varchar2(4),
    first_name varchar2(15),
    last_name varchar2(15),
    position varchar2(40),
    alternate_address varchar2(1),
    company_name varchar2(40),
    mailing_address varchar2(40),
    street_address varchar2(40),
    city varchar2(25),
    state varchar2(2),
    zipcode number(10),
    class_of_tester varchar2(1),
    phone number(20),
    symbol varchar2(35),
    specifications varchar2(25),
    cfr_section_no number(38),
    drum_mfg varchar2(1),
    cylinder_mfg varchar2(1),
    pail_mfg varchar2(1),
    other_contrs varchar2(15),
    date_received date,
    approval_date date,
    revision_date date,
    date_of_entry date,
    date_of_change date)

    Cheers,
    Dan
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0
    Dan i cant thank you enough for helping me plz if you could help me with one more table i m having trouble with precision can you plz help me?


    create table dataset_explo
    (labe_report_no varchar(12),
    reference_number number(5),
    contact_title varchar2(4),
    contact_first_name varchar2(20),
    contact_last_name varchar2(20),
    contact_position varchar2(40),
    contact_company varchar2(100),
    contact_address varchar2(50),
    contact_city varchar2(25),
    contact_state varchar2(2),
    contact_zip number(10),
    contact_phone number(20),
    mfger varchar2(80),
    alternate_company_flag varchar2(1)default null,
    additional_comment_flag varchar2(1)default null,
    received_date date,
    un_revision_date date,
    revision_date date,
    dod_cc_no NUMBER(<precision>, 1),
    national_stock_number number(26),
    un_proper_shipping_name varchar2(70),
    un_serial_number number(6),
    un_hazard_class_division varchar2(4),
    product_name varchar2(70),
    shipping_name varchar2(70),
    hazard_class varchar2(35),
    un_name_massage NUMBER(<length>,5),
    hazard_class_massage number(<length>,5),
    expire_date date,
    inactive_date date,
    fwk text 1 keyed
    date_of_entry date,
    date_of_chang date,
    user_of_change VARCHAR2(<length>)
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    create table dataset_explo
    (labe_report_no varchar(12),
    reference_number number(5),
    contact_title varchar2(4),
    contact_first_name varchar2(20),
    contact_last_name varchar2(20),
    contact_position varchar2(40),
    contact_company varchar2(100),
    contact_address varchar2(50),
    contact_city varchar2(25),
    contact_state varchar2(2),
    contact_zip number(10),
    contact_phone number(20),
    mfger varchar2(80),
    alternate_company_flag varchar2(1),
    additional_comment_flag varchar2(1),
    received_date date,
    un_revision_date date,
    revision_date date,
    dod_cc_no NUMBER(38,1),
    national_stock_number number(26),
    un_proper_shipping_name varchar2(70),
    un_serial_number number(6),
    un_hazard_class_division varchar2(4),
    product_name varchar2(70),
    shipping_name varchar2(70),
    hazard_class varchar2(35),
    un_name_massage NUMBER(38,5),
    hazard_class_massage number(38,5),
    expire_date date,
    inactive_date date,
    fwk varchar2(100),
    date_of_entry date,
    date_of_chang date,
    user_of_change VARCHAR2(100))

    I replaced <length> and <precision> in the number data types with 38, which is as high as it can be.
    I also made fwk varchar2(100).

    Cheers,
    Dan
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0
    my boss wants me to create a proc on tables which would automatically update the data every day . does anyone has any idea?
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0
    my boss wants me to create a proc on tables which would automatically update the data every day . does anyone has any idea?

IMN logo majestic logo threadwatch logo seochat tools logo