1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

newbie sql help

Discussion in 'SQL PL/SQL' started by Jamie22, Oct 27, 2008.

  1. Jamie22

    Jamie22 Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    hi everyone, since i got such great help last time, i have anither query :)

    i am using the following sql code, to create a table in oracle, it is saying invalid datatype,

    Code (Text):

    create table epr_employee(emp_id varchar2(5), Password varchar(15), Firstname varchar2(30), Lastname varchar2(30), Surname varchar2(30), DOB date, DOJ date, Father's Name varchar2(60), role_id number(3), blood_group varchar2(4), present_addr varchar2(60), permanent_addr varchar2(60), phone_number number(15), mobile_number number(15), Mail_(per) varchar2(20), Mail_(off) varchar2(20), nominee varchar2(60), Id_card_number varchar2(20), aim_id varchar2(15), bond_expiry_date date, total_exp varchar2(10), Skill_set varchar2(30), last_working_date date, resume_name varchar2(20), SSN varchar2(12), bank_a/c_number number(15), pan varchar2(10), login_attempt_cnt number(1), joining_report varchar(1), passpqort_copy varchar(1), photos varchar(1), edu_certs varchar(1), app_form varchar(1), signed_nda varchar(1), signed_nda varchar(1), filled_pf varchar(1), status varchar(1));
     
    can someone please help me
     
    Jamie22, Oct 27, 2008
    #1
  2. Arju

    Arju Forum Expert

    Messages:
    161
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    You have many mistakes. It is difficult to interrupt if you don't format your query.

    Here is the result of your DDL ,
    Code (Text):
    SQL> create table epr_employee(emp_id varchar2(5), Password varchar(15), Firstname varchar2(30), Lastname varchar2(30), Surname varchar2(30)
    , DOB date, DOJ date, Father's Name varchar2(60), role_id number(3), blood_group varchar2(4), present_addr varchar2(60), permanent_addr varc
    har2(60), phone_number number(15), mobile_number number(15), Mail_(per) varchar2(20), Mail_(off) varchar2(20), nominee varchar2(60), Id_card
    _number varchar2(20), aim_id varchar2(15), bond_expiry_date date, total_exp varchar2(10), Skill_set varchar2(30), last_working_date date, re
    sume_name varchar2(20), SSN varchar2(12), bank_a/c_number number(15), pan varchar2(10), login_attempt_cnt number(1), joining_report varchar(
    1), passpqort_copy varchar(1), photos varchar(1), edu_certs varchar(1), app_form varchar(1), signed_nda varchar(1), signed_nda varchar(1), f
    illed_pf varchar(1), status varchar(1));


    create table epr_employee(emp_id varchar2(5), Password varchar(15), Firstname varchar2(30), Lastname varchar2(30), Surname varchar2(30), DOB
     date, DOJ date, Father's Name varchar2(60), role_id number(3), blood_group varchar2(4), present_addr varchar2(60), permanent_addr varchar2(
    60), phone_number number(15), mobile_number number(15), Mail_(per) varchar2(20), Mail_(off) varchar2(20), nominee varchar2(60), Id_card_numb
    er varchar2(20), aim_id varchar2(15), bond_expiry_date date, total_exp varchar2(10), Skill_set varchar2(30), last_working_date date, resume_
    name varchar2(20), SSN varchar2(12), bank_a/c_number number(15), pan varchar2(10), login_attempt_cnt number(1), joining_report varchar(1), p
    asspqort_copy varchar(1), photos varchar(1), edu_certs varchar(1), app_form varchar(1), signed_nda varchar(1), signed_nda varchar(1), filled
    _pf varchar(1), status varchar(1))

                           *
    ERROR at line 1:
    ORA-01756: quoted string not properly terminated
     
    Arju, Oct 27, 2008
    #2
  3. tyro

    tyro Forum Genius

    Messages:
    408
    Likes Received:
    21
    Trophy Points:
    260
    Location:
    India
    yoh had several errors, you cannot use quote in a column name nor slashes (you used in a/c name). However here's the corrected code

    Code (Text):

    /* Formatted on 2008/10/27 20:38 (Formatter Plus v4.8.8) */
    CREATE TABLE epr_employee
        (   emp_id VARCHAR2(5),
            PASSWORD VARCHAR(15),
            firstname VARCHAR2(30),
            lastname VARCHAR2(30),
            surname VARCHAR2(30),
            dob DATE,
            doj DATE,
            fathers_name VARCHAR2(60),
            role_id NUMBER(3),
            blood_group VARCHAR2(4),
            present_addr VARCHAR2(60),
            permanent_addr VARCHAR2(60),
            phone_number NUMBER(15),
            mobile_number NUMBER(15),
            mail_per VARCHAR2(20),
            mail_off VARCHAR2(20),
            nominee VARCHAR2(60),
            id_card_number VARCHAR2(20),
            aim_id VARCHAR2(15),
            bond_expiry_date DATE,
            total_exp VARCHAR2(10),
            skill_set VARCHAR2(30),
            last_working_date DATE,
            resume_name VARCHAR2(20),
            ssn VARCHAR2(12),
            bank_ac_number NUMBER(15),
            pan VARCHAR2(10),
            login_attempt_cnt NUMBER(1),
            joining_report VARCHAR(1),
            passpqort_copy VARCHAR(1),
            photos VARCHAR(1),
            edu_certs VARCHAR(1),
            app_form VARCHAR(1),
            signed_nda VARCHAR(1),
            filled_pf VARCHAR(1),
            status VARCHAR(1)
       );
     
     
    tyro, Oct 27, 2008
    #3
  4. Arju

    Arju Forum Expert

    Messages:
    161
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Some analysis here,
    Code (Text):
    SQL> create table epr_employee(bank_a/c_number number(15));
    create table epr_employee(bank_a/c_number number(15))
                                    *
    ERROR at line 1:
    ORA-00902: invalid datatype
     
    So your datatype name as bank_a/c_number is not valid.

    Code (Text):
    SQL> create table epr_employee(Mail_(per) varchar2(20), Mail_(off) varchar2(20));
    create table epr_employee(Mail_(per) varchar2(20), Mail_(off) varchar2(20))
                                   *
    ERROR at line 1:
    ORA-00902: invalid datatype
    SO naming as epr_employee(Mail_(per) in not valid.

    Do it as,
    Code (Text):
    SQL> create table epr_employee(Mail_per varchar2(20), Mail_off varchar2(20));

    Table created.
    By omitting bracket.
     
    Arju, Oct 27, 2008
    #4
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,659
    Likes Received:
    375
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Many mistakes are found in this code:
    Code (Text):

      1  create table epr_employee
      2  (emp_id varchar2(5),
      3   Password varchar(15),
      4   Firstname varchar2(30),
      5   Lastname varchar2(30),
      6   Surname varchar2(30),
      7   DOB date,
      8   DOJ date,
      9   Father's Name varchar2(60),
     10   role_id number(3),
     11   blood_group varchar2(4),
     12   present_addr varchar2(60),
     13   permanent_addr varchar2(60),
     14   phone_number number(15),
     15   mobile_number number(15),
     16   Mail_(per) varchar2(20),
     17   Mail_(off) varchar2(20),
     18   nominee varchar2(60),
     19   Id_card_number varchar2(20),
     20   aim_id varchar2(15),
     21   bond_expiry_date date,
     22   total_exp varchar2(10),
     23   Skill_set varchar2(30),
     24   last_working_date date,
     25   resume_name varchar2(20),
     26   SSN varchar2(12),
     27   bank_a/c_number number(15),
     28   pan varchar2(10),
     29   login_attempt_cnt number(1),
     30   joining_report varchar(1),
     31   passpqort_copy varchar(1),
     32   photos varchar(1),
     33   edu_certs varchar(1),
     34   app_form varchar(1),
     35   signed_nda varchar(1),
     36   signed_nda varchar(1),
     37   filled_pf varchar(1),
     38*  status varchar(1))
    SQL> /
     Father's Name varchar2(60),
           *
    ERROR at line 9:
    ORA-01756: quoted string not properly terminated
     
    Without using " around such names they are not legal. Again there are illegal column naming conventions in use:

    Code (Text):

      1  create table epr_employee
      2  (emp_id varchar2(5),
      3   Password varchar(15),
      4   Firstname varchar2(30),
      5   Lastname varchar2(30),
      6   Surname varchar2(30),
      7   DOB date,
      8   DOJ date,
      9   Fathers_Name varchar2(60),
     10   role_id number(3),
     11   blood_group varchar2(4),
     12   present_addr varchar2(60),
     13   permanent_addr varchar2(60),
     14   phone_number number(15),
     15   mobile_number number(15),
     16   Mail_(per) varchar2(20),
     17   Mail_(off) varchar2(20),
     18   nominee varchar2(60),
     19   Id_card_number varchar2(20),
     20   aim_id varchar2(15),
     21   bond_expiry_date date,
     22   total_exp varchar2(10),
     23   Skill_set varchar2(30),
     24   last_working_date date,
     25   resume_name varchar2(20),
     26   SSN varchar2(12),
     27   bank_a/c_number number(15),
     28   pan varchar2(10),
     29   login_attempt_cnt number(1),
     30   joining_report varchar(1),
     31   passpqort_copy varchar(1),
     32   photos varchar(1),
     33   edu_certs varchar(1),
     34   app_form varchar(1),
     35   signed_nda varchar(1),
     36   signed_nda varchar(1),
     37   filled_pf varchar(1),
     38*  status varchar(1))
    SQL>
    SQL> /
     Mail_(per) varchar2(20),
          *
    ERROR at line 16:
    ORA-00902: invalid datatype
     
    () are not allowed in column names. And:
    Code (Text):

      1  create table epr_employee
      2  (emp_id varchar2(5),
      3   Password varchar(15),
      4   Firstname varchar2(30),
      5   Lastname varchar2(30),
      6   Surname varchar2(30),
      7   DOB date,
      8   DOJ date,
      9   Fathers_Name varchar2(60),
     10   role_id number(3),
     11   blood_group varchar2(4),
     12   present_addr varchar2(60),
     13   permanent_addr varchar2(60),
     14   phone_number number(15),
     15   mobile_number number(15),
     16   Mail_per varchar2(20),
     17   Mail_off varchar2(20),
     18   nominee varchar2(60),
     19   Id_card_number varchar2(20),
     20   aim_id varchar2(15),
     21   bond_expiry_date date,
     22   total_exp varchar2(10),
     23   Skill_set varchar2(30),
     24   last_working_date date,
     25   resume_name varchar2(20),
     26   SSN varchar2(12),
     27   bank_a/c_number number(15),
     28   pan varchar2(10),
     29   login_attempt_cnt number(1),
     30   joining_report varchar(1),
     31   passpqort_copy varchar(1),
     32   photos varchar(1),
     33   edu_certs varchar(1),
     34   app_form varchar(1),
     35   signed_nda varchar(1),
     36   signed_nda varchar(1),
     37   filled_pf varchar(1),
     38*  status varchar(1))
    SQL> /
     bank_a/c_number number(15),
           *
    ERROR at line 27:
    ORA-00902: invalid datatype
     
    creates a problem with another 'illegal' character. Correcting that still leaves:
    Code (Text):

      1  create table epr_employee
      2  (emp_id varchar2(5),
      3   Password varchar(15),
      4   Firstname varchar2(30),
      5   Lastname varchar2(30),
      6   Surname varchar2(30),
      7   DOB date,
      8   DOJ date,
      9   Fathers_Name varchar2(60),
     10   role_id number(3),
     11   blood_group varchar2(4),
     12   present_addr varchar2(60),
     13   permanent_addr varchar2(60),
     14   phone_number number(15),
     15   mobile_number number(15),
     16   Mail_personal varchar2(20),
     17   Mail_office varchar2(20),
     18   nominee varchar2(60),
     19   Id_card_number varchar2(20),
     20   aim_id varchar2(15),
     21   bond_expiry_date date,
     22   total_exp varchar2(10),
     23   Skill_set varchar2(30),
     24   last_working_date date,
     25   resume_name varchar2(20),
     26   SSN varchar2(12),
     27   bank_act_number number(15),
     28   pan varchar2(10),
     29   login_attempt_cnt number(1),
     30   joining_report varchar(1),
     31   passpqort_copy varchar(1),
     32   photos varchar(1),
     33   edu_certs varchar(1),
     34   app_form varchar(1),
     35   signed_nda varchar(1),
     36   signed_nda varchar(1),
     37   filled_pf varchar(1),
     38*  status varchar(1))
    SQL> /
     signed_nda varchar(1),
     *
    ERROR at line 36:
    ORA-00957: duplicate column name
     
    Correcting THAT allows the table to be created:
    Code (Text):

      1  create table epr_employee
      2  (emp_id varchar2(5),
      3   Password varchar(15),
      4   Firstname varchar2(30),
      5   Lastname varchar2(30),
      6   Surname varchar2(30),
      7   DOB date,
      8   DOJ date,
      9   Fathers_Name varchar2(60),
     10   role_id number(3),
     11   blood_group varchar2(4),
     12   present_addr varchar2(60),
     13   permanent_addr varchar2(60),
     14   phone_number number(15),
     15   mobile_number number(15),
     16   Mail_personal varchar2(20),
     17   Mail_office varchar2(20),
     18   nominee varchar2(60),
     19   Id_card_number varchar2(20),
     20   aim_id varchar2(15),
     21   bond_expiry_date date,
     22   total_exp varchar2(10),
     23   Skill_set varchar2(30),
     24   last_working_date date,
     25   resume_name varchar2(20),
     26   SSN varchar2(12),
     27   bank_act_number number(15),
     28   pan varchar2(10),
     29   login_attempt_cnt number(1),
     30   joining_report varchar(1),
     31   passpqort_copy varchar(1),
     32   photos varchar(1),
     33   edu_certs varchar(1),
     34   app_form varchar(1),
     35   signed_nda varchar(1),
     36   filled_pf varchar(1),
     37*  status varchar(1))
    SQL> /

    Table created.
     
    But this table contains too much information for an employee table; it should be divided into three tables as follows:
    Code (Text):

    create table epr_employee
    (emp_id varchar2(5),
     Firstname varchar2(30),
     Lastname varchar2(30),
     SSN varchar2(12),
     signed_nda varchar(1),
     status varchar(1))
    /
     
    and
    Code (Text):

    create table epr_employee_details
    (emp_id varchar2(5),
     DOB date,
     DOJ date,
     Fathers_Name varchar2(60),
     blood_group varchar2(4),
     present_addr varchar2(60),
     permanent_addr varchar2(60),
     phone_number number(15),
     mobile_number number(15),
     Mail_personal varchar2(20),
     Mail_office varchar2(20),
     nominee varchar2(60),
     Id_card_number varchar2(20),
     aim_id varchar2(15),
     bond_expiry_date date,
     total_exp varchar2(10),
     Skill_set varchar2(30),
     last_working_date date,
     resume_name varchar2(20),
     bank_act_number number(15),
     pan varchar2(10),
     joining_report varchar(1),
     passpqort_copy varchar(1),
     photos varchar(1),
     edu_certs varchar(1),
     app_form varchar(1),
     filled_pf varchar(1))
    /
     
    and
    Code (Text):

    create table epr_employee_logon
    (emp_id varchar2(5),
     Password varchar(15),
     role_id number(3),
     login_attempt_cnt number(1))
    /
     
    You should not include logon information in a basic employee table, nor should you include any non-account-related details (family names, certifications, passport information and the like belong in a separate table, as shown).
     
    zargon, Oct 27, 2008
    #5
  6. Arju

    Arju Forum Expert

    Messages:
    161
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Here is more test.

    Note that both ( and / can be used within quotes. But quote itself can't be used.
    Code (Text):

    SQL> create table epr_employee1("Mail_(per)" varchar2(20), "Mail_(off)" varchar2(20));

    Table created.

    SQL> create table epr_employee2("Mail_/(per)" varchar2(20), "Mail_(off)" varchar2(20));

    Table created.

    SQL> create table epr_employee3("Mail_\(per)" varchar2(20), "Mail_(off)" varchar2(20));

    Table created.

    SQL> create table epr_employee4("Mail_\ (per)" varchar2(20), "Mail_(off)" varchar2(20));

    Table created.

    SQL> create table epr_employee5("Mail_\" (per)" varchar2(20), "Mail_(off)" varchar2(20));
    create table epr_employee5("Mail_\" (per)" varchar2(20), "Mail_(off)" varchar2(20))
                                        *
    ERROR at line 1:
    ORA-00902: invalid datatype
    I once tried to list all these in the post http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
     
    Arju, Oct 27, 2008
    #6
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,659
    Likes Received:
    375
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Simply because such an act is possible doesn't make it a wise idea; creating columns named in such a manner also requires those column names be typed exactly as created, including case, within double quotation marks, and many applications won't create such constructs.

    More thought needs to be taken when designing tables so that such non-standard column naming is avoided.
     
    zargon, Oct 27, 2008
    #7
  8. Jamie22

    Jamie22 Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    hi everyone

    yes it was my mistake i should have formatted properly before posting
    but thank u for correcting the code. i was initially experimenting but thanks to zargon i now have 3 tables

    1 emp table
    1 emp_details table
    1 emp_logon table just as zargon suggested
    also i simply got rid of the quote and /
     
    Jamie22, Oct 27, 2008
    #8