Skip to Main Content
  • Questions

  • synonym for function inside a package

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Prince.

Asked: July 22, 2002 - 8:20 pm UTC

Last updated: December 29, 2003 - 1:06 pm UTC

Version: 8.1.7.4

Viewed 10K+ times! This question is

You Asked

Suppose I have defined a synonym for a function inside a package.
Is it possible to access the function thru the synonym?

SQL> create synonym myfun for mypkg.myfun ;

Synonym created.

SQL> select myfun(64) from dual ;
select myfun(64) from dual
*
ERROR at line 1:
ORA-00904: invalid column name

I am wondering is it possible to call the functins directly, without prefixing with the package, like the ones in STANDARD?

and Tom said...

No, synonyms are only for top level schema objects -- such as those found in ALL_OBJECTS

A function in a package is not to be found there.


You would need to create a small standalone function "myfun" that simply calls mypkg.myfun.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I got it now. Thanks Tom.

A reader, July 22, 2002 - 9:06 pm UTC


Why this?

A reader, December 29, 2003 - 12:15 pm UTC 

SQL> create public synonym current_timestamp for sysdate;
create public synonym current_timestamp for sysdate
                                            *
ERROR at line 1:
ORA-00995: missing or invalid synonym identifier

Tom Kyte
December 29, 2003 - 1:06 pm UTC 

sysdate is just an internal thingy, you cannot create an identifier for it, it does not live in the data dictionary.  sysdate is a special word and we recognize you cannot use it for an identifier:

ops$tkyte@ORA9IR2> create table sysdate ( x int);
create table sysdate ( x int)
             *
ERROR at line 1:
ORA-00903: invalid table name


sysdate is just not valid where an identifier is expected.

besides:

ops$tkyte@ORA9IR2> select current_timestamp from dual;
 
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
29-DEC-03 01.06.46.405867 PM -05:00
 


current_timestamp already has meaning in the database.

DBA, January 20, 2007 - 1:46 am UTC

Hi tom,
I have one question which I am not able to resolve.
lets say there are 3 databases.
in database A there is one schema which is having synonym over dblink which is pointing to again synonym in database B.
Database B synonym is pointing over dblink to Database C (schema x) which is again synonym :)
In database C (schema x) the synonym is pointing to a procedure / function / table / other object.

I want to check in database A whether the synonym is valid or not (may be dblink invalid / base object does not exists / grant issues).

I am not able to make it out.
Can you please help me

Thanks a lot.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library