dbms_sql

看了一天dbms_sql包，写了个导出csv的代码加深对dbms_sql的理解，虽然用developer工具很容易实现.

建directory
create directory abc_dir as 'c:\abc';

匿名块
declare
  v_file utl_file.file_type;
  v_result varchar2(3000):='';
  v_theCursor integer;
  v_sql       varchar2(1000) := 'select * from all_objects';
  type t_tab_desc is table of varchar2(30) index by pls_integer;
  v_tab_desc t_tab_desc;
  v_colN     number;
  v_colDesc  dbms_sql.desc_tab;
  v_row      integer;
begin
  execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
  v_file:=utl_file.fopen('ABC_DIR','ABC.CSV','W');
  v_theCursor := dbms_sql.open_cursor;
  dbms_sql.parse(v_thecursor, v_sql, dbms_sql.native);
  dbms_sql.describe_columns(v_thecursor, v_colN, v_colDesc);
  for i in 1 .. v_colN loop
    v_tab_desc(i) := v_colDesc(i).col_name;
  end loop;

  for i in 1 .. v_tab_desc.count loop
    v_result:=v_result||','||v_tab_desc(i);
  end loop;
  v_result:=trim(',' from v_result);
  utl_file.put_line(v_file,v_result);

  for i in 1 .. v_colN loop
    dbms_sql.define_column(v_theCursor, i, v_tab_desc(i), 30);
  end loop;

  v_row := dbms_sql.execute(v_theCursor);
  loop
    exit when dbms_sql.fetch_rows(v_theCursor) <= 0;
    v_result:='';
    for i in 1 .. v_colN loop
      dbms_sql.column_value(v_theCursor, i, v_tab_desc(i));
      v_result:=v_result||','||v_tab_desc(i);
    end loop;
    v_result:=trim(',' from v_result);
    utl_file.put_line(v_file,v_result);
  end loop;

  utl_file.fclose(v_file);
  dbms_sql.close_cursor(v_theCursor);

exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30363279/viewspace-2128876/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30363279/viewspace-2128876/