DEV Community

loading...
Cover image for Create and drop oracle table example
Bookmarks.dev

Create and drop oracle table example

ama profile image Adrian Matei Originally published at bookmarks.dev ・2 min read

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 Bookmarks.dev. Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Discussion (0)

pic
Editor guide