Monthly Archives: April 2014

Technical

Experince on Buying SSD

Leave a comment

Well, Well, everyone likes their computer to be really really fast. 2 years back i just bought Dell N5110 laptop with Core i7 , 6GB RAM, 1 GB G-force graphics card and 720GB 5400 RPM HDD . Laptop is good with configuration wise but still bit of slow. Booting computer would take like 4-5 mins and opening application is even slow, i tried to format the computer but after 2-3 weeks with all necessary software installation, things again starts to get slow 🙁 poor me. After lots of try, finally i decided to buy SSD hard disk which is expensive but i managed to buy Kingston SSD 120GB (SV300S37A 450MB/s Read and Write) which cost me like 2790 Baht (87$) . Now its been like 2 month and computer start ups in 10sec and everything is fast fast. Now I can say one this one my best investment. so have you ever thought to upgrade your computer? think about SSD as well. i can say you wont regret it 🙂

All3_SV300S37A[1]

 

And here is my Kingston Benchmark:

SSD_BenchMark

Oracle

Load File into Database as Blob

Leave a comment

You can find lots of procedure or steps to load file from filesystem to database.
one of the easy method is to make a function.
Sample of function:-
CREATE OR REPLACE FUNCTION FN_FILE_BLOB(V_FILENAME VARCHAR2, V_DIRECTORY VARCHAR2) RETURN BLOB
IS
src_loc BFILE;
dest_loc BLOB:=empty_blob();
BEGIN
SRC_LOC := BFILENAME(V_DIRECTORY, V_FILENAME);
DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
DBMS_LOB.CREATETEMPORARY(lob_loc => dest_loc, cache => true , dur => dbms_lob.session);
DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(dest_lob => dest_loc, src_lob => src_loc , amount => DBMS_LOB.getLength(src_loc));
DBMS_LOB.CLOSE(src_loc);
DBMS_LOB.CLOSE(dest_loc);
RETURN dest_loc;
EXCEPTION
WHEN Others THEN
Dbms_Output.Put_Line(SQLERRM);
RETURN NULL;
END;
/

Usage: SELECT FN_FILE_BLOB('image1.jpg','DUMP_DIR') FROM DUAL;

Oracle

Convert Blob into Insert Statment

1 Comment

There are many ways to copy Blob column from one server to Another like,

1. Generate file from blob in source server, and import file into destination server using Tools like SQL developer, TOAD.

2. Using DBLink,
INSERT INTO BLOB_TEST SELECT BLOB_COL1 FROM TEST_BLOB@SOURCEDB;
3.  I have recently discovered, you can make dynamic insert statement and run as normal insert statement in destination server.

Example here: sample_blob

Scripts to make dynamic script can be written as

Set serveroutput on
DECLARE
M BLOB;
n NUMBER:=1;
c blob;
l_limit NUMBER:=4000;
vm RAW(32767);
BEGIN
Dbms_Output.Put_Line('DECLARE
c BLOB;
BEGIN
dbms_lob.createtemporary(c,true);');
SELECT  BLOB_COL INTO M FROM TEST_BLOB;
--Dbms_Output.Put_Line(Length(M));
while (n+l_limit<=length(m)) loop
     vm:=(dbms_lob.substr(M,l_limit,n));
     Dbms_Output.Put_Line('dbms_lob.writeappend(c,'||Length(vm)/2||','''||VM||''');');
     n:=n+l_limit;
END LOOP;
vm:=(dbms_lob.substr(M,length(m)-n+999,n));
Dbms_Output.Put_Line('dbms_lob.writeappend(c,'||Length(vm)/2||','''||VM||''');');
Dbms_Output.Put_Line('INSERT INTO blob_test VALUES (c,9);
END;
/');
END;
/

Download Here