Update ignored. 1

[tekpr00]

Hello All, I am trying to update a table however eacch time I ran the update via:
CALL vs_flag_images.call_update_flag('SBTH','1065','fmed_imaged');

However the colunm is not updated though my user has update privilege on the table.

select grantee, table_name, PRIVILEGE from user_tab_privs where grantee like '%

http://WWW_IMAGING%';

http://WWW_IMAGING

BIRTH_REGS UPDATE

Please help

 

[carp]

First, without seeing the code itself, there is not much anybody can do to help you. With that in mind, I have two questions:
1. Is there a commit anywhere in your code?
2. Are there any rows in the table? If not, then there is nothing to update!

Please give us something of substance to work with and we can probably help.

 

[tekpr00]

Yes, I issued commit after the call. the tables have rows in it but the colunm that is being updated currently has NULL value

 

[Thargy]

tek,

to take this further, please post the create table statement for the relevant table, insert statements with sample data, and the precise code/script you are using to perform the update.

If you would like an example of this, look at what I posted in the thread thread759-1577721, which demonstrates what I mean. Given that information, Carp will probably need at least 10 seconds to sort out your problem.

Regards

T

 

[Turkbear]

Hi,
What user created
vs_flag_images.call_update_flag ?

Invoker vs. Definer Rights. Oracle makes a distinction between "invokers" (a user executing a stored procedure) and "definers" (the user under which the CREATE PROCEDURE statement was issued).

By default, stored procedures are executed with the rights of the definer, even when the invoker is a different user. That means all access to tables, for example, within the procedure will be controller by the rights of the definer so an invoker only needs rights to execute the procedure, not rights to the tables it uses.

This model can be changed with the keywords AUTHID CURRENT_USER as part of the procedure definition. With this directive set, rights required when executing a stored procedure are resolved at runtime against the current user executing the procedure

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

 

[tekpr00]

The user that created the vs_flag_images.call_update_flag (definer) is different from the user that call (invoker) that calls vs_flag_images.call_update_flag.

However there is a synonyms created on the table to be updated with grant all privilege granted by the definer on the vs_flag_images to the invoker.

the good thing is that the invoker is successfully updating other tables in the database by calling vs_flag_images.call_update_flag. It is only this particular table that it cannot update. Futhermore it does not generate error:

CALL vs_flag_images.call_update_flag('SBTH','8876','fmed_imaged')

This retunrs:
CALL vs_flag_images.call_update_flag('SBTH','8876','fmed_imaged') succeeded.
commit;

However, select FMED_IMAGED from sbirth_regs where sr_id = 8876

retunrs NULL,Thouht it suppose to turn it to Y:
FMED_IMAGED
-----------

1 rows selected

 

[carp]

Well, then I guess I'm back to my original post - we need to see the code.

 

[Turkbear]

Hi,
in spite of this:

grant all privilege granted by the definer on the vs_flag_images to the invoker.

can you confirm that the definer has update rights to that table?


Remember the invoker's privileges do not matter unless coding in the SP uses that AUTHID CURRENT_USER key.





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

 

[Thargy]

carp,

I presume the ol' crystal ball is being serviced then?

Regards

T

 

[carp]

T -
Yep - it's in for its 100,000 hour overhaul. Should be back out of the shop by next week. Until then, I actually have to see code in order to troubleshoot it. Terribly inconvenient!

 

[tekpr00]

Please bear with me as I cannot copy the original codes here. There are several internal information as this process is dealing with Vital Stats.

 

[carp]

tekpr00-
Could you post a censored version here? Just replace anything sensitive with words like "hoosgow", "hobbit", or "doughnut" to protect the innocent. Usually it is just a filter or structure issue that we can point to and say "check this" or "look at that". We would really like to help you with this!

 

[tekpr00]

Thanks all, I have been able to resolve this issue.

It is the date field that was, by eror, changed to number.

Again, thanks everyone.