JPA Identifier Generation Strategy

Applications can choose one of four different id generation strategies by specifying a strategy in the strategy element. The value can be any one of AUTO, TABLE, SEQUENCE, or IDENTITY enumerated values of the GenerationType enumerated type.
Table and sequence generators can be specifically defined and then reused by multiple entity classes. These generators are named and are globally accessible to all the entities in the persistence unit.

Automatic Id Generation

If an application does not care what kind of generation is used by the provider but wants generation to occur, it can specify a strategy of AUTO. This means that the provider will use whatever strategy it wants to generate identifiers.

@Id @GeneratedValue(strategy=GenerationType.AUTO)
private long id;

Id Generation Using a Table

The most flexible and portable way to generate identifiers is to use a database table. Not only will it port to different databases but it also allows for storing multiple different identifier sequences for different entities within the same table.
An id generation table should have two columns. The first column is a string type used to identify the particular generator sequence. It is the primary key for all the generators in the table. The second column is an integral type that stores the actual id sequence that is being generated. The value stored in this column is the last identifier that was allocated in the sequence. Each defined generator represents a row in the table.

CREATE TABLE "JPA"."ID_GEN" 
   ( "GEN_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
 "GEN_VAL" NUMBER NOT NULL ENABLE, 
  CONSTRAINT "ID_GEN_PK" PRIMARY KEY ("GEN_NAME"));

The easiest way to use a table to generate identifiers is to simply specify the generation strategy to be TABLE in the strategy element:

@TableGenerator(name="Emp_Gen")
@Id @GeneratedValue(generator="Emp_Gen")
private long id;

@TableGenerator(name="EmpGen",
    table="ID_GEN",
    pkColumnName="GEN_NAME",
    valueColumnName="GEN_VAL",
    pkColumnValue="Emp_Gen",
    initialValue=10,
    allocationSize=5)

@Id @GeneratedValue(generator="EmpGen")
private long id;

The table element: is the name of the database table which store generation name and value.
The name element: is the name of the generator becomes the value stored in the pkColumnName column for that row and is used by the provider to look up the generator to obtain its last allocated value.
The pkColumnName element: is the name of the primary key column in the table that uniquely identifies the generator.
The valueColumnName element: is the name of the column that stores the actual id sequence value being generated.
The pkColumnValue element: is the name of the pk Column Name record inserted before to generate this table ID.
The initialValue element: representing the last allocated identifier can be specified as part of the generator definition, but the default setting of 0 will suffice in almost every case. This setting is used only during schema generation when the table is created. During subsequent executions, the provider will read the contents of the value column to determine the next identifier to give out.
The allocationSize element: To avoid updating the row for every single identifier that gets requested, an allocation size is used. This will cause the provider to preallocate a block of identifiers and then give out identifiers from memory as requested until the block is used up. Once this block is used up, the next request for an identifier triggers another block of identifiers to be preallocated, and the identifier value is incremented by the allocation size. By default, the allocation size is set to 50. This value can be overridden to be larger or smaller through the use of the allocationSize element when defining the generator.

Id Generation Using a Database Sequence

Many databases support an internal mechanism for id generation called sequences. A database sequence can be used to generate identifiers when the underlying database supports them.

CREATE SEQUENCE EMP_SEQ 
MINVALUE 1 MAXVALUE 999999999999999999999999999 
INCREMENT BY 1;

Unless schema generation is enabled, it would require that the sequence be defined and already exist. The SQL to create such a sequence would be as follows:

@SequenceGenerator(name="Emp_Gen", sequenceName="EMP_SEQ ")
@Id @GeneratedValue(generator="Emp_Gen")
private long getId;

The initial value and allocation size can also be used in sequence generators and would need to be reflected in the SQL to create the sequence. Note that the default allocation size is 50, just as it is with table generators. If schema generation is not being used, and the sequence is being manually created, the INCREMENT BY clause would need to be configured to match the allocationSize element or default allocation size of the corresponding @SequenceGenerator annotation.

