INTERVIEW QUESTIONS J2EE JSP DETAILS

Question: How do you call stored procedures from JSP?

Answer: Creating a Stored Procedure or Function in an Oracle Database
-------------------------------------------------------------

A stored procedure or function can be created with no parameters, IN parameters, OUT parameters, or IN/OUT parameters. There can be many parameters per stored procedure or function.
An IN parameter is a parameter whose value is passed into a stored procedure/function module. The value of an IN parameter is a constant; it can't be changed or reassigned within the module.

An OUT parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module. You cannot assign a default value to an OUT parameter outside of the module's body. In other words, an OUT parameter behaves like an uninitialized variable.

An IN/OUT parameter is a parameter that functions as an IN or an OUT parameter or both. The value of the IN/OUT parameter is passed into the stored procedure/function and a new value can be assigned to the parameter and passed out of the module. An IN/OUT parameter must be a variable, not a constant. However, it can be found on both sides of an assignment. In other words, an IN/OUT parameter behaves like an initialized variable.

This example creates stored procedures and functions demonstrating each type of parameter.


try {
// To create a connection to an Oracle database,
// see e235 Connecting to an Oracle Database
Statement stmt = connection.createStatement();

//**************************************************************
// Create procedure myproc with no parameters
String procedure =
"CREATE OR REPLACE PROCEDURE myproc IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES('string 1'); "
+ "END;";
stmt.executeUpdate(procedure);

//**************************************************************
// Create procedure myprocin with an IN parameter named x.
// IN is the default mode for parameter, so both `x VARCHAR' and `x IN VARCHAR' are valid
procedure =
"CREATE OR REPLACE PROCEDURE myprocin(x VARCHAR) IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES(x); "
+ "END;";
stmt.executeUpdate(procedure);

//**************************************************************
// Create procedure myprocout with an OUT parameter named x
procedure =
"CREATE OR REPLACE PROCEDURE myprocout(x OUT VARCHAR) IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES('string 2'); "
+ "x := 'outvalue'; " // Assign a value to x
+ "END;";
stmt.executeUpdate(procedure);

//**************************************************************
// Create procedure myprocinout with an IN/OUT parameter named x;
// x functions as an IN parameter and also as an OUT parameter
procedure =
"CREATE OR REPLACE PROCEDURE myprocinout(x IN OUT VARCHAR) IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES(x); " // Use x as IN parameter
+ "x := 'outvalue'; " // Use x as OUT parameter
+ "END;";
stmt.executeUpdate(procedure);

//**************************************************************
// Create a function named myfunc which returns a VARCHAR value;
// the function has no parameter
String function =
"CREATE OR REPLACE FUNCTION myfunc RETURN VARCHAR IS "
+ "BEGIN "
+ "RETURN 'a returned string'; "
+ "END;";
stmt.executeUpdate(function);

//**************************************************************
// Create a function named myfuncin which returns a VARCHAR value;
// the function has an IN parameter named x
function =
"CREATE OR REPLACE FUNCTION myfuncin(x VARCHAR) RETURN VARCHAR IS "
+ "BEGIN "
+ "RETURN 'a return string'||x; "
+ "END;";
stmt.executeUpdate(function);

//**************************************************************
// Create a function named myfuncout which returns a VARCHAR value;
// the function has an OUT parameter named x whose value is
// returned to the calling PL/SQL block when the execution of the function ends
function =
"CREATE OR REPLACE FUNCTION myfuncout(x OUT VARCHAR) RETURN VARCHAR IS "
+ "BEGIN "
+ "x:= 'outvalue'; "
+ "RETURN 'a returned string'; "
+ "END;";
stmt.executeUpdate(function);

//**************************************************************
// Create a function named myfuncinout that returns a VARCHAR value;
// the function has an IN/OUT parameter named x. As an IN parameter, the value of x is
// defined in the calling PL/SQL block before it is passed in eyfuncinout
// function. As an OUT parameter, the new value of x, `x value||outvalue', is also
// returned to the calling PL/SQL block when the execution of the function ends.
function =
"CREATE OR REPLACE FUNCTION myfuncinout(x IN OUT VARCHAR) RETURN VARCHAR IS "
+ "BEGIN "
+ "x:= x||'outvalue'; "
+ "RETURN 'a returned string'; "
+ "END;";
stmt.executeUpdate(function);
//**************************************************************
} catch (SQLException e) {
}


Submitted by Nitin Gupta ([email protected])

Creating a Stored Procedure or Function in an Oracle Database
-------------------------------------------------------------

