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;
/

Create or Delete a Mapp Network Drive

01. For Create a Mapp Network Drive:

  Command :   
  net use drive_letter: \\domain_name\share_folder /user:domain_user_name password 
  Example   :
  net use x: \\10.11.201.105\xml /user:hasan 123

02. For Delete A Mapp Network Drive:

 Command :  net use drive_letter: /delete
 Example   :  net use x: /delete

For Details Net Use Command : Click Here
All DOS Command                    : Click Here
Dos Command Link in PDF      : Click Here 

AutoFTP is an automated ftp client software for transferring files over the Internet.

AutoFTP is an automated ftp client software for transferring files over the Internet.

Benefits, Features

• Auto-Transfers: You can schedule auto-recurring transfers for any future date/time;
• Transfer Sets: You can select any number of files from any FTP site or your local computer, from different directories, to download or upload. You can schedule the upload/download for any future date/time.
• Flexible Templates: Results of time consuming tasks or repetitive processes can be saved into templates. You can save the following settings to templates: transfer sets (files for download or upload from any FTP site or Local computer), transfer sessions.
• AutoFTP can automatically dial, connect to the Internet, upload and/or download files and finally disconnect;
• You can specify in the Preferences that auto-transfers should be aborted if the connection is too slow (auto-transfer will be rescheduled automatically); This can save you connect time and money;
• AutoFTP uses a Windows File Explorer-like user interface with popup menus and drag-and-drop support so you will feel at home;
• AutoFTP can remain invisible while transferring files so it will not disturb your other work;
• AutoFTP Assistant will guide you through the process of uploading, downloading and scheduling auto-transfers;

Requirements

PC 286 CPU or better; 2 MB RAM; Color monitor; Windows 95, 98, Me, NT; Requires only 400 Kbytes of disk space.

Click Here to Download Auto Ftp.

Create an FTP Folder with Read Access but Not List Access

Create the FTP Folder

01.  Create a folder that you want the FTP service to point to.

02.  Right-click the folder, click Properties, and then click the Security tab. Grant Full Control permissions to only the Administrators group.

[NOTE: Remove the Everyone group if it is present.]

03.  Click Advanced, and then click Add to add a new rule.

04.  In the account selection list, double-click the Anonymous User account or the group that is used for FTP access.

05.  In the Apply Onto drop-down list, select Files Only.

06.  Click to select Allow for the following permissions:

Ø      List Folder/Read Data

Ø      Read Attributes

Ø      Read Extended Attributes

Ø      Read Permissions

07.  Click OK.

08.  Click Add to add another rule.

09.  Select the account that you selected in step 4.

10.  In the Apply Onto list, click to select This Folder only.

11.  Click to select Allow for the following permissions

(note that List permissions are not listed):

Ø      Create Files/Write Data

Ø      Create Folders/Append Data

Ø      Write Attributes

Ø      Write Extended Attributes

Ø      Read Permissions

12.  Click OK until you have closed all of the property windows.

Note If you apply these permissions to an existing folder or to existing files, you click to select the Reset permissions on all child objects and enable propagation of inheritable permissions check box before you click OK.

Bangla Font Configuration With Oracle

First Collect SolaimanLipi.ttf (Click here for download) Font from a Reliable Sourse.Install this font and follow below steps:

*** It will be better if u use Developer10g Rel-2 ( Version 10.1.2.0.2 - 32 Bit )
*** During database installation must be change Product Language by AL32UTF8

[Remember that, u must have configure JRE in Webutil Configuration
For JRE, must be incldue the followinng code into Formsweb.cfg file
(Path: DevHome\forms\server\)

###########################################################################
   # Page displayed to users to allow them to download Sun's Java Plugin.
   # Sun's Java Plugin is typically used for non-Windows clients.
   # (NOTE: you should check this page and possibly change the settings)
   jpi_download_page=/forms/java/jre-6u17-windows-i586-S.exe
   # Parameter related to the version of the Java Plugin
   jpi_classid=clsid:CAFEEFAC-0016-0017-FFFF-ABCDEFFEDCBA
   # Parameter related to the version of the Java Plugin
   jpi_codebase=/forms/java/jre-6u17-windows-i586-S.exe
   # Parameter related to the version of the Java Plugin
   jpi_mimetype=application/x-java-applet;jpi-version=1.6.0_17
   # EM config parameter
   # Set this to "1" to enable Enterprise Manager to track Forms processes
   em_mode=0
 #######################################################################
  
In the Key Configuration of formsweb.cfg file 
 replace baseHTMLjinitiator=webutiljini.htm by baseHTMLJInitiator=webutiljpi.htm

   archive=frmall.jar
   webUtilArchive=frmwebutil.jar,jacob.jar
   baseHTMLJInitiator=webutiljpi.htm

#######################################################################

For Download JRE(jre-6u17-windows-i586-s.exe) Click Here  
Classid : CAFEEFAC-0016-0017-FFFF-ABCDEFFEDCBA ]

1. Start>>Run>>regedit>>Database Home>> Edit NLS_LANG file by double click

Replace AMERICAN_AMERICA.WE8MSWIN1252 by AMERICAN_AMERICA.UTF8

2. Start>>Run>>regedit>>DeveloperSuit Home>> Edit NLS_LANG file by docuble click

Replace AMERICAN_AMERICA.WE8MSWIN1252 by AMERICAN_AMERICA.UTF8


3. For showing in report go to the path

..\...\DevSuiteHome\tools\common\uifont.ali

Write "SolaimanLipi" = "SolaimanLipi.ttf"
between [ PDF:Embed ] and [ PDF Subset ]


4. Developmnent machine and for app

..\...\DevSuiteHome\forms\java\oracle\forms\registry.dat

Go to default.fontMap.appFontnames line and append SolaimanLipi font name by the following.

default.fontMap.appFontnames=SolaimanLipi,.....



If you want to show default font with SolaimanLipi.(Not Mandatory) then

Find the lines default.fontMap.defaultFontname=Dialog
and replace by default.fontMap.defaultFontname=SolaimanLipi



5. Shutdown OC4J Instance once then again Start OC4J Instance.

[N.B: 01. Use Avro Keyboard (version: avrokeyboard_4.1.0) and Install SolaimanLipi font into your operating system's FONTS folder.

02. Go Start Menu >> Settings >> Control Panel >> Regional & Language Options >>
Then Select Languages Tab and Check Install files for complex script & right-to-left languages(Including Thai)
>> Ok(You have need Operating Systems CD & your system must be restart.)]

Download Avro Keyboard From The Following Link:
http://www.omicronlab.com/news/avro-keyboard-4.1.0-released.html

Restrict or Protect Oracle Forms Developer from SQL Injection.

For Developer6i:
FORMS60_RESTRICT_ENTER_QUERY=true/false

For Developer9i/10g:
Open Default.env file from the foollowing path
\forms90\server\default.env
and write FORMS90_RESTRICT_ENTER_QUERY=true/false
Must restart from service/oc4j.

Data Convert From a FILE to CLOB

CREATE OR REPLACE Procedure Dpr_fileToClob(Fname in VARCHAR2, Fdir in VARCHAR2, Outclob out CLOB)
IS

fclob CLOB;
theBFile BFILE;
num NUMBER :=0;
src_offset NUMBER :=1;
dest_offset NUMBER :=1;
lang_context NUMBER :=1;

BEGIN

dbms_lob.createtemporary(fclob,FALSE,DBMS_LOB.SESSION);

theBFile := BFileName(Fdir,Fname);

dbms_lob.fileOpen(theBFile);

dbms_lob.loadClobFromFile(dest_lob =>fclob,
src_bfile =>theBFile,
amount =>dbms_lob.getLength(theBFile),
dest_offset =>dest_offset,
src_offset =>src_offset,
bfile_csid =>0,
lang_context =>lang_context,
warning =>num
);

dbms_lob.fileClose(theBFile);

Outclob := fclob;

end;
/

Data Convert From a FILE to BLOB

CREATE OR REPLACE Procedure Dpr_fileToBlob(Fname in VARCHAR2, Fdir in VARCHAR2, OutBlob out BLOB)

IS

fblob BLOB;
theBFile BFILE;

Bsrc_offset NUMBER :=1;
Bdest_offset NUMBER :=1;

BEGIN

dbms_lob.createtemporary(fblob,FALSE,DBMS_LOB.SESSION);

theBFile := BFileName(Fdir,Fname);

dbms_lob.fileOpen(theBFile);

dbms_lob.loadblobfromfile(dest_lob => fblob ,
src_bfile => theBFile ,
amount => dbms_lob.getLength(theBFile),
dest_offset => Bdest_offset,
src_offset => Bsrc_offset
);

dbms_lob.fileClose(theBFile);

OutBlob := fblob;

End;
/

Data Convert From CLOB to BLOB

CREATE OR REPLACE Function Fnc_Clob_to_Blob (P_clob_in in clob)
Return blob

is
v_blob blob;
v_offset integer;
v_buffer_varchar varchar2(32000);
v_buffer_raw raw(32000);
v_buffer_size binary_integer := 32000;

Begin
--
If p_clob_in is null Then
return null;
End If;
--
DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
v_offset := 1;

FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_clob_in) / v_buffer_size)

Loop

dbms_lob.read(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);
v_buffer_raw := utl_raw.cast_to_raw(v_buffer_varchar);

dbms_lob.writeappend(v_blob, utl_raw.length(v_buffer_raw), v_buffer_raw);
v_offset := v_offset + v_buffer_size;

End Loop;

Return v_blob;

End Fnc_Clob_to_Blob;
/

Clear or Delete Htree Data form Htree View

PROCEDURE Prc_Clear_Htree_Data(P_Tree_item varchar2,
P_Search_String varchar2)
IS
/*
* P_Tree_item - Htree Item.
* P_Search_String - Tree Node Label that which you want to Delete/Clear.
* give the exact node label (Character must be same).
*
* This Procedure will clear or delete Tree View from your given
* search_string & child node.
*/
Htree Item;
DeleteNode FTREE.NODE;
ItemName Varchar2(30) :=p_Tree_item;
vSearchString Varchar2(200):=p_Search_String;

BEGIN
Htree := FIND_ITEM(ItemName);

DeleteNode := FTREE.FIND_TREE_NODE(htree,vSearchString,FTREE.FIND_NEXT ,
FTREE.NODE_LABEL,
FTREE.ROOT_NODE ,
FTREE.ROOT_NODE
);
IF NOT FTREE.ID_NULL(DeleteNode)
THEN FTREE.DELETE_TREE_NODE(Htree, DeleteNode);
END IF;
END;

Read XML data from a CLOB file which is already stored into Database

CREATE TABLE XML_LOAD_IN
(
FILE_ID VARCHAR2(13 BYTE),
FILE_NAME VARCHAR2(200 BYTE),
XML_CFILE CLOB,
);


ALTER TABLE XML_LOAD_IN ADD (PRIMARY KEY (FILE_ID));

--==========================================================================


CREATE TABLE BCR
(
"ItemsWithinBundleCount" NUMBER(4) NOT NULL,
"BundleTotalAmount" NUMBER(12) NOT NULL,
"MICRValidTotalAmount" NUMBER(12),
"ImagesWithinBundleCount" NUMBER(5),
"UserField" VARCHAR2(5 BYTE)
);

--==========================================================================

/*** We assume that a XML file is Stored into your table ***/

CREATE OR REPLACE procedure Dpr_Read_Xml_Data(p_file_id in varchar2)
is
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;

TYPE tab_type IS TABLE OF BCR%ROWTYPE;
t_tab tab_type := tab_type();

BEGIN

Begin

select xml_cfile
into l_clob
from xml_load_in
where file_id = p_file_id;


Exception
When no_data_found then
raise_application_error(-2001,'XML File Not Found.');
When others then null;
End;


l_parser := dbms_xmlparser.newParser;
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);

dbms_xmlparser.freeParser(l_parser);
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/OCE/CashLetter/Bundle/BundleControl');
/* Edit above xml node as your required */

FOR CUR_BCR IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
l_n := dbms_xmldom.item(l_nl, CUR_BCR);
t_tab.extend;

-- Use XPATH syntax to assign values to he elements of the collection.

dbms_xslprocessor.valueOf(l_n,'ItemsWithinBundleCount/text()' ,t_tab(t_tab.last)."ItemsWithinBundleCount" );
dbms_xslprocessor.valueOf(l_n,'BundleTotalAmount/text()' ,t_tab(t_tab.last)."BundleTotalAmount" );
dbms_xslprocessor.valueOf(l_n,'MICRValidTotalAmount/text()' ,t_tab(t_tab.last)."MICRValidTotalAmount" );
dbms_xslprocessor.valueOf(l_n,'ImagesWithinBundleCount/text()' ,t_tab(t_tab.last)."ImagesWithinBundleCount" );
dbms_xslprocessor.valueOf(l_n,'UserField/text()' ,t_tab(t_tab.last)."UserField" );
END LOOP;


FOR CUR_BCR IN t_tab.first .. t_tab.last LOOP

INSERT INTO BCR
("ItemsWithinBundleCount" ,
"BundleTotalAmount" ,
"MICRValidTotalAmount" ,
"ImagesWithinBundleCount" ,
"UserField"
)
VALUES
(t_tab(CUR_BCR)."ItemsWithinBundleCount" ,
t_tab(CUR_BCR)."BundleTotalAmount" ,
t_tab(CUR_BCR)."MICRValidTotalAmount" ,
t_tab(CUR_BCR)."ImagesWithinBundleCount" ,
t_tab(CUR_BCR)."UserField"
);

END LOOP;

COMMIT;

dbms_xmldom.freeDocument(l_doc);

EXCEPTION
WHEN OTHERS THEN
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
END;
/

Show Database Version

Create or Replace Function Fnc_Database_version Return char IS

v_data varchar2(1000);
ShowData varchar2(4000);
p_data varchar2(4000);

Begin

For i in (select banner
from sys.v_$version)
Loop
v_data := i.banner;
ShowData:= ShowData||v_data||chr(10);
End Loop;

p_data := 'Connected to :'||chr(10)||ShowData;
p_data := substr(p_data,1,length(p_data)-1);

Return p_data;

Exception
When others then null;
End;

Manual Ordering Block Data

PROCEDURE Prc_ordering_REF ( pBlock VARCHAR2, pClause VARCHAR2, pExec VARCHAR2 default 'NO') IS
vOrder_by VARCHAR2(5);
V_ORDER VARCHAR2(500);
begin
-- Checking whether pBlock is DB Block or not
if get_block_property(pBlock, query_data_source_type) = 'TABLE' then

-- Finding out " ASC" or " DESC" from the existing "null" or "1 ASC" order clause.
-- Set " DESC" if null


