Home » SQL & PL/SQL » SQL & PL/SQL » Question about rowid when querying using database link
Question about rowid when querying using database link [message #273250] Tue, 09 October 2007 14:09 Go to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hello,

I bumped into this article in asktom http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:839298816582 and i am trying to get my head around it. I would like to know why i am getting this error (rather i actually came across this problem in asktom and i am trying to understand why it is happening)?


SQL> show user
USER is "RND"

create or replace function test_func(p_rowid in rowid)
return varchar2
is
begin
return rowidtochar(p_rowid);
end;

create table x(a number);

insert into x values(1);

commit;

SQL> select * from x;

         A
----------
         1

SQL> select test_func(rowid) from x;

TEST_FUNC(ROWID)
-----------------------------------------
AAAQ2WAAEAAADNeAAA

sql> create database link test connect to rnd identified by rnd using 'orcl';

SQL> select test_func(rowid) from x@test
select test_func(rowid) from x@test
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'TEST_FUNC'

SQL> select rowid from x@test;

ROWID
------------------
AAAQ2WAAEAAADNeAAA 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Operation System : WINDOWS XP


Regards

Raj

Re: Question about rowid when querying using database link [message #273257 is a reply to message #273250] Tue, 09 October 2007 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unsupported feature?
As Tom didn't answer to your question, I suggest you to create a SR to get an answer from development.

regards
Michel
Re: Question about rowid when querying using database link [message #273261 is a reply to message #273257] Tue, 09 October 2007 14:58 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Clarification. I didn't ask that question in asktom but i only asked in this forum. Having said that, it is complaining only when i pass rowid as a parameter. I tried to change the parameter type to number and it is working.

Anyways, if i get any answer will post here.

Regards

Raj
Re: Question about rowid when querying using database link [message #273348 is a reply to message #273261] Wed, 10 October 2007 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, I change my sentence:
As Tom didn't answer to this question, I suggest you to create a SR to get an answer from development.

It seems you can't call a custom local function using a rowid from a remote database.
But you can call a REMOTE function on it:
SQL> select rowid from x@db_link;
ROWID
------------------
AAAL4PAAEAAACgcAAA

1 row selected.

SQL> select test_func(rowid) from x@db_link;
select test_func(rowid) from x@db_link
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'TEST_FUNC'

SQL> select test_func@db_link(rowid) from x@db_link;
TEST_FUNC@DB_LINK(ROWID)
---------------------------------------------------------------
AAAL4PAAEAAACgcAAA

1 row selected.

Regards
Michel
Re: Question about rowid when querying using database link [message #273383 is a reply to message #273348] Wed, 10 October 2007 04:23 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Michael,

Thanks for the reply. As usual you are always very helpful.

Regards

Raj
Re: Question about rowid when querying using database link [message #273475 is a reply to message #273383] Wed, 10 October 2007 10:30 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe as a token of your gratitude, you could spell his name correct Wink

(joking, no offence meant)
Previous Topic: How many column we can create in an sql table
Next Topic: How Do I get 'GMT' string as part of time zone format
Goto Forum:
  


Current Time: Sat Dec 03 14:16:54 CST 2016

Total time taken to generate the page: 0.08243 seconds