Oracle stored procedure to export table structure

To export the table structure in oracle stored procedures, we need to reverse all the table structures in the database, including partition tables and non-partition tables. The fields involved include number, data, timestamp, varchar2, and char. Therefore, only the tables with these fields are exported. If the table has its type fields, you need to add code. Partition tables are partitioned by time, so the structure of the exported Partition Table is partitioned by time. I wrote it based on my actual situation and changed it based on different situations! The stored procedure includes a parameter. The default value is Y. the partition of the partition table is exported. If other values are specified, such as execu table_frame ('n'), only the table structure is exported. Usage: 1. To export all the table structures of a user, execute the following stored procedure under the user. 2. Create a directory as follows, and create a folder named 'struct' under disk D of the database server. Create or replace directory DIR_DUMP as 'd:/ '; 3. Execute the stored procedure. The generated table structure code is in the txt file under path d. Create or replace procedure table_frame (v_partition_status varchar2 default 'y') is type column_type is table of user_tab_columns.column_name % type; v_column column_type; type data_type is table of rows % type; v_type data_type; type length_type is table of user_tab_columns.data_length % type; v_length length_type; type datapre_type is table of user_tab_columns.DATA_PRECISION % type; V_ldatapre datapre_type; type datasca_type is table of variable % type; v_dayasca datasca_type; v_str clob; file_name variable; v_tables varchar2 (50); partition_status varchar2 (3); 1_varchar2 (30 ); TYPE part_cursor is ref CURSOR; part_name part_cursor; partition_name user_tab_partitions.partition_name % type; high_value user_tab_partitions.high_value % type; Begin file_name: = partition ', 'w'); -- determine whether to partition partition_status: = v_partition_status; -- loop by table for j in (select table_name from user_tables group by table_name) loop v_tables: = upper (j. table_name); v_str: = 'create table' | v_tables | '('; UTL_FILE.PUT_LINE (file_name, v_str); -- extract table Field Information select column_name, data_type, data_length, DATA_PRECISION, DATA_SCALE bulk collect V_column, v_type, v_length, v_ldatapre, v_dayasca from user_tab_columns where table_name = v_tables; -- loop by field for I in 1 .. v_column.count loop if v_type (I) = 'date' or v_type (I) like 'timestamp % 'then v_str: = v_column (I) | ''| v_type (I) | ','; elsif v_type (I) = 'number' and v_ldatapre (I) is not null then v_str: = v_column (I) | ''| v_type (I) | '(' | v_ldatapre (I) | ',' | v_dayasca (I) | '),'; elsif v_type (I) = 'numbe R' and v_ldatapre (I) is null then v_str: = v_column (I) | ''| v_type (I) | ','; else v_str: = v_column (I) | ''| v_type (I) | '(' | v_length (I) | '),'; end if; if I = v_column.count then v_str: = substr (v_str, 1, length (v_str)-1); end if; UTL_FILE.PUT_LINE (file_name, v_str); end loop; -- determine whether to add the partition if partition_status = 'y' then SELECT nvl (max (column_name), '0') into partition_keywords FROM USER_PART_KEY_COLUMNS wher E object_type = 'table' and name = v_tables; if partition_keywords! = '0' then UTL_FILE.PUT_LINE (file_name, ') partition by range (' | partition_keywords | ') ('); open part_name for select partition_name, high_value from user_tab_partitions where table_name = v_tables; v_str: = null; loop fetch part_name into partition_name, high_value; if part_name % notfound then -- remove the last comma v_str: = substr (v_str, 1, length (v_str)-1); UTL_FILE.PUT_LINE (file_name, v_str); exit; end if; UTL_FILE.PUT_LINE (file_name, v_str); v_str: = 'partition' | partition_name | 'values less than ('| high_value |'), '; end loop; end if; UTL_FILE.PUT_LINE (file_name, '); UTL_FILE.PUT_LINE (file_name,' --------------------------------------------------------- '); end loop; UTL_FILE.fclose_all; end;