OraFAQ Forum: SQL & PL/SQL » ORA-00904: invalid identifier, when calling a function inside the own schema

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: invalid identifier, when calling a function inside the own schema

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00904: invalid identifier, when calling a function inside the own schema [message #276575]

Thu, 25 October 2007 09:30

psignore
Messages: 6
Registered: October 2007

Junior Member

I have a user with the following privileges

GRANT CONNECT TO ADMINIMP WITH ADMIN OPTION;
GRANT RESOURCE TO ADMINIMP WITH ADMIN OPTION;
GRANT EXP_FULL_DATABASE TO ADMINIMP;
GRANT IMP_FULL_DATABASE TO ADMINIMP;
ALTER USER ADMINIMP DEFAULT ROLE EXP_FULL_DATABASE, IMP_FULL_DATABASE;
-- 19 System Privileges for ADMINIMP
GRANT ANALYZE ANY TO ADMINIMP;
GRANT CREATE SESSION TO ADMINIMP WITH ADMIN OPTION;
GRANT DROP ANY INDEX TO ADMINIMP;
GRANT DROP ANY TABLE TO ADMINIMP;
GRANT ALTER ANY INDEX TO ADMINIMP;
GRANT ALTER ANY TABLE TO ADMINIMP;
GRANT CREATE ANY VIEW TO ADMINIMP;
GRANT CREATE ANY INDEX TO ADMINIMP;
GRANT CREATE ANY TABLE TO ADMINIMP;
GRANT EXECUTE ANY TYPE TO ADMINIMP;
GRANT INSERT ANY TABLE TO ADMINIMP;
GRANT SELECT ANY TABLE TO ADMINIMP WITH ADMIN OPTION;
GRANT UPDATE ANY TABLE TO ADMINIMP;
GRANT SELECT ANY SEQUENCE TO ADMINIMP WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO ADMINIMP WITH ADMIN OPTION;
GRANT EXECUTE ANY PROCEDURE TO ADMINIMP;
GRANT SELECT ANY DICTIONARY TO ADMINIMP WITH ADMIN OPTION;
GRANT CREATE PUBLIC DATABASE LINK TO ADMINIMP WITH ADMIN OPTION;
GRANT CREATE ANY MATERIALIZED VIEW TO ADMINIMP;

which has a function:
Function ADMINIMP.Get_Step_Id(p_object_owner IN varchar2,
p_object_name IN varchar2,
p_step_number IN number) return number;

When I try to use the function logged as user ADMINIMP:

SELECT Get_Step_Id(T2.OBJECT_OWNER,T2.OBJECT_NAME,T2.STEP_LOADING)
FROM ADMINIMP.MD_ALL_PROCESSES T2

it gives me the error

ORA-00904: "GET_STEP_ID": invalid identifier

when I add the function owner, it works:

SELECT ADMINIMP.Get_Step_Id(T2.OBJECT_OWNER,T2.OBJECT_NAME,T2.STEP_LOADING)
FROM ADMINIMP.MD_ALL_PROCESSES T2

--OK--

Anyone has an idea why?

The funny thing is that this happens only on our test DB. It works both ways on our development DB.

I tried to look for any privs differences or oracle parameters, but I couldn?t find the answer.

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #276582 is a reply to message #276575]

Thu, 25 October 2007 09:48

Cthulhu
Messages: 381
Registered: September 2006
Location: UK

Senior Member

See if there are any other objects called GET_STEP_ID:

SELECT * from all_Objects where object_name = 'GET_STEP_ID';

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #276593 is a reply to message #276582]

Thu, 25 October 2007 10:14

psignore
Messages: 6
Registered: October 2007

Junior Member

Thanks for the reply but no, there are no other objects with the same name.

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #276598 is a reply to message #276575]

Thu, 25 October 2007 10:31

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Are you sure the function is not part of package named ADMINIMP?

Regards
Michel

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #276741 is a reply to message #276598]

Fri, 26 October 2007 03:53

psignore
Messages: 6
Registered: October 2007

Junior Member

Yes, I'm sure. ADMINIMP is the schema name.

The function is not inside any package. As I said on our development server the call GET_STEP_ID without ADMINIMP. works. The structure is pratically the same.

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #276749 is a reply to message #276741]

Fri, 26 October 2007 04:28

Frank
Messages: 7901
Registered: March 2000

Senior Member

No synonyms either?
I know ctulhu asked you to query all_objects, but did you do that or do you 'just know' there are no other objects.
[Edit: Sorry. didn't read your question very well. Did not notice it was in the same schema]

[Updated on: Fri, 26 October 2007 04:29]

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #276809 is a reply to message #276749]

Fri, 26 October 2007 06:25

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

A long shot perhaps, but could you provide is with the information you retrieve from USER_PROCEDURES on both servers?
Maybe there is a difference somewhere down the line but hard to notice.

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #276862 is a reply to message #276809]

Fri, 26 October 2007 09:03

psignore
Messages: 6
Registered: October 2007

Junior Member

It's not that either.

Doing a select from USER_PROCEDURES gives me only 1 record with that name and it is the one from my user.

It looks like it is a strange behaviour from oracle. No explanation...

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #276874 is a reply to message #276862]

Fri, 26 October 2007 10:12

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

psignore wrote on Fri, 26 October 2007 16:03

It's not that either.

Doing a select from USER_PROCEDURES gives me only 1 record with that name and it is the one from my user.

It looks like it is a strange behaviour from oracle. No explanation...

My mistake, should've been more clear on this one: the output from USER_PROCEDURES for this specific function on both your databases (development & test)

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #276883 is a reply to message #276575]

Fri, 26 October 2007 10:49

psignore
Messages: 6
Registered: October 2007

Junior Member

The answer for this query
SELECT *
FROM USER_PROCEDURES
WHERE OBJECT_NAME = 'GET_STEP_ID'

In development:
GET_STEP_ID,,NO,NO,,,NO,NO,NO,DEFINER

In test:
GET_STEP_ID,,NO,NO,,,NO,NO,NO,DEFINER

The results are identical...

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #276913 is a reply to message #276575]

Fri, 26 October 2007 15:14

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Issue the following command on both servers and paste the results.

select user from dual;

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #277182 is a reply to message #276575]

Mon, 29 October 2007 03:17

psignore
Messages: 6
Registered: October 2007

Junior Member

Development DB

SQL> select user from dual;

USER
------------------------------
ADMINIMP

SQL> SELECT Get_Step_Id('ADMINIMP','IMP_ETL','100000')
2 FROM DUAL
3 /

GET_STEP_ID('ADMINIMP','IMP_ETL','100000')
--------------------------------------------
2

Test DB

SQL> select user from dual;

USER
------------------------------
ADMINIMP

SQL> SELECT Get_Step_Id('ADMINIMP','IMP_ETL','100000')
2 FROM DUAL
3 /
SELECT Get_Step_Id('ADMINIMP','IMP_ETL','100000')
*
ERRORE alla riga 1:
ORA-00904: "GET_STEP_ID": identificativo non valido

SQL> SELECT * FROM USER_PROCEDURES
2 WHERE OBJECT_NAME = 'GET_STEP_ID'
3 /

OBJECT_NAME PROCEDURE_NAME AGG PIP
------------------------------ ------------------------------ --- ---
IMPLTYPEOWNER IMPLTYPENAME PAR INT DET
------------------------------ ------------------------------ --- --- ---
AUTHID
------------
GET_STEP_ID NO NO
NO NO NO
DEFINER

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #277186 is a reply to message #277182]

Mon, 29 October 2007 03:22

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #277239 is a reply to message #277186]

Mon, 29 October 2007 05:22

Frank
Messages: 7901
Registered: March 2000

Senior Member

Michel, please..

Either adjust the guidelines and add the 80 character/preview/whatever, or stop mentioning it.

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #277241 is a reply to message #277239]

Mon, 29 October 2007 05:26

Maaher
Messages: 7065
Registered: December 2001

Senior Member

It is in the guidelines. Perhaps Michel should have read them? Very Happy

Very Happy

MHE

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #277245 is a reply to message #277239]

Mon, 29 October 2007 05:30

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

80 characters limit is already in the guidelines (twice), I added the Preview button...
But who read them even when you give the link, so I prefer precise it in the post? I promise I'll stop as soon as it will be no longer useful.

Regards
Michel

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #277283 is a reply to message #277245]

Mon, 29 October 2007 07:00

Frank
Messages: 7901
Registered: March 2000

Senior Member

Quote:

I promise I'll stop as soon as it will be no longer useful.

Then you may as well quit it now. Count the times you ran your mantra and compare that to the number of times people actually gave you their Oracle version on 4 decimals.
The idea is that we have a general list of posting guidelines, not that each member has his own (sub)set.

Report message to a moderator

Re: ORA-00904: invalid identifier, when calling a function inside the own schema [message #277288 is a reply to message #277283]

Mon, 29 October 2007 07:37

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Given that many don't read the general guidelines, I carry on my crusade for better posting.
I think clearly posting a question is much more important in professional life than getting an immediate answer to the current problem.
(but of course it is much more boring and I greatly prefer to search, find and answer a question than continually posting the same stuff.)

Regards
Michel

Report message to a moderator

Previous Topic:	Need Help for query checking douplicate values
Next Topic:	Create dynamic table_Name

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 26 08:30:38 CDT 2025