Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: Oracle Datatypes

    Hello everybody

    I have a question about Oracle Datatypes:
    How AutoIncrement, Boolean and some datatypes like thus (in another DBMS) will be in Oracle...?

    Note: I`m beginner...

    Thanx before

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle Datatypes

    These datatypes don't exist in Oracle.

    1) For AutoIncrement the Oracle solution is a NUMBER column together with a SEQUENCE object to provide the values for it like this:

    insert into t1 ( id, name ) values ( seq1.nextval, 'xxx' );

    You can use a database trigger to populate the ID column automatically so that the insert statement can be written like this:

    insert into t1 ( name ) values ( 'xxx' );

    2) For Boolean you can choose either NUMBER(1) or VARCHAR2(1), and add a check constraint to force values to be 1/0, Y/N or whatever convention you choose to adopt (Y/N is popular). I think the lack of support for Boolean in Oracle is regrettable.

    What other types did you have in mind?

  3. #3
    Join Date
    Jan 2004
    Posts
    5

    Re: Oracle Datatypes

    Originally posted by andrewst
    These datatypes don't exist in Oracle.

    1) For AutoIncrement the Oracle solution is a NUMBER column together with a SEQUENCE object to provide the values for it like this:

    insert into t1 ( id, name ) values ( seq1.nextval, 'xxx' );

    You can use a database trigger to populate the ID column automatically so that the insert statement can be written like this:

    insert into t1 ( name ) values ( 'xxx' );

    2) For Boolean you can choose either NUMBER(1) or VARCHAR2(1), and add a check constraint to force values to be 1/0, Y/N or whatever convention you choose to adopt (Y/N is popular). I think the lack of support for Boolean in Oracle is regrettable.

    What other types did you have in mind?
    Plz, could u explain a little bit a SEQUENCE? I didn`t find it in Oracle9i. Is it a command or SQL inquery?

    And one question more: Someone said (and I saw too) that Oracle9i has BOOLEAN type in contradistinction to lower versions of Oracle... What can u say about it...

    And thanx for everything...

    Sincerely Ahad A. Mammadov/ Azerbaijan Republic

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle Datatypes

    1) A SEQUENCE is an object that you create in the server like this:

    CREATE SEQUENCE my_sequence;

    By default, its values start from 1 and increment by 1, but you can specify otherwise when you create it. You can get the next value from the sequence using the expression my_sequence.NEXTVAL in a SQL statement like this:

    select my_sequence.NEXTVAL from dual;

    insert into t ( id ) values (my_sequence.NEXTVAL);

    NB Sequence values are guaranteed to be unique, but are not guaranteed to be contiguous - i.e. there may be (will be) gaps. That is the small price you have to pay for sequences being "scalable", i.e. they can be used in a multi-user environment without degrading performance.

    2) Contrary to what you have been told, Oracle 9i does not support the Boolean data type as a column data type. You can declare Boolean variables in PL/SQL programs, but you cannot store Boolean values in the database. Maybe 10G has Boolean, I don't know!

  5. #5
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Oracle Datatypes

    Here you have all details about sequences( creation, use, examples ):

    CREATE SEQUENCE
    Purpose
    Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

    When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, the sequence numbers each user acquires may have gaps because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

    Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

    Once a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn (which returns the current value of the sequence) or the NEXTVAL pseudocolumn (which increments the sequence and returns the new value).

    http://download-west.oracle.com/docs...htm#SQLRF01314
    Joel Pérez

  6. #6
    Join Date
    Jan 2004
    Posts
    5

    Hi again

    Thanx for all, i`ve tested sequinece and trigger, but there has appeared another problem:
    it automatically gives ID numbers, but it ordering by his own ROWID, that is every id`s ROWID is like this:
    [i got it during deleting rows from down to up one by one]
    DELETE FROM "AHAD"."PROGS"
    WHERE rowid = 'AAAHb3AABAAAMjqAAC' / id=1
    DELETE FROM "AHAD"."PROGS"
    WHERE rowid = 'AAAHb3AABAAAMjqAAB' / id=3
    DELETE FROM "AHAD"."PROGS"
    WHERE rowid = 'AAAHb3AABAAAMjqAAA' / id=2

    => ID1='AAAHb3AABAAAMjqAAC'
    => ID2='AAAHb3AABAAAMjqAAA'
    => ID3='AAAHb3AABAAAMjqAAB'
    but it ordered by its' rowid value (look above)

    What to do? I`ve tested to give ID column UNIQUE and PRIMARY values, but : same problem

    Thanx before
    (if it was Hard understandeble, sorry before)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •