Home » SQL & PL/SQL » SQL & PL/SQL » if data is not there in table why the procedure getting stuck? (11g)
icon5.gif  if data is not there in table why the procedure getting stuck? [message #569422] Thu, 25 October 2012 22:58 Go to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi all,

hope doing well.

sir, i am using one stored procedure

in which one query is there that is

    SELECT to_char(Inpunch,'HH24:mi:ss') into v_Inpunch1 FROM ProcessingPunches 
ORDER BY Inpunch ASC  WHERE ROWNUM <= 1;


in this i am fetching top 1 value and storing Inpunch value in v_Inpunch1 variable
is this query is correct?
because i am getting error.

thanks

[Updated on: Thu, 25 October 2012 23:43] by Moderator

Report message to a moderator

Re: if data is not there in table why the procedure getting stuck? [message #569423 is a reply to message #569422] Thu, 25 October 2012 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 22902
Registered: January 2009
Senior Member
vikashkrburnwal@gmail.com wrote on Thu, 25 October 2012 20:58
Hi all,

hope doing well.

sir, i am using one stored procedure

in which one query is there that is

    SELECT to_char(Inpunch,'HH24:mi:ss') into v_Inpunch1 FROM ProcessingPunches 
ORDER BY Inpunch ASC  WHERE ROWNUM <= 1;


in this i am fetching top 1 value and storing Inpunch value in v_Inpunch1 variable
is this query is correct?
because i am getting error.

thanks


How should we know if query is correct?
What is existing data?
What is expected results?
What results are produced/

ERROR?
What Error?
I don't see any error.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Thu, 25 October 2012 23:43] by Moderator

Report message to a moderator

Re: if data is not there in table why the procedure getting stuck? [message #569424 is a reply to message #569422] Thu, 25 October 2012 23:42 Go to previous message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is this query is correct?


Logically not.
The ORDER BY clause is made AFTER rownum and so you currently order one row.
See SQL FAQ and go to TOP N section.

Regards
Michel
Previous Topic: Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns)
Next Topic: how to get unique value in column ?
Goto Forum:
  


Current Time: Wed Oct 22 18:53:33 CDT 2014

Total time taken to generate the page: 0.09312 seconds