dynamic sql help
Date: 7 Nov 2001 10:58:17 -0800
Message-ID: <a6cb04db.0111071058.2a187ed6_at_posting.google.com>
I am trying my hand at dynamic sql and I have run into a snag.
here is what I am trying to do. I want to take a list of tables and
get a count from each one. here is my code at the moment:
declare
begin
for i in 1..table_names.count loop
cursor_id := dbms_sql.open_cursor;
select_statment := 'select count(*) from '||table_names(i);
dbms_sql.parse(cursor_id, select_statment,dbms_sql.V7);
dbms_sql.define_column(cursor_id, 1, total_rows);
foo := dbms_sql.execute(cursor_id);
dbms_sql.column_value(cursor_id, 1, total_rows);
dbms_output.put_line(table_names(i)||'|'||total_rows||'|');
dbms_sql.close_cursor(cursor_id);
end loop;
type tab_table_names is
table of varchar2(100)
index by binary_integer;
table_names tab_table_names;
total_rows number;
cursor_id number;
select_statment varchar(200);
count_value number;
foo integer;
table_names(1) := 'FORM_SEC';
table_names(2) := 'LAW_CODES';
table_names(3) := 'LOCAL_OFFICE_CODES';
table_names(4) := 'FRM_TABLE';
table_names(5) := 'REASON_CODES';
table_names(6) := 'STATES';
table_names(7) := 'CODE_TYPES';
table_names(8) := 'CODES';
END; all I want for output is the table name and the count(*) of that table at the moment I am getting "table_name||". I can't figure out haw to get the value of the count into a variable.
Adam Received on Wed Nov 07 2001 - 19:58:17 CET