Oracle APEX - Custom Sticky Notes Region with PDF Export (Version 1)

Welcome to the first version of our innovative Sticky Notes Region for Oracle APEX – a fully interactive board to drop your quick thoughts, organize ideas visually, and export the board as a PDF! 🎉

Oracle APEX - Custom Sticky Notes region development

🎥 Watch It in Action: See how this custom Sticky Notes region works in Oracle APEX with PDF export!

📌 Why Sticky Notes in Oracle APEX?

This region is perfect for project management, brainstorming sessions, idea boards, retrospectives, sprint planning, and any task requiring visual clustering. Built with modern UI/UX in mind, it provides draggable sticky notes that persist in the database and can be exported in high quality using jsPDF and html2canvas.

🔍 Key Features

• Create, move, and edit sticky notes dynamically
• Export the canvas to PDF (supports A4, A3, Letter sizes)
• Responsive and print-ready layout
• Save & delete notes with AJAX
• Toolbar to configure canvas size, orientation, colors, and border

🧰 Technical Specs

• Oracle APEX: 24.2.6
• PDF Export: jsPDF and html2canvas
• Backend: PL/SQL AJAX Callbacks

---

🛠️ Step-by-Step Implementation

Step 1: Create the Table

CREATE TABLE STICKY_NOTES (
  ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  NOTE_CONTENT CLOB,
  POS_X NUMBER,
  POS_Y NUMBER,
  Z_INDEX NUMBER,
  CREATED_AT DATE DEFAULT SYSDATE
);

Step 2: Create the APEX Page

• Create a new Blank Page
• Add a Static Content Region and name it Sticky Board
• Inside this region, place the below HTML content:

<div class="toolbar-container">
  <div class="toolbar-controls">
    <label for="page-size">Page Size:</label>
    <select id="page-size">
      <option value="a4">A4</option>
      <option value="a3">A3</option>
      <option value="letter">Letter</option>
    </select>

    <label for="orientation">Orientation:</label>
    <select id="orientation">
      <option value="portrait">Portrait</option>
      <option value="landscape">Landscape</option>
    </select>

    <label for="note-bg">Note Background:</label>
    <select id="note-bg">
      <option value="yellow">Yellow</option>
      <option value="none">Colorless</option>
    </select>

    <label for="note-border">Border:</label>
    <select id="note-border">
      <option value="black">Black</option>
      <option value="none">None</option>
    </select>

    <input id="print-btn" type="button" value="📄 Export to PDF" class="action-btn" />
    <div id="trash-can" title="Drag here to delete"></div>
  </div>
</div>

<div id="canvas-container">
  <div id="canvas-wrapper">
    <div id="note-container">
      <!-- Sticky notes dynamically added here -->
    </div>
  </div>
</div>

Step 3: Add the Button

In the same region’s Copy position, create a button:

• Label: + Add Note
• Static ID: btn-add-note
• Action: No action (handled via JavaScript)

Step 4: Add the AJAX Callback Processes

💾 SAVE_NOTE

DECLARE
  l_id      NUMBER := APEX_APPLICATION.G_X01;
  l_content CLOB := APEX_APPLICATION.G_X02;
  l_x       NUMBER := APEX_APPLICATION.G_X03;
  l_y       NUMBER := APEX_APPLICATION.G_X04;
  l_z       NUMBER := APEX_APPLICATION.G_X05;
BEGIN
  IF l_id IS NOT NULL THEN
    UPDATE STICKY_NOTES SET
      NOTE_CONTENT = l_content,
      POS_X = l_x,
      POS_Y = l_y,
      Z_INDEX = l_z
    WHERE ID = l_id;
  ELSE
    INSERT INTO STICKY_NOTES (NOTE_CONTENT, POS_X, POS_Y, Z_INDEX)
    VALUES (l_content, l_x, l_y, l_z)
    RETURNING ID INTO l_id;
  END IF;

  APEX_JSON.OPEN_OBJECT;
  APEX_JSON.WRITE('id', l_id);
  APEX_JSON.CLOSE_OBJECT;
END;

📥 LOAD_NOTES

