Home » SQL & PL/SQL » SQL & PL/SQL » No data showing up in synonyms (Oracle 10g AIX 5.3)
No data showing up in synonyms [message #414586] Wed, 22 July 2009 17:20 Go to next message
PLSQLPROB
Messages: 11
Registered: May 2003
Junior Member
I need to view the code from packages information in Schema B from Schema A. I created a view for the packages in owner Schema A and granted select privileges to all the views in Schema A to Schema B. I created synonyms in Schema B and was able to see the code from user schema A as well as connecting as SYSTEM. I connected to Schema B and I am able to view the code from some of the synonyms that were created in Schema B, but I am unable to see the code in other synonyms in schema B as it returns with no rows selected even though I see there is code in the synonym when checked as SYSTEM user. What could be the reason? Are there any privileges that are missing. How can I view some packages and not the others? It is really confusing. Any help is appreciated.

[MERGED by LF; specified product and OS name]

[Updated on: Thu, 23 July 2009 00:40] by Moderator

Report message to a moderator

Re: No data showing up in synonyms [message #414589 is a reply to message #414586] Wed, 22 July 2009 17:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>It is really confusing.
I agree.

A whole lot of movement & even some progress.
Since we can't see what you actually did & the state of your DB, your mystery remains unsolved.

It might have been easier for you to write a simple procedure in SchemaA which can list the content any package owned by schemaA.
Then GRANT EXECUTE ON SEE_MY_PACKAGE TO schemaB.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: No data showing up in synonyms [message #414591 is a reply to message #414589] Wed, 22 July 2009 17:45 Go to previous messageGo to next message
PLSQLPROB
Messages: 11
Registered: May 2003
Junior Member
The reason why I am creating a synonyms is to have a read only code of these packages. I should not have the execute privilege on these packages. Since there are several packages, I wrote a script that generates the views for these packages and then the same is being created as synonyms in other schema. But in the other schema, I am able to view only a few synonyms and not all of them. Thanks for your quick response.
Re: No data showing up in synonyms [message #414592 is a reply to message #414586] Wed, 22 July 2009 17:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The reason why I am creating a synonyms is to have a read only code of these packages.
Not unreasonable.

>I should not have the execute privilege on these packages.
Agreed, but I did not suggest otherwise.

>I wrote a script that generates the views for these packages and then the same is being created as synonyms in other schema.

>But in the other schema, I am able to view only a few synonyms and not all of them.
Either error of commission or error of omission, but we have NOTHING to determine what might be amiss.
Something is different between those that work & those that don't,
but you have posted nothing to allow us to know which is which.

Please read carefully & consider this suggestion below:

It might have been easier for you to write a simple procedure (SEE_MY_PACKAGE) owned by SchemaA which can LIST the content any package owned by schemaA.
Then GRANT EXECUTE ON SEE_MY_PACKAGE TO schemaB.
Re: No data showing up in synonyms [message #414593 is a reply to message #414592] Wed, 22 July 2009 18:00 Go to previous messageGo to next message
PLSQLPROB
Messages: 11
Registered: May 2003
Junior Member
Can you provide an example on how to capture the package. Thanks
Re: No data showing up in synonyms [message #414598 is a reply to message #414586] Wed, 22 July 2009 19:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> @sample
SQL> set echo on term on serveroutput on
SQL> set lines 511
SQL> CREATE OR REPLACE PROCEDURE SEE_MY_PACKAGE
  2  AS
  3  LINE_SOURCE VARCHAR2(32000);
  4  
  5   cursor c_src is SELECT TEXT
  6  		      FROM USER_SOURCE
  7  		      WHERE NAME = 'SEE_MY_PACKAGE'
  8  		      ORDER BY LINE;
  9  BEGIN
 10  	DBMS_OUTPUT.ENABLE(100000);
 11  	FOR SRC_REC IN C_SRC LOOP
 12  	   DBMS_OUTPUT.PUT(SRC_REC.TEXT);
 13  	END LOOP;
 14  	LINE_SOURCE := CHR(10);
 15  	DBMS_OUTPUT.PUT_LINE(LINE_SOURCE);
 16  END SEE_MY_PACKAGE;
 17  /

Procedure created.

SQL> exec see_my_package;
PROCEDURE SEE_MY_PACKAGE
AS
LINE_SOURCE VARCHAR2(32000);

 cursor c_src is SELECT TEXT
		 FROM USER_SOURCE
		 WHERE NAME = 'SEE_MY_PACKAGE'
		 ORDER BY LINE;
BEGIN
   DBMS_OUTPUT.ENABLE(100000);
   FOR SRC_REC IN C_SRC LOOP
      DBMS_OUTPUT.PUT(SRC_REC.TEXT);
   END LOOP;
   LINE_SOURCE := CHR(10);
   DBMS_OUTPUT.PUT_LINE(LINE_SOURCE);
END SEE_MY_PACKAGE;


PL/SQL procedure successfully completed.


I leave it to you to generalize to handle any package.
Re: No data visible in synonyms [message #414607 is a reply to message #414586] Wed, 22 July 2009 22:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you posted EXACTLY what you can see and what yuo can't maybe we can help.

I bet you can see procédure and package specification code but not package body. If this is the issue, it is the expected behaviour.

Regards
Michel
Re: No data visible in synonyms [message #414779 is a reply to message #414607] Thu, 23 July 2009 07:56 Go to previous messageGo to next message
PLSQLPROB
Messages: 11
Registered: May 2003
Junior Member
I would like to see all packages, package body and procedures from one of my schema into another schema. Is it possible to have all the source from one schema into another as read only? If it is possible, then how can we achieve this task. If not, is there any other way to get the above in some other way?

[Updated on: Thu, 23 July 2009 08:01]

Report message to a moderator

Re: No data visible in synonyms [message #414784 is a reply to message #414779] Thu, 23 July 2009 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What has BlackSwan posted you?
The answer is NO unless you use BlackSwan's procedure.

Regards
Michel
Re: No data showing up in synonyms [message #415483 is a reply to message #414586] Tue, 28 July 2009 01:30 Go to previous message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
I guess "SELECT ANY DICTIONARY" privilege might help you but it would mean the schema having this privilege would be able to see package/function/procedure definitions for all the schemas.
Previous Topic: Query Help
Next Topic: need Query Help
Goto Forum:
  


Current Time: Fri Apr 26 01:14:04 CDT 2024