Saturday, February 8, 2014

Checking Reports Server

Oracle® Fusion Middleware Publishing Reports to the Web with Oracle Reports Services
11g Release 1 (11.1.1)







3.4 Verifying the Reports Server Environment

Oracle Reports Services report requests flow from the Oracle HTTP Server component, to Oracle Reports Servlet, to Reports Server. Before sending report requests to Reports Server, verify that the environment is up and running:

3.4.1 Checking Oracle HTTP Server

Before starting Reports Server through Oracle Enterprise Manager, you must verify that your Oracle HTTP Server is running. For more information about performing this task in Oracle Enterprise Manager, refer to your Oracle Enterprise Manager documentation.
Alternatively, you can verify that the Oracle HTTP Server is running by navigating to the following URL:
http://server_name.domain:port_number/

3.4.2 Checking Oracle Reports Servlet

To verify that Oracle Reports Servlet (rwservlet) is running, navigate to the following URL:
http://host:port/reports/rwservlet/help
where
host is the server that is allotted.
port is either the OHS port or the WebLogic Server port.
Note that the URL is case-sensitive. If this URL executes successfully, you should get a help page describing the rwservlet command line arguments

3.4.3 Checking Reports Server

To verify that Reports Server is running, navigate to the following URL:
http://host:port/reports/rwservlet/getserverinfo?server=server_name
where
host and port are as described in Section 3.4.2, "Checking Oracle Reports Servlet".
server=server_name is not required if you are using the default Reports Server name (rep_machine_name) or the Reports Server specified in the Oracle Reports Servlet configuration file (rwservlet.properties ).
If this URL executes successfully, you should see a listing of the job queue for the specified Reports Server.

Wednesday, February 27, 2013

Performance Programming in PL/SQL using Oracle 10G

CREATE OR REPLACE PROCEDURE preformance_compare(iterations PLS_INTEGER)
IS

/* =======================================================================================================================
PROGRAM ID : Procedure preformance_compare.
PROGRAM DESC : This Program is written to measure the Performance of the Different Control structure/Construct available
in Oracle 10G i.e FOR LOOP, FOR ALL,LOOP,WHILE LOOP and THE /*+APPEND */ /*Hint, Using of BULK Collect etc.
-----------------------------------------------------------------------------------------------------------------------
VERSION DATE CATCH STRING MODIFIED BY REASON
1.0 19.02.2013 Muhammad Asif To measure the Performance of different Oracle Control Structure For While Loop and Hints etc*/
--=================================================================================================--


TYPE NumTab IS TABLE OF t1.pnum%TYPE INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF t1.pname%TYPE INDEX BY PLS_INTEGER;

pnums NumTab;
Pnames NameTab;

CURSOR CR1(p_iterations IN NUMBER) IS
SELECT *
FROM t2
WHERE ROWNUM <= p_iterations ;

TYPE MyType IS TABLE OF t1%ROWTYPE INDEX BY BINARY_INTEGER;
COLLType MyType;

a INTEGER;
b INTEGER;
c INTEGER;
d INTEGER;
e INTEGER;
f INTEGER;
g INTEGER;
h INTEGER;
i BINARY_INTEGER := 1;
ln_pnum NUMBER;
lv_pname VARCHAR2(1000);
BEGIN
a:= dbms_utility.get_time;

FOR j IN 1.. iterations LOOP --Load index by table
pnums(j) := j;
pnames(j) := 'Part No. '|| TO_CHAR(j);
END LOOP;

a:= dbms_utility.get_time;
FOR i IN 1..iterations Loop --use FOR loop
INSERT INTO t1 VALUES (pnums(i),pnames(i));
END LOOP;

b := dbms_utility.get_time;

FORALL i IN 1.. iterations -- user FORALL statment
INSERT INTO t2 VALUES (pnums(i), pnames(i));

COMMIT;
c:= dbms_utility.get_time;


OPEN CR1(iterations);
FETCH CR1 BULK COLLECT INTO COLLType;
CLOSE CR1;
FOR CR1 IN 1..CollType.Count LOOP
INSERT INTO t3 VALUES (COLLType(CR1).pnum, COLLType(CR1).pname);
END LOOP;

d:= dbms_utility.get_time;

