insert table scripts (all tables 100 rows)

----------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 rownum<=100) where row_num>0;';
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 << EOF > /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 rownum<=100) where row_num>0;' 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.

constraint scripts with dbms_metadata.get_ddl

set feedback off
set heading off
set long 4000
SELECT DBMS_METADATA.get_ddl('CONSTRAINT' ,constraint_name,owner) FROM DBA_CONSTRAINTS
WHERE owner not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',
'QS_CB','QS_CS','PERFSTAT')
------------------------------------

set feedback off
set heading off
set long 4000
select dbms_metadata.get_ddl('CONSTRAINT',constraint_name) FROM USER_CONSTRAINTS

profile scripts with dbms_metadata.get_ddl

set feedback off
set heading off
set long 4000
SELECT dbms_metadata.get_ddl('PROFILE',profile) FROM dba_profiles WHERE profile != 'DEFAULT'

CREATE USER DDL

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

sqlplus -s /nolog << EOF > /dev/null
connect USER/PASSWORD@ORCL

SET LINESIZE 1024;
SET HEADING OFF;
SET PAGESIZE 0;
SET FEEDBACK OFF;
SET TRIMSPOOL ON;
set escape on;

SPOOL /tmp/orhan/create_users_ddl.txt

select 'create user ' || U.username || ' identified ' ||
DECODE(password,
NULL, 'EXTERNALLY',
' by values ' || '''' || password || ''''
)
|| chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||
'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace ||
decode (account_status,'LOCKED', ' account lock',
'EXPIRED', ' password expire',
'EXPIRED \& LOCKED', ' account lock password expire',
null)
||
';'
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system & default users
where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',
'QS_CB','QS_CS','PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;

set pagesize 100
set escape off

SPOOL OFF
EXIT SQL.SQLCODE;
EOF
----------------------------------------------------

.txt file inserting to table with utl_file

----örnek 'a.txt' dosyası içeriği;----

ABCD-1020 80
DCKF-5010 20
----------------------------------
drop procedure file_read;
drop table test;

create table test (
text VARCHAR2(200),
text_name VARCHAR2(200),
create_date date default sysdate);


CREATE OR REPLACE PROCEDURE file_read(file_name VARCHAR2) IS
vSFile utl_file.file_type;
vNewLine VARCHAR2(200);
BEGIN
vSFile := utl_file.fopen('ORHAN_DIR', file_name,'r');

IF utl_file.is_open(vSFile) THEN
LOOP
BEGIN
utl_file.get_line(vSFile, vNewLine);

IF vNewLine IS NULL THEN
EXIT;
END IF;

INSERT INTO test
(text, text_name, create_date)
VALUES
(vNewLine, file_name,sysdate);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
utl_file.fclose(vSFile);
EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR
But Later I/O Inconsistent');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END file_read;
/
--------------------------------------------------

DECLARE
FILE_NAME VARCHAR2(32767);

BEGIN
FILE_NAME := NULL;

SCH.FILE_READ ( 'a.txt' );
COMMIT;
END;
-------------------------------------------------

update procedure

drop table paketler;

create table paketler (
paket_kod VARCHAR2(200),
paket_ad VARCHAR2(200));

insert into paketler(paket_kod,paket_ad)values ('5010PPA','PAKET01');
insert into paketler(paket_kod,paket_ad)values ('5220PPB','PAKET02');
insert into paketler(paket_kod,paket_ad)values ('5420PPC','PAKET03');


DECLARE
CURSOR a
IS
SELECT paket_kod
FROM paketler
WHERE paket_kod LIKE '5420%';

p_kod paketler.paket_kod%TYPE;

BEGIN

OPEN a;

LOOP
FETCH a INTO p_kod;

begin
EXECUTE IMMEDIATE 'update paketler set paket_kod=''5500PPC'' where paket_kod= :b_v' USING p_kod;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (p_kod || ' - ' || SQLERRM);

end;

EXIT WHEN a%NOTFOUND;

DBMS_OUTPUT.put_line (p_kod || ' kodlu paketiniz 5500PPC olarak değiştirilmiştir.');

END LOOP;

CLOSE a;

COMMIT;
END;