No question at this time
DBA Top 10
1 A. Kavsek 8700
2 B. Vroman 5900
3 M. Cadot 5400
4 P. Wisse 4800
5 T. Boles 3700
6 J. Schnackenberg 2100
7 . Lauri 1500
8 R. Pattyn 700
9 T. P 600
9 J. PĂ©ran 600
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48409
Total active users1518
Act. users last 24h5
Act. users last hour0
Registered user hits last week284
Registered user hits last month997
Go up

Updates failed but select works fine - For query having packaged function in where clause
Next thread: datapump and dbfs
Prev thread: Oracle 12.2.0.1.0 performance issues

Message Score Author Date
There is a query that I can't post but can explain...... Nishant Kariya Mar 14, 2019, 12:57
Too few information to answer. Create a test ca...... Michel Cadot Mar 14, 2019, 17:01
Could it be there's a rollback command in the code...... Philip Wisse Mar 15, 2019, 15:15

Follow up by mail Click here


Subject: Updates failed but select works fine - For query having packaged function in where clause
Author: Nishant Kariya, India
Date: Mar 14, 2019, 12:57, 161 days ago
Oracle info: 11g
Message: There is a query that I can't post but can explain the scenario.

Query was trying to update a table specifying function(packaged) in where clause. Select of this query was working absolutely fine but updates gave me 0 rows updated.

More interesting thing is function always returned 2 values - 'Y' or 'N'. When I ran update saying where <function() = 'N'>, it worked fine but didn't updated any records for 'Y'.

What I did was, I placed PRAGMA AUTONOMOUS TRANSACTION in function and the query worked fine for both Y and N.

Although I found the solution but I am not getting the exact reason behind the behavior. Can anyone explain this?
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Updates failed but select works fine - For query having packaged function in where clause
Author: Michel Cadot, France
Date: Mar 14, 2019, 17:01, 161 days ago
Message:
Too few information to answer.
Create a test case that reproduces what you see if you don't work to post the actual code.

However, one thought, or rather one guess, if you have a different behaviour using PRAGMA AUTONOMOUS TRANSACTION this tends to make me think that your function modifies something in the database which, unless it is logging (in this case why not using the standard audit?), is a very bad thing to do in a SQL statement.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Updates failed but select works fine - For query having packaged function in where clause
Author: Philip Wisse, Netherlands
Date: Mar 15, 2019, 15:15, 160 days ago
Message: Could it be there's a rollback command in the code?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here