CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Francis Drumm
Francis Drumm

Reputation: 87

How to view/execute granted stored procedure in Oracle SQL developer

The user that I use to access a particular DB has been granted EXECUTE privilege for a stored procedure managed by another user. Is there any way to view or run that stored procedure in Oracle SQL developer like a regular stored procedure? At the moment I don't see it in the Procedure tab in connections to right click and run. I am executing the stored procedure in java but I need to be able to test it in SQL developer.

Upvotes: 2

Views: 6534

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

The procedure won't appear in the tree list immediately under your connection, as those are only ever the ones that the user you are connected as owns themselves. (If you right-click and choose 'Filter', there is an option to 'override schema filter', but even with a filter and that flag set you don't see other users' procedures.)

But right at the bottom of the list of objects types under your connection is an 'Other Users' entry. If you expand that, find the owner of the procedure and expand that, and then expand the list of procedures under that user - you'll see all of their procedures that you have permission to view/execute.

You can then run it from there the same way you would run your own procedures.

You can also right-click on your connection and choose 'Schema Browser'; then in the tab that appears you can more easily change user, and change the object type to 'Procedures'. You might find that faster than navigating the tree.

@thatjeffsmith has a post about navigating via the tree or the dropdowns (of course - should have looked their first...)

Upvotes: 3

OracleDev
OracleDev

Reputation: 535

If you have a procedure "test_procedure" in your shema you would execute it like this:

begin
  test_procedure;
end;

If you have it in another schema, lets say "parent" then you would call it like this:

begin
  parent.test_procedure;
end;

Upvotes: 0

Related Questions