Wednesday, June 20, 2012

How to save blob data to disk from oracle table using UTL_FILE ?

How to Insert multimedia data (image, video) in oracle table

How to insert image/pdf/multimedia or blob data to oracle database table from client machine without using database directory ?

1)Create Directory Where multimedia resides.

sql> create or replace directory temp as 'C:/dir_blob';

2)Grant read permission to the user who work with this directory.

sql> grant read on directory temp to test;

3)Create the Table which holds Lov object.

-- the storage table for the image file
dname VARCHAR2(30), -- directory name
sname VARCHAR2(30), -- subdirectory name
fname VARCHAR2(30), -- file name
iblob BLOB); -- image file

4) insert data to table see here

5) selecting table has multiple blob row

SQL> select * from atmutl.image ;


TEMP1 This is Image Phoenix-documents.pdf (HUGEBLOB)
TEMP This is pdf1 Phoenix-documents1.pdf (HUGEBLOB)
TEMP2 This is pdf2 Phoenix-documents2.pdf (HUGEBLOB)

6) create a procedure (pass parameter directory name)

CREATE OR REPLACE PROCEDURE atmutl.save_blob_jpg_to_disk_halim1 (
   p_directory   IN   VARCHAR2
   v_blob        BLOB;
   v_start       NUMBER             := 1;
   v_bytelen     NUMBER             := 2000;
   v_len         NUMBER;
   v_raw         RAW (2000);
   v_x           NUMBER;
   v_output      UTL_FILE.file_type;
   v_file_name   VARCHAR2 (200);

   FOR i IN (SELECT DBMS_LOB.getlength (iblob) v_len, fname v_file_name,
                    iblob v_blob
               FROM atmutl.image
              WHERE ROWNUM <= 500)
      v_output :=
           UTL_FILE.fopen (p_directory, i.v_file_name || '.PDF', 'wb', 32760);
      v_x := i.v_len;
      v_start := 1;
      v_bytelen := 2000;

      WHILE v_start < i.v_len AND v_bytelen > 0
         DBMS_LOB.READ (i.v_blob, v_bytelen, v_start, v_raw);
         UTL_FILE.put_raw (v_output, v_raw);
         UTL_FILE.fflush (v_output);
         v_start := v_start + v_bytelen;
         v_x := v_x - v_bytelen;

         IF v_x < 2000
            v_bytelen := v_x;
         END IF;
      END LOOP;

      UTL_FILE.fclose (v_output);
END save_blob_jpg_to_disk_halim1;

7) executing it   ----dirtory name is uppercase
EXEC atmutl.save_blob_jpg_to_disk_halim1('TEMP');

Announcing Certification of Oracle Database 11g R2 on Oracle Linux 6 and Redhat Linux 6

Oracle announce the general availability of the Oracle RDBMS Server 11gR2
Pre-Install RPM for Oracle Linux 6 x86_64 (64 Bit) architecture.

See more about

 Announcing: Oracle Database 11g R2 Certification on Oracle Linux 6
Public Yum Server

Oracle RDBMS Server 11gR2 Pre-Install RPM for Oracle Linux 6 x86_64 (64 Bit) architecture

Document library of oracle 11g Release 2 (11.2)

Sunday, June 17, 2012

Bangladesh Government primary School teacher viva Result 2012

Government primary School teacher viva Result 2012 will publish very soon.
plz keep your eyes on

How many Maximum datafiles can i create in oracle database (MAXDATAFILES , DB_FILES)

Today i have got a question regarding error ORA-00059: maximum number of DB_FILES exceeded and MAXDATAFILES , DB_FILES

so here i just want to explain it.

showing the the values of datafiles

SQL> select value from v$parameter where name = 'db_files'

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE'

[For oracle database 8i and above)

if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
so, you have to change the value onle DB_FILES init parameter if you want to create more datafiles from existing. you don't have to think about MAXDATAFILES 'S VALUE . I

The DB_FILES parameter limits the maximum number of datafile can exist in oracle database.We can't change this parameter dynamically.

MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command.

So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.