MySQL: Fulsh hosts in the MySQL database

 Solution:

Execute the below command to flush the connections

mysqladmin -u root -h hostname -P 3351 -p flush-hosts

DB: Upload or Download a file from Oracle Table

 1. Create a directory "DB_Files_test" under D:\SURESH\03_Working\ in Windows

2. Connect as SYSDBA and create a directory as DB_FILES 

SQL> CREATE OR REPLACE DIRECTORY DB_FILES As 'D:\SURESH\03_Working\DB_Files_test';

Directory created.

--Grant read, write access on the directory to the user which you want to perform upload & download file

SQL> grant read, write on directory db_files to test;

Grant succeeded.

3. Create a table POC_FILE_STORAGE to store the files as BLOB

CREATE TABLE POC_FILE_STORAGE (

file_name VARCHAR2 (1000),

file_content BLOB)

/

4. Create the below procedure to convert a file to BLOB

CREATE OR REPLACE FUNCTION FILE_TO_BLOB(p_file_name VARCHAR2) RETURN BLOB AS

destination_location BLOB := empty_blob();

source_location BFILE := BFILENAME('DB_FILES', p_file_name);

BEGIN

DBMS_LOB.OPEN(source_location, DBMS_LOB.LOB_READONLY);

DBMS_LOB.CREATETEMPORARY(

lob_loc => destination_location

, cache => true

, dur => dbms_lob.session);

DBMS_LOB.OPEN(destination_location, DBMS_LOB.LOB_READWRITE);

DBMS_LOB.LOADFROMFILE(

dest_lob => destination_location

, src_lob => source_location

, amount => DBMS_LOB.getLength(source_location));

DBMS_LOB.CLOSE(destination_location);

DBMS_LOB.CLOSE(source_location);

RETURN destination_location;

END FILE_TO_BLOB;

/

5. Use Below PL/SQL block to store the file in the POC_FILE_STORAGE tables

DECLARE

v_blob BLOB;

BEGIN

v_blob := FILE_TO_BLOB ('OCA.pdf');

INSERT INTO POC_FILE_STORAGE VALUES ('OCA.pdf', v_blob);

COMMIT;

END;

/

6. Use Below SQL to list the records in the table POC_FILE_STORAGE table

SQL> col file_name for a20

SQL> set lines 200

SQL> select * from POC_FILE_STORAGE

FILE_NAME            FILE_CONTENT

-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------

OCA.pdf              255044462D312E360D25E2E3CFD30D0A363935342030206F626A0D3C3C2F46696C7465722F466C6174654465636F64652F466972737420313733372F4C656E67746820343933382F4E203136372F5479

7. Create the below procedure to convert BLOB to a file:

CREATE OR REPLACE PROCEDURE blob_to_file (i_dir    IN VARCHAR2,

                                          i_file   IN VARCHAR2,

                                          i_blob   IN BLOB)

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 (i_blob);

   l_file :=

      UTL_FILE.fopen (i_dir,i_file,'WB',32767);

   WHILE l_pos < l_blob_len

   LOOP

      DBMS_LOB.read (i_blob,

                     l_amount,

                     l_pos,

                     l_buffer);

      UTL_FILE.put_raw (l_file, l_buffer, TRUE);

      l_pos := l_pos + l_amount;

   END LOOP;

   UTL_FILE.fclose (l_file);

EXCEPTION

   WHEN OTHERS

   THEN

      IF UTL_FILE.is_open (l_file)

      THEN

         UTL_FILE.fclose (l_file);

      END IF;

      RAISE;

END blob_to_file;

/

8. Use Below PL/SQL Block to download the file 

DECLARE

   v_blob   BLOB;

BEGIN

  select FILE_CONTENT into v_blob from POC_FILE_STORAGE where FILE_NAME='OCA.pdf';

  blob_to_file ('DB_FILES', 'OCA.pdf', v_blob);

END;

/