PL/SQL LOB和檔案操作，bulk collect
create directory tmp_dir as '/tmp/gbs_sql';
grant read,write on directory tmp_dir to dbmgr;
CREATE TABLE gbs_sql
AS
SELECT ROWNUM id,
a.sql_id,
REGEXP_REPLACE (a.sql_text,
'[' || CHR (128) || '-' || CHR (255) || ']',
'X')
|| ';'
sql_text,
DBMS_LOB.getlength (
REGEXP_REPLACE (a.sql_text,
'[' || CHR (128) || '-' || CHR (255) || ']',
'X')
|| ';') sql_len
FROM stage_sqlset a;
alter table gbs_sql add (dist300_hash integer);
alter table gbs_sql add (dist300 char(1));
update gbs_sql set dist300_hash=DBMS_UTILITY.GET_HASH_VALUE(dbms_lob.substr(sql_text,300),1,1000000000);
update gbs_sql set dist100_hash=DBMS_UTILITY.GET_HASH_VALUE(dbms_lob.substr(sql_text,100),1,1000000000);
alter table gbs_sql add (dist100_hash integer);
alter table gbs_sql add (dist100 char(1));
alter table gbs_sql add (sql_del_text clob);
update gbs_sql set sql_del_text=regexp_replace(sql_text,'\s+',' ');
alter table gbs_sql add (sql_del_len number);
update gbs_sql set sql_del_len=dbms_lob.getlength(sql_del_text);
alter table gbs_sql add (is_pl_sql char(1) default 'N');
grant read,write on directory tmp_dir to dbmgr;
CREATE TABLE gbs_sql
AS
SELECT ROWNUM id,
a.sql_id,
REGEXP_REPLACE (a.sql_text,
'[' || CHR (128) || '-' || CHR (255) || ']',
'X')
|| ';'
sql_text,
DBMS_LOB.getlength (
REGEXP_REPLACE (a.sql_text,
'[' || CHR (128) || '-' || CHR (255) || ']',
'X')
|| ';') sql_len
FROM stage_sqlset a;
alter table gbs_sql add (dist300_hash integer);
alter table gbs_sql add (dist300 char(1));
update gbs_sql set dist300_hash=DBMS_UTILITY.GET_HASH_VALUE(dbms_lob.substr(sql_text,300),1,1000000000);
update gbs_sql set dist100_hash=DBMS_UTILITY.GET_HASH_VALUE(dbms_lob.substr(sql_text,100),1,1000000000);
alter table gbs_sql add (dist100_hash integer);
alter table gbs_sql add (dist100 char(1));
alter table gbs_sql add (sql_del_text clob);
update gbs_sql set sql_del_text=regexp_replace(sql_text,'\s+',' ');
alter table gbs_sql add (sql_del_len number);
update gbs_sql set sql_del_len=dbms_lob.getlength(sql_del_text);
alter table gbs_sql add (is_pl_sql char(1) default 'N');
create unique index uni_gbs_sql_id on gbs_sql(id);
create index ix_gbs_sql_dist300_hash on gbs_sql(dist300_hash);
create index ix_gbs_sql_dist100_hash on gbs_sql(dist100_hash);
create index ix_gbs_sql_len on gbs_sql(sql_len);
create index ix_gbs_sql_sql_id on gbs_sql(sql_id);
create index ix_gbs_sql_dist300_hash on gbs_sql(dist300_hash);
create index ix_gbs_sql_dist100_hash on gbs_sql(dist100_hash);
create index ix_gbs_sql_len on gbs_sql(sql_len);
create index ix_gbs_sql_sql_id on gbs_sql(sql_id);
select dist300_hash,sql_len,--max(sql_len),min(sql_len),
max(id),min(id),max(sql_id),min(sql_id),count(*) from gbs_sql group by dist300_hash,sql_len order by count(*) desc;
max(id),min(id),max(sql_id),min(sql_id),count(*) from gbs_sql group by dist300_hash,sql_len order by count(*) desc;
select dist300_hash,max(sql_len),min(sql_len),
max(id),min(id),max(sql_id),min(sql_id),count(*) from gbs_sql group by dist300_hash order by count(*) desc;
max(id),min(id),max(sql_id),min(sql_id),count(*) from gbs_sql group by dist300_hash order by count(*) desc;
exp dbmgr/killua2cow file=/tmp/gbs_sql/gbs_sql.dmp tables=dbmgr.gbs_sql log=/tmp/gbs_sql/gbs_sql.log
imp dbmgr/duan5lzh file=/tmp/gbs_sql.dmp full=y
imp dbmgr/duan5lzh file=/tmp/gbs_sql.dmp full=y
exp dbmgr/killua2cow file=/tmp/gbs_sql/gbs_sql100.dmp tables=dbmgr.gbs_sql100 log=/tmp/gbs_sql/gbs_sql100.log
imp dbmgr/duan5lzh file=/tmp/gbs_sql100.dmp full=y
imp dbmgr/duan5lzh file=/tmp/gbs_sql100.dmp full=y
alter table dbmgr.gbs_sql add (sql_del_text_lower clob default empty_clob());
alter table dbmgr.gbs_sql add (give_up char(1) default 'X');
alter table dbmgr.gbs_sql add (give_up char(1) default 'X');
update gbs_sql set dist300='N',dist100='N';
update gbs_sql set dist300='Y' where id in (select min(id) from gbs_sql where is_pl_sql='N' group by dist300_hash);
update gbs_sql set dist300='Y' where id in (select min(id) from gbs_sql where is_pl_sql='N' group by dist300_hash);
update gbs_sql set dist100='Y' where id in (select min(id) from gbs_sql where is_pl_sql='N' group by dist100_hash);
create table gbs_sql100 as select id,sql_id,sql_text,sql_del_text,sql_del_text_lower,give_up FROM dbmgr.gbs_sql where dist100='Y' and is_pl_sql='N';
create unique index uni_gbs_sql100_id on gbs_sql100(id);
create unique index ix_gbs_gbs_sql100_id on gbs_sql100(sql_id);
-------------------------------------------------------
--create or replace procedure for_debug
--is
--
DECLARE
type sub_sql_list is table of varchar2(600);
type sql_id_list is table of varchar2(30);
tab_sub_sql sub_sql_list;
tab_sql_id sql_id_list;
str_sub_sql varchar2(600);
str_sql_id varchar2(30);
flag number;
str_give_up char(1);
num_resv_id number;
v_limit number := 1000;
CURSOR cur_sql
IS
SELECT sql_id, dbms_lob.substr(sql_del_text_lower,300) sub_sql
FROM dbmgr.gbs_sql100;
BEGIN
OPEN cur_sql;
create unique index uni_gbs_sql100_id on gbs_sql100(id);
create unique index ix_gbs_gbs_sql100_id on gbs_sql100(sql_id);
-------------------------------------------------------
--create or replace procedure for_debug
--is
--
DECLARE
type sub_sql_list is table of varchar2(600);
type sql_id_list is table of varchar2(30);
tab_sub_sql sub_sql_list;
tab_sql_id sql_id_list;
str_sub_sql varchar2(600);
str_sql_id varchar2(30);
flag number;
str_give_up char(1);
num_resv_id number;
v_limit number := 1000;
CURSOR cur_sql
IS
SELECT sql_id, dbms_lob.substr(sql_del_text_lower,300) sub_sql
FROM dbmgr.gbs_sql100;
BEGIN
OPEN cur_sql;
LOOP
FETCH cur_sql bulk collect
INTO tab_sql_id, tab_sub_sql limit v_limit;
FETCH cur_sql bulk collect
INTO tab_sql_id, tab_sub_sql limit v_limit;
EXIT WHEN tab_sql_id.count=0;
for i in tab_sql_id.first .. tab_sql_id.last loop
--forall i in tab_sql_id.first .. tab_sql_id.
str_sql_id := tab_sql_id(i);
str_sub_sql := tab_sub_sql(i);
select give_up into str_give_up from dbmgr.gbs_sql100 where sql_id=str_sql_id;
if str_give_up = 'X' then
select count(*),min(id) into flag,num_resv_id from dbmgr.gbs_sql100 where sql_id<>str_sql_id and give_up<>'Y' and dbms_lob.instr(sql_del_text_lower,str_sub_sql)>0;
if flag > 0 then
update dbmgr.gbs_sql100 set give_up='Y' where sql_id=str_sql_id and give_up<>'N';
update dbmgr.gbs_sql100 set give_up='N' where id=num_resv_id;
else
update dbmgr.gbs_sql100 set give_up='N' where sql_id=str_sql_id;
end if;
end if;
--execute immediate 'update dbmgr.gbs_sql a set a.give_up=''Y'' where a.sql_id=''' || str_sql_id ||''' and exists (select * from dbmgr.gbs_sql b where b.sql_id<>a.sql_id and b.give_up<>''Y'' and dbms_lob.instr(b.sql_del_text_lower,'''|| str_sub_str ||''')>0)';
end loop;
commit;
END LOOP;
close cur_sql;
END;
-------------------------------------------------------
create or replace procedure for_debug
is
--DECLARE
type sub_sql_list is table of varchar2(600);
type sql_id_list is table of varchar2(30);
tab_sub_sql sub_sql_list;
tab_sql_id sql_id_list;
str_sub_sql varchar2(600);
str_sql_id varchar2(30);
flag number;
str_give_up char(1);
num_resv_id number;
v_limit number := 1000;
CURSOR cur_sql
IS
SELECT sql_id, dbms_lob.substr(sql_del_text_lower,300) sub_sql
FROM dbmgr.gbs_sql where dist100='Y' and is_pl_sql='N';
BEGIN
OPEN cur_sql;
for i in tab_sql_id.first .. tab_sql_id.last loop
--forall i in tab_sql_id.first .. tab_sql_id.
str_sql_id := tab_sql_id(i);
str_sub_sql := tab_sub_sql(i);
select give_up into str_give_up from dbmgr.gbs_sql100 where sql_id=str_sql_id;
if str_give_up = 'X' then
select count(*),min(id) into flag,num_resv_id from dbmgr.gbs_sql100 where sql_id<>str_sql_id and give_up<>'Y' and dbms_lob.instr(sql_del_text_lower,str_sub_sql)>0;
if flag > 0 then
update dbmgr.gbs_sql100 set give_up='Y' where sql_id=str_sql_id and give_up<>'N';
update dbmgr.gbs_sql100 set give_up='N' where id=num_resv_id;
else
update dbmgr.gbs_sql100 set give_up='N' where sql_id=str_sql_id;
end if;
end if;
--execute immediate 'update dbmgr.gbs_sql a set a.give_up=''Y'' where a.sql_id=''' || str_sql_id ||''' and exists (select * from dbmgr.gbs_sql b where b.sql_id<>a.sql_id and b.give_up<>''Y'' and dbms_lob.instr(b.sql_del_text_lower,'''|| str_sub_str ||''')>0)';
end loop;
commit;
END LOOP;
close cur_sql;
END;
-------------------------------------------------------
create or replace procedure for_debug
is
--DECLARE
type sub_sql_list is table of varchar2(600);
type sql_id_list is table of varchar2(30);
tab_sub_sql sub_sql_list;
tab_sql_id sql_id_list;
str_sub_sql varchar2(600);
str_sql_id varchar2(30);
flag number;
str_give_up char(1);
num_resv_id number;
v_limit number := 1000;
CURSOR cur_sql
IS
SELECT sql_id, dbms_lob.substr(sql_del_text_lower,300) sub_sql
FROM dbmgr.gbs_sql where dist100='Y' and is_pl_sql='N';
BEGIN
OPEN cur_sql;
LOOP
FETCH cur_sql bulk collect
INTO tab_sql_id, tab_sub_sql limit v_limit;
FETCH cur_sql bulk collect
INTO tab_sql_id, tab_sub_sql limit v_limit;
EXIT WHEN cur_sql%NOTFOUND;
for i in tab_sql_id.first .. tab_sql_id.last loop
--forall i in tab_sql_id.first .. tab_sql_id.
str_sql_id := tab_sql_id(i);
str_sub_sql := tab_sub_sql(i);
select give_up into str_give_up from dbmgr.gbs_sql where sql_id=str_sql_id;
if str_give_up = 'X' then
select count(*),max(id) into flag,num_resv_id from dbmgr.gbs_sql where sql_id<>str_sql_id and give_up<>'Y' and dbms_lob.instr(sql_del_text_lower,str_sub_sql)>0;
if flag > 0 then
update dbmgr.gbs_sql set give_up='Y' where sql_id=str_sql_id and give_up<>'N';
update dbmgr.gbs_sql set give_up='N' where id=num_resv_id;
else
update dbmgr.gbs_sql set give_up='N' where sql_id=str_sql_id;
end if;
end if;
--execute immediate 'update dbmgr.gbs_sql a set a.give_up=''Y'' where a.sql_id=''' || str_sql_id ||''' and exists (select * from dbmgr.gbs_sql b where b.sql_id<>a.sql_id and b.give_up<>''Y'' and dbms_lob.instr(b.sql_del_text_lower,'''|| str_sub_str ||''')>0)';
end loop;
END LOOP;
close cur_sql;
END;
for i in tab_sql_id.first .. tab_sql_id.last loop
--forall i in tab_sql_id.first .. tab_sql_id.
str_sql_id := tab_sql_id(i);
str_sub_sql := tab_sub_sql(i);
select give_up into str_give_up from dbmgr.gbs_sql where sql_id=str_sql_id;
if str_give_up = 'X' then
select count(*),max(id) into flag,num_resv_id from dbmgr.gbs_sql where sql_id<>str_sql_id and give_up<>'Y' and dbms_lob.instr(sql_del_text_lower,str_sub_sql)>0;
if flag > 0 then
update dbmgr.gbs_sql set give_up='Y' where sql_id=str_sql_id and give_up<>'N';
update dbmgr.gbs_sql set give_up='N' where id=num_resv_id;
else
update dbmgr.gbs_sql set give_up='N' where sql_id=str_sql_id;
end if;
end if;
--execute immediate 'update dbmgr.gbs_sql a set a.give_up=''Y'' where a.sql_id=''' || str_sql_id ||''' and exists (select * from dbmgr.gbs_sql b where b.sql_id<>a.sql_id and b.give_up<>''Y'' and dbms_lob.instr(b.sql_del_text_lower,'''|| str_sub_str ||''')>0)';
end loop;
END LOOP;
close cur_sql;
END;
--update gbs_sql set give_up='X';
--select count(*),max(sql_id) from gbs_sql where give_up='N';
-------------------------------------------------------
/* Formatted on 2012/7/3 14:18:40 (QP5 v5.200.12012.37730) */
--CREATE OR REPLACE PROCEDURE for_debug
--IS
--
DECLARE
lob_sql CLOB;
lob_sql_lower CLOB;
len NUMBER;
loop_count NUMBER;
str_sql VARCHAR2 (32767);
amount_v NUMBER := 10000;
amount NUMBER;
position NUMBER;
data_buffer VARCHAR2 (32767);
str_sql_id VARCHAR2 (30);
--select count(*),max(sql_id) from gbs_sql where give_up='N';
-------------------------------------------------------
/* Formatted on 2012/7/3 14:18:40 (QP5 v5.200.12012.37730) */
--CREATE OR REPLACE PROCEDURE for_debug
--IS
--
DECLARE
lob_sql CLOB;
lob_sql_lower CLOB;
len NUMBER;
loop_count NUMBER;
str_sql VARCHAR2 (32767);
amount_v NUMBER := 10000;
amount NUMBER;
position NUMBER;
data_buffer VARCHAR2 (32767);
str_sql_id VARCHAR2 (30);
CURSOR cur_sql
IS
SELECT sql_id, sql_del_text, sql_del_text_lower
FROM dbmgr.gbs_sql
FOR UPDATE;
BEGIN
OPEN cur_sql;
IS
SELECT sql_id, sql_del_text, sql_del_text_lower
FROM dbmgr.gbs_sql
FOR UPDATE;
BEGIN
OPEN cur_sql;
LOOP
FETCH cur_sql
INTO str_sql_id, lob_sql, lob_sql_lower;
FETCH cur_sql
INTO str_sql_id, lob_sql, lob_sql_lower;
EXIT WHEN cur_sql%NOTFOUND;
len := DBMS_LOB.getlength (lob_sql);
position := 1;
len := DBMS_LOB.getlength (lob_sql);
position := 1;
--IF len <= 30000
--THEN
-- str_sql := DBMS_LOB.SUBSTR (lob_sql, len);
-- str_sql := LOWER (str_sql);
-- --lob_sql_lower := str_sql;
-- DBMS_LOB.writeappend (lob_sql_lower, len, str_sql);
--ELSE
amount := amount_v;
loop_count := CEIL (len / amount);
dbms_output.put_line('sql_id:'||str_sql_id);
--execute immediate 'update dbmgr.gbs_sql set sql_del_text_lower=empty_clob() where sql_id='''||str_sql_id||'''';
--dbms_lob.createtemporary(temp_lob,true,dbms_lob.session);
--THEN
-- str_sql := DBMS_LOB.SUBSTR (lob_sql, len);
-- str_sql := LOWER (str_sql);
-- --lob_sql_lower := str_sql;
-- DBMS_LOB.writeappend (lob_sql_lower, len, str_sql);
--ELSE
amount := amount_v;
loop_count := CEIL (len / amount);
dbms_output.put_line('sql_id:'||str_sql_id);
--execute immediate 'update dbmgr.gbs_sql set sql_del_text_lower=empty_clob() where sql_id='''||str_sql_id||'''';
--dbms_lob.createtemporary(temp_lob,true,dbms_lob.session);
FOR i IN 1 .. loop_count
LOOP
amount := amount_v;
position := (i - 1) * amount + 1;
DBMS_LOB.read (lob_sql,
amount,
position,
data_buffer);
data_buffer := LOWER (data_buffer);
DBMS_LOB.writeappend (lob_sql_lower, amount, data_buffer);
data_buffer := NULL;
END LOOP;
--END IF;
--big_amount := big_amount_v;
--loop_count := CEIL ((len+1-position) / big_amount);
END LOOP;
close cur_sql;
END;
LOOP
amount := amount_v;
position := (i - 1) * amount + 1;
DBMS_LOB.read (lob_sql,
amount,
position,
data_buffer);
data_buffer := LOWER (data_buffer);
DBMS_LOB.writeappend (lob_sql_lower, amount, data_buffer);
data_buffer := NULL;
END LOOP;
--END IF;
--big_amount := big_amount_v;
--loop_count := CEIL ((len+1-position) / big_amount);
END LOOP;
close cur_sql;
END;
-------------------------------------------------------
-------------------------------------------------------
/* Formatted on 2012/7/3 9:32:58 (QP5 v5.200.12012.37730) */
DECLARE
lob_sql CLOB;
len NUMBER;
amount_v NUMBER := 1;
amount NUMBER;
position NUMBER;
data_buffer VARCHAR2 (4);
str_sql_id VARCHAR2 (30);
/* Formatted on 2012/7/3 9:32:58 (QP5 v5.200.12012.37730) */
DECLARE
lob_sql CLOB;
len NUMBER;
amount_v NUMBER := 1;
amount NUMBER;
position NUMBER;
data_buffer VARCHAR2 (4);
str_sql_id VARCHAR2 (30);
CURSOR cur_sql
IS
SELECT sql_id, sql_del_text FROM dbmgr.gbs_sql;
BEGIN
OPEN cur_sql;
IS
SELECT sql_id, sql_del_text FROM dbmgr.gbs_sql;
BEGIN
OPEN cur_sql;
LOOP
FETCH cur_sql
INTO str_sql_id, lob_sql;
FETCH cur_sql
INTO str_sql_id, lob_sql;
EXIT WHEN cur_sql%NOTFOUND;
len := DBMS_LOB.getlength (lob_sql);
position := 1;
len := DBMS_LOB.getlength (lob_sql);
position := 1;
amount := amount_v;
<>
LOOP
DBMS_LOB.read (lob_sql,
amount,
position,
data_buffer);
LOOP
DBMS_LOB.read (lob_sql,
amount,
position,
data_buffer);
IF REGEXP_INSTR (data_buffer, '\s') > 0
THEN
position := position + 1;
ELSE
EXIT inner_loop;
END IF;
THEN
position := position + 1;
ELSE
EXIT inner_loop;
END IF;
data_buffer := NULL;
END LOOP inner_loop;
END LOOP inner_loop;
--big_amount := big_amount_v;
--loop_count := CEIL ((len+1-position) / big_amount);
IF position > 1
THEN
--temp_lob := EMPTY_CLOB ();
DBMS_LOB.COPY (lob_sql,
lob_sql,
len + 1 - position,
1,
position);
DBMS_LOB.TRIM (lob_sql,
len + 1 - position);
--lob_sql := temp_lob;
END IF;
END LOOP;
close cur_sql;
END;
-------------------------------------------------------
declare
lob_sql clob;
len number;
amount number := 1;
position number;
data_buffer varchar2(4);
str_sql_id varchar2(30);
cursor cur_sql is
select sql_id,sql_text from dbmgr.stage_sqlset;
--loop_count := CEIL ((len+1-position) / big_amount);
IF position > 1
THEN
--temp_lob := EMPTY_CLOB ();
DBMS_LOB.COPY (lob_sql,
lob_sql,
len + 1 - position,
1,
position);
DBMS_LOB.TRIM (lob_sql,
len + 1 - position);
--lob_sql := temp_lob;
END IF;
END LOOP;
close cur_sql;
END;
-------------------------------------------------------
declare
lob_sql clob;
len number;
amount number := 1;
position number;
data_buffer varchar2(4);
str_sql_id varchar2(30);
cursor cur_sql is
select sql_id,sql_text from dbmgr.stage_sqlset;
begin
open cur_sql;
loop
fetch cur_sql into str_sql_id, lob_sql;
exit when cur_sql%NOTFOUND;
len := dbms_lob.getlength(lob_sql);
position := len;
open cur_sql;
loop
fetch cur_sql into str_sql_id, lob_sql;
exit when cur_sql%NOTFOUND;
len := dbms_lob.getlength(lob_sql);
position := len;
<>
loop
dbms_lob.read(lob_sql,amount,position, data_buffer);
if regexp_instr(data_buffer,'\s') <= 0 then
exit inner_loop;
else
position := position -1;
end if;
end loop inner_loop;
loop
dbms_lob.read(lob_sql,amount,position, data_buffer);
if regexp_instr(data_buffer,'\s') <= 0 then
exit inner_loop;
else
position := position -1;
end if;
end loop inner_loop;
if data_buffer = ';' then
execute immediate 'update dbmgr.gbs_sql set is_pl_sql=''Y'' where sql_id=''' || str_sql_id || '''';
--dbms_output.put_line(str_sql_id);
end if;
end loop;
close cur_sql;
end;
execute immediate 'update dbmgr.gbs_sql set is_pl_sql=''Y'' where sql_id=''' || str_sql_id || '''';
--dbms_output.put_line(str_sql_id);
end if;
end loop;
close cur_sql;
end;
declare
len number;
len_del number;
i number :=0;
str_sql_id varchar2(30);
lob_sql clob;
lob_del_sql clob;
cursor cur_sql is
select sql_id,sql_text,sql_del_text from dbmgr.gbs_sql where is_pl_sql='Y' for update;
len number;
len_del number;
i number :=0;
str_sql_id varchar2(30);
lob_sql clob;
lob_del_sql clob;
cursor cur_sql is
select sql_id,sql_text,sql_del_text from dbmgr.gbs_sql where is_pl_sql='Y' for update;
begin
open cur_sql;
loop
fetch cur_sql into str_sql_id,lob_sql,lob_del_sql;
exit when cur_sql%NOTFOUND;
i := i + 1;
len := DBMS_LOB.getlength(lob_sql);
len_del := dbms_lob.getlength(lob_del_sql);
dbms_lob.trim(lob_sql, len-1);
dbms_lob.trim(lob_del_sql, len_del-1);
end loop;
close cur_sql;
dbms_output.put_line(i);
close cur_sql;
end;
open cur_sql;
loop
fetch cur_sql into str_sql_id,lob_sql,lob_del_sql;
exit when cur_sql%NOTFOUND;
i := i + 1;
len := DBMS_LOB.getlength(lob_sql);
len_del := dbms_lob.getlength(lob_del_sql);
dbms_lob.trim(lob_sql, len-1);
dbms_lob.trim(lob_del_sql, len_del-1);
end loop;
close cur_sql;
dbms_output.put_line(i);
close cur_sql;
end;
update dbmgr.gbs_sql set sql_len=dbms_lob.getlength(sql_text),sql_del_len=dbms_lob.getlength(sql_del_text) where is_pl_sql='Y';
-------------------------------------------------------
-------------------------------------------------------
/* Formatted on 2012/6/30 17:21:54 (QP5 v5.200.12012.37730) */
CREATE OR REPLACE PROCEDURE for_debug
IS
--DECLARE
amount_v NUMBER := 10000;
amount NUMBER;
file_maxsize NUMBER := 4000000;
num_sqlpfile NUMBER := 1500;
data_buffer VARCHAR2 (32767);
loop_count NUMBER;
lob_sql_text CLOB;
str_sql_id VARCHAR2 (30);
num_sql_len NUMBER;
fno NUMBER;
file_len NUMBER;
position NUMBER;
num_sql NUMBER;
is_exit_inner BOOLEAN := FALSE;
file_handle UTL_FILE.file_type;
CREATE OR REPLACE PROCEDURE for_debug
IS
--DECLARE
amount_v NUMBER := 10000;
amount NUMBER;
file_maxsize NUMBER := 4000000;
num_sqlpfile NUMBER := 1500;
data_buffer VARCHAR2 (32767);
loop_count NUMBER;
lob_sql_text CLOB;
str_sql_id VARCHAR2 (30);
num_sql_len NUMBER;
fno NUMBER;
file_len NUMBER;
position NUMBER;
num_sql NUMBER;
is_exit_inner BOOLEAN := FALSE;
file_handle UTL_FILE.file_type;
CURSOR cur_sql
IS
SELECT sql_id, sql_text FROM dbmgr.gbs_sql;
BEGIN
--file_handle := utl_file.fopen('TMP_DIR','t.sql','a',32767);
OPEN cur_sql;
IS
SELECT sql_id, sql_text FROM dbmgr.gbs_sql;
BEGIN
--file_handle := utl_file.fopen('TMP_DIR','t.sql','a',32767);
OPEN cur_sql;
fno := 0;
<>
LOOP
fno := fno + 1;
file_handle :=
UTL_FILE.fopen ('TMP_DIR',
'gbs_sql_' || fno || '.sql',
'w',
32767);
file_len := 0;
LOOP
fno := fno + 1;
file_handle :=
UTL_FILE.fopen ('TMP_DIR',
'gbs_sql_' || fno || '.sql',
'w',
32767);
file_len := 0;
<>
FOR i IN 1 .. num_sqlpfile
LOOP
FETCH cur_sql
INTO str_sql_id, lob_sql_text;
FOR i IN 1 .. num_sqlpfile
LOOP
FETCH cur_sql
INTO str_sql_id, lob_sql_text;
---------------------------------------------------------
IF cur_sql%NOTFOUND
THEN
IF is_exit_inner OR num_sql = num_sqlpfile
THEN
UTL_FILE.FREMOVE ('TMP_DIR', 'gbs_sql_' || fno || '.sql');
END IF;
IF cur_sql%NOTFOUND
THEN
IF is_exit_inner OR num_sql = num_sqlpfile
THEN
UTL_FILE.FREMOVE ('TMP_DIR', 'gbs_sql_' || fno || '.sql');
END IF;
EXIT outer_loop;
END IF;
END IF;
is_exit_inner := FALSE;
---------------------------------------------------------
--EXIT outer_loop WHEN cur_sql%NOTFOUND;
--dbms_output.put_line(str_sql_id||':'||length(lob_sql_text));
num_sql_len := DBMS_LOB.getlength (lob_sql_text);
amount := amount_v;
loop_count := CEIL (num_sql_len / amount);
---------------------------------------------------------
--EXIT outer_loop WHEN cur_sql%NOTFOUND;
--dbms_output.put_line(str_sql_id||':'||length(lob_sql_text));
num_sql_len := DBMS_LOB.getlength (lob_sql_text);
amount := amount_v;
loop_count := CEIL (num_sql_len / amount);
FOR j IN 1 .. loop_count
LOOP
position := (j - 1) * amount + 1;
DBMS_LOB.read (lob_sql_text,
amount,
position,
data_buffer);
UTL_FILE.put (file_handle, data_buffer);
--UTL_FILE.fflush(file_handle);
data_buffer := NULL;
END LOOP;
LOOP
position := (j - 1) * amount + 1;
DBMS_LOB.read (lob_sql_text,
amount,
position,
data_buffer);
UTL_FILE.put (file_handle, data_buffer);
--UTL_FILE.fflush(file_handle);
data_buffer := NULL;
END LOOP;
file_len := file_len + num_sql_len;
IF file_len >= file_maxsize
THEN
is_exit_inner := TRUE;
EXIT inner_loop;
END IF;
THEN
is_exit_inner := TRUE;
EXIT inner_loop;
END IF;
UTL_FILE.new_line (file_handle);
--UTL_FILE.fflush(file_handle);
file_len := file_len + 1;
--IF utl_file.is_open(file_handle) THEN
-- utl_file.put_line(file_handle,lob_sql_text);
--end IF;
num_sql := i;
END LOOP inner_loop;
--UTL_FILE.fflush(file_handle);
UTL_FILE.fclose (file_handle);
UTL_FILE.frename ('TMP_DIR',
'gbs_sql_' || fno || '.sql',
'TMP_DIR',
'gbs_sql_' || fno || '_' || num_sql || '.sql');
END LOOP outer_loop;
close cur_sql;
IF UTL_FILE.is_open (file_handle)
THEN
--UTL_FILE.fflush(file_handle);
UTL_FILE.fclose (file_handle);
--UTL_FILE.fflush(file_handle);
file_len := file_len + 1;
--IF utl_file.is_open(file_handle) THEN
-- utl_file.put_line(file_handle,lob_sql_text);
--end IF;
num_sql := i;
END LOOP inner_loop;
--UTL_FILE.fflush(file_handle);
UTL_FILE.fclose (file_handle);
UTL_FILE.frename ('TMP_DIR',
'gbs_sql_' || fno || '.sql',
'TMP_DIR',
'gbs_sql_' || fno || '_' || num_sql || '.sql');
END LOOP outer_loop;
close cur_sql;
IF UTL_FILE.is_open (file_handle)
THEN
--UTL_FILE.fflush(file_handle);
UTL_FILE.fclose (file_handle);
IF num_sql <> num_sqlpfile
THEN
UTL_FILE.frename ('TMP_DIR',
'gbs_sql_' || fno || '.sql',
'TMP_DIR',
'gbs_sql_' || fno || '_' || num_sql || '.sql');
END IF;
END IF;
--dbms_output.put_line('t:'||t);
THEN
UTL_FILE.frename ('TMP_DIR',
'gbs_sql_' || fno || '.sql',
'TMP_DIR',
'gbs_sql_' || fno || '_' || num_sql || '.sql');
END IF;
END IF;
--dbms_output.put_line('t:'||t);
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put_line ('str_sql_id:' || str_sql_id);
DBMS_OUTPUT.put_line (SQLERRM);
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put_line ('str_sql_id:' || str_sql_id);
DBMS_OUTPUT.put_line (SQLERRM);
IF UTL_FILE.is_open (file_handle)
THEN
UTL_FILE.fclose (file_handle);
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END;
THEN
UTL_FILE.fclose (file_handle);
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11411056/viewspace-736988/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【PL/SQL】初試 bulk collectSQL
- pl/sql中bulk collect的用法SQL
- Oracle 的 bulk collect用法Oracle
- Quoted- How Bulk Binds in PL/SQL Boost PerformanceSQLORM
- BULK COLLECT FAILS WITH ORA-04030AI
- 使用BULK COLLECT+FORALL加速批量提交
- APPEND,bulk collect,正常插入比較APP
- 使用Bulk Collect提高Oracle查詢效率Oracle
- Oracle PL/SQL 優化與調整 -- Bulk 說明OracleSQL優化
- 使用bulk collect實現cursor 批量fetch！
- pl/sql 7.13/7.14破解檔案SQL
- 多行資料的批處理之bulk collect
- 用BULK COLLECT 減小LOOP的開銷(1)OOP
- Oracle中巧用bulk collect實現cursor批次fetchOracle
- 如何在PL/SQL中讀寫檔案(轉)SQL
- [20180110]Oracle Bulk Collect and LimitOracleMIT
- 使用oracle bulk collect 提高select FETCH LOOP的速度OracleOOP
- oracle中bulk collect into用法 （批次效率提取遊標資料）Oracle
- 使用Forall 與bulk collect的快速複製表資料
- 利用pl/sql執行本地的sql檔案中的sql語句SQL
- 【LOB】Oracle Lob管理常用sqlOracleSQL
- 遊標+bulk collect into limit的不同方法查詢資料MIT
- oracle10g_plsql_rercursor_type_table of_小引例_bulk collect intoOracleSQL
- [20180111]Oracle Bulk Collect and Limit2OracleMIT
- Oracle資料庫的BULK COLLECT用法之批量增刪改<轉>Oracle資料庫
- PL/SQL Profiler 和SQL Developer 報表SQLDeveloper
- 一次遷移思考的記錄--bulk_collect的limit用法MIT
- mongodb批量操作， bulk_write，MongoDB
- git的忽略檔案和刪除檔案操作Git
- 操作lob，居然成功了！
- pl/sql %type和%rowtype區別SQL
- PL/SQLSQL
- Oracle中的LOB操作（轉）Oracle
- 觸發器中操作LOB觸發器
- SQL Server 中資料庫檔案的存放方式，檔案和檔案組SQLServer資料庫
- SQL Server 2005中的檔案和檔案組SQLServer
- SQL&PL/SQL （轉）SQL
- 轉摘plsql高階程式設計_table_array_for all_bulk collect into_fetchSQL程式設計