A stored procedure or function can be created with no parameters, IN parameters, OUT parameters, or IN/OUT parameters. There can be many parameters per stored procedure or function.
An IN parameter is a parameter whose value is passed into a stored procedure/function module. The value of an IN parameter is a constant; it can't be changed or reassigned within the module.

An OUT parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module. You cannot assign a default value to an OUT parameter outside of the module's body. In other words, an OUT parameter behaves like an uninitialized variable.

An IN/OUT parameter is a parameter that functions as an IN or an OUT parameter or both. The value of the IN/OUT parameter is passed into the stored procedure/function and a new value can be assigned to the parameter and passed out of the module. An IN/OUT parameter must be a variable, not a constant. However, it can be found on both sides of an assignment. In other words, an IN/OUT parameter behaves like an initialized variable.

This example creates stored procedures and functions demonstrating each type of parameter.


try {
// To create a connection to an Oracle database,
// see e235 Connecting to an Oracle Database
Statement stmt = connection.createStatement();

//**************************************************************
// Create procedure myproc with no parameters
String procedure =
"CREATE OR REPLACE PROCEDURE myproc IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES('string 1'); "
+ "END;";
stmt.executeUpdate(procedure);

//**************************************************************
// Create procedure myprocin with an IN parameter named x.
// IN is the default mode for parameter, so both `x VARCHAR' and `x IN VARCHAR' are valid
procedure =
"CREATE OR REPLACE PROCEDURE myprocin(x VARCHAR) IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES(x); "
+ "END;";
stmt.executeUpdate(procedure);

//**************************************************************
// Create procedure myprocout with an OUT parameter named x
procedure =
"CREATE OR REPLACE PROCEDURE myprocout(x OUT VARCHAR) IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES('string 2'); "
+ "x := 'outvalue'; " // Assign a value to x
+ "END;";
stmt.executeUpdate(procedure);

//**************************************************************
// Create procedure myprocinout with an IN/OUT parameter named x;
// x functions as an IN parameter and also as an OUT parameter
procedure =
"CREATE OR REPLACE PROCEDURE myprocinout(x IN OUT VARCHAR) IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES(x); " // Use x as IN parameter
+ "x := 'outvalue'; " // Use x as OUT parameter
+ "END;";
stmt.executeUpdate(procedure);

//**************************************************************
// Create a function named myfunc which returns a VARCHAR value;
// the function has no parameter
String function =
"CREATE OR REPLACE FUNCTION myfunc RETURN VARCHAR IS "
+ "BEGIN "
+ "RETURN 'a returned string'; "
+ "END;";
stmt.executeUpdate(function);

//**************************************************************
// Create a function named myfuncin which returns a VARCHAR value;
// the function has an IN parameter named x
function =
"CREATE OR REPLACE FUNCTION myfuncin(x VARCHAR) RETURN VARCHAR IS "
+ "BEGIN "
+ "RETURN 'a return string'||x; "
+ "END;";
stmt.executeUpdate(function);

//**************************************************************
// Create a function named myfuncout which returns a VARCHAR value;
// the function has an OUT parameter named x whose value is
// returned to the calling PL/SQL block when the execution of the function ends
function =
"CREATE OR REPLACE FUNCTION myfuncout(x OUT VARCHAR) RETURN VARCHAR IS "
+ "BEGIN "
+ "x:= 'outvalue'; "
+ "RETURN 'a returned string'; "
+ "END;";
stmt.executeUpdate(function);

//**************************************************************
// Create a function named myfuncinout that returns a VARCHAR value;
// the function has an IN/OUT parameter named x. As an IN parameter, the value of x is
// defined in the calling PL/SQL block before it is passed in eyfuncinout
// function. As an OUT parameter, the new value of x, `x value||outvalue', is also
// returned to the calling PL/SQL block when the execution of the function ends.
function =
"CREATE OR REPLACE FUNCTION myfuncinout(x IN OUT VARCHAR) RETURN VARCHAR IS "
+ "BEGIN "
+ "x:= x||'outvalue'; "
+ "RETURN 'a returned string'; "
+ "END;";
stmt.executeUpdate(function);
//**************************************************************
} catch (SQLException e) {
}


Submitted by Nitin Gupta ([email protected]) Source: CoolInterview.com

Use JSTL or create your custom tag
an example

<%@ taglib uri="java2s.com.tags" prefix="cbck" %>
<html>
<head><title>Calling a Stored procedure</title></head>
<body>
<h2>This JSP calls a stored procedure with a JSP 2.0 function</h2>

${cbck:addEvent("Event Name","place","16-Jul-2005")}

</body>
</html>


JSTL contains 4 types of tags

1)core tags
2)formatting tags
3)xml tags
4)sql tags

you can use sql tags for the purpose Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question