vOrder_by := nvl(substr(get_block_property(pBlock, order_by),2), ' a');

-- Assigning vice-versa
if Upper(vOrder_by) = ' A' then
vOrder_by := ' ASC';
elsif Upper(vOrder_by) = ' ASC' then
vOrder_by := ' DESC';
else
vOrder_by := ' ASC';
end if;

-- Setting order by clause to the supplied block
set_block_property (pBlock, order_by, pClause||vOrder_by);

if upper(pExec) = 'YES' then
go_block(pBlock);
:system.message_level := 5;
execute_query;
:system.message_level := 0;
end if;
set_block_property(pBlock, order_by, '1'||vOrder_by);
end if;
end;

Get Form Path Dynamically

function FORM_PATH return char is
vPath varchar2(200);
vPath_Len pls_integer;
vPath_Sep char(1);
begin
vPath := get_application_property(current_form);

if get_application_property(operating_system) in ('MSWINDOWS', 'MSWINDOWS32', 'WIN32COMMON') then
vPath_Sep := '\';
else
vPath_Sep := '/';
end if;

vPath_len := instr(vPath,vPath_Sep,-1);

if vPath_Len > 0 then
vPath := substr(vPath,1,vPath_Len);
else
vPath_Len := null;
end if;
return vPath;
end;

Create A Stop Watch with Sql

function fnc_hh_mm_ss (p_ss in number) return varchar2
is
v_ss number;
v_mm number;
v_hh number;
v_temp number;
show_hh varchar2(5);
show_mm varchar2(5);
show_ss varchar2(5);
p_show_time varchar2 (500);

begin

select decode (length(floor(floor(p_ss/60)/60)), 1 , ('0'||floor(floor(p_ss/60)/60)) , (floor(floor(p_ss/60)/60)))||':'||
decode (length(mod(floor(p_ss/60),60)) , 1 , ('0'||mod(floor(p_ss/60),60)) , (mod(floor(p_ss/60),60))) ||':'||
decode (length(mod(p_ss,60)) , 1 , ('0'||mod(p_ss,60)) , (mod(p_ss,60)))
into p_show_time
from dual;

return (p_show_time);

exception
when others then
p_show_time:='00:00:00';
return (p_show_time);
end;

WebUtil Package For WebUtil Configuration

CREATE OR REPLACE PACKAGE WEBUTIL_DB AUTHID CURRENT_USER AS

/*********************************************************************************\
* WebUtil_DB - Database functions used by the WebUtil_File_Transfer
* Package. These functions allow reading and writing direct
* to the specified BLOB in the database.
* The functions should not be called externally from WebUtil
*********************************************************************************
* Version 1.0.0
*********************************************************************************
* Change History
* DRMILLS 11/JAN/2003 - Creation
*
\*********************************************************************************/

FUNCTION OpenBlob(blobTable in VARCHAR2,
blobColumn in VARCHAR2,
blobWhere in VARCHAR2,
openMode in VARCHAR2,
chunkSize in PLS_INTEGER default null) return BOOLEAN;

FUNCTION CloseBlob(checksum in PLS_INTEGER) return BOOLEAN;

PROCEDURE WriteData(data in VARCHAR2);

FUNCTION ReadData return VARCHAR;

FUNCTION GetLastError return PLS_INTEGER;

FUNCTION GetSourceLength return PLS_INTEGER;

FUNCTION GetSourceChunks return PLS_INTEGER;

END WEBUTIL_DB;
/



CREATE OR REPLACE PACKAGE BODY WEBUTIL_DB AS
m_binaryData BLOB;
m_blobTable VARCHAR2(60);
m_blobColumn VARCHAR2(60);
m_blobWhere VARCHAR2(1024);
m_mode CHAR(1);
m_lastError PLS_INTEGER := 0;
m_sourceLength PLS_INTEGER := 0;
m_bytesRead PLS_INTEGER := 0;
MAX_READ_BYTES PLS_INTEGER := 4096;

-- internal Program Units
PROCEDURE Reset;

PROCEDURE Reset is
BEGIN
m_blobTable := null;
m_blobColumn := null;
m_blobWhere := null;
m_mode := null;
m_lastError := 0;
m_sourceLength := 0;
m_bytesRead := 0;
END Reset;


FUNCTION OpenBlob(blobTable in VARCHAR2, blobColumn in VARCHAR2, blobWhere in VARCHAR2, openMode in VARCHAR2, chunkSize PLS_INTEGER default null) return BOOLEAN is
result BOOLEAN := false;
stmtFetch VARCHAR2(2000);
hit PLS_INTEGER;
BEGIN
-- New transaction clean up
reset;

m_blobTable := blobTable;
m_blobColumn := blobColumn;
m_blobWhere := blobWhere;
m_mode := upper(openMode);

if chunkSize is not null then
if chunkSize > 16384 then
MAX_READ_BYTES := 16384;
else
MAX_READ_BYTES := chunkSize;
end if;
end if;

-- check the target row exists
stmtFetch := 'select count(*) from '||m_blobTable||' where '||m_blobWhere;
EXECUTE IMMEDIATE stmtFetch into hit;

if hit = 1 then
if m_mode = 'W' then
DBMS_LOB.CREATETEMPORARY(m_binaryData,false);
DBMS_LOB.OPEN(m_binaryData,DBMS_LOB.LOB_READWRITE);
m_sourceLength := 0;
result := true;
elsif m_mode = 'R' then
stmtFetch := 'select '||m_blobColumn||' from '||m_blobTable||' where '||m_blobWhere;
EXECUTE IMMEDIATE stmtFetch into m_binaryData;
if m_binaryData is not null then
m_sourceLength := dbms_lob.getlength(m_binaryData);
if m_sourceLength > 0 then
result := true;
else
m_lastError := 110;
end if;
else
m_lastError := 111;
end if;
else
m_lastError := 112;
end if; -- mode
else
-- too many rows
m_lastError := 113;
end if; -- Hit
return result;
END OpenBlob;

FUNCTION CloseBlob(checksum in PLS_INTEGER) return BOOLEAN is
sourceBlob BLOB;
stmtFetch VARCHAR2(2000);
stmtInit VARCHAR2(2000);
result BOOLEAN := false;
BEGIN
if m_mode = 'W' then
m_sourceLength := DBMS_LOB.GETLENGTH(m_binaryData);
end if;

-- checksum
if checksum = m_sourceLength then
if m_mode = 'W' then
-- get the locator to the table blob
stmtFetch := 'select '||m_blobColumn||' from '||m_blobTable||' where '||m_blobWhere||' for update';
EXECUTE IMMEDIATE stmtFetch into sourceBlob;

-- Check the blob has been initialised
-- and if it's not empty clear it out
if sourceBlob is null then
stmtInit := 'update '||m_blobTable||' set '||m_blobColumn||'=EMPTY_BLOB() where '||m_blobWhere;
EXECUTE IMMEDIATE stmtInit;
EXECUTE IMMEDIATE stmtFetch into sourceBlob;
elsif dbms_lob.getlength(sourceBlob) > 0 then
dbms_lob.TRIM(sourceBlob,0);
end if;
-- now replace the table data with the temp BLOB
DBMS_LOB.APPEND(sourceBlob,m_binaryData);
DBMS_LOB.CLOSE(m_binaryData);
result := true;
else
-- todo
null;
end if; --mode
else
m_lastError := 115;
end if; --checksum
return result;
END CloseBlob;

