Oracle Stored Procedure and schema user

[Kikol]

Hi!

I'm using stored procedure to my report. When I look on Show SQL I found something like this:

BEGIN
"MY_USER"."MY_PROCEDURE"();
END ;

Everything wolud by OK except this username before my procedure's name. When I logon to my database as differnt user, raport still call MY_USER.MY_PROCEDURE() - how disable this schema user before procedure name ? I want obtain something like this:
BEGIN
"MY_PROCEDURE"();
END ;


I'm using CR XI and Oracle 8i

 

[IanWaterman]

You should be able to remove schema name within Datasource.

Database - Set Datasource Location
click on + by databse and then procedure
then + by properties
Highlight Overridden Qualified Table Name:
press F2
Type in procedure name
so now looks like

Overridden Qualified Table Name: MY_PROCEDURE

Click outside of this field, provided you are logged onto server and procedure exists it will accept name.
When you look in SHOW SQL, schema name will not be there.

Ian

 

[Turkbear]

Hi,
A point of clarification, please:
Do you have multiple MY_PROCEDURE()s with the same name but in different schemas? - If not, then why do you care about the fully qualified name showing in the Sql? If so, then restrict access in Oracle to just the one that matches the login ( assuming they do different things)..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Kikol]

Thanks for help Ian, but this hint don't resolve my problem, because my procedure is a part of package, and when I type in Overridden Qualified Table Name: MY_PACKAGE.MY_PROCEDURE CR try execute query like this:
SELECT owner, 'SYNONYM'
FROM SYS.all_synonyms
WHERE owner = 'MY_PACKAGE' AND synonym_name = 'MY_PROCEDURE'

Of course my database don't have user named MY_PACKAGE. What i'm doing wrong?

 

[Kikol]

I have only one proceudre named MY_PROCEDURE(). Problem is because I have special database user to design reports. This user never would be on production database. And now if CR in SQL type my user before proceudre's name i can't use my report on this database. Production database have another special user - owner all my objects. Just these objects are shared to another database users trough synonyms. Lastly: this special users must be diffrent.
I hope i was cleary explain my problem.

 

[IanWaterman]

I think Turkbear is on the right track. This sounds more like an Oracle rights issue.

Sorry can't offer anything further.

Ian

 

[Kikol]

Thanks for all. Your guidelines was very helpful. I put procedure outside package and everything is ok.