关于从Oracle中导出文本数据的问题

Question

利用user_col_comments动态生成脚本吧

Answer 1

具体点呢？偶对Oracle不太熟。

Answer 2

declare
v_table varchar2(50);
v_sql   varchar2(2000);
v_loop  int;
begin
   dbms_output.enable(1000000);
   v_table:=&tab_name;
   v_loop:=1;
   v_sql:=' select ';
   for i in (
               select table_name,column_name 
               from user_col_comments 
               where table_name=upper(v_table) order by table_name
             ) loop
      dbms_output.put_line('v_loop='||v_loop);
      if v_loop = 1 then
         v_sql:=v_sql||trim(i.column_name);
      else
         v_sql:=v_sql||'||'',''||'||trim(i.column_name);
      end if;
      v_loop:=v_loop+1;
   end loop;
   v_sql:=v_sql||'  from '||v_table;
   dbms_output.put_line(v_sql);
end;
/

Answer 3

SQL> CREATE OR REPLACE FUNCTION get_col (tab VARCHAR2)
  2     RETURN VARCHAR2
  3  IS
  4     col_c2   VARCHAR2 (4000);
  5     sqlstr   VARCHAR2 (4000);
  6  BEGIN
  7     sqlstr := ' select ';
  8  
  9     FOR cur IN (SELECT column_name
 10                   FROM user_col_comments
 11                  WHERE table_name = UPPER (tab))
 12     LOOP
 13        col_c2 := col_c2 || cur.column_name || ',';
 14     END LOOP;
 15  
 16     col_c2 := RTRIM (col_c2, 1);
 17     col_c2 := SUBSTR (col_c2, 1, LENGTH (col_c2) - 1);
 18     sqlstr := sqlstr || col_c2 || ' from ' || tab;
 19     EXECUTE IMMEDIATE sqlstr;
 20  
 21     RETURN sqlstr;
 22  END;
 23  /Function created.SQL> select get_col('a') from dual
  2  /GET_COL('A')
-----------------------------------------------------------------
 select RECEIVE_MONTH,DEFECT,DEFECT_TIMES,TYPE from aSQL> 得出楼主想要得sql语句。

Answer 4

SQL> select get_col('ABMFV_ACT_DRIVER_SOURCES_V') from dual
  2  /GET_COL('ABMFV_ACT_DRIVER_SOURCES_V')
----------------------------------------------------------------------------------------------------
 select DATA_SET_NAME,SOURCE,SOURCE_ACTIVITY,SOURCE_ACTIVITY_NAME,SOURCE_ACTIVITY_DEPT,SOURCE_ACTIVI
TY_DEPT_NAME,SOURCE_BEGIN_DATE,SOURCE_BEGIN_EFFECTIVE_DATE,SOURCE_END_DATE,SOURCE_ID,SOURCE_NAME,SOU
RCE_DEPT,SOURCE_DEPT_NAME,SOURCE_DEPT_ACTIVITY,SOURCE_DEPT_ACTIVITY_ID,SOURCE_DEPT_ACTIVITY_NAME,SOU
RCE_DEPT_ACTIVITY_TYPE_CODE,SOURCE_DEPT_ID,SOURCE_TYPE,SOURCE_TYPE_CODE,SOURCE_VALUE,ACTIVITY_DRIVER
,ACTIVITY_DRIVER_ID,ACTIVITY_DRIVER_NAME,ACTIVITY_DRIVER_DEPT,ACTIVITY_DRIVER_DEPT_NAME,ACTIVITY_DRI
VER_DEPT_ID,ACTIVITY_DRIVER_TYPE_CODE,MASTER_LIST,MASTER_LIST_ID,MASTER_LIST_NAME,MODEL,MODEL_ID,MOD
EL_NAME,DATA_SET,DATA_SET_ID from ABMFV_ACT_DRIVER_SOURCES_V
SQL>