Tuesday, June 23, 2009

If u know "system" users' password then U know every users password in oracle 11g...

We all know that the fact all the users' passwords are encripted in the oracle database. And that could be decripted if you know the system users' password only .This is due to sys.user$ dectionary has to be queried to get the encripted password.Here we go..

Step 1: Login as a system user.
Step 2: Execute the following query.
SQL> SELECT name,password,spare4
FROM sys.user$
WHERE name ='CRS' ;
Step3 : Download the password cracker

Step4 : Fill the username,OLDSK3WLHASH(ie password) and SPARE4HASH(ie spare4) and press the start button.
Now you will get the exact password. Hope Oracle will come up with new technology where no one can decrypt at all. So guys be carefull with your DBAs.

Tuesday, June 9, 2009

How To Call a JAVA Program from PL/SQL?

Step1: Create the JAVA Class ( Here Factorial.java )

public class Factorial
{

public static int calcFactorial(int n)
{
if(n==1)
return 1;
else
return n * calcFactorial(n-1);
}

}


Step2: Load the JAVA Class into the DB ( Using the command prompt)
> loadjava -user scott/tiger@c920 E:\Factorial.java
Or
> loadjava -user scott/tiger@c920 E:\Factorial.class ( If it’s a class file)

Step3: Verify the Java source has been loaded or not by
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_TYPE LIKE ‘JAVA%’;
You Could see the source code by
SELECT text
FROM USER_SOURCE
WHERE NAME=’ Factorial’;

Step4 : Create a Procedure or Function that could call the JAVA method

CREATE OR REPLACE FUNCTION calc_fact( p_no NUMBER)
RETURN NUMBER
AS
LANGUAGE JAVA
NAME ‘Factorial. calcFactorial (int) return int’ ;

How to create Master-Detail(Sub Report) Report in Jasper Report?

Let's try to create a master-detail report. These reports are also called subreports.
Step1 : Create and populate the relevent master-detail tables.

SQL> CREATE TABLE EMPLOYEE
( ID NUMBER PRIMARY KEY,
FIRST_NAME CHAR(30),
LAST_NAME CHAR(30) );
SQL> CREATE TABLE ADDRESS
(ID NUMBER,
STREET CHAR(30),
CITY CHAR(30),
STATE CHAR(10),
COUNTRY CHAR(30),
POSTAL_CODE CHAR(30));
SQL> ALTER TABLE ADDRESS
ADD FOREIGN KEY(ID) REFERENCES EMPLOYEE(ID);
SQL> INSERT INTO EMPLOYEE VALUES (1,'King','Canber');
INSERT INTO EMPLOYEE VALUES (2,'Dehan','Duncan');
INSERT INTO EMPLOYEE VALUES (3,'Grant','Gibson');

SQL> INSERT INTO ADDRESS VALUES ( 1,'Street1','City1','State1','Country1','Code1');
INSERT INTO ADDRESS VALUES ( 1,'Street12','City12','State12','Country12','Code12');
INSERT INTO ADDRESS VALUES ( 2,'Street2','City2','State2','Country2','Code2');
INSERT INTO ADDRESS VALUES ( 3,'Street3','City3','State3','Country3','Code3');
Step 2: Create the employee report .
Step3: Drag and drop the sub-report , you will be prompt the sub-report dialogue box.
proceed with "create new report" and then write the query as
select * from addresswhere id = $P!{ID}

Note: You have to create a parmeter "ID".
Step4 : Now you are done with the detail report.
Step5 : And then you have to send the primary key of master report(employee) to the sub report(address). So you need to pass the ID. Open the properties of the sub report and add the parameter as shown below.

Step6 : Now you are done.



Thursday, June 4, 2009

How to create a pie chart in Jasper Reports?

Step1 : create a table and populate required records.

SQL> CREATE TABLE results
( party_name varchar2(30),
scored_percentage number );

SQL> INSERT INTO results VALUES ( 'UNP', 25);

SQL> INSERT INTO results VALUES ( UPFA, 60);

SQL> INSERT INTO results VALUES ( 'JVP', 5);

SQL> INSERT INTO results VALUES ( 'OTHER', 10);

Step2 : Open a new report in Jasper Report.


Step3 : Write the follwing sql query in the "report query"
select *
from results


Step4 : Drag and drop the "chart" on the summary area (i.e just after
the page footer ) and select the pie chart from the chart types.


Step5 : Right click on the chart and select "Chart Data"
set the Key expression as $F{PARTY_NAME}
Value expression as $F{SCORED_PERCENTAGE}
Label expression as
$F{PARTY_NAME}+" "+$F{SCORED_PERCENTAGE}+"%"


Step6 : Now You are done. and you will see the report preview as


Note : I've used iReport 3.1.4 here.