Home » SQL & PL/SQL » SQL & PL/SQL » Regarding declarations in SP's
Regarding declarations in SP's [message #198240] Mon, 16 October 2006 04:44 Go to next message
Messages: 1
Registered: October 2006
Location: bangalore
Junior Member
Can a stored procedure be made public to all schemas.if yes,how?
Re: Regarding declarations in SP's [message #198248 is a reply to message #198240] Mon, 16 October 2006 05:00 Go to previous messageGo to next message
Messages: 21148
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps by

GRANT EXECUTE ON stored_procedure TO PUBLIC;

[EDIT] Doing do, any other user will have to precede stored procedure name with owner's name (for example, SELECT owner.stored_procedure FROM dual; if it was a function). To avoid it, create a public synonym for this stored procedure.

[Updated on: Mon, 16 October 2006 05:02]

Report message to a moderator

Re: Regarding declarations in SP's [message #198462 is a reply to message #198240] Tue, 17 October 2006 04:43 Go to previous message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
There are two cases for this
1.In one server there are different schemas.
2.In separate servers the different schemas are there.

Suppose in schema A we have procedure proc1.
We want to access proc1 in schema B
so that we can do by writing "schema name.procedure name"
i.e A.proc1.

Case 2.
There are two servers S1 and S2.
In S1 schema is A and procedure is Proc1.
In S2 schema is B and procedure is Proc2.
So we create a DBLINK from S1 to S2.
DBLINK contains the tns entry of S2.
Then we can access.
Previous Topic: escape clause
Next Topic: avg( ) over (partition by )
Goto Forum:

Current Time: Mon Aug 21 05:25:04 CDT 2017

Total time taken to generate the page: 0.25123 seconds