Create and drop oracle table example

Create an example table in an existing Oracle schema, which holds data about bookmarks. Add some comments as metadata to the table 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';

With the table now created, 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;

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

DROP TABLE bookmark;

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

Subscribe to our newsletter for more code resources and news

Adrian Matei

Adrian Matei
Life force expressing itself as a coding capable human being

Mongo create full text index example

Code snippets showing how to create a mongo create full text index and how to search for results with its help. Continue reading

Mongo full text search example

Published on February 07, 2021

Mongoose query pagination example

Published on February 05, 2021