BEGIN
  APEX_JSON.OPEN_ARRAY;
  FOR rec IN (SELECT * FROM STICKY_NOTES) LOOP
    APEX_JSON.OPEN_OBJECT;
    APEX_JSON.WRITE('id', rec.ID);
    APEX_JSON.WRITE('content', rec.NOTE_CONTENT);
    APEX_JSON.WRITE('x', rec.POS_X);
    APEX_JSON.WRITE('y', rec.POS_Y);
    APEX_JSON.WRITE('z', rec.Z_INDEX);
    APEX_JSON.CLOSE_OBJECT;
  END LOOP;
  APEX_JSON.CLOSE_ARRAY;
END;

🗑️ DELETE_NOTE

BEGIN
  DELETE FROM STICKY_NOTES WHERE ID = APEX_APPLICATION.G_X01;

  APEX_JSON.OPEN_OBJECT;
  APEX_JSON.WRITE('status', 'success');
  APEX_JSON.CLOSE_OBJECT;
END;

Step 5: Add the JavaScript

Go to Page → Execute when Page Loads and paste this complete JS:

let zCounter = 1;

// Create and insert a new sticky note
function createStickyNote(content = '', x = null, y = null, z = null, id = null) {
    const note = document.createElement('div');
    note.classList.add('sticky-note');
    note.setAttribute('contenteditable', true);
    note.innerHTML = content || 'Edit me...';
    note.addEventListener('blur', function() {
        saveNoteToDB(note);
    });

    const bgColor = document.getElementById('note-bg').value;
    const borderStyle = document.getElementById('note-border').value;

    note.style.background = bgColor === 'none' ? 'transparent' : bgColor;
    note.style.border = borderStyle === 'none' ? 'none' : '1px solid black';

    note.style.position = 'absolute';
    note.style.padding = '10px';
    note.style.minWidth = '100px';
    note.style.minHeight = '80px';
    note.style.borderRadius = '5px';
    note.style.cursor = 'move';
    note.style.boxShadow = '2px 2px 5px rgba(0,0,0,0.2)';
    note.style.zIndex = z || zCounter++;

    note.dataset.id = id || '';
    note.dataset.z = note.style.zIndex;

    const container = document.getElementById('note-container');
    const rect = container.getBoundingClientRect();

    // Position at center if no x,y
    note.style.left = x !== null ? x + 'px' : (rect.width / 2 - 60) + 'px';
    note.style.top = y !== null ? y + 'px' : (rect.height / 2 - 40) + 'px';

    makeNoteDraggable(note);
    container.appendChild(note);
}

// Make the note draggable within the container
function makeNoteDraggable(note) {
    let offsetX = 0, offsetY = 0, isDragging = false;
    const trash = document.getElementById('trash-can');

    note.addEventListener('mousedown', function(e) {
        isDragging = true;
        offsetX = e.offsetX;
        offsetY = e.offsetY;
        note.style.zIndex = zCounter++;
    });

    document.addEventListener('mousemove', function(e) {
        if (!isDragging) return;

        const container = document.getElementById('canvas-container');
        const bounds = container.getBoundingClientRect();

        note.style.left = (e.clientX - bounds.left - offsetX) + 'px';
        note.style.top = (e.clientY - bounds.top - offsetY) + 'px';

        const trashBounds = trash.getBoundingClientRect();
        const noteBounds = note.getBoundingClientRect();

        const isOverlapping = !(noteBounds.right < trashBounds.left ||
            noteBounds.left > trashBounds.right ||
            noteBounds.bottom < trashBounds.top ||
            noteBounds.top > trashBounds.bottom);

        if (isOverlapping) {
            trash.classList.add('trash-hover');
        } else {
            trash.classList.remove('trash-hover');
        }
    });

    document.addEventListener('mouseup', function(e) {
        if (isDragging) {
            const trashBounds = trash.getBoundingClientRect();
            const noteBounds = note.getBoundingClientRect();

            const isOverlapping = !(noteBounds.right < trashBounds.left ||
                noteBounds.left > trashBounds.right ||
                noteBounds.bottom < trashBounds.top ||
                noteBounds.top > trashBounds.bottom);

            if (isOverlapping) {
                if (note.dataset.id) {
                    deleteNoteFromDB(note.dataset.id);
                }
                note.remove();
            } else {
                const canvas = document.getElementById('note-container');
                let left = parseInt(note.style.left);
                let top = parseInt(note.style.top);
                const noteWidth = note.offsetWidth;
                const noteHeight = note.offsetHeight;

                if (left < 0) left = 0;
                if (top < 0) top = 0;
                if (left + noteWidth > canvas.offsetWidth) {
                    left = canvas.offsetWidth - noteWidth;
                }
                if (top + noteHeight > canvas.offsetHeight) {
                    top = canvas.offsetHeight - noteHeight;
                }

                note.style.left = left + 'px';
                note.style.top = top + 'px';
            }

            trash.classList.remove('trash-hover');
            isDragging = false;
        }
    });
}

