Summary
A quick walkthrough of Oracle Live SQL practice: creating a table, printing output with SERVEROUTPUT, iterating a cursor, and building an AFTER INSERT trigger to log into an audit table, with screenshots and runnable snippets.
What this post covers
- Create and seed an Employee table
- Use SET SERVEROUTPUT ON for DBMS_OUTPUT in Live SQL
- Loop over rows using a cursor and print names to the script output pane
- Write a simple auditing trigger for student inserts and verify audit rows ## 1) Create table + sample data
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
Name VARCHAR2(50),
Salary NUMBER(10,2)
);
INSERT INTO Employee VALUES (1, 'Alice', 60000);
INSERT INTO Employee VALUES (2, 'Bob', 45000);
INSERT INTO Employee VALUES (3, 'Charlie', 75000);
INSERT INTO Employee VALUES (4, 'David', 50000);
2) Print output with DBMS_OUTPUT
SET SERVEROUTPUT ON;
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM Employee;
DBMS_OUTPUT.PUT_LINE('Rows in Employee: ' || v_count);
END;
/
3) Cursor demo: list high earners
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_emp IS
SELECT Name FROM Employee WHERE Salary > 50000 ORDER BY Salary DESC;
v_name Employee.Name%TYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_name;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;
CLOSE c_emp;
END;
/
4) Build a simple audit trigger
-- Base tables
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50)
);
CREATE TABLE Student_Audit (
AuditID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
StudentID NUMBER,
StudentName VARCHAR2(50),
Action VARCHAR2(20),
ActionDate DATE
);
-- AFTER INSERT row-level trigger to capture inserts
CREATE OR REPLACE TRIGGER trg_students_ins_audit
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName, Action, ActionDate)
VALUES (:NEW.StudentID, :NEW.Name, 'Inserted', SYSDATE);
END;
/
Dev.to formatting tips
- Use fenced code blocks with language hints like ```
- Keep one blank line before and after code blocks for clean rendering in Markdown-based editors like Dev.to.
- If showing code that itself contains backticks, use a longer fence (four or more backticks) to avoid premature closing in Markdown processors.
This post is dedicated to @santhoshnc Sir thank you for the assignment and continuous support.
Top comments (0)