• OPEN_CURSOR - открывет курсор и возвращает его номер
• PARSE - проверяет выражение
• BIND_VARIABLE - связывает переменные
• EXECUTE - выполняет выражение и возвращает еффективное число записей
• FETCH_ROWS - возвращает ряд
• CLOSE_CURSOR - закрывает курсор

• ALTER_COMPILE
• ANALYZE_OBJECT

CREATE OR REPLACE PROCEDURE delete_all
( p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER)
IS
 cursor_name INTEGER;
BEGIN
 cursor_name := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE( cursor_name, 'DELETE FROM ' || p_tab_name, DBMS_SQL.NATIVE );
 p_rows_del := DBMS_SQL.EXECUTE( cursor_name );
 DBMS_SQL.CLOSE_CURSOR( cursor_name );
 
END delete_all;

declare 
 p_vid int;
 sql_stmt  VARCHAR2(200);
begin
  p_vid := 1;
  sql_stmt := 'delete log_import where vid=:1';
  EXECUTE IMMEDIATE sql_stmt USING IN p_vid;
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Invalid Column: ');

end;

declare
     h_cursor integer;
     v_res int;
begin
  h_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(h_cursor,'delete log_import where vid=:VID', dbms_sql.native); 
  dbms_sql.bind_variable(h_cursor, 'VID', 1);
  v_res := dbms_sql.execute(h_cursor);
  dbms_sql.close_cursor(h_cursor);
end;

PROCEDURE fill_block (where_clause_in IN VARCHAR2)
IS
   c INTEGER := DBMS_SQL.OPEN_CURSOR;
   emprec employee%ROWTYPE;
   fdbk INTEGER;
BEGIN
   /* Parse the query  with a dynamic WHERE clause */
   DBMS_SQL.PARSE (c, 
      'SELECT employee_id, last_name ' ||
      '  FROM employee WHERE ' || where_clause_in,
      DBMS_SQL.NATIVE);

   /* Define the columns in the cursor for this query */
   DBMS_SQL.DEFINE_COLUMN (c, 1, emprec.empno);
   DBMS_SQL.DEFINE_COLUMN (c, 2, emprec.ename, 30);

   /* Now I can execute the query */
   fdbk:= DBMS_SQL.EXECUTE (c);
   LOOP
      /* Try to fetch next row. If done, then exit the loop. */
      EXIT WHEN DBMS_SQL.FETCH_ROWS (c) = 0;
      /*
      || Retrieve data via calls to COLUMN_VALUE and place those
      || values in a new record in the block.
      */
      DBMS_SQL.COLUMN_VALUE (c, 1, emprec.empno);
      DBMS_SQL.COLUMN_VALUE (c, 2, emprec.ename);
      CREATE_RECORD;
      :employee.employee_id := emprec.empno;
      :employee.employee_nm := emprec.ename;
   END LOOP;

   /* Clean up the cursor */
   DBMS_SQL.CLOSE_CURSOR (c);
END;