Oracle problem adding debug information to procedure

Question

I am trying to add some DEBUGGING information into my procedure and I can't seem to get it to compile eventhough most of it is commented out. The problem appears to be on line 29. Any help would be greatly appreciated.

create or replace
  procedure drop_partition(
                           p_tab varchar2,
                           p_date date
                          )
    authid current_user
    is
        v_high_value date;
        v_sql_stmt varchar2(200);
        cursor v_cur
          is
            select  table_name,
                    partition_name,
                    high_value,
                    partition_position
              from  user_tab_partitions
              where table_name = upper(p_tab);
    begin
        for v_rec in v_cur loop
          execute immediate 'select ' || v_rec.high_value || ' from dual'
            into v_high_value;
  
  if ( v_high_value <=
          trunc(p_date)) and 
v_rec.partition_position != 1
      then
     dbms_output.put_line ('partition ' || v_rec.partition_name || ' high value is ' || to_char(v_high_value,'mm/dd/yyyy'));

-- v_sql_stmt :=
 'EXECUTE IMMEDIATE ALTER TABLE ' || p_tab || ' DROP PARTITION FOR(DATE ' || TO_CHAR(v_high_value,'YYYY-MM-DD') || ')';

v_sql_stmt := 'execute immediate alter table '||p_tab||' drop partition '||v_rec.partition_name';';


--dbms_output.put_line(v_sql_stmt);

--EXECUTE IMMEDIATE v_sql_stmt;

    end if;    
  end loop;
end;
/

Answer 1

v_sql_stmt := 'execute immediate alter table '||p_tab||' drop partition '||v_rec.partition_name';';

Is missing a || operator near the end.

You also don't want to use EXECUTE IMMEDIATE inside a string that you are going to execute with EXECUTE IMMEDIATE.

Something like this:

create procedure drop_partition(
  p_tab varchar2,
  p_date date
) authid current_user
is
  v_high_value date;
  v_sql_stmt varchar2(200);
  cursor v_cur is
    select table_name,
           partition_name,
           high_value,
           partition_position
    from   user_tab_partitions
    where  table_name = upper(p_tab);
begin
  for v_rec in v_cur loop
    execute immediate 'select ' || v_rec.high_value || ' from dual' into v_high_value;
    if     v_high_value <= trunc(p_date)
       and v_rec.partition_position != 1
    then
      dbms_output.put_line (
        'partition ' || v_rec.partition_name || ' high value is '
        || to_char(v_high_value, 'mm/dd/yyyy')
      );

      v_sql_stmt := 'alter table '||p_tab||' drop partition '||v_rec.partition_name;

      dbms_output.put_line(v_sql_stmt);
      EXECUTE IMMEDIATE v_sql_stmt;
    end if;    
  end loop;
end;
/