Why this script is NOT extracting output ?

[Striker99]

All,

I got this script off the net and trying to make it work, without much luck. I am not getting any error message, but its not writing anything to the spool file ?

any idea why ?

cheers,
striker99

--------------------------
set verify off;
set termout off;
set feedback off;
set pagesize 0;
set termout on
select 'Creating index build script...' from dual;
set termout off;
create table i_temp
(lineno NUMBER, id_owner VARCHAR2(30), id_name VARCHAR2(30),
text VARCHAR2(800))
/
DECLARE
CURSOR ind_cursor IS select owner,
index_name,
table_owner,
table_name,
uniqueness,
tablespace_name,
ini_trans,
max_trans,
initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
pct_free
from dba_indexes
where owner != 'SYS'
order by index_name;
CURSOR col_cursor (i_own VARCHAR2, c_ind VARCHAR2, c_tab VARCHAR2) IS
select column_name
from dba_ind_columns
where index_owner = i_own
and index_name = c_ind
and table_name = c_tab
order by column_position;

lv_index_owner dba_indexes.owner%TYPE;
lv_index_name dba_indexes.index_name%TYPE;
lv_table_owner dba_indexes.table_owner%TYPE;
lv_table_name dba_indexes.table_name%TYPE;
lv_uniqueness dba_indexes.uniqueness%TYPE;
lv_tablespace_name dba_indexes.tablespace_name%TYPE;
lv_ini_trans dba_indexes.ini_trans%TYPE;
lv_max_trans dba_indexes.max_trans%TYPE;
lv_initial_extent dba_indexes.initial_extent%TYPE;
lv_next_extent dba_indexes.next_extent%TYPE;
lv_min_extents dba_indexes.min_extents%TYPE;
lv_max_extents dba_indexes.max_extents%TYPE;
lv_pct_increase dba_indexes.pct_increase%TYPE;
lv_pct_free dba_indexes.pct_free%TYPE;
lv_column_name dba_ind_columns.column_name%TYPE;
lv_first_rec BOOLEAN;
lv_string VARCHAR2(800);
lv_lineno number := 0;

procedure write_out(p_line INTEGER, p_owner varchar2, p_name VARCHAR2,
p_string VARCHAR2) is
begin
insert into i_temp (lineno,id_owner, id_name,text)
values (p_line,p_owner,p_name,p_string);
end;
BEGIN
OPEN ind_cursor;
LOOP
FETCH ind_cursor INTO lv_index_owner,
lv_index_name,
lv_table_owner,
lv_table_name,
lv_uniqueness,
lv_tablespace_name,
lv_ini_trans,
lv_max_trans,
lv_initial_extent,
lv_next_extent,
lv_min_extents,
lv_max_extents,
lv_pct_increase,
lv_pct_free;
EXIT WHEN ind_cursor%NOTFOUND;
lv_lineno := 1;
lv_first_rec := TRUE;
if (lv_uniqueness = 'UNIQUE') then
lv_string:= 'CREATE UNIQUE INDEX ' || lower(lv_index_owner) || '.' ||
lower(lv_index_name);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
else
lv_string:= 'CREATE INDEX ' || lower(lv_index_owner) || '.' ||
lower(lv_index_name);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
end if;
OPEN col_cursor(lv_index_owner,lv_index_name,lv_table_name);
LOOP
FETCH col_cursor INTO lv_column_name;
EXIT WHEN col_cursor%NOTFOUND;
if (lv_first_rec) then
lv_string := ' ON '|| lower(lv_table_owner) || '.' ||
lower(lv_table_name)||' (';
lv_first_rec := FALSE;
else
lv_string := lv_string || ',';
end if;
lv_string := lv_string || lower(lv_column_name);
END LOOP;
CLOSE col_cursor;
lv_string := lv_string || ')';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := null;
lv_string := 'PCTFREE ' || to_char(lv_pct_free);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'INITRANS ' || to_char(lv_ini_trans) ||
' MAXTRANS ' || to_char(lv_max_trans);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'TABLESPACE ' || lv_tablespace_name || ' STORAGE (';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'INITIAL ' || to_char(lv_initial_extent) ||
' NEXT ' || to_char(lv_next_extent);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'MINEXTENTS ' || to_char(lv_min_extents) ||
' MAXEXTENTS ' || to_char(lv_max_extents) ||
' PCTINCREASE ' || to_char(lv_pct_increase) || ')';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := '/';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_lineno := lv_lineno + 1;
lv_string:=' ';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
END LOOP;
CLOSE ind_cursor;
END;
/
spool tfscsndx.sql
set heading off
set recsep off
col text format a80 word_wrap
select text
from I_temp
order by id_owner, id_name, lineno;
spool off
drop table i_temp;
-------------------------------------------

 

[lokesh123]

I do not see a commit anywhere in the script. Add a commit at the end of the script before the final END; of the PL/SQL Block. Guess thats the only reason.

 

[JamesGordon]

If you remove the 'drop table' statement, have their been any rows added to the table?
Switch feedback on and what output do you get to the screen.
It is always better to output to the screen whilst debugging and then switch it off. No doubt some error is occuring but with feedback off you cannot tell.