Setting up

Prepare Environment

1. Enable Oracle Autonomous Database for Local Development

2. Enable Oracle Autonomous Database in Oracle Cloud Infrastructure

3. Download and install SQLcl for Local Development

TIP

MCP server for enabling agents to talk in native language with database can be enabled by adding to VSCode settings:

   "modelContextProtocol": {
       "servers": {
           "sqlcl": {
           "command": "sqlcl",
           "args": ["--mcp"]
           }
      }
   },

SQLCl Project Deployments

SQLcl Project Deploy executes install scripts that run before and after changeset deployment.

./db/dist/install.sql

source

set define on
set verify off
set feedback off
set serveroutput on
set sqlblanklines on

VARIABLE version VARCHAR2(128 CHAR)
VARIABLE config CLOB
VARIABLE schema VARCHAR2(200 CHAR)
VARIABLE edition VARCHAR2(200 CHAR)

EXEC :version := '&VERSION';
EXEC :config := '&CONFIG';
EXEC :schema := '&SCHEMA';
EXEC :edition := '&EDITION';

PROMPT "-variables"
PROMPT "  - version: " || '&VERSION'
exec begin dbms_output.put_line('  - app config: ' || SUBSTR(:config, 1, 10) || '...'); end;
PROMPT "  - schema: " || '&SCHEMA'
PROMPT "  - edition: " || '&EDITION'
PROMPT ""

@@000_install.sql

PROMPT ""

lb update -log -changelog-file releases/main.changelog.xml -search-path "."

PROMPT ""

@@999_install.sql

PROMPT ""

UNDEFINE version
UNDEFINE config
UNDEFINE schema
UNDEFINE edition

./db/dist/000_install.sql

source

DECLARE
    c CLOB := :config;

    v_schema_username VARCHAR2(200 CHAR) := :schema;
    v_schema_password VARCHAR2(200 CHAR);

    v_edition VARCHAR2(200 CHAR) := :edition;

    v_exists PLS_INTEGER;
BEGIN

    -- PARAMETERS

    v_schema_password := JSON_VALUE(c, '$.schema.password');

    IF v_schema_username IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Schema username not defined in config.');
    END IF;

    -- SCHEMA

    SELECT COUNT(*) 
    INTO v_exists 
    FROM dba_users 
    WHERE username = UPPER(v_schema_username);
    
    DBMS_OUTPUT.PUT_LINE('- creating schema: ' || v_schema_username);
    IF v_exists = 0 THEN
        EXECUTE IMMEDIATE '
        CREATE USER ' || v_schema_username || ' 
        IDENTIFIED BY "' || v_schema_password || '"
        DEFAULT TABLESPACE DATA
        TEMPORARY TABLESPACE TEMP
        QUOTA UNLIMITED ON DATA';
        DBMS_OUTPUT.PUT_LINE('  - schema created.');
    ELSE
        BEGIN
            EXECUTE IMMEDIATE 'ALTER USER ' || v_schema_username || ' IDENTIFIED BY "' || v_schema_password || '"';
            DBMS_OUTPUT.PUT_LINE('  - schema already exists.');
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE = -28007 THEN
                    DBMS_OUTPUT.PUT_LINE('  - password cannot be reused. Skipping password update.');
                ELSE
                    RAISE;
                END IF;
        END;
    END IF;

    -- GRANTS

    DBMS_OUTPUT.PUT_LINE('- granting privileges.');
    FOR grants IN (
        SELECT privilege 
        FROM JSON_TABLE(
            c,
            '$.schema.grants[*]' 
            COLUMNS (
                privilege VARCHAR2(200 CHAR) PATH '$'
            )
        )
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE '
                GRANT ' || grants.privilege || ' 
                TO ' || v_schema_username;
            DBMS_OUTPUT.PUT_LINE('  - privilege granted: ' || grants.privilege);
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE != -01918 THEN -- ignore "user/role does not exist"
                    NULL;
                ELSE
                    RAISE;    
                END IF;
        END;
    END LOOP;

    -- EDITION

    EXECUTE IMMEDIATE 'ALTER USER ' || v_schema_username || ' ENABLE EDITIONS';  
    BEGIN
        DBMS_OUTPUT.PUT_LINE('- creating edition: ' || v_edition);
        EXECUTE IMMEDIATE '
            CREATE EDITION ' || v_edition || ' 
            ';
        EXECUTE IMMEDIATE 'GRANT USE ON EDITION ' || v_edition || ' TO ' || v_schema_username;
        DBMS_OUTPUT.PUT_LINE('  - edition created.');
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE = -955 THEN
                DBMS_OUTPUT.PUT_LINE('  - edition already exists.');
            ELSE
                DBMS_OUTPUT.PUT_LINE('  - edition not created.');
                RAISE;
            END IF;
    END;

    -- RESOURCE PRINCIPAL

    DBMS_OUTPUT.PUT_LINE('- enabling resource principal.');
    DBMS_OUTPUT.PUT_LINE('- debug: ' || JSON_VALUE(c, '$.enable_resource_principal'));
    BEGIN
        DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(v_schema_username);
        DBMS_OUTPUT.PUT_LINE('  - resource principal enabled.');
    EXCEPTION 
        WHEN OTHERS THEN 
            IF SQLCODE = -44002 THEN
                DBMS_OUTPUT.PUT_LINE('  - resource principal already enabled.');
            ELSE
                DBMS_OUTPUT.PUT_LINE('  - resource principal not enabled.');
            END IF;  
    END;

    -- ACL

    DBMS_OUTPUT.PUT_LINE('- configuring ACL.');
    FOR ace IN (
        SELECT 
            jt.host AS host,
            jt.lower_port AS lower_port,
            jt.upper_port AS upper_port,
            jt.privilege AS privilege
        FROM JSON_TABLE(
            c,
            '$.acl[*]' 
            COLUMNS (
                host VARCHAR2(200 CHAR) PATH '$.host',
                lower_port NUMBER PATH '$.lower_port',
                upper_port NUMBER PATH '$.upper_port',
                privilege VARCHAR2(100 CHAR) PATH '$.privilege'
            )
        ) jt
    ) LOOP

        BEGIN

            DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
                host => ace.host,
                lower_port => ace.lower_port,
                upper_port => ace.upper_port,
                ace => xs$ace_type(
                    privilege_list => xs$name_list(ace.privilege),
                    principal_name => UPPER(v_schema_username),
                    principal_type => xs_acl.ptype_db
                )
            );
            DBMS_OUTPUT.PUT_LINE('  - ACL entry added for host: ' || ace.host || ', privilege: ' || ace.privilege);

            COMMIT;
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE = -45137 THEN
                    DBMS_OUTPUT.PUT_LINE('  - ACL entry already exists for host: ' || ace.host || ', privilege: ' || ace.privilege);
                ELSE
                    DBMS_OUTPUT.PUT_LINE('  - error adding ACL entry for host: ' || ace.host || ', privilege: ' || ace.privilege || '. Error: ' || SQLERRM);
                END IF;
        END;

    END LOOP;

