oracle plsql EXECUTE IMMEDIATE - ghdrako/doc_snipets GitHub Wiki

DECLARE
  sql_stmt VARCHAR2(100);
  v_zip VARCHAR2(5) := '&sv_zip';
  v_total_rows NUMBER;
BEGIN
  sql_stmt :=
  'CREATE TABLE my_student AS '||
  'SELECT * FROM student WHERE zip = '||v_zip;
  EXECUTE IMMEDIATE sql_stmt;

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student'
  INTO v_total_rows;
  DBMS_OUTPUT.PUT_LINE ('Table my_student has '||
  v_total_rows||' rows for zip code '||v_zip);
END;