Re: dynamic sql help

hap_at_mikomi.org (Adam Hapworth) wrote in message news:<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
> 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;
>
> begin
> 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';
>
> 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;
>
> 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

The only thing missing in the above code is a call to dbms_sql.fetch_rows to fetch the row from the cursor. Thus the code would look like:

DECLARE
type tab_table_names is table of varchar2(100) index by binary_integer;
table_names tab_table_names;
total_rows number;
cursor_id integer;
select_statment varchar(200);
count_value number;
foo integer;
BEGIN

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';

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);

foo := dbms_sql.FETCH_ROWS(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;

END; Regards,
Ram. Received on Thu Nov 08 2001 - 11:53:51 CET