PROCEDURE WriteData(data in VARCHAR2) is
rawData raw(16384);
BEGIN
rawData := utl_encode.BASE64_DECODE(utl_raw.CAST_TO_RAW(data));
dbms_lob.WRITEAPPEND(m_binaryData, utl_raw.LENGTH(rawData), rawData);
END WriteData;


FUNCTION ReadData return VARCHAR is
rawData RAW(16384);
bytesToRead PLS_INTEGER;
BEGIN
bytesToRead := (m_sourceLength - m_bytesRead);
if bytesToRead > MAX_READ_BYTES then
bytesToRead := MAX_READ_BYTES;
end if;
DBMS_LOB.READ(m_binaryData, bytesToRead, (m_bytesRead + 1), rawData);
m_bytesRead := m_bytesRead + bytesToRead;
return UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(rawData));
END ReadData;

FUNCTION GetLastError return PLS_INTEGER is
BEGIN
return m_lastError;
END GetLastError;


FUNCTION GetSourceLength return PLS_INTEGER is
BEGIN
return m_sourceLength;
END GetSourceLength;

FUNCTION GetSourceChunks return PLS_INTEGER is
chunks PLS_INTEGER;
BEGIN
chunks := floor(m_sourceLength/MAX_READ_BYTES);
if mod(m_sourceLength,MAX_READ_BYTES) > 0 then
chunks := chunks+1;
end if;
return chunks;
END GetSourceChunks;

END;
/

Webutil Configuration for Developer10g

It is assumed that You install developer suite in drive D:\ and have webutil tools;


step-1
-----

paste webutil folder in the following path
D:\DevSuiteHome\forms90


step-2
------

Paste webutil.cfg,webutiljini.htm in the following path
D:\DevSuiteHome\forms90\server


step-3
------

Paste additional jar file in the following path
D:\DevSuiteHome\forms90\java


Step-4
------

open deafult.env file (path :>> D:\DevSuiteHome\forms90\server)

go to last line of file ( the line is:>> CLASSPATH=D:\DevSuiteHome\j2ee\OC4J_BI_Forms\applications\forms90app\forms90web\WEB-INF\lib\f90srv.jar;D:\DevSuiteHome\jlib\repository.jar;D:\DevSuiteHome\jlib\ldapjclnt9.jar;D:\DevSuiteHome\jlib\debugger.jar;D:\DevSuiteHome\jlib\ewt3.jar;D:\DevSuiteHome\jlib\share.jar;D:\DevSuiteHome\jlib\utj90.jar;D:\DevSuiteHome\jlib\zrclient.jar;D:\DevSuiteHome\reports\jlib\rwrun.jar)
Modify this line as your required jar files
:>> example
CLASSPATH=D:\DevSuiteHome\j2ee\OC4J_BI_Forms\applications\forms90app\forms90web\WEB-INF\lib\f90srv.jar;D:\DevSuiteHome\jlib\repository.jar;D:\DevSuiteHome\jlib\ldapjclnt9.jar;D:\DevSuiteHome\jlib\debugger.jar;D:\DevSuiteHome\jlib\ewt3.jar;D:\DevSuiteHome\jlib\share.jar;D:\DevSuiteHome\jlib\utj90.jar;D:\DevSuiteHome\jlib\zrclient.jar;D:\DevSuiteHome\reports\jlib\rwrun.jar;D:\DevSuiteHome\forms90\java\getclientinfo.jar;D:\DevSuiteHome\forms90\java\frmwebutil.jar;D:\DevSuiteHome\forms90\java\GetImageFileName.jar;D:\DevSuiteHome\forms90\java\images.jar;D:\DevSuiteHome\forms90\java\infobutton.jar;D:\DevSuiteHome\forms90\java\jacob.jar;D:\DevSuiteHome\forms90\java\rolloverbutton.jar


add following line in the end of file

#Webutil Config
WEBUTIL_CONFIG=D:\DevSuiteHome\forms90\server\webutil.cfg


Step-5
------

open forms90.conf file (path :>> D:\DevSuiteHome\forms90\server)

go to line where the following code already written
# Virtual path for runform.htm (used to run a form for testing purposes)
AliasMatch ^/forms90/html/(..*) "D:\DevSuiteHome/tools/web90/html/$1"

add following line after this
AliasMatch ^/forms90/webutil/(..*) "D:\DevSuiteHome/forms90/webutil/$1"

:>> example
# Virtual path for runform.htm (used to run a form for testing purposes)
AliasMatch ^/forms90/html/(..*) "D:\DevSuiteHome/tools/web90/html/$1"
AliasMatch ^/forms90/webutil/(..*) "D:\DevSuiteHome/forms90/webutil/$1"



Step-6a
------
1. Edit D:\DevSuiteHome\forms90\webutil\sign_webutil.bat file
as:
1. Go to Line
ECHO Generating a self signing certificate for key=%JAR_KEY%...
Next Line
edit Line in replace of previous address
D:\DevSuiteHome\jdk.........
2. Go to Line
ECHO Signing %1 using key=%JAR_KEY%...
Next Line
edit Line in replace of previous address
D:\DevSuiteHome\jdk.........

Step-6b
Sign additional jar file in the following way........

1.open cmd window
2.type d: and press enter
3.type cd DevSuiteHome\forms90\webutil and press enter
4.type sign_webutil D:\DevSuiteHome\forms90\java\rolloverbutton.jar and press enter

repeat line no. 4 as your required jar files



Step-7
------

Open formsweb.cfg file (path :>> D:\DevSuiteHome\forms90\server)
go to the end of file and then type the following line

[test_config]
form=D:\test\image_browser.fmx usesdi=yes
userid=user_id/pass@database_string
width=100%
height=100%
colorscheme=blue
pageTitle=Abc
imagebase=codebase
archive_jini=f90all_jinit.jar,rolloverbutton.jar,GetImageFileName.jar
webUtilArchive=frmwebutil.jar,jacob.jar
baseHTMLjinitiator=webutiljini.htm
splashscreen=abc_splash.gif
logo=abc_logo.gif
separateFrame=false

---End Configuration---


Now Call Your Configuration From Internet Explorer or Any Other Browser as below:

Ex:http://computer_name(or Ip_address):8889/forms90/f90servlet?config=test_config

Ex:http://abc_computer:8889/forms90/f90servlet?config=test_config

How to install a report server & configuration in Oracle Developer10g…?

Step-01:
========
Open your cmd command prompt & paste the following command. Edit report_server_name.
[Make sure Developer10g install of your computer with named

DevSuiteHome

.]

rwserver -install server = rep_server_name

Step-02:
========
Open cgicmd.dat (with wordpad or Notepad) file from
.../DevSuiteHome/reports/conf/ folder & write the following line into the last line of that file.

report_key : user_id=user_name/pass@database server=rep_server_name %*

Email Id Verification

CREATE OR REPLACE FUNCTION xx_check_email (l_user_name IN VARCHAR2)
RETURN VARCHAR2
IS
l_dot_pos NUMBER;
l_at_pos NUMBER;
l_str_length NUMBER;
BEGIN
l_dot_pos := INSTR (l_user_name, '.');
l_at_pos := INSTR (l_user_name, '@');
l_str_length := LENGTH (l_user_name);

IF ( (l_dot_pos = 0)
OR (l_at_pos = 0)
OR (l_dot_pos = l_at_pos + 1)
OR (l_at_pos = 1)
OR (l_at_pos = l_str_length)
OR (l_dot_pos = l_str_length)
)
THEN
RETURN 'FAILURE';
END IF;

