No question at this time
DBA Top 10
1 A. Kavsek 8900
2 B. Vroman 6600
3 M. Cadot 5000
4 T. Boles 4550
5 P. Wisse 4500
6 J. Schnackenberg 3700
7 . Lauri 1300
8 G. Lambregts 800
9 T. P 700
9 R. Pattyn 700
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48392
Total active users1527
Act. users last 24h5
Act. users last hour0
Registered user hits last week277
Registered user hits last month713
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, 124 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, 123 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, 122 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