function saveNoteToDB(noteEl) {
    const content = noteEl.innerHTML;
    const x = parseInt(noteEl.style.left);
    const y = parseInt(noteEl.style.top);
    const z = parseInt(noteEl.style.zIndex);
    const id = noteEl.dataset.id || null;

    apex.server.process("SAVE_NOTE", {
        x01: id,
        x02: content,
        x03: x,
        x04: y,
        x05: z
    }, {
        dataType: 'json',
        success: function(pData) {
            if (pData && pData.id) {
                noteEl.dataset.id = pData.id;
            }
        },
        error: function(jqXHR, textStatus, errorThrown) {
            console.error("Save failed: ", textStatus, errorThrown);
        }
    });
}

document.getElementById('btn-add-note').addEventListener('click', () => {
    createStickyNote();
});

function loadNotesFromDB() {
    apex.server.process("LOAD_NOTES", {}, {
        dataType: 'json',
        success: function(pData) {
            if (Array.isArray(pData)) {
                pData.forEach(note => {
                    createStickyNote(note.content, note.x, note.y, note.z, note.id);
                });
            }
        },
        error: function(jqXHR, textStatus, errorThrown) {
            console.error("Load failed: ", textStatus, errorThrown);
        }
    });
}

const sizeMap = {
    a4: {
        portrait: [595.28, 841.89],
        landscape: [841.89, 595.28]
    },
    a3: {
        portrait: [841.89, 1190.55],
        landscape: [1190.55, 841.89]
    },
    letter: {
        portrait: [612, 792],
        landscape: [792, 612]
    }
};

function updateCanvasSize() {
    const size = document.getElementById('page-size').value;
    const orientation = document.getElementById('orientation').value;
    const dims = sizeMap[size][orientation];
    const width = dims[0] * 1.333;
    const height = dims[1] * 1.333;

    const container = document.getElementById('note-container');
    const wrapper = document.getElementById('canvas-wrapper');

    container.style.width = width + 'px';
    container.style.height = height + 'px';
    wrapper.style.width = width + 40 + 'px';

    const notes = container.querySelectorAll('.sticky-note');
    notes.forEach(note => {
        let x = parseInt(note.style.left);
        let y = parseInt(note.style.top);
        const noteWidth = note.offsetWidth;
        const noteHeight = note.offsetHeight;

        if (x + noteWidth > width) {
            x = width - noteWidth - 10;
            note.style.left = (x < 0 ? 0 : x) + 'px';
        }
        if (y + noteHeight > height) {
            y = height - noteHeight - 10;
            note.style.top = (y < 0 ? 0 : y) + 'px';
        }
    });
}

document.getElementById('print-btn').addEventListener('click', () => {
    const size = document.getElementById('page-size').value;
    const orientation = document.getElementById('orientation').value;
    const dims = sizeMap[size][orientation];
    const container = document.getElementById('note-container');

    html2canvas(container, {
        scale: 2,
        useCORS: true
    }).then(canvas => {
        const imgData = canvas.toDataURL('image/png');
        const { jsPDF } = window.jspdf;
        const pdf = new jsPDF({
            orientation: orientation,
            unit: 'pt',
            format: size
        });
        const width = dims[0];
        const height = dims[1];
        pdf.addImage(imgData, 'PNG', 0, 0, width, height);
        pdf.save("sticky-board.pdf");
    });
});

function deleteNoteFromDB(noteId, onSuccessCallback) {
    apex.server.process("DELETE_NOTE", {
        x01: noteId
    }, {
        dataType: 'json',
        success: function(response) {
            if (response && response.status === 'success') {
                console.log("Note deleted from DB:", noteId);
                if (typeof onSuccessCallback === 'function') {
                    onSuccessCallback();
                }
            } else {
                console.warn("Unexpected delete response:", response);
                alert("Note might not have been deleted. Please refresh and check.");
            }
        },
        error: function(jqXHR, textStatus, errorThrown) {
            console.error("Delete failed:", textStatus, errorThrown);
            alert("Note deletion failed. Please try again.");
        }
    });
}

