Home » SQL & PL/SQL » SQL & PL/SQL » ORA-6550, 904 when I create a function
icon9.gif  ORA-6550, 904 when I create a function [message #219769] Thu, 15 February 2007 22:21 Go to next message
vickiwutw
Messages: 2
Registered: February 2007
Location: Taiwan
Junior Member

when I create a function as below to retrieve user name from another oracle database through a public DB link, i was gave an error PL/SQL ORA 904 "NAME" invalid column name. PLEASE HELP..

I am in Oracle 10.1 and TEST.WORKER@TB is in Oracle 8.1.6,
(TB is a public db link, test is a schema and worker is a table;
TEST.WORKER already grant select privilege to me)

create or replace function USRID2NAME(IP_ID IN CHAR) return CHAR
is
return_val varchar2(2000);
begin
return_val := 'N';
begin
if IP_ID is not NULL then
select UNAME into return_val from IUSER where IFSUSERID=IP_ID;
end if;
exception
when NO_DATA_FOUND then
NULL;
end;
begin
if (return_val='N') and (IP_ID is not NULL) then
select NAME into return_val from TEST.WORKER@TB where EMPNO=IP_ID;
end if;
exception
when NO_DATA_FOUND then
NULL;
end;
return(trim(return_val));
end;
Re: ORA-6550, 904 when I create a function [message #219772 is a reply to message #219769] Thu, 15 February 2007 22:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
#1 "NAME" is a reserved word & should NOT be used.

Post the DESC of TEST.WORKER@TB; which may need to be done directly from @TB.
Re: ORA-6550, 904 when I create a function [message #219783 is a reply to message #219769] Fri, 16 February 2007 01:10 Go to previous messageGo to next message
vickiwutw
Messages: 2
Registered: February 2007
Location: Taiwan
Junior Member

SQL> desc test.WORKER@TB
Name Null? Type
------------------------------- -------- ----
EMPNO NOT NULL CHAR(5)
DEPTNO CHAR(3)
TITLENO NUMBER
ID CHAR(10)
NAME VARCHAR2(20)
Re: ORA-6550, 904 when I create a function [message #219785 is a reply to message #219769] Fri, 16 February 2007 01:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
#1 "NAME" is a reserved word & should NOT be used.
So what happens if a non-reserved word is used?
Re: ORA-6550, 904 when I create a function [message #220014 is a reply to message #219769] Sat, 17 February 2007 11:28 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
vickiwutw wrote on Fri, 16 February 2007 04:21
TEST.WORKER already grant select privilege to me

Who is "me"? It needs to be granted to the user account at the other end of the DB link.
Previous Topic: don't get any rows when given 'like' condition
Next Topic: NLS_LANG PROBLEM
Goto Forum:
  


Current Time: Fri Dec 09 13:26:18 CST 2016

Total time taken to generate the page: 0.17826 seconds