PL/SQL question

[moepower]

I know the following sample code does not work because Oracle will not let me use the cursor values as part of the statemet. But is there a way I can do something similar to that? I use the cursor to return tables that meet a certain criterias, then use the loop to get a count of the records in each table. Thanks,

ex.

cursor get_table is
select table_name
from user_tables
where table_name like 'xyz%';

begin
...
for ex in get_table is
select count(*)
from ex.table_name;
end loop;
end;

 

[Guest_imported]

You can use the built-in PL/SQL package DBMS_SQL that allows SQL strings to be parsed. Here is an example of a function that accepts a table name and return the number of records in it:

FUNCTION select_count(table_name IN VARCHAR2)
RETURN NUMBER
IS
/* Function to return the count of records in a table. Passed argument must
be a valid table name e.g. FLATFILE. */
c1 INTEGER;
rows_processed INTEGER;
col_count NUMBER;
BEGIN
c1 := dbms_sql.open_cursor;
/* Parse the assembled SQL string e.g. SELECT count(*) FROM flatfile */
dbms_sql.parse(c1, 'SELECT count(*) FROM ' || table_name, dbms_sql.native);
/* Define the result set produced by the query */
dbms_sql.define_column(c1,1,col_count);
rows_processed := dbms_sql.execute(c1);
/* Loop through each retrieved row of the query. Only one row will be output
as this is a select count. Value of row is assigned to col_count variable */
LOOP
IF dbms_sql.fetch_rows(c1) > 0
THEN
dbms_sql.column_value(c1,1,col_count);
ELSE
exit;
END IF;
END LOOP;
dbms_sql.close_cursor(c1);

/* Return col_count varaible e.g. the count of records in passed table name */
RETURN(col_count);

EXCEPTION WHEN OTHERS THEN
/* On error close the cursor if open */
dbms_output.put_line(sqlerrm);
IF dbms_sql.is_open(c1)
THEN
dbms_sql.close_cursor(c1);
END IF;
END;

END;

Hope this helps

 

[sem]

Since Oracle 8.1.6 you can open cursor for a string expression using implicit dynamic sql

declare
type tc is ref cursor;
c tc;
cnt integer;
begin
..................
open c for 'select count(*) from '||ex.table_name;
fetch c into cnt;
close c;
..................
end;