PL/SQL

Blob File Move From DB to Directory

Introduction/ Issue:   CSV blob file to raw file move from database table blob column to database directory. Why we need to do / Cause of the issue:  We can move from blob to raw CSV file from database table blob column to database directory. How do we solve:  Step 1: Create one database procedure PROCEDURE P_FILE_TO_DBSER (p_blob      IN  BLOB, p_dir       IN  VARCHAR2, p_filename  IN  VARCHAR2) AS l_file      UTL_FILE.FILE_TYPE; l_buffer    RAW(32767); l_amount    BINARY_INTEGER := 32767; l_pos       INTEGER := 1; l_blob_len  INTEGER; BEGIN l_blob_len := DBMS_LOB.getlength(p_blob);   — Open the destination file. l_file := UTL_FILE.fopen(p_dir, p_filename,’wb’, 32767); — Read chunks of the BLOB and write them to the file until complete. WHILE l_pos <= l_blob_len LOOP DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer); UTL_FILE.put_raw(l_file, l_buffer, TRUE); l_pos := l_pos + l_amount; END LOOP;   — Close the file. UTL_FILE.fclose(l_file);  …

Read More

File Store From Client to DB

Introduction/ Issue:   CSV file move from client local machine to database table blob column. Why we need to do / Cause of the issue:  We can store the CSV file from client local machine to database table blob column. How do we solve:  Step 1: Create new table in database CREATE TABLE TB_FILE_UPLOAD (ID NUMBER, FILE_DATA BLOB); Step 2: Create new sequence in database CREATE SEQUENCE FILE_UPLOAD_SEQ START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 INCREMENT BY 1 NOCYCLE CACHE 20 NOORDER NOKEEP GLOBAL; Step 3: In oracle form builder create on button and write the below code in WHEN-BUTTON-PRESSED trigger. DECLARE v_seq       number; v_file_path varchar2(500) := ‘C:\test_file.csv’; v_bool          boolean; BEGIN SELECT FILE_UPLOAD_SEQ .NEXTVAL INTO v_seq FROM DUAL; INSERT INTO TB_FILE_UPLOAD (ID, file_data)              VALUES (v_seq, NULL); IF v_file_path IS NOT NULL THEN…

Read More

Understanding SQL Server Execution Plans: A Beginner’s Guide

SQL Server execution plans are valuable tools for developers and database administrators aiming to optimize query performance.

Read More

SQL Server Indexes: The Ultimate GPS for Your Data Journey

We all have noticed how some SQL Server queries feel like a luxury express train, zooming to their destination, while others resemble a sluggish car through traffic jams?

Read More

Conquering Primary Key Violation – SQL Server Replication

Transactional replication in SQL Server is a robust feature that allows you to replicate data from a primary database (publisher) to one or more secondary databases (subscribers). While this is…

Read More

Creating Dynamic SQL Generator in Oracle PL/SQL

Table of Contents Introduction Technologies and Tools Used Steps Conclusion 1. Introduction The Dynamic SQL Generator in Oracle PL/SQL allows you to dynamically construct SQL statements, providing flexibility and reusability…

Read More

Oracle APEX Application Availability Using PL/SQL

Overview : The objective is to enhance Oracle APEX Application Availability through the implementation of PL/SQL code, ensuring uninterrupted and reliable operation by monitoring, managing, and responding to potential factors…

Read More

How to Handle Client Secret Expiry in Azure Authentication for Oracle APEX

Overview : The primary objective of this content is to guide users on how to handle client secret expiry in Azure Authentication for Oracle APEX. Specifically, the content provides step-by-step…

Read More

Interatcive Organizational Charts in Oracle APEX

Overview: This blog talks about organization charts in Oracle Apex using third-party libraries or plugins since Oracle APEX does not have a built-in organization chart component. Technologies and Tools Used:…

Read More

Virtual Private Database (VPD) in Oracle APEX for securing data access

Overview: This blog talks about VPD (Virtual Private Database) in Oracle Apex, a feature that helps address security concerns by restricting access to data at the row level based on…

Read More