IF INSTR (SUBSTR (l_user_name, l_at_pos), '.') = 0
THEN
RETURN 'FAILURE';
END IF;

RETURN 'SUCCESS';
END xx_check_email;


select INSTR (:l_user_name, '.'),
INSTR (:l_user_name, '@'),
LENGTH (:l_user_name)
from dual;


begin
if owa_pattern.match(p_email,'^\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}'||
'@\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}$') then
dbms_output.put_line('email válido : '||p_email);
return true;
else
dbms_output.put_line('email inválido : '||p_email);
return false;
end if;
end f_valida_email;

xml to Oracle

-- As SYS
CREATE DIRECTORY xml_dir AS 'c:\';
GRANT READ ON DIRECTORY xml_dir TO ;

-- As schema owner
CREATE TABLE EMP (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));

The source of the data is the emp.xml document. Extracting the data from the document involves several steps. First the XML document must be parsed and a DOMDocument created from it. Once the DOMDocument is created the parser is no longer needed so it's resources can be freed:
l_parser := dbms_xmlparser.newParser;

dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);

dbms_xmlparser.freeParser(l_parser);
Next the XPATH syntax is used to get a DOMNodeList containing all the EMP nodes:
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMP');
Once we have the DOMNodeList we can loop through it getting the values for each node in turn. The values are returned using the XPATH sytax and placed in a table collection:
l_n := dbms_xmldom.item(l_nl, cur_emp);
dbms_xslprocessor.valueOf(l_n,'EMPNO/text()',t_tab(t_tab.last).empno);
With all the data retrieved into the table collection the inserts can be performed. Putting it all together we get:

DECLARE
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
l_temp VARCHAR2(1000);

TYPE tab_type IS TABLE OF emp%ROWTYPE;
t_tab tab_type := tab_type();
BEGIN

l_bfile := BFileName('XML_DIR', 'emp.xml');
dbms_lob.createtemporary(l_clob, cache=>FALSE);
dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
dbms_lob.loadFromFile(dest_lob => l_clob,
src_lob => l_bfile,
amount => dbms_lob.getLength(l_bfile));
dbms_lob.close(l_bfile);

-- make sure implicit date conversions are performed correctly
dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');

-- Create a parser.
l_parser := dbms_xmlparser.newParser;

-- Parse the document and create a new DOM document.
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);

-- Free resources associated with the CLOB and Parser now they are no longer needed.
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);

-- Get a list of all the EMP nodes in the document using the XPATH syntax.
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMP');

-- Loop through the list and create a new record in a tble collection
-- for each EMP record.
FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
l_n := dbms_xmldom.item(l_nl, cur_emp);

t_tab.extend;

-- Use XPATH syntax to assign values to he elements of the collection.
dbms_xslprocessor.valueOf(l_n,'EMPNO/text()',t_tab(t_tab.last).empno);
dbms_xslprocessor.valueOf(l_n,'ENAME/text()',t_tab(t_tab.last).ename);
dbms_xslprocessor.valueOf(l_n,'JOB/text()',t_tab(t_tab.last).job);
dbms_xslprocessor.valueOf(l_n,'MGR/text()',t_tab(t_tab.last).mgr);
dbms_xslprocessor.valueOf(l_n,'HIREDATE/text()',t_tab(t_tab.last).hiredate);
dbms_xslprocessor.valueOf(l_n,'SAL/text()',t_tab(t_tab.last).sal);
dbms_xslprocessor.valueOf(l_n,'COMM/text()',t_tab(t_tab.last).comm);
dbms_xslprocessor.valueOf(l_n,'DEPTNO/text()',t_tab(t_tab.last).deptno);
END LOOP;

-- Insert data into the real EMP table from the table collection.
-- Form better performance multiple collections should be used to allow
-- bulk binding using the FORALL construct but this would make the code
-- too long-winded for this example.
FOR cur_emp IN t_tab.first .. t_tab.last LOOP
INSERT INTO emp
(empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno)
VALUES
(t_tab(cur_emp).empno,
t_tab(cur_emp).ename,
t_tab(cur_emp).job,
t_tab(cur_emp).mgr,
t_tab(cur_emp).hiredate,
t_tab(cur_emp).sal,
t_tab(cur_emp).comm,
t_tab(cur_emp).deptno);
END LOOP;

COMMIT;

-- Free any resources associated with the document now it
-- is no longer needed.
dbms_xmldom.freeDocument(l_doc);

EXCEPTION
WHEN OTHERS THEN
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
END;

--===================End===============

Best way to derive site traffic

Best way to derive site traffic
--------------------------------
Website traffic or visitor is an important thing for webmasters. It’s not hard to derive traffic if you follow some simple tips and tricks. Those tips will help you to derive massive traffic in your site within few days. I will discuss those tips in the following.

1. Original Content: Write always original content and avoid duplicate content for your site. Search engine will penalize your duplicate contents. Write yourself whatever you know from reading articles or searching sites. Try to keep your article’s words minimum 200 words. It will take 15 to 30 minutes to write a 200 words article. Write daily what interests you. But best will be chose a specific niche such as sports, health, relation, food, travel etc. So remember original content is king to derive website traffic.

2. Keywords: Write keywords on your article’s title and first paragraph of your article. Also try to write those keywords at the last paragraph of your article.

3. Inbound link: Keep inbound link in your content. Inbound link is to link some keywords with your other article or any other site.

4. Submit sitemap to search engine: It’s very important to submit your sitemap to popular search engines like google, yahoo and msn. You can know the way of submitting site map from searching web.

5. Submit to Article directory: Write an article according to your niche and submit it to some number of article directories. Those article directories will allow you to keep your site link and after reading your article visitor will come to your site clicking the link. Some popular article directories are Ezine, ArticlesBase, Isnare etc.

6. Join Forums: Join some forum site related to your niche and participate conversation with the members of the forum site. Keep your site or article link at the end of your speech.

7. Join Answer site: It easy to derive traffic by joining answering site. On those site you can find to many topics and members are asking questions for an specific topic. Answer the topic of your niche and keep your site link as source of your answer.

Those are the most effective and basic tips to derive traffic and if you follow those tips you will not have to wait for traffic.

Generate a XML File From Oracle

Grant dba to scott;

Connect scott/tiger;

CREATE OR REPLACE DIRECTORY
XML_DIR AS
'D:\';

CREATE OR REPLACE PROCEDURE Generate_XML_file ( p_filename in varchar2 default 'Dept',
p_dir in varchar2 default 'XML_DIR'
) IS
v_OutFile utl_file.file_type;
v_value varchar2(2000);
v_QryCtx DBMS_XMLQuery.ctxType;
v_Xml CLOB;
v_More BOOLEAN := TRUE;
v_SqlQuery varchar2(2000):='Select * from dept';
v_RowsetTag Varchar2(200) :='Dept';
v_RowTag varchar2(200) :='Dept';

BEGIN


v_OutFile := utl_file.fopen( p_dir, p_filename||'.xml', 'w' );
v_More := TRUE;
v_QryCtx := DBMS_XMLQuery.newContext(v_SqlQuery);
DBMS_XMLQuery.setRowsetTag(v_QryCtx, v_RowsetTag);
DBMS_XMLQuery.setRowTag(v_QryCtx, v_RowTag);
v_Xml := DBMS_XMLQuery.GetXML(v_QryCtx);
DBMS_XMLQuery.closeContext(v_QryCtx);