function applyNoteStyles() {
    const bgColor = document.getElementById('note-bg').value;
    const borderStyle = document.getElementById('note-border').value;
    const notes = document.querySelectorAll('.sticky-note');

    notes.forEach(note => {
        note.style.background = bgColor === 'none' ? 'transparent' : bgColor;
        note.style.border = borderStyle === 'none' ? 'none' : '1px solid black';
    });
}

document.getElementById('note-bg').addEventListener('change', applyNoteStyles);
document.getElementById('note-border').addEventListener('change', applyNoteStyles);
document.getElementById('page-size').addEventListener('change', updateCanvasSize);
document.getElementById('orientation').addEventListener('change', updateCanvasSize);

updateCanvasSize();
loadNotesFromDB();

Step 6: Include External JS Libraries

• Under Page → JavaScript → File URLs, add:

https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js
https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js

Step 7: Add CSS for Styling

Go to Page → CSS → Inline and paste this:

/* ===== Toolbar Styling ===== */
.toolbar-container {
  background: #f4f4f9;
  border-radius: 8px;
  padding: 10px 15px;
  margin-bottom: 15px;
  box-shadow: 0 2px 6px rgba(0, 0, 0, 0.1);
}

.toolbar-controls {
  display: flex;
  flex-wrap: wrap;
  align-items: center;
  gap: 10px;
}

.toolbar-controls label {
  margin-right: 4px;
  font-weight: bold;
}

.toolbar-controls select,
.toolbar-controls input[type="button"] {
  padding: 6px 10px;
  border-radius: 5px;
  border: 1px solid #ccc;
  font-size: 14px;
}

.action-btn {
  background-color: #0066cc;
  color: white;
  border: none;
  transition: background-color 0.3s ease;
}

.action-btn:hover {
  background-color: #004999;
  cursor: pointer;
}

/* ===== Trash Styling ===== */
#trash-can {
  width: 40px;
  height: 40px;
  background: url('https://cdn-icons-png.flaticon.com/512/3096/3096673.png') center center no-repeat;
  background-size: contain;
  border: 2px dashed red;
  border-radius: 8px;
  opacity: 0.8;
  flex-shrink: 0;
  transition: all 0.2s ease;
}

#trash-can.trash-hover {
  box-shadow: 0 0 12px 4px red;
}

/* ===== Canvas Responsiveness ===== */
#canvas-container {
  display: block;
  overflow: visible;
  padding: 10px;
}

#canvas-wrapper {
  max-width: 100%;
  overflow: visible;
}

#note-container {
  background-color: #fff;
  border: 2px solid #ccc;
  border-radius: 8px;
  width: 100%;
  min-height: 500px;
  max-width: 100%;
  position: relative;
  overflow: visible;
  box-shadow: 0 4px 10px rgba(0, 0, 0, 0.1);
}

/* ===== Sticky Notes ===== */
.sticky-note {
  font-family: 'Segoe UI', sans-serif;
  position: absolute;
  padding: 10px;
  min-width: 100px;
  min-height: 80px;
  border-radius: 5px;
  cursor: move;
  box-shadow: 2px 2px 5px rgba(0,0,0,0.2);
  background-color: #fff89c;
  border: 1px solid #999;
  z-index: 10;
  transition: transform 0.1s ease;
}

.sticky-note:hover {
  transform: scale(1.02);
}

.sticky-note:focus {
  outline: none;
  background-color: #fffaab;
}

/* Print-friendly styles */
@media print {
  body * {
    visibility: hidden;
  }

  #note-container, #note-container * {
    visibility: visible;
  }

  #note-container {
    position: absolute;
    left: 0;
    top: 0;
  }
}

/* ===== Responsive Breakpoint ===== */
@media (max-width: 768px) {
  .toolbar-controls {
    flex-direction: column;
    align-items: stretch;
  }

  #note-container {
    height: 500px !important;
  }
}

🎬 What’s Coming in Version 2

• Sticky note color selector per note
• Resize handles for notes
• Category grouping and filters
• Improved performance with virtual scroll
• Integration into a plugin region

💬 Final Thoughts

This sticky note board can become a core collaboration tool within your APEX application. Whether you're managing tasks, ideating features, or just capturing thoughts, this region helps visualize data in a fun and effective way.

Don’t forget to save your changes and test the export functionality. Drop your feedback and enhancements in the comments. 🚀