hi,
getting the following error when running the script below.
it seems to me that the cursor is retrieving more then one value each loop.
any idea how to fix this?

error:

ERROR at line 10:
ORA-06550: line 10, column 19:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored


script:

declare
cursor u_tab is select table_name from user_tables;
u_tab_rec user_tables.table_name%type;

begin
execute immediate 'create global temporary table temp_tab1 ( col_count number)';

for u_tab_rec in u_tab
loop
execute immediate 'insert count(*) into temp_tab1 from '||u_tab;

end loop;
end;

"Doron" <doron_almog (AT) msn (DOT) com> wrote


U_TAB is a record type (implicitly declared in your for loop), and
concatenation (||) only works with character data types (or expressions that
can be implicitly converted to a character data type)

what you need to do in this case is specify which element of the record you
want to concatenate -- even though there is only one element

that being said, your use of a global temporary table is inappropriate and
shows a misunderstanding of what a temporary table is in oracle -- do a
little more reading up on that and you'll see that the table is permanent,
and only the contents are temporary -- so it makes no sense to create one in
a PL/SQL script

++ mcs

Try This:

declare
cursor u_tab is select table_name from user_tables;

begin
execute immediate 'create global temporary table temp_tab1 (
col_count number)';

for u_tab_rec in u_tab
loop
execute immediate 'insert into temp_tab1 select count(*) from
' || u_tab_rec.table_name;

end loop;
end;

Wario,
thank you very much!

sergeant.rock (AT) gmail (DOT) com (Wario) wrote in message news:<c75b43bb.0409010603.40a975ff (AT) posting (DOT) google.com>...