while v_more loop
utl_File.Put(v_OutFile, Substr(v_Xml, 1, 32767));
if length(v_Xml) > 32767 then
v_Xml := substr(v_Xml, 32768);
else
v_More := FALSE;
end if;
end loop;

utl_file.fclose( v_OutFile );

Exception
when others then
utl_file.fclose( v_OutFile );
End;

--========================
exec Generate_XML_file;
--========================

Move an Item with Oracle Developer

procedure prc_move_item (p_item varchar2)
is
  x number; --Max 495(Canvas Width....)
  y number := 3; --Y Position of Move Item
  w number;
  diff_width number:=(:global.can_width-:global.item_width);
   
begin

/*
 Please Assign :global.w :=get_item_property ('control.show_title',width); (get item width)
  :global.can_width :=get_canvas_property('can_main',width); (get canvas width)
  :global.item_width :=get_item_property ('control.show_title',width); :global.x_pos:=0;
   
  in NEW-FORM-INSTANCE trigger.
   
 Also Create a Timer in NEW-FORM-INSTANCE trigger.
 This Procedure is referred into WHEN-TIMER-EXPIRED trigger.
 Change Item name which Item you want to move.
*/  
  
  x := :global.x_pos + 1;
  w := :global.w;

  if x < :global.can_width --(Canvas Width....) Start 1
  then  
  if x < diff_width --(Canvas width - Item width)Maximum Move of X position of Move Item. Start 2
  then  
  if w < :global.item_width --Item Width Start 3
  then  
  set_item_property (p_item, width, w);
  :global.w := :global.w + 1;
  x := 0; --X Position still 0(zero) untill item width 181.
  end if; --end 3

  set_item_property (p_item, position, x, y);
  :global.x_pos := x;
  else
  w := :global.w - 1;
  set_item_property (p_item, alignment, alignment_left);
  set_item_property (p_item, width, w);
  set_item_property (p_item, position, x, y);
  :global.w := w;
  :global.x_pos := x;  
  end if; --end 2
  else
  :global.x_pos := 0;
  set_item_property (p_item, alignment, alignment_right);
  end if; --end 1
end;
/

Expand or Collapse an Item Value

--====================================================================
:global.wait := null;
:global.value := null;

The above varibale assign into New-Form-Instance trigger.
Also create a timer into New-Form-Instance trigger.
Then the following Procedure call from When-Timer-expired trigger
Ex: prc_increase_text('my_item',5); 

--====================================================================
procedure prc_increase_text(p_item in varchar2,p_iteration number) is
  
  v_item varchar2(100);
 v_display varchar2(1000);
 v_ini_value varchar2(100);
 v_char varchar2(1);
 v_length number;

begin

  copy(name_in(p_item),'global.value');
  v_length := length(:global.value);
  :global.wait:= :global.wait+1;
  
  if :global.wait = p_iteration then
  
  copy(:global.ini_value,p_item);
  set_item_property(p_item,foreground_color,'r100g100b50');
  :global.wait:=1;
  else
  if :global.wait = 1 then :global.ini_value:= :global.value; end if;

  for i in 1..v_length loop
  v_char := substr(:global.value,i,1);
  if v_char <> chr(32) then
  v_display := v_display||chr(32)||v_char;
  else 
  v_display := v_display||v_char;
  end if;
  end loop;
   
  --Display TEXT into the following item.
  --Change Visual Attribute.
  if mod(:global.wait,2)= 0 then
  set_item_property(p_item,foreground_color,'r100g100b50');
  v_item := rtrim(ltrim(v_display));
  copy(v_item,p_item); 
  else
  set_item_property(p_item,foreground_color,'r0g0b0');
  v_item := rtrim(ltrim(v_display));
  copy(v_item,p_item);  
  end if;
   
  end if;

  
exception
 when others then null;
end;

Data Transfer from Developer(Oracle) to Excel

PROCEDURE fpr_forms_to_excel(p_block_name in varchar2 default NAME_IN('system.current_block'),
  p_path in varchar2 default 'C:\',
  p_file_name in varchar2 default 'Temp') IS
-- Declare the OLE objects
 application OLE2.OBJ_TYPE;
 workbooks OLE2.OBJ_TYPE;
 workbook OLE2.OBJ_TYPE;
 worksheets OLE2.OBJ_TYPE;
 worksheet OLE2.OBJ_TYPE;
 cell OLE2.OBJ_TYPE;
 range OLE2.OBJ_TYPE;
 range_col OLE2.OBJ_TYPE;
 
 -- Declare handles to OLE argument lists
 args OLE2.LIST_TYPE;
 arglist OLE2.LIST_TYPE;
 -- Declare form and block items
 form_name VARCHAR2(100);
 f_block VARCHAR2(100);
 l_block VARCHAR2(100);
 f_item VARCHAR2(100);
 l_item VARCHAR2(100);
 cur_block VARCHAR2(100):= NAME_IN('system.current_block');
 cur_item VARCHAR2(100);
 cur_record VARCHAR2(100);
 item_name VARCHAR2(100);
 baslik VARCHAR2(100);
 row_n NUMBER;
 col_n NUMBER;
 filename VARCHAR2(1000):= p_path||p_file_name;
 
 
 ExcelFontId OLE2.list_type;

