Export the SQL script for table creation from the Oracle database

Basically, the syntax used is as follows:

A. Obtain the syntax for creating a single table and an index
Set heading off;
Set echo off;
Set pages 999;
Set long 90000;

Spool Dept. SQL
Select dbms_metadata.get_ddl ('table', 'dept', 'Scott ') from dual;
Select dbms_metadata.get_ddl ('index', 'dept _ idx', 'Scott ') from dual;
Spool off;

B. obtain all the table creation and index creation syntaxes in a schema. Take Scott as an example:

Set pagesize 0
Sets long 90000

Set feedback off

Set echo off
Spool scott_schema. SQL
Connect Scott/tiger;
Select dbms_metadata.get_ddl ('table', U. table_name)
From user_tables U;
Select dbms_metadata.get_ddl ('index', U. index_name)
From user_indexes U;
Spool off;

C. Obtain the syntax for creating all stored procedures in a schema

Connect brucelau/brucelau;

Spool procedures. SQL

Select
Dbms_metadata.get_ddl ('processed', U. object_name)
From
User_objects u

Where
Object_type = 'processure ';

Spool off;


In addition:

Dbms_metadata.get_ddl ('table', 'tab1', 'user1 ')
Among the three parameters, the first specifies the object type defined by the Export DDL (in this example, the table type), and the second is the object name (in this example, the table name ), the third is the user name of the object ..

You have to write the program yourself and execute an SQL statement to make the SQL query result the SQL script we want:

As follows:

Select
'Insert
Into B _stattemplate (n_id, c_name, c_kbh, n_print, n_type, n_app, n_valid )'
|
'Values ('| to_char (n_id) |', ''' | c_name | ''', ''' | c_kbh | ''','
|
To_char (n_print) | ',' | to_char (n_type) | ',' | '0, 0 );'
From
B _stattemplate