Compress Blob Data

CREATE OR REPLACE FUNCTION  Fnc_Compress_Blob_Data
                                                               (pData in blob ,
                                                                pQuality in number default 6 )
                                                            -- pQuality value may be 1 to 9, but 6 is standard.
RETURN blob
IS

BEGIN

Return utl_compress.lz_compress(pData,pQuality);

END Fnc_Compress_Blob_Data;
/

Create an Img file from flat file image(multiple image)

CREATE USER TEST_USER IDENTIFIED BY TEST_USER;

GRANT DBA TO TEST_USER;

CONN TEST_USER/TEST_USER;

CREATE OR REPLACE DIRECTORY MY_DIR AS 'C:\temp\';
-- Img file will be create into "C:\temp\" directory.

CREATE OR REPLACE DIRECTORY IMAGE_DIR AS 'C:\image\';
-- Assume that your image into "C:\image\" directory.

CREATE OR REPLACE Procedure Img_File_Gen_From_Flat_File
                                (p_Source_Dir in varchar2 default 'IMAGE_DIR',
                                 p_Image_Name in varchar2,  -- Perticular Image_Name only.
                                Out_Img_Name in varchar2)   -- A File Name without space
is
v_blob blob;
v_data_length number;
v_chunk constant number := 32766; -- maximum chunk size
v_offset number:=1;
OutPutFile utl_file.file_type;
vFile_name varchar2(1000):= Out_Img_Name||'.img';
vData raw(32767);


begin

OutPutFile := utl_file.fopen ('MY_DIR', vFile_name, 'ab', v_chunk);
-- 'ab' value for Write append, u can change as ur required.


--"Dpr_fileToBlob" is User defined procedure  for this procedure Click Here
 Dpr_fileToBlob(Fname => p_Image_Name,
                          Fdir => p_Source_Dir,
                          OutBlob => v_blob);

v_offset := 1;
v_data_length := DBMS_LOB.getlength (v_blob);

Loop
Exit When v_offset > v_data_length;

vData := DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset);
utl_file.put_raw (OutPutFile,vData , true);
v_offset := v_offset + v_chunk;

End Loop;

--End loop;

utl_file.fflush(OutPutFile);
utl_file.fclose_all;



Exception
when others then

utl_file.fflush(OutPutFile);
utl_file.fclose_all;

End Img_File_Gen_From_Flat_File;
/

How to Create a DB Link between two Databases(ORACLE)

TNS_NAME must be create between DataBase_1(Loacl) and DataBase_2(Remote) (If already have then no need)

Conn : DataBase_1

create database link "MY_DB_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM"
connect to User_Name -- User_Name of DataBase_2
identified by Password  -- Password of DataBase_2
using 'DataBase_2';      -- TNS Name Alias or TNS Name


select *
from table_name@MY_DB_LINK

NB: The following syntax is not supported for LOBs:

SELECT lobcol FROM table1@MY_DB_LINK;

INSERT INTO lobtable
SELECT type1.lobattr
FROM table1@MY_DB_LINK;

SELECT DBMS_LOB.getlength(lobcol) FROM table1@MY_DB_LINK;

(This statement produces error: ORA-22992 cannot use LOB locators selected from remote tables.)

==========================================================================

========================================================================== 

However, the following statement is not supported:

• CREATE TABLE AS SELECT dbms_lob.substr(clob_col) from tab@dbs2;
•  Clusters cannot contain LOBs, either as key or non-key columns. This produces error, ORA-02335: invalid datatype for cluster column.
• You cannot create a VARRAY of LOBs. This produces error, ORA-02348: cannot create VARRAY column with embedded LOB.
• You cannot specify LOB columns in the ORDER BY clause of a query, or in the GROUP BY clause of a query or in an aggregate function. This produces error, ORA-00932: inconsistent datatypes.
• You cannot specify a LOB column in a SELECT... DISTINCT or SELECT... UNIQUE statement or in a join. However, you can specify a LOB attribute of an object type column in a SELECT... DISTINCT statement or in a query that uses the UNION or MINUS set operator if the column's object type has a MAP or ORDER function defined on it.
• You cannot specify an NCLOB as an attribute of an object type when creating a table. However, you can specify NCLOB parameters in methods.
• You cannot specify LOB columns in ANALYZE... COMPUTE or ANALYZE... ESTIMATE statements.
• You cannot define an UPDATE DML trigger on a LOB column.
• You cannot specify a LOB as a primary key column.
• You cannot specify a LOB column as part of an index key. However, you can specify a LOB column in the function of a function-based index or in the indextype specification of a domain index. In addition, Oracle Text lets you define an index on a CLOB column.

Create a Calander with sql

CREATE OR REPLACE Procedure Prc_Calander ( p_month in   varchar2 default to_char(sysdate,'MM'),
                                                                                 p_year    in   varchar2 default to_char(sysdate,'YYYY'),
                                                                                 p_Data   out varchar2
                                                                                ) IS
v_Line varchar2(2000);

Begin

For i in (select Nop, Line
from (
With
-- days: 1 line per week day
days as ( select level day from dual connect by level <= 7 ),
-- weeks: 1 line per possible week in a month
weeks as ( select level-1 week from dual connect by level <= 6 ),
-- mdays: each day of the month within each week
mdays as (
select week, weekday,
case
when day > to_char(last_day(to_date(p_month||'/'||p_year,'MM/YYYY')),'DD')
then ' '
when day <= 0 then ' '
else to_char(day,'99')
end monthday
from ( select week, day weekday,
7*week+day-to_char(to_date(p_month||'/'||p_year,'MM/YYYY'),'D')+1 day
from weeks, days
)
)
-- Display blank line
select 0 nop, null line from dual
union all
-- Display Month title
select 1 nop,
to_char(to_date(p_month||'/'||p_year,'MM/YYYY'),' FMMonth YYYY') line
from dual
union all
-- Display blank line
select 2 nop, null line from dual
union all
-- Display week day name
select 3 nop,
sys_connect_by_path(substr(to_char(trunc(sysdate,'D')+day-1,'Day'),
1,3),' ') line
from days
where day = 7
connect by prior day = day-1
start with day = 1
union all
-- Display each week
select 4+week nop, replace(sys_connect_by_path(monthday,'/'), '/', ' ') line
from mdays
where weekday = 7
connect by prior week = week and prior weekday = weekday-1
start with weekday = 1)
)
Loop

v_Line := v_Line||chr(10)||i.Line;

End Loop;

p_Data := v_Line;

Exception
When others then null;
End Prc_Calander;
/

Create a img file from multiple tif/jpg/bmp or any other file

CREATE OR REPLACE Procedure Img_File_Generation
is
v_blob                                           blob;
v_data_length                                Number;
v_chunk                                        CONSTANT NUMBER := 32767; -- maximum chunk size
v_offset                                         Number:=1;
OutPutFile                                     utl_file.file_type;

begin

OutPutFile := utl_file.fopen ('MY_DIR', 'test.img', 'ab', v_chunk);

For i in (select image_Name
From Table_Name
)
Loop

v_offset := 1;
v_blob := i.image_name;
v_data_length := DBMS_LOB.getlength (v_blob);

Loop
Exit When v_offset > v_data_length;

utl_file.put_raw (OutPutFile, DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset), true);
v_offset := v_offset + v_chunk;

End Loop;


End loop;

utl_file.fflush(OutPutFile);
utl_file.fclose_all;


Exception
when others then

utl_file.fflush(OutPutFile);
utl_file.fclose_all;

End Img_File_Generation;
/