To use a PL/SQL cursor we set up a scenario where we want to update values from a main table and also add auditing data about it. We create some tables in oracle database to hold data about bookmarks and revision info data (bookmarks auditing table, and a revision info table):

  • BOOKMARK - to hold data about bookmarks
  • REVINFO - holds data about the revisions that took place in our system
  • BOOKMARK_AUD - audit table, to hold data from the main table (BOOKMARK) that were affected by the different revisions

The concept from the scenarios is inspired from the Hibernate Envers project, where you would the same functionality in java, automatically, with annotations. There might be cases where you need to this at the SQL level, maybe for some single time house keeping jobs or similar…

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 )
);


-- revision info table
CREATE TABLE revinfo
(
    id NUMBER(10, 0) NOT NULL,
    revtstmp NUMBER(10, 0), -- unix time in milliseconds
    PRIMARY KEY( id )
)

-- audit table for BOOKMARK table
CREATE TABLE bookmark_aud (
    id                 NUMBER(10, 0),
    rev NUMBER(10, 0) NOT NULL, -- the id of the revision
    revtype NUMBER(1, 0), -- 0=CREATE, 1=MODIFY and 2=DELETE
    title              VARCHAR2(255 CHAR) NOT NULL,
    url                VARCHAR2(500 CHAR) UNIQUE NOT NULL,
    category      VARCHAR2(500 CHAR) NOT NULL,
    is_public      NUMBER(1, 0) NOT NULL,
    created_at   DATE NOT NULL,
    PRIMARY KEY( id )
);

More exactly, we want to update all the entries from the bookmark table that have a given category (v_category in plsql code), with a new one (v_new_category in the plsql script). We select the values afect in a cursor (bookmark_cur in plsql script), which we iterate over in a plsql FOR LOOP to do the individual updates and inserts. If any exception occurs in the loop it will be printed in the DBMS_OUTPUT:

SET SERVEROUTPUT ON;
DECLARE
    v_category VARCHAR2(500) := 'javax';
    v_new_category VARCHAR2(500) := 'java';
    next_rev_id NUMBER := HIBERNATE_SEQUENCE.nextval;
    created_at NUMBER(19, 0);
    CURSOR bookmark_cur IS
        SELECT
            *
        FROM
            bookmark
        WHERE
          category = v_category
       );
