Technical blogs: 2013

Sunday, February 3, 2013

Bind Variables with PL/SQL

Static SQL
========
When you are using the static SQL with PL/SQL then the PL/SQL will automatically bind the variables in WHERE clause with the Values provided as parameter to PL/SQL block.

For Example

CREATE OR REPLACE PROCEDURE fetchEmpName(id IN NUMBER)

BEGIN

SELECT empName FROM emp
WHERE empId = id
END;

For the above PL/SQL block, the id parameter is bind with the WHERE clause empId variable. Oracle will use the same compiled code for the execution of the enclosed query in PL/SQL block. This will save the time required to parse the query again and again. Will execute the same query plan for each call. This will dramatically increase the performance of execution.

Dynamic SQL
==========
In case of Dynamic SQL we need to bind the variable explicitly with the WHERE clause column by USING.

In the below sample procedure we are doubling the salary by binding the procedure IN parameter
'p_empno ' with WHERE clause column 'empno' by USING.

Example:
CREATE OR REPLACE PROCEDURE doubleSalary(p_empno IN NUMBER)
AS
BEGIN
execute immediate
'update emp set
sal = sal * 2
WHERE empno = :x' USING p_empno;
COMMIT;
END;

PL SQL Parameters (IN, OUT, IN OUT)

Three types assigning values to variables in PL/SQL

By assignment expression à variable := value;

Example:

ord_id := 291;

2. Assign value to variable by fetching the database values into it

Example:

DECLARE

amount NUMBER(9,2);

ord_id NUMBER(6) := 291;

BEGIN

SELECT bill_amount INTO amount FROM Orders

WHERE order_id = ord_id;

END;

3. Assign value to variable by passing it as an OUT, IN or IN OUT parameter to a sub program.

a. OUT parameter returns the value from PL SQL function or procedure.

Example:

CREATE OR REPLACE PROCEDURE P_GET_SAL (P_EMPID NUMBER, P_SAL OUT NUMBER)

BEGIN

SELECT SALARY INTO P_SAL

FROM EMPLOYEE

WHERE EMPLOYEE_ID=P_EMPID;

END;

Usage:

SQL> VAR G_SAL NUMBER;

SQL> EXEC P_GET_SAL(100,:G_SAL);

PL/SQL procedure successfully completed.

SQL> PRINT G_SAL;

G_SAL

----------

2300

b. IN parameter passed the value in PL SQL function or procedure.

Example:

CREATE OR REPLACE PROCEDURE empName (empid IN NUMBER, empName OUT VARCHAR2(100))

BEGIN

SELECT firstName INTO empName

FROM emp WHERE empID = empid;

END;

c. IN OUT parameter both pass in the value in function or procedure and can return back after modify the value with in the procedure.

Example:

CREATE OR REPLACE PROCEDURE empNameAndSal (empid IN NUMBER, empName OUT VARCHAR2(100), empSal IN OUT NUMBER)

BEGIN

SELECT firstName INTO empName

FROM emp WHERE empID = empid;

empSal := empSal *2;

END;

Wednesday, January 16, 2013

Hibernate Tools Tutorial

You need to have the following tool installed on your machine.

Eclipse
JBoss Hibernate Tools plugin for eclipse
Oracle jdbc driver
Required Hibernate Jars

Follow the very simple steps to configure hibernate with JSF.

Open the Hibernate Tools perspective in Eclipse.

Go to the Hibernate Configuration Area in the left panel and upon right clicking in that panel a context menu will appear. Select the “Add Configuration” option from that menu.

Upon selecting the “Add Configuration” option a popup will appear asking for the configuration edit. Please provide the name of configuration, Configuration Type and Hibernate version.

Next Select the JSF Project with which you want to integrate hibernate configuration. and press the OK button.

Now create the oracle database connection for hibernate configuration by clicking New Button.

Upon Pressing New Button. A popup will appear with title connection profile. Select Oracle from the available list and give the connection profile a name and press Next Button.

Upon pressing Next Button the popup will ask for the Connection and Driver details. Provide the driver details as follows.

Select the JAR List from the available tabs and provide the path of driver’s jar file.

Set the driver properties by clicking on Properties tab.

Once you complete the driver’s settings. Then the driver’s information updated in the “Specify a Driver and Connection Details” popup as follows. Select the Save Password checkbox to keep remember the password and select “User” from Catalog.

Press the “Test Connection” button to test the DB connection. If the connection details are correct then you have the success message in the popup as follows.

Press Finish button to complete the connection.

Next Create the cfg.xml configuration file by click “Setup Button” in front of Configuration file option as follows. A new popup will appear asking for Create new configuration file or use existing configuration file.

Upon clicking “Create New…” button, you will asked to provide the path and name of the configuration file as follows. Then press Next button.

Upon pressing Next button. You need to provide configuration details for hibernate as follows and press Finish.

Upon pressing “Finish”. The configuration file name loaded in the “Specify a Driver and Connection Details” popup as follows. Now press Apply and OK button to complete the configuration.

Now you have new configuration appeared in the left panel.

Now right click the configuration name in the left panel and select “Edit Configuration” to update the database dialect.

Now select the database Dialect from the available list under the Options Tab. Then press Apply and OK.

Now expand hibernate configuration node from the left panel and then expand Database node. Database schemas populated if the configuration is correct.

From the option bar on top select the Hibernate Code Generation and select the “Hibernate Code Generation Configuration” option from the menu.

To generate the code Select configuration that we created before, provide the path of output directory and select the check box against “Reverse engineer from JDBC Connection”, provide the optional package name then create setup button to generatge “reveng.xml” file.