No question at this time
DBA Top 10
1 M. Cadot 20600
2 A. Kavsek 14800
3 M. Hidayathullah ... 10500
4 B. Vroman 8100
5 P. Wisse 5300
6 T. Boles 4200
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1400
10 K. Pagadala 1100
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48057
Total active users1738
Act. users last 24h12
Act. users last hour0
Registered user hits last week391
Registered user hits last month1277
Go up

Problem with SQL sorting in stored procedure
Next thread: Needed help to understand behaviour of sessions parameter
Prev thread: Oracle license for Standard Edition 2 on 2 physical quad-core CPUs with Hyper-V

Message Score Author Date
Hi, We have a stored procedure to get the field...... ankababu sayampu Jun 19, 2017, 08:18
Dear, Please check following link and mos notes...... Mirza Hidayathullah Baig Jun 19, 2017, 10:43
Hi Mirza, Thanks for your quick response. I ...... ankababu sayampu Jun 19, 2017, 13:10
Looking at the values that you specified in your "...... Tim Boles Jun 20, 2017, 15:54
Hi Tim, Thank you very much for your time. Good...... ankababu sayampu Jun 20, 2017, 19:10

Follow up by mail Click here


Subject: Problem with SQL sorting in stored procedure
Author: ankababu sayampu, India
Date: Jun 19, 2017, 08:18, 122 days ago
Os info: Windows
Oracle info: 11.2.0.3
Message: Hi,

We have a stored procedure to get the fields of XML file from the table and later it processes the results.

But the query is not giving the same result every time even we pass the same XML file with same data. below is the SQL query.

INSERT INTO XML_TEMPORARY
(ID, XMLINFO)
VALUES
('1AC11582902JLKK83','<Requests>
<Request RequestID="FCD758FEF5DF4084A6D3903697FCBC18" RequestTypeID="1" OrderID="80E35FCADA3E1D4DBC6CCDA3CD5FB12A" ProductID="1B75910EF73F4BD396737281C46CBFFC" Barcode="MS68013003" OrderCreated="2017-06-19"/>
<Request RequestID="5DE0227CF7E447BBA235C98B180CEB82" RequestTypeID="1" OrderID="9388FEE86AFC444EA8331F36C5A53E4D" ProductID="582A4688D682480FB754CCCEF4377275" Barcode="MS68014969" OrderCreated="2017-06-19"/>
<Request RequestID="7AED50045EA9452881826B1B151072F0" RequestTypeID="2" OrderID="9388FEE86AFC444EA8331F36C5A53E4D" ProductID="582A4688D682480FB754CCCEF4377275" Barcode="MS68014969" OrderCreated="2017-06-19"/>
<Request RequestID="E6ABCC56892B4921A9C6367A7DB5F5EE" RequestTypeID="2" OrderID="80E35FCADA3E1D4DBC6CCDA3CD5FB12A" ProductID="1B75910EF73F4BD396737281C46CBFFC" Barcode="MS68013003" OrderCreated="2017-06-19"/>
</Requests>')
RETURNING ID INTO XMLID


SELECT t.RequestID
,t.RequestTypeID
,t.OrderID
,t.ProductID
,t.Barcode
,t.OrderCreated,
,t.NextRequestID
,l.MaxLimit
FROM E$TEMP_XML X
,XMLTABLE('//Requests/Request' PASSING X.XMLData
COLUMNS
RequestID RAW(16) PATH '/Request/@RequestID',
RequestTypeID NUMBER PATH '/Request/@RequestTypeID',
OrderID RAW(16) PATH '/Request/@OrderID',
ProductID RAW(16) PATH '/Request/@ProductID',
Barcode VARCHAR2(100) PATH '/Request/@Barcode',
OrderCreated VARCHAR2(50) PATH '/Request/@OrderCreated',
NextRequestID NUMBER PATH '/Request/@NextRequestID') t, ORDERLIMIT l
WHERE t.RequestTypeID = l.RequestTypeID
AND X.ID = XMLID
ORDER BY OrderID ASC, NextRequestID DESC)

Expected result:
Query has to fetch OrderID along with other fields mentioned in SQL

Problem:
Sometimes qury is showing orderID as null with same data. When I use nvl(NextRequestID,0), it is working fine and showing ordered with proper value.

Kindly help me how this sorting impacts the orderID column
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Problem with SQL sorting in stored procedure
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Jun 19, 2017, 10:43, 122 days ago
Message: Dear,

Please check following link and mos notes, whether it helps you, not sure.

https://www.codeproject.com/Articles/590341/Stored-Procedure-with-Sorting-Paging-and-Filtering

How to Tune PL/SQL Applications and Identify Hot Spots using DBMS_HPROF (Doc ID 763944.1)

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

Subject: Re: Problem with SQL sorting in stored procedure
Author: ankababu sayampu, India
Date: Jun 19, 2017, 13:10, 122 days ago
Message: Hi Mirza,

Thanks for your quick response.

I could not find reason for missing column(OrderID) due to sorting operation.

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

Subject: Re: Problem with SQL sorting in stored procedure
Author: Tim Boles, United States
Date: Jun 20, 2017, 15:54, 121 days ago
Message: Looking at the values that you specified in your "INSERT" statement there is no NextRequestID in your data. So I am going to assume therefore that it is coming back as NULL. You are also doing a sort using that field (which is NULL). Perhaps it has something to do with the way Oracle sorts NULL values.

http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

•If the null ordering is not specified then the handling of the null values is: ◦NULLS LAST if the sort is ASC
◦NULLS FIRST if the sort is DESC

However, I am not sure how that factors into the OrderID column not showing data.

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

Subject: Re: Problem with SQL sorting in stored procedure
Author: ankababu sayampu, India
Date: Jun 20, 2017, 19:10, 120 days ago
Message: Hi Tim,

Thank you very much for your time. Good information about sorting.

It is still a mystery to me how OrderID column is giving null values due to the sort order.

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