Labels
Package provides methods for managing labels
example
sql
BEGIN
-- Numeric
pck_api_labels.link_label_nm('Important', 'document', 123);
pck_api_labels.link_label_nm('Important', 'document', 234);
pck_api_labels.link_label_nm('Important', 'document', 345);
pck_api_labels.link_label_nm('Nice to have', 'document', 456);
pck_api_labels.link_label_nm('Nice to have', 'document', 567);
pck_api_labels.unlink_label_nm('Important', 'document', 234);
pck_api_labels.unlink_label_nm('Important', 'document', 678); -- non-existing link
-- Variable Character
pck_api_labels.link_label_vc('Important', 'folder', 'abc123');
pck_api_labels.link_label_vc('Important', 'folder', 'def456');
pck_api_labels.link_label_vc('Important', 'folder', 'ghi789');
pck_api_labels.link_label_vc('Nice to have', 'folder', 'jkl012');
pck_api_labels.link_label_vc('Nice to have', 'folder', 'mno345');
pck_api_labels.unlink_label_vc('Important', 'folder', 'def456');
pck_api_labels.unlink_label_vc('Important', 'folder', 'xyz999'); -- non-existing link
-- done
COMMIT;
END;
/
SELECT
ll.entity_name,
COALESCE(ll.entity_id_vc, TO_CHAR(ll.entity_id_nm)) AS entity_id,
l.name AS label_name
FROM app_label_links ll
JOIN app_labels l ON ll.label_id = l.id
ORDER BY ll.entity_name, l.code;specification
sql
CREATE OR REPLACE PACKAGE odbvue.pck_api_labels AS -- Package provides methods for managing labels
PROCEDURE link_label_nm ( -- Procedure links a label to an entity
p_label_name app_labels.name%TYPE, -- Name of the label
p_entity_name app_label_links.entity_name%TYPE, -- Name of the entity
p_entity_id_nm app_label_links.entity_id_nm%TYPE -- Numeric Id of the entity
);
PROCEDURE unlink_label_nm ( -- Procedure unlinks a label from an entity
p_label_name app_labels.name%TYPE, -- Name of the label
p_entity_name app_label_links.entity_name%TYPE, -- Name of the entity
p_entity_id_nm app_label_links.entity_id_nm%TYPE DEFAULT NULL -- Numeric Id of the entity
);
PROCEDURE link_label_vc ( -- Procedure links a label to an entity
p_label_name app_labels.name%TYPE, -- Name of the label
p_entity_name app_label_links.entity_name%TYPE, -- Name of the entity
p_entity_id_vc app_label_links.entity_id_vc%TYPE -- Variable character Id of the entity
);
PROCEDURE unlink_label_vc ( -- Procedure unlinks a label from an entity
p_label_name app_labels.name%TYPE, -- Name of the label
p_entity_name app_label_links.entity_name%TYPE, -- Name of the entity
p_entity_id_vc app_label_links.entity_id_vc%TYPE DEFAULT NULL -- Variable character Id of the entity
);
PROCEDURE purge_unused_labels ( -- Procedure removes labels that are not linked to any entity
p_batch_size IN PLS_INTEGER DEFAULT 1000 -- Number of labels to process in one batch
);
END;
/
-- sqlcl_snapshot {"hash":"f3a560ff901a2e580b7e786a6e0ec116b9118661","type":"PACKAGE_SPEC","name":"PCK_API_LABELS","schemaName":"ODBVUE","sxml":""}implementation
sql
CREATE OR REPLACE PACKAGE BODY odbvue.pck_api_labels AS
FUNCTION normalize_name (
p_name IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
RETURN upper(replace(p_name, ' ', '_'));
END;
FUNCTION get_label_id (
p_label_name IN app_labels.name%TYPE
) RETURN app_labels.id%TYPE AS
v_label_id app_labels.id%TYPE;
v_label_code app_labels.code%TYPE := normalize_name(p_label_name);
BEGIN
BEGIN
INSERT INTO app_labels ( name ) VALUES ( p_label_name ) RETURNING id INTO v_label_id;
EXCEPTION
WHEN dup_val_on_index THEN
SELECT
id
INTO v_label_id
FROM
app_labels
WHERE
code = v_label_code;
END;
RETURN v_label_id;
END;
PROCEDURE link_label_nm (
p_label_name app_labels.name%TYPE,
p_entity_name app_label_links.entity_name%TYPE,
p_entity_id_nm app_label_links.entity_id_nm%TYPE
) AS
v_label_id app_labels.id%TYPE := get_label_id(p_label_name);
v_entity_name app_label_links.entity_name%TYPE := normalize_name(p_entity_name);
BEGIN
INSERT INTO app_label_links (
label_id,
entity_name,
entity_id_nm
) VALUES ( v_label_id,
v_entity_name,
p_entity_id_nm );
EXCEPTION
WHEN dup_val_on_index THEN
NULL;
END;
PROCEDURE unlink_label_nm (
p_label_name app_labels.name%TYPE,
p_entity_name app_label_links.entity_name%TYPE,
p_entity_id_nm app_label_links.entity_id_nm%TYPE DEFAULT NULL
) AS
v_label_id app_labels.id%TYPE;
v_label_code app_labels.code%TYPE := normalize_name(p_label_name);
v_entity_name app_label_links.entity_name%TYPE := normalize_name(p_entity_name);
BEGIN
SELECT
id
INTO v_label_id
FROM
app_labels
WHERE
code = v_label_code;
DELETE FROM app_label_links
WHERE
label_id = v_label_id
AND entity_name = v_entity_name
AND ( p_entity_id_nm IS NULL
OR entity_id_nm = p_entity_id_nm );
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
PROCEDURE link_label_vc (
p_label_name app_labels.name%TYPE,
p_entity_name app_label_links.entity_name%TYPE,
p_entity_id_vc app_label_links.entity_id_vc%TYPE
) AS
v_label_id app_labels.id%TYPE := get_label_id(p_label_name);
v_entity_name app_label_links.entity_name%TYPE := normalize_name(p_entity_name);
BEGIN
INSERT INTO app_label_links (
label_id,
entity_name,
entity_id_vc
) VALUES ( v_label_id,
v_entity_name,
p_entity_id_vc );
EXCEPTION
WHEN dup_val_on_index THEN
NULL;
END;
PROCEDURE unlink_label_vc (
p_label_name app_labels.name%TYPE,
p_entity_name app_label_links.entity_name%TYPE,
p_entity_id_vc app_label_links.entity_id_vc%TYPE DEFAULT NULL
) AS
v_label_id app_labels.id%TYPE;
v_label_code app_labels.code%TYPE := normalize_name(p_label_name);
v_entity_name app_label_links.entity_name%TYPE := normalize_name(p_entity_name);
BEGIN
SELECT
id
INTO v_label_id
FROM
app_labels
WHERE
code = v_label_code;
DELETE FROM app_label_links
WHERE
label_id = v_label_id
AND entity_name = v_entity_name
AND ( p_entity_id_vc IS NULL
OR entity_id_vc = p_entity_id_vc );
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
PROCEDURE purge_unused_labels (
p_batch_size IN PLS_INTEGER DEFAULT 1000
) AS
v_deleted PLS_INTEGER := 0;
v_deleted_total PLS_INTEGER := 0;
BEGIN
LOOP
DELETE FROM app_labels l
WHERE
ROWID IN (
SELECT
rid
FROM
(
SELECT
l2.rowid rid
FROM
app_labels l2
WHERE
NOT EXISTS (
SELECT
1
FROM
app_label_links ll
WHERE
ll.label_id = l2.id
)
ORDER BY
l2.id
)
WHERE
ROWNUM <= nvl(p_batch_size, 2147483647)
);
v_deleted := SQL%rowcount;
v_deleted_total := v_deleted_total + v_deleted;
EXIT WHEN v_deleted = 0;
dbms_output.put_line('Purged unused labels: ' || v_deleted_total);
END LOOP;
END;
END;
/
-- sqlcl_snapshot {"hash":"4faf3bb30594a5a2fd2ca78f02d17e608931dc9e","type":"PACKAGE_BODY","name":"PCK_API_LABELS","schemaName":"ODBVUE","sxml":""}Dependencies:
| Referenced type | Referenced name |
|---|---|
| TABLE | LABEL_LINKS |
| TABLE | LABELS |
LINK_LABEL_NM
Procedure links a label to an entity
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_LABEL_NAME | IN | VARCHAR2 | Name of the label | |
| P_ENTITY_NAME | IN | VARCHAR2 | Name of the entity | |
| P_ENTITY_ID_NM | IN | NUMBER | Numeric Id of the entity |
LINK_LABEL_VC
Procedure links a label to an entity
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_LABEL_NAME | IN | VARCHAR2 | Name of the label | |
| P_ENTITY_NAME | IN | VARCHAR2 | Name of the entity | |
| P_ENTITY_ID_VC | IN | VARCHAR2 | Variable character Id of the entity |
UNLINK_LABEL_NM
Procedure unlinks a label from an entity
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_LABEL_NAME | IN | VARCHAR2 | Name of the label | |
| P_ENTITY_NAME | IN | VARCHAR2 | Name of the entity | |
| P_ENTITY_ID_NM | IN | NUMBER | NULL | Numeric Id of the entity |
UNLINK_LABEL_VC
Procedure unlinks a label from an entity
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_LABEL_NAME | IN | VARCHAR2 | Name of the label | |
| P_ENTITY_NAME | IN | VARCHAR2 | Name of the entity | |
| P_ENTITY_ID_VC | IN | VARCHAR2 | NULL | Variable character Id of the entity |