Managing LOB Objects
-----------------------
>CREATE TABLE PROD
(PR_ID NUMBER, PRD_DESC CLOB, PRD_IMAGE BLOB);
>CREATE TABLE EMP_BFILE AS
SELECT EMPNO,ENAME,JOB,SAL FROM EMP;
>ALTER TABLE EMP_BFILE
ADD MOVIE BFILE;
CREATE OR REPLACE PROCEDURE LOAD_EMP_BFILE
(P_FILE_LOC IN VARCHAR2) IS
V_FILE BFILE; V_FILENAME VARCHAR2(16);
CURSOR EMP_CURSOR IS SELECT EMPNO FROM EMP_BFILE FOR UPDATE;
BEGIN
FOR EMP_RECORD IN EMP_CURSOR LOOP
V_FILENAME:=EMP_RECORD.EMPNO||'.AVI';
V_FILE :=BFILENAME(P_FILE_LOC,V_FILENAME);
DBMS_LOB.FILEOPEN(V_FILE);
UPDATE EMP_BFILE SET MOVIE=V_FILE
WHERE CURRENT OF EMP_CURSOR;
DBMS_OUTPUT.PUT_LINE('LOADED FILE: '||V_FILENAME ||' SIZE: '||DBMS_LOB.GETLENGTH(V_FILE));
DBMS_LOB.FILECLOSE(V_FILE);
END LOOP;
END LOAD_EMP_BFILE;
/
SQL> EXEC LOAD_EMP_BFILE('UTL_DIR');
-------------------------
>ALTER TABLE EMP_BFILE
ADD MOVI_ADDRESS VARCHAR2(100);
CREATE OR REPLACE PROCEDURE LOAD_EMP_FILE_ADD(P_FILE_LOC IN VARCHAR2) IS
V_FILE BFILE;
V_FILENAME VARCHAR2(16);
CURSOR EMP_CURSOR IS SELECT * FROM EMP_BFILE FOR UPDATE;
FILE_LOC VARCHAR2(4000);
BEGIN
SELECT DIRECTORY_PATH INTO FILE_LOC FROM SYS.DBA_DIRECTORIES
WHERE DIRECTORY_NAME='UTL_DIR';
&D(FILE_LOC);
FOR EMP_RECORD IN EMP_CURSOR LOOP
V_FILENAME:=EMP_RECORD.EMPNO||'.AVI';
V_FILE :=BFILENAME(P_FILE_LOC,V_FILENAME);
UPDATE EMP_BFILE
SET MOVI_ADDRESS=FILE_LOC||'\'||V_FILENAME
WHERE CURRENT OF EMP_CURSOR;
DBMS_OUTPUT.PUT_LINE('LOADED FILE '||FILE_LOC||'\'||V_FILENAME);
END LOOP;
END LOAD_EMP_FILE_ADD;
/
SQL> EXECUTE LOAD_EMP_FILE_ADD('UTL_DIR');
---------------------
> ALTER TABLE EMP_BFILE
ADD IMAGE_ADDRESS VARCHAR2(100);
CREATE OR REPLACE PROCEDURE LOAD_EMP_FILE_ADD(P_FILE_LOC IN VARCHAR2,
FILE_TYPE IN VARCHAR2) IS
V_FILE BFILE;
V_FILENAME VARCHAR2(16);
CURSOR EMP_CURSOR IS SELECT * FROM EMP_BFILE FOR UPDATE;
FILE_LOC VARCHAR2(4000);
BEGIN
SELECT DIRECTORY_PATH INTO FILE_LOC FROM SYS.DBA_DIRECTORIES
WHERE DIRECTORY_NAME='UTL_DIR';
&D(FILE_LOC);
FOR EMP_RECORD IN EMP_CURSOR LOOP
IF FILE_TYPE='AVI' THEN
V_FILENAME:=EMP_RECORD.EMPNO||'.AVI';
V_FILE :=BFILENAME(P_FILE_LOC,V_FILENAME);
UPDATE EMP_BFILE
SET MOVI_ADDRESS=FILE_LOC||'\'||V_FILENAME
WHERE CURRENT OF EMP_CURSOR;
DBMS_OUTPUT.PUT_LINE('LOADED FILE '||FILE_LOC||'\'||V_FILENAME);
ELSIF FILE_TYPE='JPG' THEN
V_FILENAME:=EMP_RECORD.EMPNO||'.JPEG';
V_FILE :=BFILENAME(P_FILE_LOC,V_FILENAME);
UPDATE EMP_BFILE
SET IMAGE_ADDRESS=FILE_LOC||'\'||V_FILENAME
WHERE CURRENT OF EMP_CURSOR;
DBMS_OUTPUT.PUT_LINE('LOADED FILE '||FILE_LOC||'\'||V_FILENAME);
END IF;
END LOOP;
END LOAD_EMP_FILE_ADD;
/
SQL> EXECUTE LOAD_EMP_FILE_ADD('UTL_DIR','JPG');
SQL> CREATE TABLE EMP_CLOB AS
SELECT EMPNO,ENAME,JOB,HIREDATE,SAL
FROM EMP WHERE EMPNO IN(7788,7839);
SQL> ALTER TABLE EMP_CLOB ADD RESUME CLOB;
SQL> UPDATE EMP_CLOB
SET RESUME ='KASHIF MASOOD HASHMI
SUMMARY:
AN EXTENSIVE WORK EXPERIENCES IN THE AREA OF IT AND BUSINESS SOFTWARE AND
PROGRAMMING. EXCELLENT SYSTEM DEVELOPING, TESTING, MANAGEMENT AND DATABASE
ADMINISTRATION ABILITIES IN ORACLE 8, ALSO WORKING ABILITY IN ORACLE 8I
AND ORACLE9I. EXCELLENT PROGRAMMING ABILITIES IN ORACLE DEVELOPERS 2000
REL (1,2.1, 6, 6I) AND ORACLE 9I DS.
PERSONAL DETAILS:
FATHERS NAME AHMED SAEED HASHMI.
RELIGION ISLAM.
DATE OF BIRTH JULY 11TH 1973.
PLACE OF BIRTH KARACHI.
NATIONALITY PAKISTANI.
N.I.C. NO. 42201-5142334-3
MARTIAL STATUS MARRIED.
MOBILE # 0300-9826153.(CURRENTLY USED)
MAILING AND CURRENT C/O. GHULAM ISHAQUE.
ADDRESS A-183, 1ST FLOOR, QASR -E- KHURSHEED,
KHUDADAD COLONY,
KARACHI #05, PAKISTAN. POSTAL CODE. 74800.
EMAIL CAPT_KASHIF73@YAHOO.COM
EXPERIENCE:
COMPANY. M/S. DYNAMIC DEVELOPERS.
HIRING DATE. AUGUST 2000 TO-DATE.
DESIGNATION. PROGRAMMER.
RESPONSIBILITIES. (DEVELOPING SOFTWARE IN DEVELOPER REL (1,2, 6,6I)
USING ORACLE DATABASE (7.X, 8.X, 9I.)
COMPANY. M/S. SOFTEES (PVT.) LTD.
HIRING DATE. MAY 1999 TO JULY 2000.
DESIGNATION. COMPUTER PROGRAMMER.
RESPONSIBILITIES. DEVELOPED SALES AND PURCHASE CONTROLLING SYSTEM.
ALSO PARTICIPATE IN DIFFERENT PROJECT AS A PROGRAMMER.
COMPANY. M/S. RASHID ELECTRONIC.
HIRING DATE. FEB 1998 TO FEB 1999.
DESIGNATION. JUNIOR OFFICER.
RESPONSIBILITIES. CHANGES IN PREVIOUS PROGRAM AND MAKING NEW REPORTS.
TAKE THE PROPER BACKUP OF IMPORTANT FILES AFTER
CLOSING.
COMPANY. M/S. INTERNATIONAL FOUNDATION AND GARMENTS (PVT.) LTD.
HIRING DATE. NOV 1993 TO JAN 1998.
DESIGNATION. JUNIOR OFFICER.
RESPONSIBILITIES. CHANGES IN PREVIOUS PROGRAM AND MAKING NEW REPORTS.
TAKE THE PROPER BACKUP OF IMPORTANT FILES AFTER
CLOSING. COMMUNICATE WITH OTHER DEPARTMENTS.
QUALIFICATION:
* ACADEMIC QUALIFICATION:
BACHELOR DEGREE BACHELOR OF COMMERCE FROM UNIVERSITY OF KARACHI.
* TECHNICAL QUALIFICATION:
CATEGORY NAME ORACLE PROFESSIONAL PROGRAM.
INSTITUTE. ORASOFT EDUCATION CENTER.
SKILLS. SQL/PL SQL. , DEVELOPER 2000. (RELEASE 2.1).
DATA BASE ADMINISTRATION. (ORACLE 8)
BACKUP AND RECOVERY. (ORACLE 8)
PERFORMANCE AND TUNING. (ORACLE 8)
NETWORK ADMINISTRATION. (ORACLE 8)
ERWIN.3.0 (CASE TOOL)
CATEGORY NAME DIPLOMA IN COMPUTER SCIENCE.
INSTITUTE. PETROMAN TRAINING INSTITUTE.
SKILLS. DBASE III+.
FOX PRO (DOS AND WINDOWS VERSION)
BASIC, WORD STAR, LOTUS 123,
ELECTRONIC DATA PROCESSING.
CATEGORY NAME SIX MONTHS MODULE COMPUTER SCIENCE.
INSTITUTE. PETROMAN TRAINING INSTITUTE.
SKILLS. DBASE III+.
BASIC, WORD STAR,
ELECTRONIC DATA PROCESSING.
REFERENCE:
AVAILABLE ON REQUEST.'
WHERE EMPNO=7839
/
SQL> SELECT RESUME FROM EMP_CLOB WHERE RESUME IS NOT NULL;
RESUME
--------------------------------------------------------------------------------
KASHIF MASOOD HASHMI
SUMMARY:
AN EXTENSIVE WORK EXPERIENCES IN THE AREA OF I
SQL> SHOW LONG
long 80
SQL> SET LONG 4000
SQL> SELECT RESUME FROM EMP_CLOB WHERE RESUME IS NOT NULL;
Storing A Word Document In Table
1. Logon As Sys User
2. @Mydocs.Sql
3. Set Serveroutput On
4. Exec Mydocs.Doc_Dir_Setup
5. Exec Mydocs.List(‘Your Document Here.Doc');
6. Exec Mydocs.Load(‘Your Document Here.Doc', 1);
7. Exec Mydocs.Search(‘Search Pattern', 1);
Create Table My_Docs
(Doc_Id Number,
Bfile_Loc Bfile,
Doc_Title Varchar2(255),
Doc_Blob Blob Default Empty_Blob() );
--Create A Sql File Mydocs.Sql
Create Or Replace Package
Mydocs
As
Procedure Doc_Dir_Setup;
Procedure List (In_Doc In Varchar2);
Procedure Load (In_Doc In Varchar2, In_Id In Number);
Procedure Search (In_Search In Varchar2, In_Id In Number);
End Mydocs;
/
Create Or Replace Package Body
Mydocs
As
Vexists Boolean;
Vfile_Length Number;
Vblocksize Number;
Procedure Doc_Dir_Setup Is
Begin
Execute Immediate
'Create Directory Doc_Dir As'||'''"C:\Oracle"''';
End Doc_Dir_Setup;
Procedure List (In_Doc In Varchar2) Is
Begin
Utl_File.Fgetattr('Doc_Dir',
In_Doc,
Vexists,
Vfile_Length,
Vblocksize);
If Vexists Then
Dbms_Output.Put_Line(In_Doc||' '||Vfile_Length);
End If;
End List;
Procedure Load (In_Doc In Varchar2,
In_Id In Number) Is
Temp_Blob Blob := Empty_Blob();
Bfile_Loc Bfile;
Bytes_To_Load Integer := 4294967295;
Begin
Bfile_Loc := Bfilename('Doc_Dir', In_Doc);
Insert Into My_Docs (Doc_Id, Bfile_Loc, Doc_Title)
Values (In_Id, Bfile_Loc, In_Doc);
Select Doc_Blob Into Temp_Blob
From My_Docs Where Doc_Id = In_Id
For Update;
Dbms_Lob.Open(Bfile_Loc, Dbms_Lob.Lob_Readonly);
Dbms_Lob.Open(Temp_Blob, Dbms_Lob.Lob_Readwrite);
Dbms_Lob.Loadfromfile(Temp_Blob, Bfile_Loc, Bytes_To_Load);
Dbms_Lob.Close(Temp_Blob);
Dbms_Lob.Close(Bfile_Loc);
Commit;
End Load;
Procedure Search (In_Search Varchar2,
In_Id Number) Is
Lob_Doc Blob;
Pattern Varchar2(30);
Position Integer := 0;
Offset Integer := 1;
Occurrence Integer := 1;
Begin
Pattern := Utl_Raw.Cast_To_Raw(In_Search);
Select Doc_Blob Into Lob_Doc
From My_Docs Where Doc_Id = In_Id;
Dbms_Lob.Open (Lob_Doc, Dbms_Lob.Lob_Readonly);
Position := Dbms_Lob.Instr(Lob_Doc, Pattern, Offset, Occurrence);
If Position = 0 Then
Dbms_Output.Put_Line('Pattern Not Found');
Else
Dbms_Output.Put_Line('The Pattern Occurs At '|| Position);
End If;
Dbms_Lob.Close (Lob_Doc);
End Search;
Begin
Dbms_Output.Enable(1000000);
End Mydocs;
/
Easiest way to insert image into oracle database:
-------------------------------------------------
Method 1 (Standard way):
First we need to create directory on the database server to store files on it.
command:
Create or replace directory as TEMP_DIR '/temp'
Now copy all the files to that directory.
Sql>create table reader(reader_id varchar2(10),signature BLOB);
Sql>Insert into reader (reader_id) values ('01');
Create Procedure to update table with signature file.
CREATE OR REPLACE procedure user_signature(v_id IN number)
IS
BEGIN
DECLARE
v_bfile bfile := BFILENAME('TEMP_DIR', 'sign.JPG'); --first parameter in all caps, second is case sensitive
v_lob blob;
BEGIN
select signature into v_lob from reader where reader_id = v_id for update;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
DBMS_LOB.CLOSE(v_bfile);
commit;
END;
END;
Now run it with sql plus
SQL> exec user_signature('01')
or in TOAD (sql editor)
begin
user_signature('01');
end;
Method 2 (Easiest way): Need TOAD !!!
Select table from schema browser and click on data tab. Double click on signature data (HugeBlob) (Hint: Not all cap in BLOB data)
Now you'll see following window. See data length is displayed as zero.
At the top-left corner of window you can see "Load a File" icon. Just click on it and it will ask you for file location.
(Hint: now BLOB data dispalys as in all caps letters i.e HUGEBLOB)
SQL*Plus Shows BFILE(11g)
-------------------------
In Oracle Database 11g, SQL*Plus shows you the location of the file as a value in the column.
Let's see an example. First you have to create a directory to store the BFILE.
SQL> CREATE DIRECTORY DOC_DIR AS '/u01/oracle/docs';
Now, create the table.
SQL> CREATE TABLE DOCS
2 (
3 DOC_ID NUMBER(20),
4 DOC_FILE BFILE,
5 DOC_TYPE VARCHAR2(10)
6 );
Create the row.
SQL> INSERT INTO DOCS VALUES
2 (
3 1,
4 BFILENAME('DOC_DIR','METRIC_DAILY_REPORT.PDF'),
5 'PDF'
6 );
Now if you select this row in SQL*Plus:
SQL> col doc_file format a50
SQL> SELECT * FROM DOCS;
DOC_ID DOC_FILE DOC_TYPE
---------- --------------------------------------------------
1 bfilename('DOC_DIR', 'metric_daily_report.pdf') PDF
The output under the column DOC_FILE, which is a BFILE, shows the location of the file instead of erroring out.
-----------------------
>CREATE TABLE PROD
(PR_ID NUMBER, PRD_DESC CLOB, PRD_IMAGE BLOB);
>CREATE TABLE EMP_BFILE AS
SELECT EMPNO,ENAME,JOB,SAL FROM EMP;
>ALTER TABLE EMP_BFILE
ADD MOVIE BFILE;
CREATE OR REPLACE PROCEDURE LOAD_EMP_BFILE
(P_FILE_LOC IN VARCHAR2) IS
V_FILE BFILE; V_FILENAME VARCHAR2(16);
CURSOR EMP_CURSOR IS SELECT EMPNO FROM EMP_BFILE FOR UPDATE;
BEGIN
FOR EMP_RECORD IN EMP_CURSOR LOOP
V_FILENAME:=EMP_RECORD.EMPNO||'.AVI';
V_FILE :=BFILENAME(P_FILE_LOC,V_FILENAME);
DBMS_LOB.FILEOPEN(V_FILE);
UPDATE EMP_BFILE SET MOVIE=V_FILE
WHERE CURRENT OF EMP_CURSOR;
DBMS_OUTPUT.PUT_LINE('LOADED FILE: '||V_FILENAME ||' SIZE: '||DBMS_LOB.GETLENGTH(V_FILE));
DBMS_LOB.FILECLOSE(V_FILE);
END LOOP;
END LOAD_EMP_BFILE;
/
SQL> EXEC LOAD_EMP_BFILE('UTL_DIR');
-------------------------
>ALTER TABLE EMP_BFILE
ADD MOVI_ADDRESS VARCHAR2(100);
CREATE OR REPLACE PROCEDURE LOAD_EMP_FILE_ADD(P_FILE_LOC IN VARCHAR2) IS
V_FILE BFILE;
V_FILENAME VARCHAR2(16);
CURSOR EMP_CURSOR IS SELECT * FROM EMP_BFILE FOR UPDATE;
FILE_LOC VARCHAR2(4000);
BEGIN
SELECT DIRECTORY_PATH INTO FILE_LOC FROM SYS.DBA_DIRECTORIES
WHERE DIRECTORY_NAME='UTL_DIR';
&D(FILE_LOC);
FOR EMP_RECORD IN EMP_CURSOR LOOP
V_FILENAME:=EMP_RECORD.EMPNO||'.AVI';
V_FILE :=BFILENAME(P_FILE_LOC,V_FILENAME);
UPDATE EMP_BFILE
SET MOVI_ADDRESS=FILE_LOC||'\'||V_FILENAME
WHERE CURRENT OF EMP_CURSOR;
DBMS_OUTPUT.PUT_LINE('LOADED FILE '||FILE_LOC||'\'||V_FILENAME);
END LOOP;
END LOAD_EMP_FILE_ADD;
/
SQL> EXECUTE LOAD_EMP_FILE_ADD('UTL_DIR');
---------------------
> ALTER TABLE EMP_BFILE
ADD IMAGE_ADDRESS VARCHAR2(100);
CREATE OR REPLACE PROCEDURE LOAD_EMP_FILE_ADD(P_FILE_LOC IN VARCHAR2,
FILE_TYPE IN VARCHAR2) IS
V_FILE BFILE;
V_FILENAME VARCHAR2(16);
CURSOR EMP_CURSOR IS SELECT * FROM EMP_BFILE FOR UPDATE;
FILE_LOC VARCHAR2(4000);
BEGIN
SELECT DIRECTORY_PATH INTO FILE_LOC FROM SYS.DBA_DIRECTORIES
WHERE DIRECTORY_NAME='UTL_DIR';
&D(FILE_LOC);
FOR EMP_RECORD IN EMP_CURSOR LOOP
IF FILE_TYPE='AVI' THEN
V_FILENAME:=EMP_RECORD.EMPNO||'.AVI';
V_FILE :=BFILENAME(P_FILE_LOC,V_FILENAME);
UPDATE EMP_BFILE
SET MOVI_ADDRESS=FILE_LOC||'\'||V_FILENAME
WHERE CURRENT OF EMP_CURSOR;
DBMS_OUTPUT.PUT_LINE('LOADED FILE '||FILE_LOC||'\'||V_FILENAME);
ELSIF FILE_TYPE='JPG' THEN
V_FILENAME:=EMP_RECORD.EMPNO||'.JPEG';
V_FILE :=BFILENAME(P_FILE_LOC,V_FILENAME);
UPDATE EMP_BFILE
SET IMAGE_ADDRESS=FILE_LOC||'\'||V_FILENAME
WHERE CURRENT OF EMP_CURSOR;
DBMS_OUTPUT.PUT_LINE('LOADED FILE '||FILE_LOC||'\'||V_FILENAME);
END IF;
END LOOP;
END LOAD_EMP_FILE_ADD;
/
SQL> EXECUTE LOAD_EMP_FILE_ADD('UTL_DIR','JPG');
SQL> CREATE TABLE EMP_CLOB AS
SELECT EMPNO,ENAME,JOB,HIREDATE,SAL
FROM EMP WHERE EMPNO IN(7788,7839);
SQL> ALTER TABLE EMP_CLOB ADD RESUME CLOB;
SQL> UPDATE EMP_CLOB
SET RESUME ='KASHIF MASOOD HASHMI
SUMMARY:
AN EXTENSIVE WORK EXPERIENCES IN THE AREA OF IT AND BUSINESS SOFTWARE AND
PROGRAMMING. EXCELLENT SYSTEM DEVELOPING, TESTING, MANAGEMENT AND DATABASE
ADMINISTRATION ABILITIES IN ORACLE 8, ALSO WORKING ABILITY IN ORACLE 8I
AND ORACLE9I. EXCELLENT PROGRAMMING ABILITIES IN ORACLE DEVELOPERS 2000
REL (1,2.1, 6, 6I) AND ORACLE 9I DS.
PERSONAL DETAILS:
FATHERS NAME AHMED SAEED HASHMI.
RELIGION ISLAM.
DATE OF BIRTH JULY 11TH 1973.
PLACE OF BIRTH KARACHI.
NATIONALITY PAKISTANI.
N.I.C. NO. 42201-5142334-3
MARTIAL STATUS MARRIED.
MOBILE # 0300-9826153.(CURRENTLY USED)
MAILING AND CURRENT C/O. GHULAM ISHAQUE.
ADDRESS A-183, 1ST FLOOR, QASR -E- KHURSHEED,
KHUDADAD COLONY,
KARACHI #05, PAKISTAN. POSTAL CODE. 74800.
EMAIL CAPT_KASHIF73@YAHOO.COM
EXPERIENCE:
COMPANY. M/S. DYNAMIC DEVELOPERS.
HIRING DATE. AUGUST 2000 TO-DATE.
DESIGNATION. PROGRAMMER.
RESPONSIBILITIES. (DEVELOPING SOFTWARE IN DEVELOPER REL (1,2, 6,6I)
USING ORACLE DATABASE (7.X, 8.X, 9I.)
COMPANY. M/S. SOFTEES (PVT.) LTD.
HIRING DATE. MAY 1999 TO JULY 2000.
DESIGNATION. COMPUTER PROGRAMMER.
RESPONSIBILITIES. DEVELOPED SALES AND PURCHASE CONTROLLING SYSTEM.
ALSO PARTICIPATE IN DIFFERENT PROJECT AS A PROGRAMMER.
COMPANY. M/S. RASHID ELECTRONIC.
HIRING DATE. FEB 1998 TO FEB 1999.
DESIGNATION. JUNIOR OFFICER.
RESPONSIBILITIES. CHANGES IN PREVIOUS PROGRAM AND MAKING NEW REPORTS.
TAKE THE PROPER BACKUP OF IMPORTANT FILES AFTER
CLOSING.
COMPANY. M/S. INTERNATIONAL FOUNDATION AND GARMENTS (PVT.) LTD.
HIRING DATE. NOV 1993 TO JAN 1998.
DESIGNATION. JUNIOR OFFICER.
RESPONSIBILITIES. CHANGES IN PREVIOUS PROGRAM AND MAKING NEW REPORTS.
TAKE THE PROPER BACKUP OF IMPORTANT FILES AFTER
CLOSING. COMMUNICATE WITH OTHER DEPARTMENTS.
QUALIFICATION:
* ACADEMIC QUALIFICATION:
BACHELOR DEGREE BACHELOR OF COMMERCE FROM UNIVERSITY OF KARACHI.
* TECHNICAL QUALIFICATION:
CATEGORY NAME ORACLE PROFESSIONAL PROGRAM.
INSTITUTE. ORASOFT EDUCATION CENTER.
SKILLS. SQL/PL SQL. , DEVELOPER 2000. (RELEASE 2.1).
DATA BASE ADMINISTRATION. (ORACLE 8)
BACKUP AND RECOVERY. (ORACLE 8)
PERFORMANCE AND TUNING. (ORACLE 8)
NETWORK ADMINISTRATION. (ORACLE 8)
ERWIN.3.0 (CASE TOOL)
CATEGORY NAME DIPLOMA IN COMPUTER SCIENCE.
INSTITUTE. PETROMAN TRAINING INSTITUTE.
SKILLS. DBASE III+.
FOX PRO (DOS AND WINDOWS VERSION)
BASIC, WORD STAR, LOTUS 123,
ELECTRONIC DATA PROCESSING.
CATEGORY NAME SIX MONTHS MODULE COMPUTER SCIENCE.
INSTITUTE. PETROMAN TRAINING INSTITUTE.
SKILLS. DBASE III+.
BASIC, WORD STAR,
ELECTRONIC DATA PROCESSING.
REFERENCE:
AVAILABLE ON REQUEST.'
WHERE EMPNO=7839
/
SQL> SELECT RESUME FROM EMP_CLOB WHERE RESUME IS NOT NULL;
RESUME
--------------------------------------------------------------------------------
KASHIF MASOOD HASHMI
SUMMARY:
AN EXTENSIVE WORK EXPERIENCES IN THE AREA OF I
SQL> SHOW LONG
long 80
SQL> SET LONG 4000
SQL> SELECT RESUME FROM EMP_CLOB WHERE RESUME IS NOT NULL;
Storing A Word Document In Table
1. Logon As Sys User
2. @Mydocs.Sql
3. Set Serveroutput On
4. Exec Mydocs.Doc_Dir_Setup
5. Exec Mydocs.List(‘Your Document Here.Doc');
6. Exec Mydocs.Load(‘Your Document Here.Doc', 1);
7. Exec Mydocs.Search(‘Search Pattern', 1);
Create Table My_Docs
(Doc_Id Number,
Bfile_Loc Bfile,
Doc_Title Varchar2(255),
Doc_Blob Blob Default Empty_Blob() );
--Create A Sql File Mydocs.Sql
Create Or Replace Package
Mydocs
As
Procedure Doc_Dir_Setup;
Procedure List (In_Doc In Varchar2);
Procedure Load (In_Doc In Varchar2, In_Id In Number);
Procedure Search (In_Search In Varchar2, In_Id In Number);
End Mydocs;
/
Create Or Replace Package Body
Mydocs
As
Vexists Boolean;
Vfile_Length Number;
Vblocksize Number;
Procedure Doc_Dir_Setup Is
Begin
Execute Immediate
'Create Directory Doc_Dir As'||'''"C:\Oracle"''';
End Doc_Dir_Setup;
Procedure List (In_Doc In Varchar2) Is
Begin
Utl_File.Fgetattr('Doc_Dir',
In_Doc,
Vexists,
Vfile_Length,
Vblocksize);
If Vexists Then
Dbms_Output.Put_Line(In_Doc||' '||Vfile_Length);
End If;
End List;
Procedure Load (In_Doc In Varchar2,
In_Id In Number) Is
Temp_Blob Blob := Empty_Blob();
Bfile_Loc Bfile;
Bytes_To_Load Integer := 4294967295;
Begin
Bfile_Loc := Bfilename('Doc_Dir', In_Doc);
Insert Into My_Docs (Doc_Id, Bfile_Loc, Doc_Title)
Values (In_Id, Bfile_Loc, In_Doc);
Select Doc_Blob Into Temp_Blob
From My_Docs Where Doc_Id = In_Id
For Update;
Dbms_Lob.Open(Bfile_Loc, Dbms_Lob.Lob_Readonly);
Dbms_Lob.Open(Temp_Blob, Dbms_Lob.Lob_Readwrite);
Dbms_Lob.Loadfromfile(Temp_Blob, Bfile_Loc, Bytes_To_Load);
Dbms_Lob.Close(Temp_Blob);
Dbms_Lob.Close(Bfile_Loc);
Commit;
End Load;
Procedure Search (In_Search Varchar2,
In_Id Number) Is
Lob_Doc Blob;
Pattern Varchar2(30);
Position Integer := 0;
Offset Integer := 1;
Occurrence Integer := 1;
Begin
Pattern := Utl_Raw.Cast_To_Raw(In_Search);
Select Doc_Blob Into Lob_Doc
From My_Docs Where Doc_Id = In_Id;
Dbms_Lob.Open (Lob_Doc, Dbms_Lob.Lob_Readonly);
Position := Dbms_Lob.Instr(Lob_Doc, Pattern, Offset, Occurrence);
If Position = 0 Then
Dbms_Output.Put_Line('Pattern Not Found');
Else
Dbms_Output.Put_Line('The Pattern Occurs At '|| Position);
End If;
Dbms_Lob.Close (Lob_Doc);
End Search;
Begin
Dbms_Output.Enable(1000000);
End Mydocs;
/
Easiest way to insert image into oracle database:
-------------------------------------------------
Method 1 (Standard way):
First we need to create directory on the database server to store files on it.
command:
Create or replace directory as TEMP_DIR '
Now copy all the files to that directory.
Sql>create table reader(reader_id varchar2(10),signature BLOB);
Sql>Insert into reader (reader_id) values ('01');
Create Procedure to update table with signature file.
CREATE OR REPLACE procedure user_signature(v_id IN number)
IS
BEGIN
DECLARE
v_bfile bfile := BFILENAME('TEMP_DIR', 'sign.JPG'); --first parameter in all caps, second is case sensitive
v_lob blob;
BEGIN
select signature into v_lob from reader where reader_id = v_id for update;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
DBMS_LOB.CLOSE(v_bfile);
commit;
END;
END;
Now run it with sql plus
SQL> exec user_signature('01')
or in TOAD (sql editor)
begin
user_signature('01');
end;
Method 2 (Easiest way): Need TOAD !!!
Select table from schema browser and click on data tab. Double click on signature data (HugeBlob) (Hint: Not all cap in BLOB data)
Now you'll see following window. See data length is displayed as zero.
At the top-left corner of window you can see "Load a File" icon. Just click on it and it will ask you for file location.
(Hint: now BLOB data dispalys as in all caps letters i.e HUGEBLOB)
SQL*Plus Shows BFILE(11g)
-------------------------
In Oracle Database 11g, SQL*Plus shows you the location of the file as a value in the column.
Let's see an example. First you have to create a directory to store the BFILE.
SQL> CREATE DIRECTORY DOC_DIR AS '/u01/oracle/docs';
Now, create the table.
SQL> CREATE TABLE DOCS
2 (
3 DOC_ID NUMBER(20),
4 DOC_FILE BFILE,
5 DOC_TYPE VARCHAR2(10)
6 );
Create the row.
SQL> INSERT INTO DOCS VALUES
2 (
3 1,
4 BFILENAME('DOC_DIR','METRIC_DAILY_REPORT.PDF'),
5 'PDF'
6 );
Now if you select this row in SQL*Plus:
SQL> col doc_file format a50
SQL> SELECT * FROM DOCS;
DOC_ID DOC_FILE DOC_TYPE
---------- --------------------------------------------------
1 bfilename('DOC_DIR', 'metric_daily_report.pdf') PDF
The output under the column DOC_FILE, which is a BFILE, shows the location of the file instead of erroring out.
No comments:
Post a Comment