END;
/

DECLARE
    v_schema VARCHAR2(200 CHAR) := :schema;
BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = ' || v_schema;
END;
/

ALTER SESSION SET EDITION = "&EDITION"
/

./db/dist/999_install.sql

source

DECLARE
    c CLOB := :config;

    PROCEDURE setting(
        p_id IN VARCHAR2,
        p_name IN VARCHAR2,
        p_value IN VARCHAR2
    ) AS 
    BEGIN
        DBMS_OUTPUT.PUT_LINE('  - upserting setting: ' || p_id);
        EXECUTE IMMEDIATE 'MERGE INTO app_settings t 
        USING (SELECT :1 AS id, :2 AS name, :3 AS value FROM dual) s
        ON (t.id = s.id)
        WHEN MATCHED THEN
            UPDATE SET
                t.name = s.name,
                t.value = s.value
        WHEN NOT MATCHED THEN
            INSERT (id, name, value)
            VALUES (s.id, s.name, s.value)'
        USING
            p_id,
            p_name,
            p_value;

        COMMIT;
        DBMS_OUTPUT.PUT_LINE('    - completed.');
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('    - failed: ' || SQLERRM);
    END;   

BEGIN

    -- ADMIN user

    DECLARE 
        c_salt VARCHAR2(32 CHAR) := DBMS_RANDOM.STRING('X', 32);
        v_app_username VARCHAR2(2000 CHAR);
        v_app_password VARCHAR2(2000 CHAR);
        v_app_fullname VARCHAR2(2000 CHAR);
        v_app_host VARCHAR2(2000 CHAR);

    BEGIN

        DBMS_OUTPUT.PUT_LINE('- upserting admin user.');

        SELECT 
            JSON_VALUE(c, '$.app.username'),
            JSON_VALUE(c, '$.app.password'),
            JSON_VALUE(c, '$.app.fullname'),
            JSON_VALUE(c, '$.app.host')
        INTO 
            v_app_username,
            v_app_password,
            v_app_fullname,
            v_app_host
        FROM dual;

        v_app_password := c_salt || DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(TRIM(v_app_password) || c_salt),4);

        EXECUTE IMMEDIATE '
            MERGE INTO app_users t
            USING (SELECT 1 AS id FROM dual) s
            ON (t.id = s.id)
            WHEN MATCHED THEN
                UPDATE SET
                    username = :username,
                    password = :password,
                    fullname = :fullname
            WHEN NOT MATCHED THEN
                INSERT (id, username, password, fullname)
                VALUES (1, :username, :password, :fullname)'
        USING
            UPPER(TRIM(v_app_username)),
            v_app_password,
            v_app_fullname,
            UPPER(TRIM(v_app_username)),
            v_app_password,
            v_app_fullname;

        COMMIT;

        DBMS_OUTPUT.PUT_LINE('  - completed.');

    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('  - failed: ' || SQLERRM);
    END;

    -- Create admin role

    BEGIN
        DBMS_OUTPUT.PUT_LINE('- upserting admin role.');
        EXECUTE IMMEDIATE 'MERGE INTO app_roles r
        USING (SELECT 1 AS id FROM dual) s
        ON (r.id = s.id)
        WHEN MATCHED THEN
            UPDATE SET
                role = :role,
                description = :description
        WHEN NOT MATCHED THEN
            INSERT (id, role, description)
            VALUES (1, :role, :description)'
        USING 'ADMIN', 'Administrator access', 'ADMIN', 'Administrator access';
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('  - completed.');
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('  - failed: ' || SQLERRM);
    END;

    -- Create admin permissions

    BEGIN
        DBMS_OUTPUT.PUT_LINE('- upserting admin permissions.');
        EXECUTE IMMEDIATE 'MERGE INTO app_permissions p
        USING (
            SELECT 
                1 AS id_user,
                1 AS id_role
            FROM dual
        ) s ON (p.id_user = s.id_user AND p.id_role = s.id_role)
        WHEN MATCHED THEN 
            UPDATE SET p.permission = p.permission
        WHEN NOT MATCHED THEN 
            INSERT (id_user, id_role, permission) VALUES (s.id_user, s.id_role, ''Y'')';
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('  - completed.'); 
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('  - failed: ' || SQLERRM);
    END;

    -- SMTP

    DECLARE
        v_smtp_host VARCHAR2(2000 CHAR);
        v_smtp_port NUMBER;
        v_smtp_username VARCHAR2(2000 CHAR);
        v_smtp_password VARCHAR2(2000 CHAR);
        v_smtp_addr VARCHAR2(2000 CHAR);
        v_smtp_name VARCHAR2(2000 CHAR);

        v_schema_name VARCHAR2(200 CHAR);
        v_smtp_credential VARCHAR2(200 CHAR);
    BEGIN

        DBMS_OUTPUT.PUT_LINE('- upserting SMTP settings.');   
    
        SELECT 
            JSON_VALUE(c, '$.smtp.host'),
            TO_NUMBER(JSON_VALUE(c, '$.smtp.port')),
            JSON_VALUE(c, '$.smtp.username'),
            JSON_VALUE(c, '$.smtp.password'),
            JSON_VALUE(c, '$.smtp.addr'),
            JSON_VALUE(c, '$.smtp.name')
        INTO
            v_smtp_host,
            v_smtp_port,
            v_smtp_username,
            v_smtp_password,
            v_smtp_addr,
            v_smtp_name
        FROM dual;

        v_schema_name := JSON_VALUE(c, '$.schema.username');
        v_smtp_credential := UPPER(v_schema_name || '_SMTP_CREDENTIAL');

        BEGIN

            DBMS_CLOUD.CREATE_CREDENTIAL(
                credential_name => v_smtp_credential,
                username        => v_smtp_username,
                password        => v_smtp_password
            );
            COMMIT;
            DBMS_OUTPUT.PUT_LINE('  - credential created.');
        EXCEPTION
             -- credential creation failed: ORA-20022: Credential "***"."ODBVUE_SMTP_CREDENTIAL" already exists
            WHEN OTHERS THEN
                IF SQLCODE = -20022 THEN
                    DBMS_OUTPUT.PUT_LINE('  - credential already exists, skipping creation.');
                ELSE
                    DBMS_OUTPUT.PUT_LINE('  - credential creation failed: ' || SQLERRM);
                END IF; 
        END;

        setting('APP_EMAILS_SMTP_CRED', 'App email SMTP credential', v_smtp_credential);
        setting('APP_EMAILS_SMTP_HOST', 'App email SMTP host', v_smtp_host);
        setting('APP_EMAILS_SMTP_PORT', 'App email SMTP port', TO_CHAR(v_smtp_port));
        setting('APP_EMAILS_FROM_ADDR', 'App email senders address', v_smtp_addr);
        setting('APP_EMAILS_FROM_NAME', 'App email senders name', v_smtp_name);
        setting('APP_EMAILS_REPLYTO_ADDR', 'App email reply to address', v_smtp_addr);
        setting('APP_EMAILS_REPLYTO_NAME', 'App email reply to name', v_smtp_name);

    END;

    -- S3

    BEGIN
        DBMS_OUTPUT.PUT_LINE('- upserting S3 settings.');

        DECLARE
            v_s3_endpoint VARCHAR2(2000 CHAR);
        BEGIN

            SELECT 
                JSON_VALUE(c, '$.s3')
            INTO 
                v_s3_endpoint
            FROM dual;

            setting('APP_STORAGE_S3_URI', 'S3 Endpoint', v_s3_endpoint);

            DBMS_OUTPUT.PUT_LINE('  - settings upsert completed.');
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('  - failed - ' || SQLERRM);
        END;
    END;

    -- JWT

    BEGIN
        DBMS_OUTPUT.PUT_LINE('- upserting JWT settings.');

        DECLARE
            v_issuer VARCHAR2(2000 CHAR);
            v_audience VARCHAR2(2000 CHAR);
            v_secret VARCHAR2(2000 CHAR);
        BEGIN

            SELECT 
                JSON_VALUE(c, '$.jwt.issuer'),
                JSON_VALUE(c, '$.jwt.audience'),
                JSON_VALUE(c, '$.jwt.secret')
            INTO 
                v_issuer,
                v_audience,
                v_secret
            FROM dual;

            setting('APP_AUTH_JWT_ISSUER', 'App authentication JWT Issuer', v_issuer);
            setting('APP_AUTH_JWT_AUDIENCE', 'App authentication JWT Audience', v_audience);
            setting('APP_AUTH_JWT_SECRET', 'App authentication JWT Secret', v_secret);

            DBMS_OUTPUT.PUT_LINE('  - settings upsert completed.');

            FOR rec IN (
                SELECT 
                    id,
                    name,
                    expiration,
                    stored
                FROM JSON_TABLE(
                    c,
                    '$.jwt.types[*]' COLUMNS (
                        id VARCHAR2(100) PATH '$.id',
                        name VARCHAR2(200) PATH '$.name',
                        expiration NUMBER PATH '$.expiration',
                        stored VARCHAR2(10) PATH '$.stored'
                    )
                ) 
            ) LOOP
                EXECUTE IMMEDIATE 'MERGE INTO app_token_types t
                USING (SELECT :id AS id FROM dual) s
                ON (t.id = s.id)
                WHEN MATCHED THEN
                    UPDATE SET
                        name = :name,
                        expiration = :expiration,
                        stored = :stored
                WHEN NOT MATCHED THEN
                    INSERT (id, name, expiration, stored)
                    VALUES (:id, :name, :expiration, :stored)'
                USING
                    rec.id,
                    rec.name,
                    rec.expiration,
                    rec.stored,
                    rec.id,
                    rec.name,
                    rec.expiration,
                    rec.stored;
            END LOOP;

            COMMIT;
            DBMS_OUTPUT.PUT_LINE('  - completed');
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('  - failed - ' || SQLERRM);
        END;
    END;

    BEGIN
        DBMS_OUTPUT.PUT_LINE('- creating rest services.');
        prc_ordsify;
        DBMS_OUTPUT.PUT_LINE('  - completed.');
    EXCEPTION 
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('  - failed: ' || SQLERRM);
    END;   
