Before when working with dynamic sql we have to use DBMS_SQL which is a big mess and not
easy to maintain and debug, now in pl/sql we can write the dynamic sql just like any other front end tools.

If we want to analyze the user tables every week we should write a static SQL which includes all the tables. Say we added 10 more tables in the database then we need to change the sql. Instead of that we can write a following example so that it analyzes the tables all the all the tables.

Here we dont know the table name until we select it from the user tables. So concate the table name on the fly and use Execute Immediate to run it. If we don't have this Execute immediate we have to use DBMS_SQL which is a pain.

Example ( Execute Immediate )

DECLARE
  cursor c_table_name is select table_name from user_tables;
   v_Table_name user_tables.table_name%type;
   v_sql varchar(300);
   cursor c_index_name is select index_name from user_indexes;
   v_index_name user_indexes.index_name%type;
BEGIN
  Open c_table_name;
   Loop
       Fetch c_table_name into v_table_name;
       Exit when c_table_name%notfound;
        v_sql := 'analyze table '|| v_table_name || ' compute statistics'
        Execute Immediate v_sql;
   End Loop;
   Close c_table_name;
 
   Open c_index_name;
   Loop
       Fetch c_index_name into v_index_name;
       Exit when c_index_name%notfound;
       v_sql := 'analyze index ' || v_index_name || ' compute statistics ';
       Execute immediate v_sql ;
  End Loop;
  Close c_index_name;
END;
 

Example ( By passing value to an sql Using )

DECLARE
  TYPE CustCurType IS REF CURSOR;
  cust_cursor CustCurType;
  cust_rec Customer%rowtype;
  v_state_cd  char(2);
  v_sql varchar(1000);
BEGIN
  v_sql := 'select * from customer where state_cd = :1';
  v_state_cd = 'TX';
  Open cust_cursor FOR v_sql USING v_state_cd;
   LOOP
           Fetch cust_cursor into cust_rec;
           Exit when cust_cursor%NOTFOUND;
   END LOOP;
  Close cust_cursor;
END;
 

Example 3 (Dynamically build the whole SQL )
create or replace customerlists ( var_select in varchar2, var_from in varchar2, var_where in varchar2)
is
  type cust_list is ref cursor;
  var_cust_list cust_list;
 
begin
  open var_cust_list FOR
       var_select || var_from || var_where;
  Loop
     Fetch var_cust_list into col1, col2;
  End Loop;
End;