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!

Oracle equivalent data types

Discussion in 'SQL PL/SQL' started by dsub42, Apr 1, 2010.

  1. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Could anyone help me as to what the oracle equivalent would be to the following data types:

    Auto-incrementing
    String{},
    Boolean
    Real
    Text memo
    Integer(16bit)
    integer(32bit)

    Any help would be appriciated
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Oracle has no auto-incrementing data type; the functionality is provided by a sequence and a trigger:

    Code (SQL):
    CREATE SEQUENCE identity_seq
    START WITH 1 INCREMENT BY 1 nocycle nocache nomaxvalue;

    CREATE OR REPLACE TRIGGER pop_ident
    BEFORE INSERT ON mytable
    FOR each ROW
    BEGIN
         SELECT identity_seq.NEXTVAL
         INTO :NEW.id
        FROM dual;
    END;
    /

     
    String() is a VARCHAR2 field.
    Oracle has no BOOLEAN datatype outside of PL/SQL; often this is implemented with a NUMBER(1) declaration.
    Real is a NUMBER(38,19) or similar declaration.
    Text memoi used to be s LONG, however it could be a VARCHAR2(4000) or CLOB.
    Integer, both 16 and 32 bit, would be NUMBER.
     
  3. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Thanks,

    If I import a data set into a table which uses StaffID as a primary key.... this in the origional table being set to Auto increment, how will this effect the sequence / trigger on an empty table that im moving the data across into , considering that the autonumber is currently on "912"

    Cheers
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The smart move is to import your data into this empty table, find the maximum StaffID value and create your sequence accordingly:

    Code (SQL):
    CREATE SEQUENCE myseq
    START WITH 913 INCREMENT BY 1 nomaxvalue nocycle nocache;
     
    After the sequence is created you then create the trigger. You should then have no issues from the previously loaded data.
     
  5. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Will this still work if I want this field to be the primary key for the table?

    i.e using a trigger?

    also could you tell me the difference between 16bit integers and 32 bit integers orcle equivelants?

    thanks
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, the trigger uses the non-repeating, non-terminating sequence to generate values, very much like your IDENTITY column. A PK definition won't be hampered by this mechanism.

    NUMBER is, absent any further definition, is a NUMBER(38), meaning a 38-digit number wihch can be an integer or floating point value:

    Code (SQL):
    SQL>CREATE TABLE numtest(tstnum NUMBER);

    TABLE created.

    SQL>
    SQL>INSERT ALL
      2  INTO numtest
      3  VALUES(99999999999999999999999999999999999999)
      4  INTO numtest
      5  VALUES(9.9999999999999999999999999999999)
      6  INTO numtest
      7  VALUES(99999999999999999999.99999999999)
      8  INTO numtest
      9  VALUES(999999999.99999999999999999999999)
     10  INTO numtest
     11  VALUES(9999999999999999999999999999999.9)
     12  INTO numtest
     13  VALUES(.99999999999999999999999999999999)
     14  INTO numtest
     15  VALUES(9999999999999999)
     16  INTO numtest
     17  VALUES(99999999999999999999999999999999)
     18  SELECT * FROM dual;

    8 ROWS created.

    SQL>
    SQL>SET numwidth 38
    SQL>
    SQL>SELECT * FROM numtest;

                                    TSTNUM
    --------------------------------------
    99999999999999999999999999999999999999
         9.9999999999999999999999999999999
          99999999999999999999.99999999999
         999999999.99999999999999999999999
         9999999999999999999999999999999.9
         .99999999999999999999999999999999
                          9999999999999999
          99999999999999999999999999999999

    8 ROWS selected.

    SQL>
    Since you can store both 16-bit and 32-bit integers there is no difference.
     
  7. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    O.K thanks, do you think the following sounds like a good idea?...

    Im going to move the data from an old table into an oracle table, so my plan was to export as a csv, and then just import the csv into a temporary table in oracle (all field types as varchar or something)..

    then I will move the data from the temp table in oracle to the table i want it in (with correct field types) and on the way I will format the mismatches in data types: eg..

    INSERT INTO PERM_TBL SELECT COL1, COL2, to_date(col3,'DD/MM/YYYY'), COL4 FROM TEMP_TBL

    but.... lets say that COL1 above is just a number field, which was origionally the autonumber... so i just move the data across as it is into a number(x) field as the primary key and then once the data has been moved over add the trigger to it which then would hopefully use the next num in the sequaence as a primary key for new records??

    does that make sense?
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It makes sense in a way, however I would not use a temporary table declared as all varchar2 as your first stop into the Oracle database; I would declare your datatypes as they should be and make your initial load, using SQL*Loader, into the proper data types. You can then modify your control file to address any loading issues you experience. Once you have your data loaded you can rename the temp table to your production name then build any necessary indexes, your sequence with a proper starting point and your trigger.
     
  9. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    ok thanks, can you give me an example of how to modify the control file or point me in the direction of any tutorials etc onlne?
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    How were you planning on loading these tables from a flat file if not with sql*loader? The utility is fairly user-friendly as it creates a .bad file (containing all records which could not be loaded) as well as a .log file (indicating in a fair amount of detail what succeeded and what did not, and why). Using the log and bad files it should be a simple task to modify the control file to adjust datatypes and lengths to allow you to successfully load the data. The online documentation can be found here:

    http://tahiti.oracle.com

    where you can select the version of Oracle you're usnig and search for "SQL*Loader" to find relevant informatoin. You can get started on this utility here

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_ldr.htm#g996849

    which has documentation, instructions and examples to assist you.