Creation of a table | Primary key constraint | Sequence | Public synonyms with Grants

Script defines the creation of a table, a primary key constraint, a sequence, and public synonyms along with grants in an Oracle database schema. Here's the detailed explanation:

---

🔹 Step 1: Create Table

CREATE TABLE CGO_OWNR.res_fwb_shc_mapping (
id NUMBER(15) NOT NULL,
res_shc VARCHAR2(3),
fwb_shc VARCHAR2(3),
priority_shc VARCHAR2(3)
);

➤ What it does:

Creates a new table named res_fwb_shc_mapping under the CGO_OWNR schema.

This table has four columns:

id: A numeric field of up to 15 digits. It cannot be NULL, so it's mandatory.

res_shc: A VARCHAR2 (string) column allowing up to 3 characters.

fwb_shc: Another 3-character string field.

priority_shc: Another 3-character string field.

➤ Purpose:

This table appears to be a mapping table between different SHC codes (likely Special Handling Codes in logistics or freight systems).

---

🔹 Step 2: Add Primary Key Constraint

ALTER TABLE CGO_OWNR.res_fwb_shc_mapping
ADD CONSTRAINT RFW_PK PRIMARY KEY (id);

➤ What it does:

Adds a primary key constraint named RFW_PK to the id column of the table.

➤ Why it's important:

A primary key enforces uniqueness and non-nullability on the id column.

This ensures that every record has a unique identifier.

---

🔹 Step 3: Create Sequence

CREATE SEQUENCE CGO_OWNR.RFW_SEQ;

➤ What it does:

Creates a sequence named RFW_SEQ in the CGO_OWNR schema.

➤ Purpose of a sequence:

Sequences are typically used to auto-generate unique values, usually for primary key columns like id.

The default settings will:

Start from 1

Increment by 1

Have no maximum value (unless specified)

So you would typically insert into the table like:

INSERT INTO res_fwb_shc_mapping (id, ...) VALUES (RFW_SEQ.NEXTVAL, ...);

---

🔹 Step 4: Create Public Synonym & Grant Permissions for Table

CREATE OR REPLACE PUBLIC SYNONYM RES_FWB_SHC_MAPPING FOR CGO_OWNR.res_fwb_shc_mapping;

GRANT SELECT, INSERT, UPDATE, DELETE ON CGO_OWNR.res_fwb_shc_mapping TO cgo_siud;

➤ What it does:

1. Public Synonym:

CREATE OR REPLACE PUBLIC SYNONYM RES_FWB_SHC_MAPPING ... allows any user in the database to reference the table without using the schema name.

Instead of CGO_OWNR.res_fwb_shc_mapping, users can just use RES_FWB_SHC_MAPPING.

1. Grants:

Grants SELECT, INSERT, UPDATE, and DELETE privileges on the table to the user/role cgo_siud.

➤ Why it’s done:

To simplify access and allow a user (like an app or another team) to perform CRUD operations without prefixing schema names.

---

🔹 Step 5: Create Public Synonym & Grant for Sequence

CREATE OR REPLACE PUBLIC SYNONYM RFW_SEQ FOR CGO_OWNR.RFW_SEQ;

GRANT SELECT ON CGO_OWNR.RFW_SEQ TO cgo_siud;

➤ What it does:

1. Public Synonym for Sequence:

Allows access to RFW_SEQ without qualifying it with the schema name.

1. Grant SELECT on Sequence:

Grants permission to use RFW_SEQ.NEXTVAL or CURRVAL to the cgo_siud user/role.

➤ Why:

The application or user cgo_siud can generate IDs using the sequence without schema references.

---

✅ Summary

Step Description

1 Create a table for mapping SHC codes.
2 Add a primary key on id to ensure uniqueness.
3 Create a sequence for auto-generating id values.
4 Create a public synonym and grant full DML permissions on the table.
5 Create a public synonym for the sequence and grant access to fetch the next value.

---