Oracle exports INSERT INTO statement with stored procedure

A few days ago, I saw some friends. To export data from Oracle, you can use Pl/sql devoleper and export tables to export data from n tables to insert INTO statements, but how to export them with SQL statements, only with SQL constructs, Here's the code I implemented with the stored procedure

Create or Replace package pk_export_table is type result is ref cursor;end;

CREATE OR REPLACE PROCEDURE p_export_table (v_table in varchar,
Cresult out Pk_export_table.result
--OUTSTR out varchar2/* Test sql*/
)
/*----------------------------------------------------------------------------------------
Functional Requirements: Export SQL name, in the form of INSERT into TABLE (...). ) VALUES (.....) ）；
Author: Chimo
Write Start date: 20080120
Write End Date: 20080120
Parameter definition: Table name V_table,cresult is a custom cursor.
Custom cursors: Create or Replace package pk_export_table is type result is ref cursor;end pk_export_table;
Data Source:
Call method: Call in other languages, pl/sql process such as: EXEC p_xqcn_cycsjcx (' parameter 1 ');
-----------------------------------------------------------------------------------------*/
As
TYPE v_cur_tab_columns is REF cursor;/* table field Information * *
V_tab_columns V_cur_tab_columns;
V_sql_columns VARCHAR2 (500); * Query Field information * *
V_column_infor User_tab_columns%rowtype; /* Line Word record each field number type is usre_tab_columns*/
V_sqlstr_part_1 VARCHAR2 (200): = ';/* deposit The first part of the form SQL * *
V_sqlstr_part_2 VARCHAR2 (500): = '; * Deposit the second part of the form SQL * * *
V_sqlstr_part_3 varchar2 (4000): = ';/* deposit form the third part of SQL * *
V_sql VARCHAR2 (5000): = '; * Deposit formed sql*/
V_sqlneed varchar2 (100): = ', ' | | | ', ' | | ', '; /* The delimiter formed in Values "," * *
Begin
v_sqlstr_part_1:= ' insert INTO ' | | Upper (v_table) | | (';
* * Query table in each field information * *
V_sql_columns:= ' select * from User_tab_columns where table_name= ' | | | UPPER (v_table) | | | and Data_type not in (' | | | ') BLOB ' | | | ', ' | | ', ' | | CLOB ' | | | ' and Rownum<20 ';
Open v_tab_columns for V_sql_columns;
Loop
Fetch v_tab_columns into v_column_infor;
Exit when V_tab_columns%notfound;
/* is used to form an INSERT into table (...). ) in the field * *
v_sqlstr_part_2:=v_sqlstr_part_2| | V_column_infor. column_name| | ', ';
/* Used to form values (...). ) in the field * *
V_sqlstr_part_3:=case
When V_column_infor. Data_type= ' DATE ' THEN v_sqlstr_part_3| | ' To_date (To_char) (' | | ') Bzrq ' | | | ', ' | | Yyyy-mm-dd hh24:mi:ss ' | | | '), ' | | ' | Yyyy-mm-dd hh24:mi:ss ' | | | ') ' | | V_sqlneed
When V_column_infor. Data_type= ' VARCHAR2 ' OR v_column_infor. Data_type= ' VARCHAR ' or v_column_infor. Data_type= ' CHAR ' THEN v_sqlstr_part_3| | | v_column_infor.column_name| | ' is not NULL THEN ' | | | ' ' | | ' | | V_column_infor. column_name| | ' | | ' ' | | ' ' | ELSE NULL end as ' | | V_column_infor. column_name| | V_sqlneed
When V_column_infor. Data_type= ' number ' THEN v_sqlstr_part_3| | | v_column_infor.column_name| | ' is not NULL THEN ' | | V_column_infor. column_name| | ' ELSE NULL end as ' | | V_column_infor. column_name| | V_sqlneed
Else
--v_sqlstr_part_3| | ' case when ' | | v_column_infor.column_name| | ' is not NULL THEN ' | | | ' ' | | ' | | V_column_infor. column_name| | ' | | ' ' | | ' ' | ELSE NULL end as ' | | V_column_infor. column_name| | V_sqlneed
v_sqlstr_part_3| | ' NULL ' | | V_sqlneed
End
End Loop;
/* Remove the Last "," and add values*/
V_sqlstr_part_2:=substr (V_sqlstr_part_2,0,length (v_sqlstr_part_2)-1) | | Values (';
/* Remove the final "," * * *
V_sqlstr_part_3:=substr (V_sqlstr_part_3,0,length (v_sqlstr_part_3)-5) | | ', ' | | | ';
/* Formation of the sql*/
v_sql:= ' SELECT ' | | v_sqlstr_part_1| | v_sqlstr_part_2| | ' | | | v_sqlstr_part_3| | ' From ' | | Upper (v_table) | | ';
/* Open Cursor and return * *
Open Cresult for V_sql;
Exception
When others then
Open Cresult for ' select * from ' | | v_table;
--outstr:=v_sql;
End p_export_table;