create or replace procedure use_dsql(i_sql in varchar2, i_exec in number:= 0 ) is
  c number;
  d number;
  col_cnt integer;
  rec_tab dbms_sql.desc_tab;
  col_num number;
  l_k_vc2_len number :=  1 ;
  l_res_cnt number :=  0 ;
  l_info_result varchar2( 32000 );
  l_string varchar2( 2000 );
  l_number number;
  l_date date;
  l_cur number;
  l_exe number;
begin
  if substr(sys_context('USERENV','LANGUAGE'),instr(sys_context('USERENV','LANGUAGE'),'.')+ 1 ) = 'AL32UTF8' then 
    l_k_vc2_len:= 0 . 25 ;
  end if;  
  -- формирование скрипта для вывода запроса через DBMS_SQL
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, i_sql,  1 );

  dbms_output.put_line('declare');
  dbms_output.put_line('  l_cur number;');
  dbms_output.put_line('  l_exe number;');
  dbms_output.put_line('  type q_rec is record (');
  dbms_sql.describe_columns(c, col_cnt, rec_tab);
  
  col_num := rec_tab.first;
  if (col_num is not null) then
    loop
     dbms_output.put('  '||rec_tab(col_num).col_name||' '||case rec_tab(col_num).col_type when  1  then 'VARCHAR2('||to_char(rec_tab(col_num).col_max_len*l_k_vc2_len)||' CHAR)' when  2  then 'NUMBER' when  12  then 'DATE' else 'VARCHAR2(2000 CHAR)' end);
     col_num := rec_tab.next(col_num);
     exit when (col_num is null);
     dbms_output.put_line(',');
    end loop;
    dbms_output.put_line(');');
  end if;
  dbms_output.put_line('  l_rec q_rec;');
  print('  l_sql varchar2(32767):= '''||replace(i_sql,'''','''''')||''';');

  dbms_output.put_line('begin');
  dbms_output.put_line('  l_cur:=dbms_sql.open_cursor;');
  dbms_output.put_line('  dbms_sql.parse(l_cur,l_sql,1);');

  col_num := rec_tab.first;
  if (col_num is not null) then
    loop
      dbms_output.put_line('  dbms_sql.define_column(l_cur,'||to_char(col_num)||', l_rec.'||rec_tab(col_num).col_name||
      case rec_tab(col_num).col_type when  1  then ','||to_char(rec_tab(col_num).col_max_len*l_k_vc2_len) 
                                     when  2  then ''
                                     when  12  then ''
                                     else ', 2000 CHAR' end ||');');
        col_num := rec_tab.next(col_num);
        exit when (col_num is null);
    end loop;
  end if;
  dbms_output.put_line('  -- bind variables --');
  dbms_output.put_line('  -- dbms_sql.bind_variable(l_cur,'''||'<bind>'||''',<val>);');

  dbms_output.put_line('  l_exe:=dbms_sql.execute(l_cur);');
  dbms_output.put_line('  while dbms_sql.fetch_rows(l_cur)<>0 loop');

  col_num := rec_tab.first;
  if (col_num is not null) then
    loop
      dbms_output.put_line('    dbms_sql.column_value(l_cur,'||to_char(col_num)||',l_rec.'||rec_tab(col_num).col_name||');');
      col_num := rec_tab.next(col_num);
      exit when (col_num is null);
    end loop;
  end if;

  col_num := rec_tab.first;
  if (col_num is not null) then
    dbms_output.put_line('    --- values ---');
    dbms_output.put_line('    dbms_output.put_line(');
    loop
      dbms_output.put('    l_rec.'||rec_tab(col_num).col_name||'||'';''');
      col_num := rec_tab.next(col_num);
      exit when (col_num is null);
      dbms_output.put_line('||');
    end loop;
    dbms_output.put_line(');');
  end if;
  dbms_output.put_line('  end loop;');
  dbms_output.put_line('  dbms_sql.close_cursor(l_cur);');
  dbms_output.put_line('end;');
  dbms_sql.close_cursor(c);
  if i_exec =  0  then 
    return;
  end if;
  -- вывод результатов запроса
  dbms_output.put_line('');
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, i_sql,  1 );
  d := dbms_sql.execute(c);
  
  dbms_sql.describe_columns(c, col_cnt, rec_tab);
  col_num := rec_tab.first;
  if (col_num is not null) then
    loop
      l_info_result:=l_info_result||rec_tab(col_num).col_name||'; ';
      if rec_tab(col_num).col_type =  1  then 
        DBMS_SQL.define_COLUMN(c, col_num, 'l_string',  2000 );
      elsif rec_tab(col_num).col_type =  2  then  
        DBMS_SQL.define_COLUMN(c, col_num,  1 );    
      elsif rec_tab(col_num).col_type =  12  then 
        DBMS_SQL.define_COLUMN(c, col_num, sysdate); 
      end if;
      col_num := rec_tab.next(col_num);
      exit when (col_num is null);    
    end loop;
  end if;
  print(l_info_result);
  l_info_result := '';
  loop
    d := dbms_sql.fetch_rows(c);
    exit when d =  0  or l_res_cnt >  20 ;
    l_res_cnt := l_res_cnt +  1 ;
    l_info_result:=l_info_result;--||chr(10);
      for i in  1 ..rec_tab.count loop
        if rec_tab(i).col_type =  1  then 
          dbms_sql.column_value(c, i, l_string);
          l_info_result:=l_info_result||l_string||'; ';
        elsif rec_tab(i).col_type =  2  then 
          dbms_sql.column_value(c, i, l_number);
          l_info_result:=l_info_result||l_number||'; ';
        elsif rec_tab(i).col_type =  12  then 
          dbms_sql.column_value(c, i, l_date);
          l_info_result:=l_info_result||to_char(l_date,'dd.mm.yyyy hh24:mi:ss')||'; ';
        end if;  
        dbms_output.put(l_info_result);
        l_info_result :='';
      end loop;
      dbms_output.put_line('');
  end loop;
end;