Oracle with S@m: August 2009

Sunday, August 30, 2009

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;

Saturday, August 29, 2009

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