I have come upon a moment of reckoning. The time has come for me to learn everything I am supposed to have known 30 years ago. I am serious about this and explain why if I get an impression it would be of interest.
Today this reckoning is taking the form of a ridiculously long write up, to show what I know, and what I don't know, to ask for yet another of the tremendous gifts people have a habit of giving to people when it comes to relational databases, especially Oracle. Short, I was doing that here about a year ago, then was assigned to a new kind of non-normal database technology. I am back from that.
The problem I have today is I cannot figure out how to find the cause of a problem I encountered applying a simple change to a stored procedure. The procedure is ~1500 lines. The change is to one line. It was this (names changed to protect data):
left join deprecated_tablename tn on oooow.one_of_our_org_words = tn.deprecated_col_1
Now it is this:
left join replacement_tablename tn on oooow.one_of_our_org_words = tn.replacement_name_for_col_1
When I try updating the procedure without that one line change, I get a happy
Procedure X_OUR_PROCEDURE_NAME compiled
When I run the sql outside the procedure with that change, I get
~120k records, identical in every way to the records produced using the deprecated table I need to replace with the new one.
However, when I try building a new procedure with that change, I get the
Procedure X_OUR_PROCEDURE_NAME compiled
Error
More error.
table does not exist
now that I have written this out, it seems the cause and result a simple. Ask someone with the permissions why I don't have the permissions and in the meantime, could you please possibly do what the permissions need, plase tell me the specific change needed , and yes I know it is importangt to protect our databases from negative effects and not having the rights to make any damaged caused is a great reason to not even want the permissions, i just hate a deliverable being blocked
And no, I won't bring that up where I work.
One thing I definitely learned here last year is people here really know what you are doing and you really do put excellent thought into every aspect of the work we do.
There are few groups I respect as much as this one. I know there are many experienced, innovative, massively intelligent hard working people in the exact field relavant to most of what I do that has any potential to make a positive impact on this world. I do not take any of your time for granted, knowing I have right to none of it.
The permissions I have are DBA-like in many ways, with some exceptions. Maybe the problem is I need rights i lack to replacement_tablename.
I tried grant select, insert, update, delete on customer to user2
, system said I could not grant privilege to myself
It bothers me a lot I cannot find the exact cause of the problem myself.