No question at this time
DBA Top 10
1 A. Kavsek 8600
2 M. Cadot 7200
3 B. Vroman 6200
4 T. Boles 4550
5 J. Schnackenberg 4100
6 P. Wisse 3200
7 T. P 1200
8 G. Lambregts 1100
8 . Lauri 1100
10 R. Pattyn 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48373
Total active users1542
Act. users last 24h7
Act. users last hour1
Registered user hits last week192
Registered user hits last month1051
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, 69 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, 69 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, 68 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