| Need Help on Sql%Not Found [message #559195] |
Fri, 29 June 2012 13:03  |
 |
Ora_Deeps
Messages: 17
Registered: June 2012
Location: San Francisco
|
Junior Member |
|
|
|
After an Update statement, Sql%NotFound is being validated. Sql%NotFound validates to true even though a record matching the update condition is found. Because of this, the procedure execution Exits. If the Sql%NotFound is commented, then the Record gets updated. Please help me as to any special scenario which is validating Sql%NotFound condition to True.
|
|
|
|
|
|
| Re: Need Help on Sql%Not Found [message #559197 is a reply to message #559195] |
Fri, 29 June 2012 13:10  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
According to the documentation:
Sql%NotFound validates to TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it yields FALSE.
Every time I have encountered problems with Sql%NotFound was based on the error that there the Sql%NotFound was not set by the statement the person writing the code thought it was set by.
|
|
|
|
|
|
|
|
|
|
| Re: Need Help on Sql%Not Found [message #559204 is a reply to message #559202] |
Fri, 29 June 2012 14:35 |
|
Ora_Deeps
Messages: 17
Registered: June 2012
Location: San Francisco
|
Junior Member |
|
|
Since the application is EBS, Order Line and Order Header tables are considered as Standard.
Below is the query :
Update View (Select * from OE_ORDER_LINES_ALL
WHERE NVL (
ORG_ID,
NVL (
TO_NUMBER (
DECODE (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 1),
' ', NULL,
SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10))),
-99)) =
NVL (
TO_NUMBER (
DECODE (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 1),
' ', NULL,
SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10))),
-99)) with Line_ID = xxx and Header_Id = yyy;
Problem here is that after the Update, when i do the SQLRowCOunt, it is giving the value as Zero. In this query condition, ORG_ID is already set and USERENV ('CLIENT_INFO') is an Application variable. It gives the same value as ORG_ID during the program execution. So why is it that the record update is not shown in the SQL%NotCount validation. Also if i remove the SQL%NotCount validation, then no error is reported and the data is updated.
I am curious to know here whether there are any scenarios where SQL%NotCount might act in a different way. Because the above code is from Oracle Standard Package APPS.OE_LINE_UTIL.UPDATE_ROW procedure and this code was fine till recently.
|
|
|
|
| Re: Need Help on Sql%Not Found [message #559207 is a reply to message #559204] |
Fri, 29 June 2012 14:56 |
John Watson
Messages: 9000
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This is an example of why you should never use your own SQL to update EBS tables. The docs are very strong on this. If you want to update the OE tables, you should do it through the supplied APIs, such as the OE_ORDER_PUB package. If you write your own code, there is no way of telling if what you are doing is destroying the integrity of the system. Your client will not thank you for making their system unsupported by Oracle. Furthermore, your code could be broken by the next patch you apply.
Use the APIs instead.
|
|
|
|
| Re: Need Help on Sql%Not Found [message #559209 is a reply to message #559207] |
Fri, 29 June 2012 15:17 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
|
Also, that is not really a valid SQL update statement. There is no "SET" in it for example. So it must be some EBS specific syntax, which might not even touch Sql%NotFound at all.
|
|
|
|
| Re: Need Help on Sql%Not Found [message #559213 is a reply to message #559207] |
Fri, 29 June 2012 16:24 |
|
Ora_Deeps
Messages: 17
Registered: June 2012
Location: San Francisco
|
Junior Member |
|
|
I am sorry if my words have mis-guided the team. I only mentioned that it is Oracle Standard package but never mentioned that it is being modified. With my analysis i observed that this was the area where the error was happening and needs more investigation to find the Exact root cause. If at all, there is need to change also, there are lot of process that needs to be followed before implenting it.
Now coming to the code aspect, below is the Update statement in the OE_LINE_UTIL.UPDATE_ROW().Also for analysis purpose, i have added the code to find the row_count.
UPDATE OE_ORDER_LINES
SET ACCOUNTING_RULE_ID = p_line_rec.accounting_rule_id
, ACCOUNTING_RULE_DURATION = p_line_rec.accounting_rule_duration
, CALCULATE_PRICE_FLAG = p_line_rec.calculate_price_flag
, ACTUAL_ARRIVAL_DATE = p_line_rec.actual_arrival_date
, ACTUAL_SHIPMENT_DATE = p_line_rec.actual_shipment_date
-- all the columns (> 200) are updated in the Order Line table
WHERE LINE_ID = p_line_rec.line_id
AND HEADER_ID = p_line_rec.header_id ;
row_count := sql%rowcount;
IF SQL%NOTFOUND THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
So after the UPDATE statement, if the SQL%NOTFOUND validation is commented, then the Update happens correctly.
Also i request to send across the links that Explain the EBS Packages in detail.
|
|
|
|
| Re: Need Help on Sql%Not Found [message #559239 is a reply to message #559213] |
Sat, 30 June 2012 02:13 |
John Watson
Messages: 9000
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This is my last attempt to disuade you from taking your EBS out of support, and possibly damaging it. If you want to update tables, use the published open APIs. Do not write your own SQL, do not invoke any other procedures. But it is your decision: you can lose your job if you want. From the System Adminsitrator's Guide:
Do Not Use Database Tools to Modify Oracle E-Business Suite Data
Oracle STRONGLY RECOMMENDS that you never use SQL*Plus, Oracle Data
Browser, database triggers, or any other tool to modify Oracle E-Business Suite data
unless otherwise instructed.
Oracle provides powerful tools you can use to create, store, change, retrieve, and
maintain information in an Oracle database. But if you use Oracle tools such as
SQL*Plus to modify Oracle E-Business Suite data, you risk destroying the integrity of
your data and you lose the ability to audit changes to your data.
Because Oracle E-Business Suite tables are interrelated, any change you make using an
Oracle E-Business Suite form can update many tables at once. But when you modify
Oracle E-Business Suite data using anything other than Oracle E-Business Suite, you
xvii
may change a row in one table without making corresponding changes in related tables.
If your tables get out of synchronization with each other, you risk retrieving erroneous
information and you risk unpredictable results throughout Oracle E-Business Suite.
When you use Oracle E-Business Suite to modify your data, Oracle E-Business Suite
automatically checks that your changes are valid. Oracle E-Business Suite also keeps
track of who changes information. If you enter information into database tables using
database tools, you may store invalid information. You also lose the ability to track who
has changed your information because SQL*Plus and other database tools do not keep a
record of changes.
|
|
|
|
| Re: Need Help on Sql%Not Found [message #560441 is a reply to message #559239] |
Thu, 12 July 2012 14:14 |
|
Ora_Deeps
Messages: 17
Registered: June 2012
Location: San Francisco
|
Junior Member |
|
|
|
Thanks all for the suggestions. I was able to resolve the problem. Header Id was not refreshed in one of the Custom Packages due to which the error was happening. Now with the Header_Id being refreshed, the functionality is working fine.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
