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.tab le_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]

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


"Doron" <doron_almog@ms n.comwrote in message
news:995517bc.0 408310919.4b9f0 783@posting.goo gle.com...
| 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.tab le_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 misunderstandin g 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]

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

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]

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

Wario,
thank you very much!

sergeant.rock@g mail.com (Wario) wrote in message news:<c75b43bb. 0409010603.40a9 75ff@posting.go ogle.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;