Parsing in oracle

Parsing

The following operations are made during the parsing.

1. Validate the syntax of the statement: is the query a valid SQL statement?
   
   SQL> select nothing where 1=2;
select nothing where 1=2
               *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
2. Validate the semantic of the statement: are the objects valid? is there any ambiguity? have you the privilege to access them? does the constant fit into the column?...
   
   SQL> select col from not_existent_table;
select col from not_existent_table
                *
ERROR at line 1:
ORA-00942: table or view does not exist
3. Search in the shared pool:
   • Is the query text already known (search among all the query texts)?
     if not, go to step 4
   • Does the query referenced the same objects (search among all versions of the query)?
     if not, go to step 4
   • Is the execution environment identical (same search)? If yes, execute the query.
4. Allocate memory in the shared pool to store the data about the query
5. Get the values of the bind variables and check if all values fit in the columns
   
   SQL> var v varchar2(20);
SQL> exec :v := '12345678901'
PL/SQL procedure successfully completed.
SQL> insert into michel.t values (:v);
insert into michel.t values (:v)
                              *
ERROR at line 1:
ORA-12899: value too large for column "MICHEL"."T"."COL" (actual: 11, maximum: 10)
6. Optimize the query execution
7. Build the parse tree and the execution plan in a format that the SQL engine can use, this is named row source generation
8. Store the parse tree and the execution plan in the shared pool.

The execution of all these steps is named a hard parse. If Oracle executes only the steps 1 to 3, it is named a soft parse.