如何将ORACLE DB中约300个表中的数据导出到csv或txt文件 [英] how to export data from around 300 tables in ORACLE DB to csv or txt files

问题描述

是否可以使用任何PL/SQL过程将具有数百万条记录的单个架构中的大约300个表中的数据导出到CSV或TXT?

Is there any possibility to export data from around 300 tables within single schema with millions of records to CSV or TXT using any PL/SQL procedure?

您提出什么建议，这是最快的方法?目前，我不需要将这些导出的文件导入任何其他架构...

What do you propose, which is fastest way to do it? For the moment I do not need to import these exported files to any other schema...

我尝试使用Toad手动逐表导出...

I tried with Toad manually exporting table by table...

推荐答案

我设法动态浏览所有表并获取列名并写入文件.我正在努力在执行立即查询时如何动态地从表中获取数据行?我应该如何保存数据行，而不是读取并写入文件?这是代码:

I managed to dynamically go through all tables and get column names and write to a file. I am struggling into part how to fetch data rows from tables dynamically when execute immediate query? how should I save data rows and than fetch it and write to files? Here is the code:

DECLARE p_table        VARCHAR2 (100);
l_file UTL_FILE.FILE_TYPE;
l_string       VARCHAR2 (10000);
query_string   VARCHAR2 (4000);
BEGIN
FOR tab IN (SELECT *
             FROM dba_tables
            WHERE owner = 'XYZ' AND table_name LIKE 'XYZ%')
LOOP
  p_table := tab.table_name;

  l_file :=
     UTL_FILE.FOPEN ('my_path',
                     tab.table_name || '.txt',
                     'w',
                     10000);
  l_string := NULL;


  FOR col_he IN (SELECT *
                   FROM dba_tab_columns
                  WHERE owner = 'DWHCO' AND table_name = p_table)
  LOOP
     CASE
        WHEN l_string IS NULL
        THEN
           l_string := col_he.column_name;
        ELSE
           l_string := l_string || ',' || col_he.column_name;
     END CASE;
  END LOOP;

  UTL_FILE.PUT_LINE (l_file, l_string);            --Printng table columns

  query_string := 'select ' || l_string || ' from DWHCO.' || p_table      
  --Execute immediate query_string into ??????????;
        --??????
  UTL_FILE.FCLOSE (l_file);  END LOOP;END;

这篇关于如何将ORACLE DB中约300个表中的数据导出到csv或txt文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！