Id Generation Using Database Identity

Some databases support a primary key identity column, sometimes referred to as an autonumber column. Whenever a row is inserted into the table, the identity column will get a unique identifier assigned to it. It can be used to generate the identifiers for objects, but once again is available only when the underlying database supports it. Identity is often used when database sequences are not supported by the database or because a legacy schema has already defined the table to use identity columns. They are generally less efficient for object-relational identifier generation because they cannot be allocated in blocks and because the identifier is not available until after commit time.

@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
private long id;

There is no generator annotation for IDENTITY because it must be defined as part of the database schema definition for the primary key column of the entity. Because each entity primary key column defines its own identity characteristic, IDENTITY generation cannot be shared across multiple entity types.

Reference: Pro JPA 2 Mastering the Java™ Persistence API By Mike Keith , Merrick Schincariol

Factors that you consider for compiling a PL/SQL program unit

Factors that you consider for compiling a PL/SQL program unit for interpreted mode.

• A PL/SQL program which needs to be recompiled frequently
• A PL/SQL program that spends most of the time executing SQL

Types of PL/SQL programs for which you consider setting the compilation method to native mode.

• PL/SQL programs that have computation-intensive procedural operations
• A PL/SQL program, which is called with the same parameters by multiple sessions

Displays all PL/SQL code objects

To displays all PL/SQL code objects created by user OE that reference any table or view owned by other users except SYS and SYSTEM, use this code

   
SELECT 
owner || '.' || NAME refs_table
, referenced_owner || '.' || referenced_name AS table_referenced
FROM all_dependencies
WHERE owner = USER
AND TYPE IN ('PACKAGE', 'PACKAGE BODY','PROCEDURE', 'FUNCTION')
AND referenced_type IN ('TABLE', 'VIEW')
AND referenced_owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, NAME, referenced_owner, referenced_name;

Oracle PL Data Structure

• Varrays must use sequential numbers as subscripts.
• The DELETE(n) method cannot be used with varrays.
• Both associative arrays and nested tables can hold an arbitrary number of elements.
• Both nested tables and varrays can be used as column types in database tables.
• Associative arrays can use numbers and strings for subscripts.
• Nested tables LAST and COUNT may give different values.

SQL Query Result Cache

True statements about the SQL Query Result Cache:

1. It can be set at the system, session, or query level.
2. Cached query results become invalid when the data accessed by the query is modified.
3. Conditions must be true for a PL/SQL function to be result cached
1. It must not be defined in an anonymous block.
2. It must have at least one OUT or IN OUT parameter. 

Types of query results cannot be stored in the query result cache:

1. Subquery results
2. Results of a query having the SYSDATE function
3. The query on dictionary and temporary tables
4. Queries having SYSDATE and SYS_TIMESTAMP SQL functions
5. For Functions
1. If the first time a session on this database instance invokes the function with a parameter value.
2. If a session executes a data manipulation language (DML) statement on a table or view that was specified in the RELIES_ON clause of a result-cached function.

Actions can be performed by using the DBMS_ASSERT package to prevent SQL injection

1. Prefix all calls to DBMS_ASSERT with the SYS schema name.
2. Escape single quotes when you use the ENQUOTE_LITERAL procedure.
3. Define and raise exceptions explicitly to handle DBMS_ASSERT exceptions.
4. Verify qualified SQL names.
5. Verify an existing schema name.
6. Enclose string literals within double quotation marks.

Strategies against SQL injection.

1. In PL/SQL API, expose only those routines that are intended for customer use.
2. Do not use APIs that allow arbitrary query parameters to be exposed.
3. Restrict users accessing specified web page.
4. Use static SQL as a technique when all Oracle identifiers are known at the time of code compilation
5. Using parameterized queries with bind arguments.
6. Validate user inputs to functions that use dynamic SQLs built with concatenated values.
7. Validate all input concatenated to dynamic SQL.