Oracle: Parsing



Hard Parsing

declare 
l_cursor integer default 0;
rc       integer default 0;
j        integer default 0;
BEGIN
l_cursor:=dbms_sql.open_cursor;
FOR i IN 1..3000 LOOP
  select lfs.nextval into j from dual;
  l_cursor:=dbms_sql.open_cursor;
  dbms_sql.parse(l_cursor,
    
'select count(*) from dual where 1='
  ||to_char(j)
,dbms_sql.native);
  rc:=dbms_sql.execute(l_cursor);
  dbms_sql.close_cursor(l_cursor);
  -- rc:=DBMS_SQL.FETCH_ROWS (l_cursor);
END LOOP;
END;
/

Soft Parsing

declare 
l_cursor integer default 0;
rc       integer default 0;
j        integer default 0;
BEGIN
l_cursor:=dbms_sql.open_cursor;
FOR i IN 1..3000 LOOP
 select lfs.nextval into j from dual;
 l_cursor:=dbms_sql.open_cursor;
 dbms_sql.parse(l_cursor,
  
'select count(*) from dual where 1=:id',
 dbms_sql.native);
 dbms_sql.bind_variable(l_cursor,':id',j);
 rc:=dbms_sql.execute(l_cursor);
 dbms_sql.close_cursor(l_cursor);
 -- rc:=DBMS_SQL.FETCH_ROWS (l_cursor);
END LOOP;
END;
/

Comments