• Support for Oracle Grid Computing
• Support for JDK 1.5 and JDK 1.6 (strictfp, Assert, Generics, Enhanced for Loop Autoboxing/Unboxing, Typesafe Enums, Varargs, Static Import, Annotation)
• Enhanced SQLJ Profile Print Option
• Outline Generation - Execution Plan Fixing
• SQLJ Support for Global Transactions
  • Connection Contexts
  • Execution Contexts
  • You can use SQLJ in writing multithreaded applications. However, any use of multithreading in your SQLJ application is subject to the limitations of your JDBC driver or proprietary database access vehicle. This includes any synchronization limitations.
  • Advanced Transaction Control: #sql { SET TRANSACTION , };
  • SQLJ and JDBC InteroperabilityOracle JDBCSQLJ statements are typically used for static SQL operations. Oracle Database 11g has extensions to support dynamic SQL as well, but another alternative is to use JDBC code within your SQLJ application for dynamic operations, which would be more portable.
  • The Oracle SQLJ implementation includes extensions to support dynamic SQL, operations that are not predefined and can change in real time.
    Dynamic SQL expressions embedded in SQLJ statements are referred to as meta bind expressions.
    Using JDBC code is still an option for dynamic SQL in Oracle Database 11g and might be preferable if code portability is a concern, but SQLJ support for dynamic SQL permits use of SQLJ as a single, simplified API for data access.
• Oracle Audit Vault: The audit trail goes into a one-way "lockbox." No one, not even a highly skilled DBA, can alter that audit trail once it's in the lockbox. People can view it, but no one can change it or delete it.
• Hot Patching: you can run your patches, including security fixes, without affecting production.
• Oracle Database 11g is the ability to store data in compressed format—called advanced table compression.
• Query Result Cache.

• JHeadstart with JSF support: JSF ADF Faces pages: Instead of generating UIX pages, generates ADF Faces pages (in .jspx format).
• Generation of Faces-config. JSF is used as the Controller instead of Struts.
  As a result, managed bean definitions and navigation rules are generated in the faces-config, instead of the old struts-config generation.
• Oracle TopLink: Mapping support for XDB XML Type mappings
• Isolated client-session cache and cache expiration
• Use of JAXB validation and compiler (Java Architecture for XML Binding).
• Support for Object-XML projects and mappings
• JDeveloper:Preconfigured OC4J (OC4J:containers, APIs, services mandated by the J2EE specification.)
• Java Persistence API
• EJB 3.0 Inheritance support

var v2 varchar2(200);

var v3 varchar2(200);

select sysdate from dual;

EXEC QUERY_TESTED('V00177', 'ST70', 'ST70', 'ST70', 'ST70', :v2,:v3);

print v2;

print v3;

create or replace view scott.emp_jp_view

as

select
 
a.empno, 

decode(b.username,'GREG',a.ename,'***') ename, 

decode(b.username,'GREG',a.job,'***') job, 

decode(b.username,'GREG',a.mgr,0) mgr, 

a.hiredate,

decode(b.username,'GREG',a.sal,0)  sal,

a.comm,

a.deptno

from scott.emp a,

(select sys_context('USERENV','SESSION_USER') username from dual) b;

connect scott/tiger

select * from scott.emp_jp_view;

connect GREG/greg

select * from scott.emp_jp_view;

connect scott/tiger;

create or replace view   scott.emp_jp_view as  

select a.empno, a.ename, a.job, a.mgr, a.hiredate,a.sal,a.comm,a.deptno

from scott.emp a,

(select sys_context('USERENV','SESSION_USER')  username from dual) loggedEmployee

where a.ename = loggedEmployee.username;

/*Grant select on scott.emp_jp_view to loggedEmployee; not needed in 9i */ 

connect greg/greg

select * from scott.emp_jp_view;

connect scott/tiger

select * from scott.emp_jp_view;

CREATE OR REPLACE PROCEDURE ABS.RETURN_LAST_TESTED( RTESTPLANRUN OUT INTEGER)

AS

BEGIN

   SELECT GEN_TESTPLANRUN.NEXTVAL INTO RTESTPLANRUN FROM DUAL;

END;

alter sequence ABS.GEN_TESTPLANRUN increment by 20000000;

SELECT ABS.GEN_TESTPLANRUN.NEXTVAL FROM DUAL;

alter sequence ABS.GEN_TESTPLANRUN increment by 1;

SELECT ABS.GEN_TESTPLANRUN.NEXTVAL FROM DUAL;

IS

-- Define local variables

h       NUMBER;         -- handle returned by 'OPEN'

th      NUMBER;         -- handle returned by 'ADD_TRANSFORM'

doc     CLOB;           -- metadata is returned in a CLOB

BEGIN

 -- Specify the object type.

 h := DBMS_METADATA .OPEN('TABLE');

 -- Use filters to specify the schema.

 DBMS_METADATA.SET_FILTER(h,'SCHEMA','ABS');

 -- Request that the metadata be transformed into creation DDL.

 th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

 -- Fetch the objects.

 LOOP

   doc := DBMS_METADATA.FETCH_CLOB(h);

  -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.

   EXIT WHEN doc IS NULL;

   -- Store the metadata in a table.

   INSERT INTO ABS.my_metadataABS(md) VALUES (doc);

   COMMIT;

 END LOOP;

 -- Release resources.

 DBMS_METADATA.CLOSE(h);

 
 /* 

 

 first execute the below in SQL worksheet

DROP TABLE ABS.my_metadataABS;

CREATE TABLE ABS.my_metadataABS (md clob);

CREATE OR REPLACE PROCEDURE get_tables_md IS

-- Define local va riables

h       NUMBER;         -- handle returned by 'OPEN'

th      NUMBER;         -- handle returned by 'ADD_TRANSFORM'

doc     CLOB;           -- metadata is returned in a CLOB

BEGIN

 -- Specify the object type.

 h := DBMS_METADATA.OPEN('TABLE');

 -- Use filters to specify the schema.

 DBMS_METADATA.SET_FILTER(h,'SCHEMA','M22');

 -- Request that the metadata be transformed into creation DDL.

 th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

 -- Fetch the objects.

 LOOP

   doc := DBMS_METADATA.FETCH_CLOB(h);

  -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.

   EXIT WHEN doc IS NULL; 

   -- Store the metadata in a table.

   INSERT INTO ABS.my_metadataABS(md) VALUES (doc);

   COMMIT;

 END LOOP;

 

 -- Release resources.

 DBMS_METADATA.CLOSE(h);

END;

*/


/*

Then invoke running this:

CONNECT ABS/abs@IBM0050

EXECUTE GET_TABLES_ABS;

SET LONG 9000000

SET PAGES 0

SELECT * FROM ABS.my_metadataABS;

*/

 
END;