OPEN CR1(iterations);
LOOP
FETCH CR1 INTO ln_pnum,lv_pname;
EXIT WHEN CR1%NOTFOUND;
INSERT INTO t4 VALUES (ln_pnum,lv_pname);
END LOOP;
e:= dbms_utility.get_time;
BEGIN
WHILE pnums(i) IS NOT NULL LOOP
INSERT INTO t5 VALUES (ln_pnum,lv_pname);
i := i + 1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
f:= dbms_utility.get_time;
FORALL i IN 1.. iterations -- user FORALL statment
INSERT /*+APPEND*/ INTO t2 VALUES (pnums(i), pnames(i));

COMMIT;
g:= dbms_utility.get_time;

dbms_output.put_line('This Program is written to measure the Performance of the Different Control structure/Construct available
in Oracle 10G i.e FOR LOOP, FOR ALL,LOOP,WHILE LOOP and THE /*+APPEND */ /*Hint, Using of BULK Collect etc.' );

dbms_output.put_line('Total No of Records Inserted : '||iterations);
dbms_output.put_line('Execution Time (secs)');
dbms_output.put_line('FOR ALL loop with /*+APPEND*/ Hint in Insert : '|| TO_CHAR((g - f) / 100));
dbms_output.put_line('FOR ALL loop: '|| TO_CHAR((c - b) / 100));
dbms_output.put_line('WHILE LOOP: '|| TO_CHAR((f - e) / 100));
dbms_output.put_line('FOR loop: '|| TO_CHAR((b - a) / 100));
dbms_output.put_line('FOR BULK Collect loop: '|| TO_CHAR((d - c) / 100));
dbms_output.put_line('LOOP: '|| TO_CHAR((e - d) / 100));

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' ERROR !!!!! ' || '\' || SQLERRM);

END preformance_compare;

--Script

drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
CREATE TABLE t1 (pnum INTEGER, pname VARCHAR2(4000));
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
CREATE TABLE t4 AS SELECT * FROM t1;
CREATE TABLE t5 AS SELECT * FROM t1;
set serveroutput on

exec preformance_compare(150000);

SELECT COUNT(1) FROM t1;
SELECT COUNT(1) FROM t2;
SELECT COUNT(1) FROM t3;
SELECT COUNT(1) FROM t4;
SELECT COUNT(1) FROM t5;
SELECT count(1) FROM t2
where rownum <= 50000 ;


--OutPut


This Program is written to measure the Performance of the Different Control structure/Construct available
in Oracle 10G i.e FOR LOOP, FOR ALL,LOOP,WHILE LOOP and THE /*+APPEND */ /*Hint, Using of BULK Collect etc.
Total No of Records Inserted : 150000
Execution Time (secs)
FOR ALL loop with /*+APPEND*/ Hint in Insert : .24
FOR ALL loop: .31
WHILE LOOP: 6.26
FOR loop: 6.17
FOR BULK Collect loop: 6.61
LOOP: 9.25

Saturday, February 16, 2013

How to convert d2k report in excel


How to convert d2k report in excel

first create a  RPT2XLS.pll  as under below :
first step :-
/start****************RPT2XLS(package space)@***********************/
PACKAGE RPT2XLS IS
BOLD constant binary_integer := 1;
ITALIC constant binary_integer := 2;
UNDERLINE constant binary_integer := 4;
PROCEDURE put_cell(ColNo binary_integer, CellValue in varchar2,
FontName in varchar2 DEFAULT null,
FontSize in binary_integer DEFAULT null,
FontStyle in binary_integer DEFAULT null,
FontColor in binary_integer DEFAULT null,
BgrColor in binary_integer DEFAULT null,
Format in varchar2 DEFAULT null  );
PROCEDURE new_line;
PROCEDURE run;
PROCEDURE release_memory;
END;
/end****************RPT2XLS(package space)@***********************/
/start****************RPT2XLS(package body)@***********************/
PACKAGE BODY RPT2XLS IS
TYPE ExcelCell IS RECORD(RowNo binary_integer,
ColNo binary_integer,
Val varchar2(2000),
FontName varchar2(20),
FontSize binary_integer,
FontStyle binary_integer,
FontColor binary_integer,
BgrColor binary_integer,
Format varchar2(60) DEFAULT null );
TYPE ExcelCells IS TABLE OF ExcelCell;
Cell ExcelCells := ExcelCells();
CurrentRow binary_integer := 1;
PROCEDURE new_line IS
BEGIN
CurrentRow := CurrentRow + 1;
END;
PROCEDURE put_cell(ColNo binary_integer, CellValue in varchar2,
FontName in varchar2 DEFAULT null,
FontSize in binary_integer DEFAULT null,
FontStyle in binary_integer DEFAULT null,
FontColor in binary_integer DEFAULT null,
BgrColor in binary_integer DEFAULT null,
Format in varchar2 DEFAULT null) IS
EGIN
Cell.Extend;
Cell(Cell.Last).RowNo := CurrentRow;
Cell(Cell.Last).ColNo := ColNo;
Cell(Cell.Last).Val := CellValue;
Cell(Cell.Last).FontName := FontName;
Cell(Cell.Last).FontSize := FontSize;
Cell(Cell.Last).FontStyle := FontStyle;
Cell(Cell.Last).FontColor := FontColor;
Cell(Cell.Last).BgrColor := BgrColor;
Cell(Cell.Last).Format := Format;
END;
PROCEDURE run IS
Application OLE2.OBJ_TYPE;
Workbooks OLE2.OBJ_TYPE;
Workbook OLE2.OBJ_TYPE;
Worksheets OLE2.OBJ_TYPE;
Worksheet OLE2.OBJ_TYPE;
WorkCell OLE2.OBJ_TYPE;
WorkColumn OLE2.OBJ_TYPE;
WorkFont OLE2.OBJ_TYPE;
WorkInterior OLE2.OBJ_TYPE;
ArgList OLE2.LIST_TYPE;
BEGIN
Application := OLE2.create_obj('Excel.Application');
OLE2.set_property(Application, 'Visible', 1);
Workbooks := OLE2.get_obj_property(Application, 'Workbooks');
Workbook := OLE2.invoke_obj(WorkBooks, 'Add');
Worksheets := OLE2.get_obj_property(Workbook, 'Worksheets');
Worksheet := OLE2.get_obj_property(Application, 'ActiveSheet');

for i in Cell.First .. Cell.Last
loop
if Cell(i).Val is not null then
ArgList := OLE2.create_arglist;
OLE2.add_arg(ArgList, Cell(i).RowNo);
ole2.add_arg(ArgList, Cell(i).ColNo);
WorkCell := OLE2.get_obj_property(Worksheet, 'Cells', ArgList);
ole2.destroy_arglist(ArgList);
ole2.set_property(WorkCell, 'Value', Cell(i).Val);
ole2.set_property(WorkCell, 'NumberFormat', Cell(i).Format);
WorkFont := OLE2.get_obj_property(WorkCell, 'Font');
WorkInterior := ole2.Get_Obj_Property(WorkCell, 'Interior');
if Cell(i).FontName is not null then
OLE2.set_property(WorkFont, 'Name', Cell(i).FontName);
end if;
if Cell(i).FontSize is not null then
OLE2.set_property(WorkFont, 'Size', Cell(i).FontSize);
end if;
if mod(Cell(i).FontStyle, 2) = 1  then
OLE2.set_property(WorkFont, 'Bold', 1);
end if;
if mod(Cell(i).FontStyle, 4) > 2  then
OLE2.set_property(WorkFont, 'Italic', 1);
end if;
if mod(Cell(i).FontStyle, 8) > 4  then
OLE2.set_property(WorkFont, 'Underline', 2);
end if;
 if Cell(i).FontColor is not null then
OLE2.set_property(WorkFont, 'ColorIndex', Cell(i).FontColor);
end if;
if Cell(i).BgrColor is not null then
OLE2.set_property(WorkInterior, 'ColorIndex', Cell(i).BgrColor);
end if;
OLE2.release_obj(WorkInterior);
OLE2.release_obj(WorkFont);
OLE2.release_obj(WorkCell);
end if;
end loop;
ArgList := ole2.create_arglist;
ole2.add_arg(ArgList, 'A:Z'); 
WorkColumn := ole2.Get_Obj_Property(WorkSheet, 'Columns', ArgList); 
ole2.destroy_arglist(ArgList);
ole2.invoke(WorkColumn, 'AutoFit');
OLE2.release_obj(WorkColumn);
OLE2.release_obj(Worksheet);
OLE2.release_obj(Worksheets);
OLE2.release_obj(Workbook);
OLE2.release_obj(Workbooks);
OLE2.release_obj(Application);
END;
PROCEDURE release_memory IS
BEGIN
Cell := ExcelCells();
SYS.DBMS_SESSION.free_unused_user_memory;
END;
END;
/end****************RPT2XLS(package body)@***********************/
Second Step:
Use RPT2XLZ.PLL in report builder 
attach lib fiel in reort builder 

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1_gzCu2-KXcj8_qI3t6NJ_RmdSA3_d2IWUBLjZE75esijc8jbSR-pILpT9rTkRx_eDQ8ORNMvNIRhdqMTP_rmwes_Zm9_awLxFVBPDrcWNTnowFhQMr0bXShTEHsiIm6HI-TcUVr31w/s320/a.JPG


attach lib file in reort builder 

After report trigger 
function AfterReport return boolean is
begin
rpt2xls.release_memory;
return (TRUE);
end;
format trigger 
function F_21FormatTrigger return boolean is
begin
--  RPT2XLS.PUT_CELL(1,:countasper);--,null,null,03,null,null,null);
--RPT2XLS.PUT_CELL(2,:p_date);
RPT2XLS.PUT_CELL(3,'Grand Total',null,null,03,null,null,null);
RPT2XLS.PUT_CELL(4,:sumoftotalweight1);
RPT2XLS.PUT_CELL(5,:sumofoutcenwtsoc);
RPT2XLS.PUT_CELL(6,:sumtotalwt);
--RPT2XLS.PUT_CELL(7,:govt_rule);
RPT2XLS.PUT_CELL(8,:sum_soc_comm_tot);
RPT2XLS.PUT_CELL(9,:sum_cf_4);
RPT2XLS.PUT_CELL(10,:sum_cf_4);
RPT2XLS.PUT_CELL(11,:sum_cf_4);
RPT2XLS.PUT_CELL(12,:sum_cf_4);
rpt2xls.new_line;
return (TRUE);
end;
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-R_pmTeyaoMeysq9zv9v19b2OY3d_zL4b2utYHByI7GXYr_rdhLDAzWCwU0TixzQJHWGaHYF5efS2OfkQmeKG3Xy4RSGZG34Ou9koqMYwoh4VD5f5aENVqtoqCfY1CQu04zl8TRSVVA/s320/b.JPG