BEGIN
  -- Start Excel 
  application:=OLE2.CREATE_OBJ('Excel.Application');
  OLE2.SET_PROPERTY(application, 'Visible', 'False');
  
  -- Return object handle to the Workbooks collection 
  workbooks:=OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
  
  -- Add a new Workbook object to the Workbooks collection
  workbook:=OLE2.GET_OBJ_PROPERTY(workbooks,'Add');
  
  -- Return object handle to the Worksheets collection for the Workbook
  worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
  
  -- Get the first Worksheet in the Worksheets collection
  -- worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,'Add');
  args:=OLE2.CREATE_ARGLIST; 
  OLE2.ADD_ARG(args, 1);
  worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,'Item',args);
  OLE2.DESTROY_ARGLIST(args);
  
  -- Return object handle to cell A1 on the new Worksheet
  go_block(p_block_name);
  
  baslik := get_block_property(p_block_name,FIRST_ITEM);  
  f_item := p_block_name||'.'||get_block_property(p_block_name,FIRST_ITEM); 
  l_item := p_block_name||'.'||get_block_property(p_block_name,LAST_ITEM);
  first_record;

  LOOP
  item_name := f_item;
  row_n := NAME_IN('SYSTEM.CURSOR_RECORD');
  col_n := 1;
   
  LOOP 
  IF get_item_property(item_name,ITEM_TYPE)<>'BUTTON' AND get_item_property(item_name,VISIBLE)='TRUE' THEN
  -- Set first row with the item names 
  IF row_n=1 THEN
  args := OLE2.create_arglist;
  OLE2.add_arg(args, 1);
  OLE2.add_arg(args, col_n);
  cell := OLE2.get_obj_property(worksheet, 'Cells', args);
  OLE2.destroy_arglist(args);
  --cell_value := OLE2.get_char_property(cell, 'Value');
  ExcelFontId := OLE2.get_obj_property(Cell, 'Font');
  OLE2.set_property(ExcelFontId, 'Bold', 'True');
  -------------------------------------------- 
  baslik:=NVL(get_item_property(item_name,PROMPT_TEXT),baslik);
  args:=OLE2.CREATE_ARGLIST; 
  OLE2.ADD_ARG(args, row_n);
  OLE2.ADD_ARG(args, col_n); 
  cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
  OLE2.DESTROY_ARGLIST(args);
  OLE2.SET_PROPERTY(cell, 'Value', baslik);
  OLE2.RELEASE_OBJ(cell);
  END IF;
  -- Set other rows with the item values 
  args:=OLE2.CREATE_ARGLIST; 
  OLE2.ADD_ARG(args, row_n+1);
  OLE2.ADD_ARG(args, col_n); 
  cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
  OLE2.DESTROY_ARGLIST(args);
   
  IF get_item_property(item_name,DATATYPE)<>'NUMBER' THEN
  OLE2.SET_PROPERTY(cell, 'NumberFormat', '@');
  END IF;
  OLE2.SET_PROPERTY(cell, 'Value', name_in(item_name));
  OLE2.RELEASE_OBJ(cell);
  END IF;
   
  IF item_name = l_item THEN
  exit;
  END IF;
  baslik := get_item_property(item_name,NEXTITEM);
  item_name := p_block_name||'.'||get_item_property(item_name,NEXTITEM);
  col_n := col_n + 1;
  END LOOP;
  
  EXIT WHEN NAME_IN('system.last_record') = 'TRUE';
  NEXT_RECORD;
  END LOOP;

  -- Autofit columns
  range := OLE2.GET_OBJ_PROPERTY( worksheet,'UsedRange');
  range_col := OLE2.GET_OBJ_PROPERTY( range,'Columns');
  OLE2.INVOKE( range_col,'AutoFit' );
  OLE2.RELEASE_OBJ( range );
  OLE2.RELEASE_OBJ( range_col );

  -- Save as worksheet with a Specified file path & name.
  IF NVL(filename,'0')<>'0' THEN
  args := OLE2.CREATE_ARGLIST;
  OLE2.ADD_ARG(args,filename );
  OLE2.INVOKE(worksheet,'SaveAs',args );
  OLE2.DESTROY_ARGLIST( args );
  END IF;
  
  -- Release the OLE objects
  OLE2.RELEASE_OBJ(worksheet);
  OLE2.RELEASE_OBJ(worksheets);
  OLE2.RELEASE_OBJ(workbook);
  OLE2.RELEASE_OBJ(workbooks);
  OLE2.INVOKE (application,'Quit');
  OLE2.RELEASE_OBJ(application);
  
  -- Focus to the original location

exception
 when others then null;
  raise form_trigger_failure;
END; 

Data Trasnfer From Excel to Oracle Developer

PROCEDURE Prc_get_from_xls(p_File_Path_File_Name varchar2) IS

 CONVID PLS_INTEGER;
 APPID PLS_INTEGER;
 i number;
 x number;
 v_card varchar2(500) :=null;
 v_actnum varchar2(500) :=null;
 v_amount varchar2(500) :=null;
 v_table varchar2(4000);
 OUT_FILR TEXT_IO.FILE_TYPE;

BEGIN
  synchronize;
  Appid := dde.app_begin('C:\Program Files\Microsoft Office\Office\excel.exe '||p_File_Path_File_Name,dde.app_mode_minimized);
  dde.app_focus(appid);
  convid := dde.initiate('EXCEL.EXE','Sheet1' );

  go_block('temp');
 
  x := 0;

  FOR I IN 2..1000 loop
  dde.request(convid,'R' || to_char(i) ||'C1',v_card ,dde.cf_text,100000);
  dde.request(convid,'R' || to_char(i) ||'C2',v_actnum,dde.cf_text,100000);
  dde.request(convid,'R' || to_char(i) ||'C3',v_amount,dde.cf_text,100000);
 
  if substr(v_card ,1,length(v_card)-2) is null
  then exit;
  end if;
 
  :temp.col1:= substr(v_card ,1,length(v_card)-2 );
  :temp.col2:= substr(v_actnum,1,length(v_actnum)-2);
  :temp.col3:= substr(v_amount,1,length(v_amount)-2);

  next_record;
 
  x:= x + 1;
  end loop;

  dde.terminate(convid);
  dde.app_end(appid);
  first_record;
 
Exception
 when others then msgbox(sqlerrm);
END;

Call a Drilldown Report from a Report with Oracle Developer10g

--=======================================
CREATE TABLE REPORT_STANDARD
(
RPT_SERVER VARCHAR2(50 BYTE),
RPT_BUILDER VARCHAR2(50 BYTE),
RPT_FORMAT VARCHAR2(20 BYTE),
RPT_KEY VARCHAR2(10 BYTE),
MODULE VARCHAR2(10 BYTE),
RPT_PATH VARCHAR2(2000 BYTE),
RPT_URL VARCHAR2(2000 BYTE)
);

ALTER TABLE REPORT_STANDARD ADD (
CONSTRAINT PK_REPORT_STANDARD
PRIMARY KEY(MODULE));

