ORA-06503: PL/SQL: Function returned without value
Amit, November 21, 2006 - 1:31 pm UTC
Hi Tom,
I am facing the error in the below package :
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "STRMADMIN.REPLICATION_DIFF", line 534
ORA-06512: at line 1
CREATE OR REPLACE PACKAGE BODY replication_diff AS
TYPE v50_table IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
FUNCTION open_file(dir_name VARCHAR2,
file_name VARCHAR2,
append_file BOOLEAN)
RETURN UTL_FILE.FILE_TYPE IS
FileHandle UTL_FILE.FILE_TYPE;
BEGIN
dbms_output.put_line('Opening file');
IF (append_file) THEN
FileHandle := UTL_FILE.FOPEN(dir_name, file_name, 'a');
ELSE
FileHandle := UTL_FILE.FOPEN(dir_name, file_name, 'w');
END IF;
RETURN(FileHandle);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR in opening file '||dir_name||','||file_name);
DBMS_OUTPUT.PUT_LINE('ERROR in opening file:'||TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE('ERROR in opening file:'||SQLERRM);
END;
FUNCTION subset_str(full_str IN OUT VARCHAR2, subset_str IN OUT VARCHAR2, look_str VARCHAR2,no_of_chars INTEGER) RETURN VARCHAR2 is
loc PLS_INTEGER := 0;
pre_loc PLS_INTEGER := 0;
i PLS_INTEGER := 1;
BEGIN
if (instr(full_str,look_str,1)=0) then
return(1);
end if;
if (NVL(length(full_str),0) <= no_of_chars) then
subset_str := full_str;
full_str := '';
return(0);
end if;
loop
loc := instr(full_str,look_str,1,i);
--dbms_output.put_line('loc = '||loc);
if ((loc > no_of_chars) OR (loc = 0)) then
exit;
end if;
if (i > 400) then
dbms_output.put_line('Exceeding the limit....');
exit;
end if;
pre_loc := loc;
i := i + 1;
end loop;
subset_str := substr(full_str,1,pre_loc-1);
full_str := substr(full_str,pre_loc);
return(0);
END;
PROCEDURE print(str VARCHAR2) IS
len PLS_INTEGER;
BEGIN
len := NVL(LENGTH(str),0);
FOR i in 1..len LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,(i-1)*255,255));
END LOOP;
IF ((len*255) > LENGTH(str)) THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,len*255));
END IF;
END;
PROCEDURE init_file(dir_name VARCHAR2,
file_name VARCHAR2)
IS
FileHandle UTL_FILE.FILE_TYPE;
BEGIN
FileHandle := open_file(dir_name,file_name,FALSE);
UTL_FILE.FCLOSE(FileHandle);
END;
PROCEDURE set_switch_off(fd UTL_FILE.FILE_TYPE,slink varchar2,tlink varchar2) IS
BEGIN
UTL_FILE.PUTF(fd,'\nwhenever sqlerror exit sqlcode\n');
UTL_FILE.PUTF(fd,'exec dbms_reputil.replication_off@%s\n',slink);
UTL_FILE.PUTF(fd,'exec repconflict_tmstp_pkg.update_timestamp_off@%s\n',slink);
UTL_FILE.PUTF(fd,'exec dbms_streams.set_tag@%s(tag => HEXTORAW(''1D''))\n',slink);
UTL_FILE.PUTF(fd,'exec dbms_reputil.replication_off@%s\n',tlink);
UTL_FILE.PUTF(fd,'exec repconflict_tmstp_pkg.update_timestamp_off@%s\n',tlink);
UTL_FILE.PUTF(fd,'exec dbms_streams.set_tag@%s(tag => HEXTORAW(''1D''))\n',tlink);
UTL_FILE.PUTF(fd,'\nwhenever sqlerror continue\n');
END;
PROCEDURE get_columns(sschema VARCHAR2,
stable VARCHAR2,
cols OUT col_tab)
IS
CURSOR col_cur(sname VARCHAR2, oname VARCHAR2) IS
SELECT column_name, data_type
FROM dba_tab_columns
WHERE owner=sname AND
table_name = oname
ORDER BY column_id;
idx PLS_INTEGER := 1;
BEGIN
FOR row IN col_cur(sschema,stable) LOOP
cols(idx).col_name := row.column_name;
cols(idx).col_type := row.data_type;
cols(idx).pos := idx;
idx := idx + 1;
END LOOP;
END;
PROCEDURE table_minus_table(table1 col_tab,
table2 col_tab,
out_table OUT col_tab)
IS
BEGIN
out_table := table1;
IF (table2.COUNT = 0) THEN
RETURN;
END IF;
FOR i IN 1..out_table.COUNT LOOP
FOR j IN 1..table2.COUNT LOOP
IF (out_table(i).col_name = table2(j).col_name) THEN
out_table.DELETE(i);
END IF;
END LOOP;
END LOOP;
END;
FUNCTION get_col_str(cols col_tab,
delimiter char,
prefix VARCHAR2,
postfix VARCHAR)
RETURN VARCHAR2 IS
out_str VARCHAR2(32000);
BEGIN
FOR i IN 1..cols.COUNT LOOP
IF (out_str IS NULL) THEN
out_str := prefix||cols(i).col_name||postfix;
ELSE
out_str := out_str||delimiter||prefix||cols(i).col_name||postfix;
END IF;
END LOOP;
RETURN(out_str);
END;
FUNCTION diff_table_internal(sschema VARCHAR2,
stable VARCHAR2,
slink VARCHAR2,
tschema VARCHAR2,
ttable VARCHAR2,
tlink VARCHAR2,
keycols VARCHAR2,
diff_file_handle UTL_FILE.FILE_TYPE,
fix_file_handle UTL_FILE.FILE_TYPE,
diff_limit NUMBER ,
where_clause_1 VARCHAR2,
where_clause_2 VARCHAR2,
where_clause_3 VARCHAR2,
columns_list VARCHAR2,
skip_columns VARCHAR2,
commit_batch NUMBER,
timestamp_col VARCHAR2,
check_again BOOLEAN,
diff_table_nm VARCHAR2,
compare_tmstp BOOLEAN )
RETURN NUMBER;
FUNCTION get_key(sname VARCHAR2, oname VARCHAR2)
RETURN VARCHAR2 IS
CURSOR pkeys_cur(sname VARCHAR2, oname VARCHAR2) IS
SELECT column_name
FROM dba_constraints a, dba_cons_columns b
WHERE a.constraint_name=b.constraint_name AND
a.owner = b.owner AND
a.owner = sname AND
a.constraint_type='P' AND
a.table_name=oname
ORDER BY position;
key_str VARCHAR2(500);
BEGIN
FOR i IN pkeys_cur(sname,oname) LOOP
IF (key_str IS NULL) THEN
key_str := i.column_name;
ELSE
key_str := key_str||','||i.column_name;
END IF;
END LOOP;
RETURN(key_str);
END;
PROCEDURE get_table_for_str(
p_arr OUT v50_table ,
p_string VARCHAR2,
delim VARCHAR2 := ',',
enclose_str VARCHAR2 DEFAULT NULL)
IS
pos INTEGER := 1;
v_idx INTEGER := 1;
tmp_str VARCHAR2(4000);
BEGIN
IF p_string IS NULL THEN
return;
END IF;
tmp_str := p_string;
LOOP
EXIT WHEN (pos = 0);
pos := INSTR(tmp_str,delim);
IF (pos = 0) THEN
p_arr(v_idx) := enclose_str||tmp_str||enclose_str;
ELSE
p_arr(v_idx) := enclose_str||SUBSTR(tmp_str,1,pos-1)||enclose_str;
v_idx := v_idx + 1;
tmp_str := SUBSTR(tmp_str,pos+1);
END IF;
END LOOP;
END;
FUNCTION getcols(sname VARCHAR2, tname VARCHAR2) RETURN VARCHAR2 IS
columns_list v50_table;
col_str VARCHAR2(32000);
BEGIN
SELECT column_name BULK COLLECT INTO columns_list
FROM dba_tab_columns
WHERE owner=sname AND table_name=tname;
FOR i IN 1..columns_list.COUNT loop
col_str := col_str||columns_list(i)||',';
END LOOP;
RETURN(col_str);
END;
PROCEDURE diff_table(sschema VARCHAR2,
stable VARCHAR2,
slink VARCHAR2,
tschema VARCHAR2,
ttable VARCHAR2,
tlink VARCHAR2,
keycols VARCHAR2,
dir_name VARCHAR2 ,
file_name VARCHAR2,
fix_file_name VARCHAR2,
diff_limit NUMBER DEFAULT 500,
commit_batch NUMBER DEFAULT 500,
timestamp_col VARCHAR2 DEFAULT NULL,
where_clause VARCHAR2 DEFAULT NULL,
columns_list VARCHAR2 DEFAULT '*',
check_again BOOLEAN DEFAULT FALSE,
compare_tmstp BOOLEAN DEFAULT TRUE,
skip_columns VARCHAR2 DEFAULT NULL) IS
diff_file_handle UTL_FILE.FILE_TYPE;
fix_file_handle UTL_FILE.FILE_TYPE;
no_of_diff NUMBER;
where_clause_1 VARCHAR2(5000);
where_clause_2 VARCHAR2(5000);
where_clause_3 VARCHAR2(5000);
key_str VARCHAR2(500);
all_cols VARCHAR2(32000);
skip_cols_tab v50_table;
BEGIN
diff_file_handle := open_file(dir_name,file_name,FALSE);
fix_file_handle := open_file(dir_name,fix_file_name,FALSE);
IF (keycols IS NULL) THEN
key_str := get_key(sschema,stable);
ELSE
key_str := keycols;
END IF;
IF (skip_columns IS NOT NULL) THEN
all_cols := ','||getcols(sschema,stable);
get_table_for_str(skip_cols_tab,skip_columns);
FOR i IN 1..skip_cols_tab.COUNT LOOP
--all_cols := REPLACE(all_cols,skip_cols_tab(i)||',',NULL);
dbms_output.put_line('Replace='||','||skip_cols_tab(i)||',');
all_cols := REPLACE(all_cols,','||skip_cols_tab(i)||',',',');
END LOOP;
all_cols := SUBSTR(all_cols,2,length(all_cols)-2);
dbms_output.put_line('All cols='||all_cols);
ELSE
all_cols := columns_list;
END IF;
no_of_diff := diff_table_internal(sschema,stable,
slink,
tschema,
ttable,
tlink,
key_str,
diff_file_handle,
fix_file_handle,
diff_limit,
where_clause,
where_clause,
where_clause,
all_cols,
skip_columns,
commit_batch,
timestamp_col,
check_again,
stable,
compare_tmstp);
UTL_FILE.FCLOSE(diff_file_handle);
UTL_FILE.FCLOSE(fix_file_handle);
if ((check_again) AND (no_of_diff > 0)) THEN
IF (where_clause IS NULL) then
where_clause_1 := 'where ('||keycols||') in (select '||keycols||' from '||stable||'$_diff where
diff_type=''AK-BK'')';
where_clause_2 := 'where ('||keycols||') in (select '||keycols||' from '||stable||'$_diff where
diff_type=''BK-AK'')';
where_clause_3 := 'where ('||keycols||') in (select '||keycols||' from '||stable||'$_diff where
diff_type=''A-B'')';
ELSE
where_clause_1 := ' and ('||keycols||') in (select '||keycols||' from '||stable||'$_diff where
diff_type=''AK-BK'')';
where_clause_2 := ' and ('||keycols||') in (select '||keycols||' from '||stable||'$_diff where
diff_type=''BK-AK'')';
where_clause_3 := ' and ('||keycols||') in (select '||keycols||' from '||stable||'$_diff where
diff_type=''A-B'')';
END IF;
diff_file_handle := open_file(dir_name,file_name||'_1',FALSE);
fix_file_handle := open_file(dir_name,fix_file_name||'_1',FALSE);
no_of_diff := diff_table_internal(sschema,stable,
slink,
tschema,
ttable,
tlink,
key_str,
diff_file_handle,
fix_file_handle,
diff_limit,
where_clause_1,
where_clause_2,
where_clause_3,
columns_list,
NULL,
commit_batch,
timestamp_col,
FALSE,
stable||'$$_diff',
compare_tmstp);
UTL_FILE.FCLOSE(diff_file_handle);
UTL_FILE.FCLOSE(fix_file_handle);
end if;
END;
FUNCTION get_localdb RETURN varchar2 is
dbname VARCHAR2(10);
begin
select name into dbname from v$database;
return(dbname);
end;
PROCEDURE diff_group_streams(sname VARCHAR2,
dir_name VARCHAR2 ,
file_name VARCHAR2 DEFAULT NULL,
fix_file_name VARCHAR2 DEFAULT NULL,
diff_limit NUMBER DEFAULT 500,
commit_batch NUMBER DEFAULT 500,
timestamp_col VARCHAR2 DEFAULT NULL,
skip_columns VARCHAR2 DEFAULT NULL,
where_clause VARCHAR2 DEFAULT NULL,
individual_files BOOLEAN DEFAULT TRUE,
compare_tmstp BOOLEAN DEFAULT TRUE) IS
CURSOR objects_cur(sname VARCHAR2) IS
select distinct a.table_owner sname, a.table_name oname
from DBA_STREAMS_TABLE_RULES a, DBA_RULE_SET_RULES b
where a.table_owner=sname and a.streams_type='CAPTURE' and a.RULE_NAME = b.RULE_NAME
--select distinct source_object_owner sname, source_object_name oname
--from DBA_APPLY_INSTANTIATED_OBJECTS r
--where source_object_owner=sname
--and source_object_type='TABLE'
and not exists
(select 1
from dba_tab_columns c
where a.table_owner=c.owner and
a.table_name=c.table_name and
c.data_type in ('CLOB','BLOB','LONG','LONG RAW','NCLOB','XMLTYPE'));
CURSOR links_cur(sname VARCHAR2, oname VARCHAR2) IS
SELECT source_database dblink
FROM dba_apply_instantiated_objects
WHERE source_object_owner=sname AND
source_object_name=oname;
TYPE v150_table IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
dblinks v150_table;
schemas v150_table;
objects v150_table;
skip_cols_tab v50_table;
idx NUMBER:=1;
j NUMBER;
key_cols VARCHAR2(32000);
all_cols VARCHAR2(32000);
loopCount NUMBER;
diff_file_handle UTL_FILE.FILE_TYPE;
fix_file_handle UTL_FILE.FILE_TYPE;
no_of_diff NUMBER;
whereclause VARCHAR2(5000) := ' WHERE repconflict_tmstp > sysdate - 8 ';
FUNCTION getkeys(sname VARCHAR2, oname VARCHAR2) RETURN VARCHAR2 IS
CURSOR pkeys_cur(sname VARCHAR2, oname VARCHAR2) IS
SELECT column_name
FROM dba_constraints a, dba_cons_columns b
WHERE a.constraint_name=b.constraint_name AND
a.owner = b.owner AND
a.owner = sname AND
a.constraint_type='P' AND
a.table_name=oname
ORDER BY position;
CURSOR rep_key_cols_cur(psname VARCHAR2, poname VARCHAR2) IS
SELECT column_name
FROM dba_apply_key_columns
WHERE object_owner = psname AND
object_name = poname;
key_str VARCHAR2(32000);
BEGIN
FOR i IN pkeys_cur(sname,oname) LOOP
IF (key_str IS NULL) THEN
key_str := i.column_name;
ELSE
key_str := key_str||','||i.column_name;
END IF;
END LOOP;
IF (key_str IS NULL) THEN
FOR i IN rep_key_cols_cur(sname,oname) LOOP
IF (key_str IS NULL) THEN
key_str := i.column_name;
ELSE
key_str := key_str||','||i.column_name;
END IF;
END LOOP;
END IF;
IF (key_str IS NULL) THEN
RAISE NO_DATA_FOUND;
END IF;
RETURN(key_str);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR IN getkeys : '||TO_CHAR(SQLCODE));
RAISE;
END;
BEGIN
/*
IF (file_name IS NULL) THEN
file_name := sname||'.diff';
END IF;
IF (fix_file_name IS NULL) THEN
fix_file_name := sname||'.fix';
END IF;
*/
init_file(dir_name,NVL(file_name,sname||'.diff'));
init_file(dir_name,NVL(file_name,sname||'.fix'));
diff_file_handle := open_file(dir_name,NVL(file_name,sname||'.diff'),FALSE);
fix_file_handle := open_file(dir_name,NVL(fix_file_name,sname||'.fix'),FALSE);
idx := 1;
FOR i in objects_cur(sname) LOOP
schemas(idx) := i.sname;
objects(idx) := i.oname;
idx := idx + 1;
END LOOP;
FOR tabidx IN 1..schemas.COUNT LOOP
IF (individual_files) THEN
diff_file_handle := open_file(dir_name,objects(tabidx)||'.diff',FALSE);
fix_file_handle := open_file(dir_name,objects(tabidx)||'.fix',FALSE);
END IF;
key_cols := getkeys(schemas(tabidx),objects(tabidx));
IF (skip_columns IS NOT NULL) THEN
all_cols := ','||getcols(schemas(tabidx),objects(tabidx));
--all_cols := getcols(schemas(tabidx),objects(tabidx));
get_table_for_str(skip_cols_tab,skip_columns);
FOR i IN 1..skip_cols_tab.COUNT LOOP
--all_cols := REPLACE(all_cols,skip_cols_tab(i)||',',NULL);
dbms_output.put_line('Replace='||','||skip_cols_tab(i)||',');
all_cols := REPLACE(all_cols,','||skip_cols_tab(i)||',',',');
END LOOP;
all_cols := SUBSTR(all_cols,2,length(all_cols)-2);
dbms_output.put_line('All cols='||all_cols);
ELSE
all_cols := '*';
END IF;
dbms_output.put_line('all_cols='||substr(all_cols,1,200));
dblinks(1) := get_localdb;
idx := 2;
FOR l in links_cur(sname,objects(tabidx)) LOOP
dblinks(idx) := l.dblink;
idx := idx +1 ;
END LOOP;
IF (dblinks.COUNT = 2) THEN
LoopCount := 1;
ELSE
LoopCount := NVL(dblinks.COUNT,1)-1;
END IF;
FOR i IN 1..LoopCount LOOP
dbms_output.put_line('Processing '||schemas(tabidx)||'.'||objects(tabidx)||'@'||dblinks(1)||' AND '||schemas(tabidx)||'.'||objects(tabidx)||'@'||dblinks(i+1));
--no_of_diff := diff_table_internal(schemas(tabidx),objects(tabidx),dblinks(1),schemas(tabidx),objects(tabidx),dblinks(i+1),key_cols,diff_file_handle,fix_file_handle,diff_limit,whereclause,whereclause,whereclause,'*',commit_batch,timestamp_col,FALSE,NULL);
no_of_diff := diff_table_internal(schemas(tabidx),
objects(tabidx),
dblinks(1),
schemas(tabidx),
objects(tabidx),
dblinks(i+1),
key_cols,
diff_file_handle,
fix_file_handle,
diff_limit,
where_clause,
where_clause,
where_clause,
all_cols,
skip_columns,
commit_batch,
timestamp_col,
FALSE,
NULL,
compare_tmstp);
END LOOP;
IF (individual_files) THEN
UTL_FILE.FCLOSE(diff_file_handle);
UTL_FILE.FCLOSE(fix_file_handle);
END IF;
END LOOP;
IF NOT(individual_files) THEN
UTL_FILE.FCLOSE(diff_file_handle);
UTL_FILE.FCLOSE(fix_file_handle);
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
PROCEDURE diff_group(groupname VARCHAR2,
dir_name VARCHAR2 ,
file_name VARCHAR2 DEFAULT NULL,
fix_file_name VARCHAR2 DEFAULT NULL,
diff_limit NUMBER DEFAULT 500,
commit_batch NUMBER DEFAULT 500,
timestamp_col VARCHAR2 DEFAULT NULL,
skip_columns VARCHAR2 DEFAULT NULL,
where_clause VARCHAR2 DEFAULT NULL,
individual_files BOOLEAN DEFAULT TRUE,
compare_tmstp BOOLEAN DEFAULT TRUE) IS
CURSOR objects_cur(groupname VARCHAR2) IS
SELECT sname, oname
FROM dba_repobject r
WHERE GNAME=groupname AND TYPE='TABLE' and not exists
(select 1
from dba_tab_columns c
where r.sname=c.owner and
r.oname=c.table_name and
c.data_type in ('CLOB','BLOB','LONG','LONG RAW','NCLOB','XMLTYPE'));
--and oname >= 'PERS%';
CURSOR links_cur(groupname VARCHAR2) IS
SELECT dblink
FROM dba_repsites
WHERE GNAME=groupname;
TYPE v150_table IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
dblinks v150_table;
schemas v150_table;
objects v150_table;
skip_cols_tab v50_table;
idx NUMBER:=1;
j NUMBER;
key_cols VARCHAR2(500);
all_cols VARCHAR2(32000);
loopCount NUMBER;
diff_file_handle UTL_FILE.FILE_TYPE;
fix_file_handle UTL_FILE.FILE_TYPE;
no_of_diff NUMBER;
whereclause VARCHAR2(5000) := ' WHERE repconflict_tmstp > sysdate - 8 ';
FUNCTION getkeys(sname VARCHAR2, oname VARCHAR2) RETURN VARCHAR2 IS
CURSOR pkeys_cur(sname VARCHAR2, oname VARCHAR2) IS
SELECT column_name
FROM dba_constraints a, dba_cons_columns b
WHERE a.constraint_name=b.constraint_name AND
a.owner = b.owner AND
a.owner = sname AND
a.constraint_type='P' AND
a.table_name=oname
ORDER BY position;
CURSOR rep_key_cols_cur(psname VARCHAR2, poname VARCHAR2) IS
SELECT col
FROM dba_repkey_columns
WHERE sname = psname AND
oname = poname;
key_str VARCHAR2(500);
BEGIN
FOR i IN pkeys_cur(sname,oname) LOOP
IF (key_str IS NULL) THEN
key_str := i.column_name;
ELSE
key_str := key_str||','||i.column_name;
END IF;
END LOOP;
IF (key_str IS NULL) THEN
FOR i IN rep_key_cols_cur(sname,oname) LOOP
IF (key_str IS NULL) THEN
key_str := i.col;
ELSE
key_str := key_str||','||i.col;
END IF;
END LOOP;
END IF;
RETURN(key_str);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR IN getkeys : '||TO_CHAR(SQLCODE));
RAISE;
END;
BEGIN
/*
IF (file_name IS NULL) THEN
file_name := group_name||'.diff';
END IF;
IF (fix_file_name IS NULL) THEN
fix_file_name := group_name||'.fix';
END IF;
*/
init_file(dir_name,NVL(file_name,groupname||'.diff'));
init_file(dir_name,NVL(file_name,groupname||'.fix'));
diff_file_handle := open_file(dir_name,NVL(file_name,groupname||'.diff'),FALSE);
fix_file_handle := open_file(dir_name,NVL(fix_file_name,groupname||'.fix'),FALSE);
FOR i in links_cur(groupname) LOOP
dblinks(idx) := i.dblink;
idx := idx +1 ;
END LOOP;
idx := 1;
FOR i in objects_cur(groupname) LOOP
schemas(idx) := i.sname;
objects(idx) := i.oname;
idx := idx + 1;
END LOOP;
FOR tabidx IN 1..schemas.COUNT LOOP
IF (individual_files) THEN
diff_file_handle := open_file(dir_name,objects(tabidx)||'.diff',FALSE);
fix_file_handle := open_file(dir_name,objects(tabidx)||'.fix',FALSE);
END IF;
key_cols := getkeys(schemas(tabidx),objects(tabidx));
IF (skip_columns IS NOT NULL) THEN
all_cols := ','||getcols(schemas(tabidx),objects(tabidx));
get_table_for_str(skip_cols_tab,skip_columns);
FOR i IN 1..skip_cols_tab.COUNT LOOP
--dbms_output.put_line('to be replaced='||'<'||all_cols||'>');
--dbms_output.put_line('Replace='||','||skip_cols_tab(i)||',');
all_cols := REPLACE(all_cols,','||skip_cols_tab(i)||',',',');
END LOOP;
all_cols := SUBSTR(all_cols,2,length(all_cols)-2);
ELSE
all_cols := '*';
END IF;
dbms_output.put_line('all_cols='||substr(all_cols,1,200));
IF (dblinks.COUNT = 2) THEN
LoopCount := 1;
ELSE
LoopCount := NVL(dblinks.COUNT,1)-1;
END IF;
FOR i IN 1..LoopCount LOOP
dbms_output.put_line('Processing '||schemas(tabidx)||'.'||objects(tabidx)||'@'||dblinks(1)||' AND '||schemas(tabidx)||'.'||objects(tabidx)||'@'||dblinks(i+1));
--no_of_diff := diff_table_internal(schemas(tabidx),objects(tabidx),dblinks(1),schemas(tabidx),objects(tabidx),dblinks(i+1),key_cols,diff_file_handle,fix_file_handle,diff_limit,whereclause,whereclause,whereclause,'*',commit_batch,timestamp_col,FALSE,NULL);
no_of_diff := diff_table_internal(schemas(tabidx),
objects(tabidx),
dblinks(1),
schemas(tabidx),
objects(tabidx),
dblinks(i+1),
key_cols,
diff_file_handle,
fix_file_handle,
diff_limit,
where_clause,
where_clause,
where_clause,
all_cols,
skip_columns,
commit_batch,
timestamp_col,
FALSE,
NULL,
compare_tmstp);
END LOOP;
IF (individual_files) THEN
UTL_FILE.FCLOSE(diff_file_handle);
UTL_FILE.FCLOSE(fix_file_handle);
END IF;
END LOOP;
IF NOT(individual_files) THEN
UTL_FILE.FCLOSE(diff_file_handle);
UTL_FILE.FCLOSE(fix_file_handle);
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
November 22, 2006 - 3:54 pm UTC
amazing that you would actually post hundreds and hundreds of lines of CODE
and believe that I'd just debug it.
amazing.
anyway, problem in first function, you get flamed for this one:
</code>
http://asktom.oracle.com/Misc/ouch-that-hurts.html <code>
you have the equivalent of a when others then null there,
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR in opening file '||dir_name||','||file_name);
DBMS_OUTPUT.PUT_LINE('ERROR in opening file:'||TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE('ERROR in opening file:'||SQLERRM);
END;
WHY WHY WHY WHY WHY do you do that, what is the POINT, the PURPOSE, the "logic".
this (from the very end) is funny too:
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
what is the point of that - just to PURPOSELY HIDE the actual line number the error happened on??
sorry - when I see a major mistake in the very beginning and ending of a source code file, one that is HUNDREDS of lines long.....