Oracle stored procedure to export table structure

To reverse all the table structures in the Oracle database, there are partition tables and non-partition tables. The fields involved include number, data, timestamp, varchar2, and char. So only needle

To reverse all the table structures in the Oracle database, there are partition tables and non-partition tables. The fields involved include number, data, timestamp, varchar2, and char. So only needle

To reverse all the table structures in the Oracle database, there are 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 has a parameter. The default value is Y. Export the partition of the partition table. If other values are specified, such
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 user_tab_columns.data_type % 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 user_tab_columns.DATA_SCALE % type;
V_dayasca datasca_type;
V_str clob;
File_name UTL_FILE.file_type;
V_tables varchar2 (50 );
Partition_status varchar2 (3 );
Partition_keywords 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: = utl_file.fopen('dir_dump', 'table.txt ', 'w ');
-- Determine whether partitions are required
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 into 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) = 'number' 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 a partition
If partition_status = 'y' then
SELECT nvl (max (column_name), '0') into partition_keywords FROM USER_PART_KEY_COLUMNS
Where 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;
End if;
UTL_FILE.PUT_LINE (file_name ,');');
UTL_FILE.PUT_LINE (file_name ,'-------------------------------------------------------------');
End loop;
UTL_FILE.fclose_all;
End;