Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' (ORACLE 11.2.0.3.0)
ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629151] Wed, 03 December 2014 22:34 Go to next message
sajeeshts
Messages: 6
Registered: December 2014
Location: Noida
Junior Member
Hi,

I am using the below query on a table through DBLINK, which is running into an error

Select * from Terra_Master@PEDRA Where OnCheckflag = 4 And Mod(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),5) = 3;

The error which I am getting is
ERROR at line 1:ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER'

Please advice, whether the ROWID_BLOCK_NUMBER function cannot be used in query accessing the table through DBLINK ?

Rgds
Sajeesh Satyan

[Updated on: Wed, 03 December 2014 22:37]

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629153 is a reply to message #629151] Wed, 03 December 2014 23:16 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.

What kind of table is Terra_Master? Is it an IOT? Please post the DDL.

SQL> ed
Wrote file afiedt.buf

  1  create table t(a number,
  2  constraint a_pk primary key (a))
  3* organization index
SQL> /

Table created.

SQL> select dbms_rowid.rowid_block_number(rowid) from t;
select dbms_rowid.rowid_block_number(rowid) from t
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to
'ROWID_BLOCK_NUMBER'


SQL>

[Updated on: Wed, 03 December 2014 23:19]

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629154 is a reply to message #629153] Wed, 03 December 2014 23:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
select count(*) from user_objects where Mod(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),5) = 3
*
ERROR at line 1:
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY,
etc.

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629159 is a reply to message #629151] Thu, 04 December 2014 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I doubt you can use DBMS_ROWID on a remote rowid.
What are the version of both databases (4 decimals like 11.2.0.4)?

[Updated on: Thu, 04 December 2014 00:51]

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629160 is a reply to message #629154] Thu, 04 December 2014 00:51 Go to previous messageGo to next message
sajeeshts
Messages: 6
Registered: December 2014
Location: Noida
Junior Member
Hi,

This is not an IOT. Below is the DDL

-- Create table
create table TERRA_MASTER
(
ti_custom_decision_key101 NUMBER(7) default 0,
ti_custom_decision_key102 NUMBER(7) default 999,
ti_aligned_score1 NUMBER(4) default 0,
ti_aligned_score2 NUMBER(4) default 0,
ti_amt_due1 NUMBER(11,2) default 0,
ti_amt_due2 NUMBER(11,2) default 0,
ti_amt_pur1 NUMBER(11,2) default 0,
ti_amt_pur2 NUMBER(11,2) default 0,
ti_delq_scen_id NUMBER(3) default 0,
ti_spid NUMBER(2) default 0,
caccserno NUMBER(10),
pr10_scrd_type NUMBER(1) default 0,
ti_acct_num VARCHAR2(19) default ' ',
grpname VARCHAR2(25),
OnCheckflag NUMBER(1),
billingflag NUMBER(1),
postingflag NUMBER(1),
newflag NUMBER(1),
checkflag NUMBER(1)
)
tablespace ONLINE00
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index I_ACCTNUM on TERRA_MASTER (TI_ACCT_NUM)
tablespace ONLINE00
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index I_OnCheckflag on TERRA_MASTER (OnCheckflag)
tablespace ONLINE00
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create unique index I_CACCSERNO on TERRA_MASTER (CACCSERNO)
tablespace ONLINE00
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index I_CHECKFLAG on TERRA_MASTER (CHECKFLAG)
tablespace ONLINE00
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create unique index I_GRPNAME on TERRA_MASTER (GRPNAME)
tablespace ONLINE00
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index I_NEWFLAG on TERRA_MASTER (NEWFLAG)
tablespace ONLINE00
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index I_POSTINGFLAG on TERRA_MASTER (POSTINGFLAG)
tablespace ONLINE00
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index I_SCEN_ID on TERRA_MASTER (TI_DELQ_SCEN_ID)
tablespace ONLINE00
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index I_SPID on TERRA_MASTER (TI_SPID)
tablespace ONLINE00
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629161 is a reply to message #629159] Thu, 04 December 2014 01:16 Go to previous messageGo to next message
sajeeshts
Messages: 6
Registered: December 2014
Location: Noida
Junior Member
The exact version of ORACLE is 11.2.0.3.0

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629162 is a reply to message #629161] Thu, 04 December 2014 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For both databases, local and remote? This was my question.

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629163 is a reply to message #629162] Thu, 04 December 2014 01:24 Go to previous messageGo to next message
sajeeshts
Messages: 6
Registered: December 2014
Location: Noida
Junior Member
Yes Michel... both Db are on the same version..

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629168 is a reply to message #629159] Thu, 04 December 2014 01:51 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Michel Cadot wrote on Thu, 04 December 2014 06:51

I doubt you can use DBMS_ROWID on a remote rowid.
What are the version of both databases (4 decimals like 11.2.0.4)?

Looks to me as though you are right:
c:\users\john>
c:\users\john>sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 4 07:47:44 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Dec 04 2014 07:46:32 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


Session altered.

orclz> Select * from dept Where Mod(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),5) = 3;

no rows selected

orclz> create database link l1 using 'orclz';

Database link created.

orclz> Select * from dept@l1 Where Mod(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),5) = 3;
Select * from dept@l1 Where Mod(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),5) = 3
                                *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER'


orclz>

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629171 is a reply to message #629168] Thu, 04 December 2014 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks I currently have no database to test.
Can you check using (I think it should work): 
Select * from dept@l1 Where Mod(DBMS_ROWID.ROWID_BLOCK_NUMBER@l1(ROWID),5) = 3;

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629172 is a reply to message #629171] Thu, 04 December 2014 02:33 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Sussed.
orclz>
orclz> Select * from dept@l1 Where Mod(DBMS_ROWID.ROWID_BLOCK_NUMBER@l1(ROWID),5) = 3;

no rows selected

orclz>

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629183 is a reply to message #629171] Thu, 04 December 2014 04:05 Go to previous messageGo to next message
sajeeshts
Messages: 6
Registered: December 2014
Location: Noida
Junior Member
Hi Michel

It worked !!! Thanks !!!

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629220 is a reply to message #629168] Thu, 04 December 2014 09:27 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
John Watson wrote on Thu, 04 December 2014 02:51

I doubt you can use DBMS_ROWID on a remote rowid.


On remote DB:

SQL> select dbms_rowid.rowid_block_number(rowid) from t_event_notification;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                7347
                                7347
                                7347

SQL>


Now on local DB:

SQL> select dbms_rowid.rowid_block_number(rowid) from t_event_notification@apsbox;
select dbms_rowid.rowid_block_number(rowid) from t_event_notification@apsbox
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to
'ROWID_BLOCK_NUMBER'


SQL> select dbms_rowid.rowid_block_number@apsbox(rowid) from t_event_notification@apsbox;

DBMS_ROWID.ROWID_BLOCK_NUMBER@APSBOX(ROWID)
-------------------------------------------
                                       7347
                                       7347
                                       7347

SQL>


As you can see, we need to call package using same link. Then package will be called by same remote session.

SY.

Report message to a moderator

Re: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER' [message #629222 is a reply to message #629220] Thu, 04 December 2014 09:39 Go to previous message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, this is what I suggested and John has tested. Smile

Report message to a moderator

Previous Topic: Updating Specified Fields
Next Topic: number to char convertion
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 13:20:40 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.