By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,376 Members | 1,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,376 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a

"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

P: n/a
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

P: n/a
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.