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