DEV Community

Cover image for Create and drop oracle table example
Adrian Matei for Codever

Posted on • Edited on • Originally published at codever.dev

Create and drop oracle table example

Let's create an example table in an existing Oracle schema, which holds data about bookmarks. Also add some comments as metadata to the table itself and to a column:

CREATE TABLE bookmark (
    id                 NUMBER(10, 0), -- number 10 digits before the decimal and 0 digits after the decimal
    title              VARCHAR2(255 CHAR) NOT NULL, -- String with a maximum length of 255 charachters
    url                VARCHAR2(500 CHAR) UNIQUE NOT NULL, -- holds unique values across the table data
    category      VARCHAR2(500 CHAR) NOT NULL, -- holds unique values across the table data
    is_public      NUMBER(1, 0) NOT NULL, -- plays the role of a boolean '0'-false, '1'-true ,
    created_at   DATE NOT NULL, --  when the entry is created
    PRIMARY KEY( id )
);

COMMENT ON TABLE bookmark IS
    'Table holding data about bookmarks';

COMMENT ON COLUMN bookmark.is_public IS
    'Boolean like 1-is public accessible, 0-is private';
Enter fullscreen mode Exit fullscreen mode

With the table now created, we can now insert values in it with the following syntax

INSERT INTO bookmark ( id, title, url, category, is_public, created_at )
VALUES (
    1,
    'BookmarksDev - Bookmarks and Code Snippets Manager',
    'https://www.bookmarks.dev',
    'developer-tools',
    1,
    TO_DATE( '2021-01-01', 'YYYY-MM-DD' )
);

INSERT INTO bookmark ( id, title, url, category, is_public, created_at )
VALUES (
    2,
    'CodepediaOrg - Share code knowledge',
    'https://www.codepedia.org',
    ' blog',
    1,
    SYSDATE -- current time in oracle
);

SELECT * FROM bookmark;
Enter fullscreen mode Exit fullscreen mode

To remove the table, all rows from the table, table indexes and domain indexes are removed with the following command

DROP TABLE bookmark;
Enter fullscreen mode Exit fullscreen mode

Shared with love from Codever. Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Top comments (1)

Collapse
 
andreasneuman profile image
andreasneuman

Thanks for the tips! There is also a handy way to copy table in Oracle database. It could be performed by executing Oracle SQL commands and also at the way the same procedures could be performed via an intuitive interface of dbForge Studio for Oracle.