PLS-00306: wrong number or types of arguments in call to '||'

[Doron]

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;

[Mark C. Stock]

"Doron" <do*********@msn.com> wrote in message
news:99**************************@posting.google.c om...
| 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;
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

[Wario]

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;

[Doron]

Wario,
thank you very much!

se***********@gmail.com (Wario) wrote in message news:<c7**************************@posting.google. com>...

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;