when button pressed
procedure U_1ButtonAction is
begin
rpt2xls.run;
end;
then compile report and run properly 


Report Convert to Excel Sheet RPT2XLS


first of all you should create library
or you should create two packages in Programm Unit
name is
1. RPT2XLS (Package Spec)
2. RPT2XLS (Package Body)
and in each you should describe the row and column and each thing of related to excell.
then you go to LAYOUT MODEL
and create a button name is U_EXCEL1
and type is =  pl/sql
and call 
RPT2XLS.RUN;
Then you define to each field that where you put like
example
------------ --------- Code for Report Header (ABDUL KHALIQUE 08/08/08)--- --------- --------- ---

 RPT2XLS.put_ cell(1, 'GL/Account Code :', FontSize => 8,FontStyle => RPT2XLS.BOLD+ RPT2XLS.ITALIC+ RPT2XLS.UNDERLINE) ;

Report Layout Modes | Confine Mode & Flex Mode


Confine Mode & Flex Mode

Changing the current mode (Confine or Flex)

To set or override Confine mode: 
·         In the Paper Layout view, click the Confine On button or the Confine Off button in the toolbar to toggle the mode:
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.

To set or override Flex mode: 
·         In the Paper Layout view, click the Flex On button or the Flex Off button in the toolbar to toggle the mode:
On: parent borders "stretch" when child objects are moved against them. The child object maintains the same distance from the side it moves against.
Off: parent borders remain fixed when child objects are moved against them. 

Friday, February 15, 2013

Declaring an Autonomous Function in a Package


Example 6-43 Declaring an Autonomous Function in a Package
CREATE OR REPLACE PACKAGE emp_actions AS  -- package specification
   FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS  -- package body
-- code for function raise_salary
   FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER IS
     PRAGMA AUTONOMOUS_TRANSACTION;
     new_sal NUMBER(8,2);
   BEGIN
     UPDATE employees SET salary = salary + sal_raise WHERE employee_id = emp_id;
     COMMIT;
     SELECT salary INTO new_sal FROM employees WHERE employee_id = emp_id;
     RETURN new_sal;
   END raise_salary;
END emp_actions;
/

Create GLOBAL TEMPORARY Table.


CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;