RESULT SETS EXPLORED in Font

Generating Code 39 Extended in Font RESULT SETS EXPLORED

} private static String ename = null; private static int deptno = -1; } // end of program Let s run the program now. For Case 1, to retrieve all records, we execute the program as follows: B:\>java DemoDynamicQueryUsingPstmt URL:jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=rmeno n-lap))(CONNECT_DATA=(SID=ora10g))) SMITH, 20, CLERK, 800 ALLEN, 30, SALESMAN, 1600 <- trimmed to conserve space -> FORD, 20, ANALYST, 3000 MILLER, 10, CLERK, 5308 To print employees whose name begins with A (Case 2), we execute the program as follows: B:\>java DemoDynamicQueryUsingPstmt A ename = A URL:jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521) (HOST=rmenon-lap))(CONNECT_DATA=(SID=ora10g))) ALLEN, 30, SALESMAN, 1600 ADAMS, 20, CLERK, 1100 To print all records of department 10 (Case 3), we execute the program as follows: B:\>java DemoDynamicQueryUsingPstmt null 10 deptno = 10 URL:jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=rmeno n-lap))(CONNECT_DATA=(SID=ora10g))) CLARK, 10, MANAGER, 8268 KING, 10, PRESIDENT, 16875 MILLER, 10, CLERK, 5308 And finally, to print all records of department 20 and for which the employee names begin with A (Case 4), we execute the program as follows: B:\>java DemoDynamicQueryUsingPstmt A 20 ename = A deptno = 20 URL:jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=rmeno n-lap))(CONNECT_DATA=(SID=ora10g))) ADAMS, 20, CLERK, 1100 Next, we ll look at an approach that solves the same problem, but this time using PL/SQL code in conjunction with the CallableStatement interface.

The CallableStatement-based solution uses a PL/SQL stored procedure to dynamically generate the query and return a ref cursor to it to the Java program. There are two methods to generate and execute a dynamic SQL statement in PL/SQL: Use the DBMS_SQL PL/SQL package. Please see the description of this package in the document PL/SQL Packages and Types Reference (Oracle 10g Release 1). Unfortunately, it is not possible to return a ref cursor using this PL/SQL package, which is what we need. Use execute immediate (native dynamic SQL). This category of dynamic SQL uses the execute immediate statement to dynamically execute an SQL command. Please see the section Performing SQL Operations Using Native Dynamic SQL in PL/SQL User s Guide and Reference (Oracle 10g Release 1). This technique to generate a query requires that the number of bind variables be known at compile time. Recall that in our particular scenario, the number of bind variables is only known at runtime. Fortunately, we can overcome this problem by combining the concept of application contexts and native dynamic SQL. Let s briefly look at application contexts next.

An application context provides you with a namespace with which you can associate arbitrary string name/value pairs. The application context itself is bound to a trusted PL/SQL package or procedure. To set a name/value pair in an application context, you have to use the PL/SQL package or procedure. This is for security reasons, to ensure that no one can set a value in the application context because the values in these contexts may be driving query criteria and the corresponding results. To get the value of a given name stored as a name/value pair in an application context, use the sys_context() function, which works in both SQL and PL/SQL. To create an application context, the user needs to have the create any context privilege apart from the privileges connect and resource. We will grant create any context to the SCOTT user first: sys@ORA10G> grant create any context to scott; Grant succeeded. Next, we ll create the application context: scott@ORA10G> create or replace context hr_app_ctx using hr_app_ctx_pkg; Context created. We create a context called HR_APP_CTX, which we can set using the PL/SQL package HR_APP_CTX_PKG. To set a value in a context, we need to use the dbms_session.set_context procedure (please see PL/SQL Packages and Types Reference [10g Release 1] for more details on this package). To verify that we can set the values in this context only via the package HR_APP_CTX_PKG, we ll try to set the value in an anonymous PL/SQL block: scott@ORA10G> begin 2 dbms_session.set_context( 'HR_APP_CTX', 'ENAME', 'BLAKE'); 3 end;