BEGIN
    -- get current timestamp as number
    SELECT
                EXTRACT(DAY FROM(sys_extract_utc(systimestamp) - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000
            + to_number(TO_CHAR(sys_extract_utc(systimestamp), 'SSSSSFF3'))
    INTO created_at
    FROM
        dual;

    -- update REVISION table
    INSERT
    INTO revinfo (
               rev,
               revtstmp
    )
    VALUES (
       next_rev_id,
       created_at
    );

    FOR c IN bookmark_cur LOOP
        BEGIN
            -- update BOOKMARK table
            UPDATE bookmark
            SET
                category = v_new_category
            WHERE
                 id = c.id;

            -- INSERT ENTRY into BOOKMARK_AUD table
            INSERT INTO bookmark_aud (
               id
               title,
               url,
               category,
               is_public,
               created_at,
               rev,
               revtype
            ) VALUES (
               c.id
               c.title,
               c.url,
               c.category,
               c.is_public,
               c. created_at,
               next_rev_id,
               1 -- 1=UPDATE/MODIFY (0=INSERT and 2=DELETE)
           );
        EXCEPTION
            WHEN OTHERS THEN --handle all exceptions
                DBMS_OUTPUT.PUT_LINE('Exception' || SQLCODE || ' captured for bookmark with id ' || c.id);
        END;
    END LOOP;
END;
/

But beware, it is not recommended using a cursor FOR loop if the body executes non-query DML (INSERT, UPDATE, DELETE, MERGE), because the INSERT or UPDATE will happen on a row-by-row basis1.

In this particular case you can ditch the cursor and use use plain SQL updates and inserts in the plsql body, as shown in the snippet below:

BEGIN
    -- get current timestamp as number
    SELECT
                EXTRACT(DAY FROM(sys_extract_utc(systimestamp) - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000
            + to_number(TO_CHAR(sys_extract_utc(systimestamp), 'SSSSSFF3'))
    INTO created_at
    FROM
        dual;

    -- update REVISION table
    INSERT
        INTO revinfo (
           rev,
           revtstmp
        )
        VALUES (
           next_rev_id,
           created_at
        );


    -- update BOOKMARK table
    UPDATE bookmark
    SET
        category = v_new_category
    WHERE
        category = v_category; --

    -- INSERT ENTRY into BOOKMARK_AUD table
    INSERT INTO bookmark_aud (
        id
        title,
        url,
        category,
        is_public,
        created_at,
        rev,
        revtype
    )
    SELECT
        c.id
        c.title,
        c.url,
        c.category,
        c.is_public,
        c. created_at,
        next_rev_id,
        1 -- 1=UPDATE/MODIFY (0=INSERT and 2=DELETE)
    FROM bookmark c
    WHERE
        c.id in (
           SELECT
               id
           FROM
                bookmark
           WHERE
             category = v_category
        )
    );
    EXCEPTION
        WHEN OTHERS THEN --handle all exceptions
              DBMS_OUTPUT.PUT_LINE('Exception' || SQLCODE || ' captured for bookmark with id ' || c.id);
END;
/

Note some times is not possible and then you might have to stick to the cursor. In oracle you can take advantage of the CURSOR bulk updates and INSERTS2

References

Let’s say we have the following bookmark table and we want to add an unique constraint on the url column:

CREATE TABLE bookmark (
    id          NUMBER(10, 0),
    url         VARCHAR2(500 CHAR) ,
    PRIMARY KEY ( id )
);

INSERT INTO bookmark ( id, title, url, is_public) VALUES (1, 'https://www.bookmarks.dev');
INSERT INTO bookmark ( id, title, url, is_public) VALUES (2, 'https://www.codepedia.org');
INSERT INTO bookmark ( id, title, url, is_public) VALUES (3, 'https://www.bookmarks.dev');

Normally you would use the following ALTER TABLE command to add a unique constraint on the url column

ALTER TABLE bookmark ADD CONSTRAINT unique_url UNIQUE (url);

-- but you get an ORA-02299 saying the "unique_url" cannot be validated, because the table has duplicate key values

In this case and if you have existing data, that might not be unique you will get an ORA: error, if you execute the command before. However, you can overcome this if you use one of the following:

-- tell oracle not to validate the existing data at the time of the creation of the uniqueconstraint
ALTER TABLE bookmark ADD CONSTRAINT unique_url UNIQUE (url) DEFERRABLE NOVALIDATE;

-- create the unique constraint "disabled" and "enable" after that with the "NOVALIDATE" option
ALTER TABLE bookmark ADD CONSTRAINT unique_url UNIQUE (url) DISABLE;
ALTER TABLE bookmark ENABLE NOVALIDATE CONSTRAINT unique_url;

To see the created constraint and its attributes use the following command(s):

-- show all constraint for the table
SELECT * FROM user_constraints WHERE table_name = 'BOOKMARK'; -- note upper case

-- show our created unique constraint
SELECT * FROM user_constraints WHERE table_name = 'BOOKMARK'
   AND CONSTRAINT_NAME ='UNIQUE_URL';  -- note also here the upper case

To drop the unique constraint use the following command:

ALTER TABLE bookmark DROP CONSTRAINT unique_url;

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.

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.

if is a control flow tag, that can change the information Liquid shows using programming logic:


{% if page.title %}
  {% if page.jsonld %}
    {% include {{page.jsonld}}.html %}
  {% else %}
    {% include postJSONLD.html %}
  {% endif %}
{% else %}
  {% include homeJSONLD.html %}
{% endif %}

Reference - https://shopify.github.io/liquid/tags/control-flow/


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.

Use the page.categories variable and select index 0 of the array:


  {% if page.categories[0] != "snippets" %}
    {% include promote-bookmarks.dev.html %}
  {% endif %}

Reference - https://jekyllrb.com/docs/variables/


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.