END;
/

ALTER DATABASE DEFAULT EDITION = "&EDITION"
/

BEGIN    
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('Database edition set to: ' || SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME'));
    DBMS_OUTPUT.PUT_LINE('');
END;
/

These scripts apply application configuration from Environment variables:

• VERSION - release version. e.g. v0.0.161
• SCHEMA - schema name. e.g. odbvue
• EDITION - concatenation of schema and edition - ODBVUE_V_0_0_161
• CONFIG - application configuration in JSON format (single lined). See example below.

./db/config.json.example

source

{
  "schema": {
    "username": "odbvue",
    "password": "************",
    "grants": [
      "CREATE SESSION",
      "CREATE TABLE",
      "CREATE VIEW",
      "CREATE SEQUENCE",
      "CREATE PROCEDURE",
      "CREATE TRIGGER",
      "CREATE TYPE",
      "CREATE SYNONYM",
      "MANAGE SCHEDULER",
      "EXECUTE ON DBMS_SCHEDULER",
      "EXECUTE ON DBMS_CRYPTO",
      "EXECUTE ON DBMS_CLOUD"
    ],
    "enable_resource_principal": true
  },
  "acl":[
    {"host":"api.chucknorris.io","lower_port":443,"upper_port":443,"privilege":"http"},
    {"host":"api.openai.com","lower_port":443,"upper_port":443,"privilege":"http"},
    {"host":"smtp.email.eu-stockholm-1.oci.oraclecloud.com","lower_port":587,"upper_port":587,"privilege":"smtp"}
  ],
  "app":{
    "username": "admin@odbvue.com",
    "password": "************",
    "fullname": "OdbVue Admin",
    "host": "apps.odbvue.com"
  },
  "smtp": {
    "host": "smtp.email.eu-stockholm-1.oci.oraclecloud.com",
    "port": 587,
    "username": "ocid1.user.oc1..aaaa..fnhq.xy.com",
    "password": "************",
    "addr": "admin@odbvue.com",
    "name": "OdbVue Admin"
  },
  "s3": "https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/",
  "jwt": {
    "issuer": "OdbVue",
    "audience": "OdbVue Users",
    "secret": "************",
    "types": [
      {
        "id": "ACCESS",
        "name": "Access Token",
        "expiration": 900,
        "stored": "N"
      },
      {
        "id": "REFRESH",
        "name": "Refresh Token",
        "expiration": 604800,
        "stored": "Y"
      },
      {
        "id": "VERIFY",
        "name": "Identity Verification Token",
        "expiration": 86400,
        "stored": "Y"
      }
    ]
  }
}

Edition Based Redefinition

Oracle Edition-Based Redefinition (EBR) is a feature that allows online application upgrades with zero downtime.

It works by using “editions”, which are private copies of database objects (like PL/SQL packages, views, synonyms, etc.). You can create a new edition, make changes there, and run the old and new application versions side by side until the upgrade is complete.

Above scripts provide automatic creation of new edition per each release.

NOTE

Feature specifics is that each new edition is chained to previous and by time the chain can get quite long. That has a tiny impact as edition itself is just metadata but from clarity perspective it is advisable to do cleanup (drop editions down to ORA$BASE) when there is a maintenance window.

Information about editions and editioned objects:

SELECT * FROM all_editions;
SELECT * FROM all_objects_ae WHERE edition_name IS NOT NULL;

Enabling ORDS

Concept

OdbVue Database has an automation feature that auto enables REST services for PL/SQL package procedures that matches certain pattern.

If package includes procedures with get_, post_, put_, delete_ prefix, they are immediately after deployment publicly exposed in ORDS.

Pattern in as follows:

• Packages becomes modules (prefix pck_ is removed if exists; _ are replaced to -)

• Procedures become methods (prefixes get_, post_, put_, delete_ are removed; _ are replaced to -)

• Non-defaulted attributes become path parameters

• Defaulted attributes become query parameters

• Everything get lower-cased

Example

This:

CREATE OR REPLACE EDITIONABLE PACKAGE pck_app AS -- Package for the main application 
    
    PROCEDURE get_context ( -- Returns application context
        r_version OUT VARCHAR2 -- Application version
    );

END pck_app;
/

CREATE OR REPLACE EDITIONABLE PACKAGE BODY pck_app AS

    g_version VARCHAR2(30 CHAR) := '...';

    PROCEDURE get_context (
        r_version OUT VARCHAR2
    ) IS
    BEGIN
        r_version := g_version;

    END get_context;

BEGIN
    SELECT REPLACE(LOWER(REGEXP_REPLACE(SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME'), '^[A-Z0-9#$_]+_V_', 'v')), '_', '.')
    INTO g_version
    FROM dual;
END pck_app;
/

becomes this:

Creating module: app
  Creating endpoint: GET context/

and is publicly accessible.

Requesting:

curl -X GET "https://localhost:8443/ords/odbvue/context/" -k

returns:

{"version":"v0.0.61"}

TIP

Pattern is as follows:

• for GET https://localhost:8443/ords/[schema-name]/[package-name]/[procedure-name]/[non-defualted-attributes]?[defaulted-attributes]
• same for POST/PUT/DELETE, just [defaulted-attributes] shall be passed in request body

TIP

ORDS automatically generates Open API manifest:

• local: https://localhost:8443/ords/<schema>/open-api-catalog/
• oci: https://<domain>.adb.<region>.oraclecloudapps.com/ords/<schema>/open-api-catalog/

Implementation underneath

Standalone procedure is initiated by Trigger each time when schema objects changes.

./db/src/database/odbvue/triggers/trg_ordsify.sql

source

CREATE OR REPLACE EDITIONABLE TRIGGER odbvue.trg_ordsify
    AFTER CREATE OR ALTER ON SCHEMA BEGIN
        IF (
            ora_dict_obj_type = 'PACKAGE'
            AND ora_dict_obj_name IS NOT NULL
            AND ora_sysevent = 'CREATE'
        ) THEN
            dbms_scheduler.create_job(
                job_name   => 'JOB_ORDSIFY_' || ora_dict_obj_name,
                job_type   => 'PLSQL_BLOCK',
                job_action => 'BEGIN prc_ordsify('''
                              || ora_dict_obj_name
                              || ''', '''', FALSE); END;',
                start_date => systimestamp + INTERVAL '5' SECOND,
                enabled    => TRUE
            );

        END IF;
    END;
/

ALTER TRIGGER odbvue.trg_ordsify ENABLE;


-- sqlcl_snapshot {"hash":"f04620fb1a08aa29a8a1d6e122accfbca2a8b72d","type":"TRIGGER","name":"TRG_ORDSIFY","schemaName":"ODBVUE","sxml":""}

./db/src/database/odbvue/procedures/prc_ordsify.sql

source

CREATE OR REPLACE PROCEDURE odbvue.prc_ordsify (
    p_package      VARCHAR2 DEFAULT NULL,
    p_version_name VARCHAR2 DEFAULT NULL,
    p_silent_mode  BOOLEAN DEFAULT FALSE
) AS

    v_schema_name     VARCHAR2(30 CHAR);
    v_is_ords_enabled PLS_INTEGER;
    v_module          VARCHAR2(30 CHAR);
    v_role            VARCHAR2(30 CHAR);
    v_privilege       VARCHAR2(30 CHAR);
    v_method          VARCHAR2(30 CHAR);
    v_pattern         VARCHAR2(2000 CHAR);
    v_params          VARCHAR2(2000 CHAR);
    v_argument        VARCHAR2(30 CHAR);
    v_type            VARCHAR2(30 CHAR);
    v_comment         VARCHAR2(2000 CHAR);

    PROCEDURE log (
        p VARCHAR2
    ) AS
    BEGIN
        IF NOT p_silent_mode THEN
            dbms_output.put_line(p);
        END IF;
    END;

    FUNCTION get_comment (
        p_package   IN VARCHAR2,
        p_procedure IN VARCHAR2,
        p_argument  IN VARCHAR2,
        p_overload  IN PLS_INTEGER DEFAULT 1
    ) RETURN VARCHAR2 AS
        TYPE t_lines IS
            TABLE OF PLS_INTEGER;
        v_lines t_lines;
        v_text  VARCHAR2(2000 CHAR);
    BEGIN
        IF p_package IS NULL THEN
            RETURN NULL;
        END IF;
        IF p_procedure IS NULL THEN
            SELECT
                CASE
                    WHEN text LIKE '%--%' THEN
                        replace(
                            trim(substr(text,
                                        instr(text, '--') + 2)),
                            chr(10),
                            ''
                        )
                    ELSE
                        NULL
                END
            INTO v_text
            FROM
                user_source
            WHERE
                    type = 'PACKAGE'
                AND name = upper(trim(p_package))
                AND replace(
                    upper(trim(text)),
                    ' ',
                    ''
                ) LIKE '%PACKAGE'
                       || upper(trim(p_package))
                       || '%';

            RETURN v_text;
        END IF;

        SELECT
            line
        BULK COLLECT
        INTO v_lines
        FROM
            user_source
        WHERE
                type = 'PACKAGE'
            AND name = upper(trim(p_package))
            AND replace(
                trim(upper(text)),
                ' ',
                ''
            ) LIKE 'PROCEDURE'
                   || upper(trim(p_procedure))
                   || '%'
        ORDER BY
            line;

        IF p_argument IS NULL THEN
            SELECT
                CASE
                    WHEN text LIKE '%--%' THEN
                        replace(
                            trim(substr(text,
                                        instr(text, '--') + 2)),
                            chr(10),
                            ''
                        )
                    ELSE
                        NULL
                END
            INTO v_text
            FROM
                user_source
            WHERE
                    type = 'PACKAGE'
                AND name = upper(trim(p_package))
                AND line = v_lines(coalesce(p_overload, 1));

            RETURN v_text;
        END IF;

        BEGIN
            SELECT
                CASE
                    WHEN text LIKE '%--%' THEN
                        replace(
                            trim(substr(text,
                                        instr(text, '--') + 2)),
                            chr(10),
                            ''
                        )
                    ELSE
                        NULL
                END
            INTO v_text
            FROM
                user_source
            WHERE
                    type = 'PACKAGE'
                AND name = upper(trim(p_package))
                AND replace(
                    trim(upper(text)),
                    ' ',
                    ''
                ) LIKE upper(trim(p_argument))
                       || '%'
                AND line > v_lines(coalesce(p_overload, 1))
            ORDER BY
                line
            FETCH FIRST 1 ROWS ONLY;

        EXCEPTION
            WHEN no_data_found THEN
                v_text := NULL;
        END;

        RETURN v_text;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN NULL;
    END;

BEGIN
    log('Begin setup of ORDS services');
    SELECT
        lower(sys_context('userenv', 'current_schema'))
    INTO v_schema_name
    FROM
        dual;

    log('Schema: ' || v_schema_name);

    -- Enable schema 
    SELECT
        COUNT(id)
    INTO v_is_ords_enabled
    FROM
        user_ords_schemas
    WHERE
            parsing_schema = upper(v_schema_name)
        AND status = 'ENABLED';

    IF ( v_is_ords_enabled = 0 ) THEN
        ords.enable_schema(
            p_enabled             => TRUE,
            p_schema              => v_schema_name,
            p_url_mapping_type    => 'BASE_PATH',
            p_url_mapping_pattern => lower(v_schema_name),
            p_auto_rest_auth      => FALSE
        );

        COMMIT;
        log('ORDS enabled for schema');
    END IF;    

-- modules

    FOR m IN (
        SELECT
            o.object_name
        FROM
            all_objects o
        WHERE
                owner = upper(v_schema_name)
            AND o.object_type = 'PACKAGE'
            AND ( ( p_package IS NULL )
                  OR ( upper(p_package) = o.object_name ) )
            AND EXISTS (
                SELECT
                    p.procedure_name
                FROM
                    all_procedures p
                WHERE
                        p.owner = upper(v_schema_name)
                    AND p.object_name = o.object_name
                    AND ( p.procedure_name LIKE 'GET_%'
                          OR p.procedure_name LIKE 'POST_%'
                          OR p.procedure_name LIKE 'PUT_%'
                          OR p.procedure_name LIKE 'DELETE_%' )
            )
    ) LOOP
        v_module := lower(replace(
            CASE
                WHEN substr(m.object_name, 1, 4) = 'PCK_' THEN
                    substr(m.object_name, 5)
                ELSE
                    m.object_name
            END
            ||
            CASE
                WHEN p_version_name IS NOT NULL THEN
                    '-' || p_version_name
                ELSE
                    ''
            END,
            '_',
            '-'));

        log('');
        log('Creating module: ' || v_module);
        v_comment := get_comment(m.object_name, NULL, NULL);
        ords.define_module(
            p_module_name    => v_module,
            p_base_path      => v_module || '/',
            p_items_per_page => 0,
            p_comments       => v_comment
        );

        -- methods

        FOR p IN (
            SELECT
                o.procedure_name,
                o.overload
            FROM
                all_procedures o
            WHERE
                    o.object_name = m.object_name
                AND o.procedure_name IS NOT NULL
                AND o.owner = upper(v_schema_name)
            ORDER BY
                o.subprogram_id
        ) LOOP
            v_method :=
                CASE
                    WHEN p.procedure_name LIKE 'POST_%' THEN
                        'POST'
                    WHEN p.procedure_name LIKE 'PUT_%' THEN
                        'PUT'
                    WHEN p.procedure_name LIKE 'DELETE_%' THEN
                        'DELETE'
                    WHEN p.procedure_name LIKE 'GET_%' THEN
                        'GET'
                    ELSE
                        NULL
                END;

            IF v_method IS NOT NULL THEN
                v_params := '';
                v_pattern := '';
                FOR a IN (
                    SELECT
                        argument_name,
                        defaulted,
                        in_out
                    FROM
                        all_arguments a
                    WHERE
                            package_name = m.object_name
                        AND object_name = p.procedure_name
                        AND nvl(a.overload, '0') = nvl(p.overload, '0')
                        AND owner = upper(v_schema_name)
                    ORDER BY
                        position
                ) LOOP
                    v_argument :=
                        CASE
                            WHEN substr(a.argument_name, 1, 2) IN ( 'P_', 'R_' ) THEN
                                substr(
                                    lower(a.argument_name),
                                    3
                                )
                            ELSE
                                lower(a.argument_name)
                        END;

                    v_params := v_params
                                || lower(a.argument_name)
                                || ' => :'
                                || replace(v_argument, '_', '-')
                                || ',';

                    IF a.defaulted = 'N' THEN
                        IF a.in_out = 'IN' THEN
                            v_pattern := v_pattern
                                         || ':'
                                         || replace(v_argument, '_', '-')
                                         || '/';

                        END IF;
                    END IF;

                END LOOP;

                v_params := substr(v_params,
                                   1,
                                   length(v_params) - 1);
                IF ( length(v_params) > 0 ) THEN
                    v_params := '('
                                || v_params
                                || ')';
                END IF;

                v_pattern := substr(v_pattern,
                                    1,
                                    length(v_pattern) - 1);
                v_pattern := lower(replace(
                    replace(
                        replace(
                            replace(
                                replace(p.procedure_name, 'POST_'),
                                'GET_'
                            ),
                            'PUT_'
                        ),
                        'DELETE_'
                    ),
                    '_',
                    '-'
                ))
                             || '/'
                             ||
                    CASE
                        WHEN v_method = 'GET' THEN
                            v_pattern
                        ELSE
                            NULL
                    END;

                v_comment := get_comment(m.object_name, p.procedure_name, NULL, p.overload);

                log('  Creating endpoint: '
                    || v_method
                    || ' ' || v_pattern);
                ords.define_template(
                    p_module_name => v_module,
                    p_pattern     => v_pattern,
                    p_comments    => v_comment
                );

                COMMIT;
                ords.define_handler(
                    p_module_name    => v_module,
                    p_pattern        => v_pattern,
                    p_method         => v_method,
                    p_source_type    => ords.source_type_plsql,
                    p_source         => 'BEGIN '
                                || lower(m.object_name)
                                || '.'
                                || lower(p.procedure_name)
                                || ''
                                || v_params
                                || '; END;',
                    p_items_per_page => 0,
                    p_comments       => v_comment
                );

                COMMIT;
                FOR a IN (
                    SELECT
                        argument_name,
                        defaulted,
                        in_out,
                        data_type
                    FROM
                        all_arguments a
                    WHERE
                            package_name = m.object_name
                        AND object_name = p.procedure_name
                        AND nvl(a.overload, '0') = nvl(p.overload, '0')
                        AND owner = upper(v_schema_name)
                    ORDER BY
                        position
                ) LOOP
                    v_argument :=
                        CASE
                            WHEN substr(a.argument_name, 1, 2) IN ( 'P_', 'R_' ) THEN
                                substr(
                                    lower(a.argument_name),
                                    3
                                )
                            ELSE
                                lower(a.argument_name)
                        END;

                    -- https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/18.3/aelig/ords-database-type-mappings.html#GUID-4F7FA58A-1C29-4B7E-819F-21DB4B68FFE1
                    v_type :=
                        CASE a.data_type -- The native type of the parameter. Valid values: STRING, INT, DOUBLE, BOOLEAN, LONG, TIMESTAMP
                            WHEN 'REF CURSOR'     THEN
                                'RESULTSET'
                            WHEN 'BINARY_INTEGER' THEN
                                'INT'
                            ELSE
                                'STRING'
                        END;

                    IF a.argument_name NOT IN ( 'P_BODY' ) THEN
                        v_comment := get_comment(m.object_name, p.procedure_name, a.argument_name, p.overload);

                        ords.define_parameter(
                            p_module_name        => v_module,
                            p_pattern            => v_pattern,
                            p_method             => v_method,
                            p_name               => v_argument,
                            p_bind_variable_name => replace(v_argument, '_', '-'),
                            p_source_type        =>
                                           CASE a.in_out
                                               WHEN 'OUT' THEN
                                                   'RESPONSE'
                                               ELSE
                                                   'HEADER'
                                           END,
                            p_param_type         => v_type,
                            p_access_method      => a.in_out,
                            p_comments           => v_comment
                        );

                        COMMIT;
                    END IF;

                END LOOP;

            END IF;

        END LOOP;

    END LOOP;

    -- done

    log('');
    log('Success!');
END;
/


-- sqlcl_snapshot {"hash":"99022cecc183b13d9af3bbec62bebb0862e1fb46","type":"PROCEDURE","name":"PRC_ORDSIFY","schemaName":"ODBVUE","sxml":""}