--=======Data=====================================================
SET DEFINE OFF;
Insert into REPORT_STANDARD
(RPT_SERVER, RPT_BUILDER, RPT_FORMAT, RPT_KEY, MODULE,
RPT_PATH, RPT_URL)
Values
('rep_micr', 'rep', 'pdf', 'micr', 'MICR',
'D:\Sampad\forms\Reports\', 'http://10.11.201.121:8889/reports/rwservlet?');
Insert into REPORT_STANDARD
(RPT_SERVER, RPT_BUILDER, RPT_FORMAT, RPT_KEY, MODULE,
RPT_PATH, RPT_URL)
Values
('rep_outward', 'rep', 'pdf', 'out', 'OUTWARD',
'D:\Sampad\forms\Reports\', 'http://10.11.201.121:8889/reports/rwservlet?');
Insert into REPORT_STANDARD
(RPT_SERVER, RPT_BUILDER, RPT_FORMAT, RPT_KEY, MODULE,
RPT_PATH, RPT_URL)
Values
('rep_inward', 'rep', 'pdf', 'inw', 'INWARD',
'D:\Sampad\forms\Reports\', 'http://10.11.201.121:8889/reports/rwservlet?');
COMMIT;


--=========================================================================================
CREATE OR REPLACE Procedure Dpr_Run_Report_Drilldown(p_Rpt_name in varchar2,
p_Dest in varchar2 default 'C',
p_Module in varchar2,
p_Url out varchar2
)
is

v_url varchar2(2000) ;
v_report_name varchar2(100) :=p_Rpt_name ;
v_rpt_name_with_path varchar2(500) ;
v_dest varchar2(20) := p_Dest ;
v_rpt_builder report_standard.rpt_builder%type;
v_rpt_key report_standard.rpt_key%type ;
v_rpt_format report_standard.rpt_format%type ;
v_rpt_server report_standard.rpt_server%type ;
v_rpt_path report_standard.rpt_path%type ;
v_rpt_url report_standard.rpt_url%type ;
begin


--------------------------------------- Retrieving Report info from Report_Standard Table -------------------
begin
select rpt_server ,
rpt_builder,
rpt_format ,
rpt_key ,
rpt_path ,
rpt_url
into v_rpt_server ,
v_rpt_builder,
v_rpt_format ,
v_rpt_key ,
v_rpt_path ,
v_rpt_url
from report_standard
where upper(module)= upper(p_module);

v_rpt_name_with_path:= v_rpt_path||v_report_name;

exception
when others then null;
end;

--------------------------Report Destination------------------------

v_dest:=upper(p_dest);
if v_dest ='C' then
v_dest:='cache';
elsif v_dest='P' then
v_dest:='printer';
elsif v_dest='M' then
v_dest:='mail';
elsif v_dest='F' then
v_dest:='file';
end if;

--------------------------Start Call Report------------------------
v_url:= v_rpt_url||v_rpt_key
||'+desformat=' ||v_rpt_format
||'+destype=' ||v_dest
--||'+mimetype='||'application/vnd.ms-excel'
||'+report=' ||v_rpt_name_with_path;

p_Url:= v_url;


Exception
When others then null;
End Dpr_Run_Report_Drilldown;
/

--==================================================================
--Call from a report with format trigger

function trigger_name return boolean is

v_url varchar2(2000);
Begin

Dpr_Run_Report_Drilldown(Report_name, 'C','OUTWARD',v_url);

SRW.SET_HYPERLINK(v_url||'+Paramerte_name1='||:Paramerte_name1
||'+Paramerte_name1='||:Paramerte_name2
);
return (TRUE);

Exception
When others then return (false);
End;

--=============================================

Create a View with Parameter for Oracle10g

CREATE TABLE CITIES
 (
 REGION VARCHAR2 (30),
 COUNRTY VARCHAR2 (30),
 CITY VARCHAR2 (30)
 );

Insert into CITIES (REGION,COUNRTY,CITY) values ('AMERICA','UNITED STATED','NEW YORK');

Insert into CITIES (REGION,COUNRTY,CITY) values ('EUROPE','FRANCE','PARIS');

Insert into CITIES (REGION,COUNRTY,CITY) values ('ASIA','JAPAN','TOKYO');

Insert into CITIES (REGION,COUNRTY,CITY) values ('ASIA','INDIA','MUMBAI');

Insert into CITIES (REGION,COUNRTY,CITY) values ('EUROPE','UNITED KINGDOM','LONDON');

Insert into CITIES (REGION,COUNRTY,CITY) values ('AMERICA','UNITED STATES','WASHINGTON DC');

COMMIT;

CREATE OR REPLACE PACKAGE PKG_PARAM AS
PROCEDURE SET_REGION (P_REGION IN VARCHAR2);
FUNCTION GET_REGION RETURN VARCHAR2;
END PKG_PARAM;
/

CREATE OR REPLACE PACKAGE BODY PKG_PARAM AS
REGION VARCHAR2 (30);

PROCEDURE SET_REGION (P_REGION IN VARCHAR2)
IS
BEGIN
REGION := P_REGION;
END;

FUNCTION GET_REGION RETURN VARCHAR2
IS
BEGIN
RETURN REGION;
END;
END PKG_PARAM;
/

CREATE OR REPLACE VIEW PARAM_VIEW AS
SELECT *
FROM CITIES
WHERE REGION=PKG_PARAM.GET_REGION;

SELECT * FROM PARAM_VIEW;

no rows returned....

EXEC PKG_PARAM.SET_REGION('ASIA');

SELECT * FROM PARAM_VIEW;

ORDImage type for Database ORACLE10g

CREATE OR REPLACE PROCEDURE MICR.Dpr_Insert_ORDImage (p_date in date,p_batch in number) IS
   
  Image_front ORDSYS.ORDImage;
  Image_rear ORDSYS.ORDImage;
  ctx RAW(6000) := NULL;
  vDocumentNum outwdclr.document_num%type;
  v_ErrorText Error_Tab."ErrorText"%type:= Null;
  v_ErrorCode Error_Tab."ErrorCode"%type:= Null;  
  vImage ORDSYS.ORDImage;  
  vWidth_f INTEGER; -- image.getWidth);
  vHeight_f INTEGER; -- image.getHeight);
  vWidth_r INTEGER; -- image.getWidth);
  vHeight_r INTEGER; -- image.getHeight);
  vContentLength INTEGER; -- image.getContentLength);
  vImageFileFormat_f VARCHAR2(4000); -- image.getFileFormat);
  vImageFileFormat_r VARCHAR2(4000); -- image.getFileFormat);
  vContentFormat VARCHAR2(4000); -- image.getContentFormat);
  vCompressionFormat_f VARCHAR2(4000); -- image.getCompressionFormat);
  vCompressionFormat_r VARCHAR2(4000); -- image.getCompressionFormat);
  vMimeType_f VARCHAR2(4000); -- image.getMimeType);
  vMimeType_r VARCHAR2(4000); -- image.getMimeType);
   
  vMyCommands VARCHAR2(4000);
   
BEGIN
  
  --vMyCommands := 'fileFormat=tiff compressionFormat=packbits contentFormat = 8bitlut';
  /*** Inserting ORDImage with Update of OUTWDCLR table ***/ 
  For i in (select document_num,
  check_number||bank_code||district_code||branch_code||check_digit||chk_actnum||tran_code Image_name 
  from outwdclr
  where document_date = p_date
  and batch_no = p_batch  
  and nvl(post_flag,'N') = 'N')
  Loop
   
  Insert Into MICR.OUT_IMAGE(document_num ,
  document_date , 
  batch_no , 
  image_front ,
  image_rear ,
  entry_timestamp ,
  entry_by)
  Values (i.document_num ,
  p_date , 
  p_batch , 
  ORDSYS.ORDImage.init(),
  ORDSYS.ORDImage.init(),
  sysdate ,
  'SYSTEM'
  );
   
   
  Select image_front,image_rear 
  Into Image_front,Image_rear 
  From out_image
  Where document_date = p_date
  and batch_no = p_batch 
  and document_num = i.document_num for UPDATE;  
   
  Image_front.setSource('FILE','MY_IMAGE_DIR',i.Image_name||'_Front.tif');
  Image_front.import(ctx);
  Image_rear.setSource('FILE','MY_IMAGE_DIR' ,i.Image_name||'_Rear.tif');
  Image_rear.import(ctx);
   
  vWidth_f := Image_front.getWidth ;
  vHeight_f := Image_front.getHeight ;
  vWidth_r := Image_rear.getWidth ;
  vHeight_r := Image_rear.getHeight ;
  vImageFileFormat_f := image_front.getFileFormat ;
  vImageFileFormat_r := image_rear.getFileFormat ;
  vCompressionFormat_f := image_front.getCompressionFormat;
  vCompressionFormat_r := image_rear.getCompressionFormat ;
  vMimeType_f := image_front.getMimeType ;
  vMimeType_r := image_rear.getMimeType ;
   
  --vMyCommands := 'compressionFormat=packbits';
  --Image_front.processCopy(vMyCommands, Image_front);
  --Image_rear.processCopy(vMyCommands, Image_rear);
   
  Update out_image 
  set image_front = Image_front ,
  image_rear = Image_rear , 
  width_front = vWidth_f ,  
  height_front = vHeight_f ,
  width_rear = vWidth_r ,
  height_rear = vHeight_r ,
  file_fmt_front = vImageFileFormat_f , 
  file_fmt_rear = vImageFileFormat_r ,  
  com_fmt_front = vCompressionFormat_f , 
  com_fmt_rear = vCompressionFormat_r ,
  mime_type_front = vMimeType_f ,
  mime_type_rear = vMimeType_r ,  
  update_timestamp = sysdate ,
  update_by = 'SYSTEM'
  Where document_num = i.document_num
  and document_date = p_date
  and batch_no = p_batch;
   
  End Loop;  
  
  COMMIT;  
  
   
Exception
  when others then Null;
END;
/