No question at this time
DBA Top 10
1 M. Cadot 9900
2 B. Vroman 9200
3 A. Kavsek 7500
4 T. Boles 6250
5 J. Schnackenberg 5100
6 P. Wisse 2700
7 M. Hidayathullah ... 1500
8 T. P 1200
9 G. Lambregts 1100
9 . Lauri 1100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48352
Total active users1553
Act. users last 24h2
Act. users last hour0
Registered user hits last week279
Registered user hits last month926
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, 9 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, 9 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, 8 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