Ramesh Gnanasekaran - Oracle Consulting Services | USA | 99% Customer Retention

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); …

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…

Credit Card Format

Introduction/ Issue: Write the issue that you face or the issue that you want to provide a solution through this blog. Credit Card number will be formatting based on input…

Zip code Validation

Introduction/ Issue: Write the issue that you face or the issue that you want to provide a solution through this blog. Zip code will be validated based on input parameters…

Using Bulk Collect with FORALL

1. Overview This blog explains about, How to insert bulk records within a milli seconds by using Bulk Collect with FORALL. 2. Technologies and Tools Used The following technology has been used to achieve the same. Ø Oracle PLSQL 3. Use Case Create one procedure in database object. Run the procedure. 4. Architecture Oracle Database 18C PLSQL 5. Examples Step 1: Create one Procedure in Database object like below: CREATE OR REPLACE PROCEDURE increase_salary ( department_id_in IN employees.department_id%TYPE, increase_pct_in IN NUMBER) IS TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER; l_employee_ids employee_ids_t; l_eligible_ids employee_ids_t; l_eligible BOOLEAN; BEGIN SELECT employee_id BULK COLLECT INTO l_employee_ids FROM employees WHERE department_id = increase_salary.department_id_in; FOR indx IN 1 .. l_employee_ids.COUNT LOOP check_eligibility (l_employee_ids (indx),…

Using Webutil browse file from desktop to dir

1. Overview This blog explains about, How to Use Webutil browse file from desktop to directory . 2. Technologies and Tools Used The following technology has been used to achieve the same. Ø Oracle FORMS 3. Use Case Create the form like the below. Use the below code In load file “button” using “when-button-pressed” trigger. 4. Architecture Oracle FORMS FORM Builder 5. Examples Step 1: Create one form like below: Step 2: using the below code In load file “button” using “when-button-pressed” trigger. Code: DECLARE l_userhome VARCHAR2 (200) := webutil_clientinfo.get_system_property (‘user.home’) || ‘\Desktop’; l_filename VARCHAR2 (200) := NULL; l_bare_filename VARCHAR2 (200) := NULL; l_fileprefix VARCHAR2 (200) := NULL; l_serverfilename VARCHAR2 (200) := NULL; l_upload_path VARCHAR2 (200) := NULL; l_version_path VARCHAR2 (200) := NULL; l_option_grade_path VARCHAR2 (200) := NULL; l_size_curve_path VARCHAR2 (200) := NULL; l_filesize NUMBER; al_con alert; l_status BOOLEAN;…

Display Any Country’s Current Date and Time in Oracle Forms

1. Overview This blog explains about, how we can shows a text box to display the current date and time for the selected country in oracle forms. 2. Technologies and…

How to Generate Serial Number in Oracle Forms

1. Overview This blog explains about, how we can Generate Serial Number in Oracle Forms. 2. Technologies and Tools Used The following technology has been used to achieve the same.…

We take you from best practices to next practices of software implementation

Solutions that Ensure You Stay Ahead of Competition

Your Partner for Strategic Technology Solutions

Guiding Your Digital Transformation Journey

Posts by Ramesh Gnanasekaran