———-function————-

CREATE OR REPLACE FUNCTION ORHAN.GET_INSERT_SCRIPT(V_TABLE_NAME long)
RETURN long AS
B_FOUND BOOLEAN := FALSE;
V_TEMPA long;
V_TEMPB long;
V_TEMPC long;
BEGIN
FOR TAB_REC IN (SELECT TABLE_NAME
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER (V_TABLE_NAME)) LOOP
B_FOUND := TRUE;
V_TEMPA := ‘select test from (
select rownum row_num,A.* from (select ”insert into ‘ || TAB_REC.TABLE_NAME || ‘ (‘;

FOR COL_REC IN (SELECT *
FROM user_tab_columns
WHERE TABLE_NAME = TAB_REC.TABLE_NAME
ORDER BY COLUMN_ID) LOOP
IF COL_REC.COLUMN_ID = 1 THEN
V_TEMPA := V_TEMPA || ”’||chr(10)||”’;
ELSE
V_TEMPA := V_TEMPA || ‘,”||chr(10)||”’;
V_TEMPB := V_TEMPB || ‘,”||chr(10)||”’;
END IF;

V_TEMPA := V_TEMPA || COL_REC.COLUMN_NAME;

IF INSTR (COL_REC.DATA_TYPE, ‘CHAR’) > 0 THEN
V_TEMPC := ””””’||’ || COL_REC.COLUMN_NAME || ‘||””””’;
ELSIF INSTR (COL_REC.DATA_TYPE, ‘DATE’) > 0 THEN
V_TEMPC :=
”’to_date(”””||to_char(‘
|| COL_REC.COLUMN_NAME
|| ‘,”mm/dd/yyyy hh24:mi”)||”””,””mm/dd/yyyy hh24:mi””)”’;
ELSE
V_TEMPC := COL_REC.COLUMN_NAME;
END IF;

V_TEMPB :=
V_TEMPB
|| ”’||decode(‘
|| COL_REC.COLUMN_NAME
|| ‘,Null,”Null”,’
|| V_TEMPC
|| ‘)||”’;
END LOOP;

V_TEMPA :=
V_TEMPA
|| ‘) values (‘
|| V_TEMPB
|| ‘);” as test from ‘
|| TAB_REC.TABLE_NAME
|| ‘ )A where rownum0;’;
END LOOP;

IF NOT B_FOUND THEN
V_TEMPA := ‘- Table ‘ || V_TABLE_NAME || ‘ not found’;
ELSE
V_TEMPA :=’INSERT INTO ORHAN.INSERT_SCRIPTS(test) ‘|| V_TEMPA || CHR (10);
END IF;

RETURN V_TEMPA;
END;
/
———————————————

————–procedure———————-

CREATE OR REPLACE procedure ORHAN.pro_insert is

RetVal long;
V_TABLE_NAME long;
t_name long;

cursor cur is select table_name as t_name from user_tables;

rec_cur cur%ROWTYPE;

BEGIN

open cur;
loop
fetch cur into rec_cur.t_name;
exit when cur%notfound;

V_TABLE_NAME := rec_cur.t_name;

RetVal := ORHAN.GET_INSERT_SCRIPT ( V_TABLE_NAME );
COMMIT;

–DBMS_OUTPUT.put_line (RetVal);

INSERT /*+ APPEND */ INTO ORHAN.select_insert (
TEST)
VALUES ( RetVal );

commit;
end loop;

close cur;

END;
/
———————————————-

——–insert_scripts.sh———————-

#!/usr/bin/ksh
. $HOME/.profile

sqlplus -s /nolog < /dev/null
connect ORHAN/PASSWORD@ORCL

set verify off;
set termout off;
set feedback off;
set pagesize 0;

BEGIN
ORHAN.PRO_INSERT;
COMMIT;
END;
/

SPOOL /tmp/orhan/insert_scripts.txt

set heading off
set recsep off
col text format a80 word_wrap
spool off;
——————————————–

for example ‘INSERT INTO ORHAN.INSERT_SCRIPTS(test) select test from
(select rownum row_num,A.* from (select ‘insert into table_name
(‘||chr(10)||’no,’||chr(10)||’mesaj) values (‘||decode(no,Null,’Null’,””||no||””)||’,
‘||chr(10)||”||decode(mesaj,Null,’Null’,””||mesaj||””));’
as test from table_name ) A where rownum0;’ script see;

select * from ORHAN.select_insert;
and
for example ‘insert into table_name(no,mesaj)
values (1233,’selam’);’ script see;

select * from ORHAN.INSERT_SCRIPTS;

on toad save as.. to text file.