470,573 Members | 1,582 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,573 developers. It's quick & easy.

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

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;
Jul 19 '05 #1
3 58992

"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
Jul 19 '05 #2
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;
Jul 19 '05 #3
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;

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.