Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: invalid identifier, when calling a function inside the own schema
ORA-00904: invalid identifier, when calling a function inside the own schema [message #276575] Thu, 25 October 2007 09:30 Go to next message
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.
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 Go to previous messageGo to next message
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';
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 Go to previous messageGo to next message
psignore
Messages: 6
Registered: October 2007
Junior Member


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


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 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure the function is not part of package named ADMINIMP?

Regards
Michel
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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...
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 Go to previous messageGo to next message
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)
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 Go to previous messageGo to next message
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...


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 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Issue the following command on both servers and paste the results.


select user from dual;
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel, please..

Either adjust the guidelines and add the 80 character/preview/whatever, or stop mentioning it.
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 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It is in the guidelines. Perhaps Michel should have read them? Very Happy

MHE
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
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 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Need Help for query checking douplicate values
Next Topic: Create dynamic table_Name
Goto Forum:
  


Current Time: Fri Dec 02 12:42:36 CST 2016

Total time taken to generate the page: 0.05784 seconds