dbms_sql

create table  grade(
gid number(10),
studentname varchar2(100),
course varchar2(100),
score number(10)
)
select * from  grade for update
select grouping(g.studentname),grouping(g.course) ,g.studentname ,g.course,sum(g.score)
 from grade g group by  rollup(g.studentname,g.course) 

create or replace function getSql(sqlStr varchar2  ) return  varchar2 is
  source_cursor number;
  week_num number(10);
  week varchar2(10);
  results  varchar2(400);
  tmpCount  number(10);
    dummy NUMBER;
begin
  tmpCount:=0;
    source_cursor:=dbms_sql.open_cursor;
    dbms_sql.parse(source_cursor,sqlStr,dbms_sql.native);
    dbms_sql.define_column(source_cursor,1,week,100);
    dbms_sql.define_column(source_cursor,2,week_num);
      dummy :=dbms_sql.execute(source_cursor);
   loop
     if  dbms_sql.fetch_rows(source_cursor) >0 then
       dbms_sql.column_value(source_cursor,1,week);
        dbms_sql.column_value(source_cursor,2,week_num);
        if tmpCount=0 then
          results:='max(decode(weekid,'||week_num||','''||week||'''))';
          tmpCount:=+1;
        else
             results:=results||',max(decode(weekid,'||week_num||','''||week||'''))';
        end if;
           else
          exit;
     end if;  
 
     
   end loop;

  results:=results;
  return(results);
end getSql;