Purpose: ======== The purpose of this article is to explain how you can transfer table definitions from one database to another database by generating script files. How to Generate 'CREATE TABLE' Scripts from Existing Tables: ============================================================ If basic tables, that is, tables with no partitions, constraints, indexes, etc. are sufficient for your purposes, then you can use the script listed at the end of this article. If you need detailed CREATE TABLE SQL statements, then you can follow this example: exp user_name/password file=table_name.dmp rows=n tables=\('table_name'\); (Use brackets to escape the backslash because of shell interpretation.) imp user_name/password file= table_name.dmp indexfile=table_name.log on UNIX: sed 's/REM //' table_name.log >table_name.sql on another platforms: open file table_name.log in any text editor and replace string 'REM ' by '' (empty string). save as table_name.sql Check the file table_name.sql for the script that you need. ============ Script for basic CREATE TABLE SQL statement ================ spool table.lst set serveroutput on size 1000000 declare starting boolean :=true; r_owner varchar2(30) := '&1'; r_table_name varchar2(30) := '&2'; begin dbms_output.put_line('create table '||r_owner||'.'||r_table_name||'('); for r in (select column_name, data_type, data_length, data_precision, data_scale, data_default, nullable from all_tab_columns where table_name = upper(r_table_name) and owner=upper(r_owner) order by column_id) loop if starting then starting:=false; else dbms_output.put_line(','); end if; if r.data_type='NUMBER' then if r.data_scale is null then dbms_output.put(r.column_name||' NUMBER('||r.data_precision||')'); else dbms_output.put(r.column_name||' NUMBER('||r.data_precision||','||r.data_scale||')'); end if; else if r.data_type = 'DATE' then dbms_output.put_line(r.column_name||' DATE'); else if instr(r.data_type, 'CHAR') >0 then dbms_output.put(r.column_name||' '||r.data_type||'('||r.data_length||')'); else dbms_output.put(r.column_name||' '||r.data_type); end if; end if; end if; if r.data_default is not null then dbms_output.put(' DEFAULT '||r.data_default); end if; if r.nullable = 'N' then dbms_output.put(' NOT NULL '); end if; end loop; dbms_output.put_line(' ); '); end; / spool off
使用POI生成word文档的table表格
2024-04-21 09:10:04 49 阅读