Oracle stored procedure generates executable export script with CLOB Field table

Oracle tables cannot be exported with SQL scripts if they contain CLOB fields, especially when data backup is required or data import and export is inconvenient. This stored procedure you simply pass in the table name and the results that need to be returned, you can get the executable SQL, directly in PL/SQL to perform the returned results, you can export the data into SQL, the stored procedure is as follows

Create or Replace procedureGenerate_insert_sql (t_nameinch varchar2, Execute_sql outvarchar2) as
  CURSORCol_cur is
    Selectcolumn_name, Data_type fromUser_tab_colswheretable_name likeT_name; col_name  varchar2( -); BEGIN
   forColinchCol_cur LoopifCol.data_type='CLOB'   Then
      col_name:= 'To_char (' ||Col.column_name|| ') as'||Col.column_name||' ,'; Else
      col_name:=Col.column_name|| ','; End if; Execute_sql:=Execute_sql|| col_name;
Endloop; Execute_sql:= 'Select' ||RTRIM(Execute_sql,',')|| ' from'||T_name;End;

Test examples are as follows, and the test tool is PL/SQL

1. Create a test table

Create Table test_table (
    varchar2(),
    test_clob1 clob,
    test_clob2 clob    
);

2. Inserting initialization data

3. Execute the Stored procedure

4. Execute get SQL 5. Export the query data to SQL 6. View exported SQL finally you can import the SQL you get into where you need to go.

Oracle stored procedure generates executable export script with CLOB Field table