Help - Search - Members - Calendar
Full Version: How to use synonym in stored procedure
Oracle DBA Forums > Oracle > Oracle Forum
katikineni
Hi All,

Could anyone let me know how to use a synonym in a stored procedure.

I have created a public synonym for a remote table on a different schema.

Im now trying to use the synonym to load that data into a temporary table in my schema using a stored procedure and im getting an error.

Please let me know how to use a synonym in a stored procedure.

Thanks
Vishal
Asad
QUOTE (katikineni @ Aug 28 2009, 09:00 PM) *
Hi All,

Could anyone let me know how to use a synonym in a stored procedure.

I have created a public synonym for a remote table on a different schema.

Im now trying to use the synonym to load that data into a temporary table in my schema using a stored procedure and im getting an error.

Please let me know how to use a synonym in a stored procedure.

Thanks
Vishal


do Public have execute privilege on public synoym ?
katikineni
QUOTE (Asad @ Aug 28 2009, 05:22 PM) *
do Public have execute privilege on public synoym ?



how do i check whether it has execute privilages.

when i use the select * from <synonym name> i'm getting the record set.
dave
QUOTE (katikineni @ Aug 28 2009, 10:00 PM) *
Hi All,

Could anyone let me know how to use a synonym in a stored procedure.

I have created a public synonym for a remote table on a different schema.

Im now trying to use the synonym to load that data into a temporary table in my schema using a stored procedure and im getting an error.

Please let me know how to use a synonym in a stored procedure.

Thanks
Vishal


why dont you put the error? or an example? or anything?
burleson
Hi,

>> how to use a synonym in a stored procedure.

The synonym applies to SQL, and the synonym is stored in the data dictionary:

CODE
create public synonym customer for myschema.customer;


Omce defined, you can reference the synonym anywhere, in PL/SQL or external programs.

See here for details:

http://www.dba-oracle.com/concepts/synonyms.htm

Remember, a synonym just just a device to allow multiple names for the same object, nothing more.

>> and im getting an error.

What error message?
Raghu_07

Execute the below from SYS or Table_owner

GRANT ALL ON <TABLE> to <SYNONYM_OWNER> ;
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.