Oralce ROWNUM Dymistified

(from: http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html)

The following sql doesn’t work as many expected.

select * 
  from t 
 where ROWNUM > 1;

It will return 0 rows, because a ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned.

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
  a.*, ROWNUM rnum 
      from ( your_query_goes_here, 
      with order by ) a 
      where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;
  • FIRST_ROWS(N) tells the optimizer, “Hey, I’m interested in getting the first rows, and I’ll get N of them as fast as possible.”
  • :MAX_ROW_TO_FETCH is set to the last row of the result set to fetch—if you wanted rows 50 to 60 of the result set, you would set this to 60.
  • :MIN_ROW_TO_FETCH is set to the first row of the result set to fetch, so to get rows 50 to 60, you would set this to 50.

Slove the issue of command prompt has been disabled by your administrator

cmd

(from: http://www.tweakandtrick.com/2013/08/enable-command-prompt.html)

Enable Command Prompt using the Group Policy Editor

1) Click on Start. Go to Run. Users running Windows 8, Windows 7 or Vista, go to Search.
2) Type gpedit.msc and press Enter.
3) Navigate to User Configuration/ Administrative Templates / System.
4) In the work area on the right side, double click on “Prevent access to the command prompt“.
5) In the popup dialog, encircle Disabled and click OK.
6) You should be able to immediately use the command prompt. If you still cannot access it, restart your computer.

Home editions of Windows do not contain the Group Policy Editor.

Enable Command Prompt from Registry in Windows 8, 7, XP or Vista

Registry Editor is an in-built tool in Windows which is used to view and edit the Windows registry. You can use it to easily enable the command prompt by simply modifying a registry key. To do so, follow these steps.

1) Users running Windows 8, Windows 7 and Windows Vista, go to Start>Search. Users of Windows XP, got to Start>Run.
2) Type regedit and press Enter.
3) Registry Editor will start. If it is disabled, you will need to enable Registry Editor first.
4) Navigate to HKEY_CURRENT_USER\Software\Policies\Microsoft\Windows\System. If the Windows key is not there, you will need to create a new key called Windows. Also, if the System key is not there, you will need to create it.
5) In the work area on right side, double click on DisableCMD and set its value to 0. If you cannot locate DisableCMD, you will need to create a new DWORD value called DisableCMD and set its value to 0.
6) Close the Registry Editor.

Enable CMD
The Command Prompt in Windows 8

The command prompt should immediately be accessible. If it isn’t, restart your computer.

Enable Command Prompt by simply running a command

If you are unfamiliar with directly editing the Registry, you can use a simple command to quickly enable the command prompt. Just follow these steps:-
1) Click on Start and go to Run. Users of Windows 8.1, Windows 8, Windows 7 and Windows Vista, go to Search.
2) Copy and paste the following command and execute it.

REG add HKCU\Software\Policies\Microsoft\Windows\System /v DisableCMD /t REG_DWORD /d 0 /f

3) This should enable the command prompt. If it is still not enabled, restart your computer.

The methods mentioned in this article work on Windows 8.1, Windows 8, Windows 7, Windows Vista and Windows XP.

Oracle SQL Pagination

Select records from Mth row to Nth row:

SELECT
id
,uen
,entity_legal_name
,trade_system
,trade_date
,mapping_booking_location
,booking_region
,product_type
,trade_type
,expected_count_of_trades
,actual_count_of_trades
,value
,currency
,in_previous_weeks_report
FROM
(
SELECT
rownum row_number
,a.*
FROM
dnb_dly_sys_profile a
WHERE
trade_date>=’2014-09-08′
AND trade_date<‘2014-09-15’
AND entity_legal_name LIKE ‘RAYMOND JAMES%’
AND product_type=’OUTRIGHT TRADE’
AND rownum <= N
)a
WHERE
row_number>=M

Insert “YES” and “NO” to boolean filed type in DB (char(1), etc)

(from: http://stackoverflow.com/questions/1154833/configure-hibernate-using-jpa-to-store-y-n-for-type-boolean-instead-of-0-1)

Hibernate has a built-in “yes_no” type that would do what you want. It maps to a CHAR(1) column in the database.

Basic mapping: <property name="some_flag" type="yes_no"/>

Annotation mapping (Hibernate extensions):

@Type(type="yes_no")
public boolean getFlag();

here is also a “true_false” type that will store either “T” or “F”.

A pure JPA solution:

As of 2013/2014 it is the best answer without using any Hibernate specific annotations, but please note this solution is JPA 2.1, and was not available when the question was first asked:

@Entity
public class Person {    

    @Convert(converter=BooleanToStringConverter.class)
    private Boolean isAlive;    
    ...
}

And then:

@Converter
public class BooleanToStringConverter implements AttributeConverter<Boolean, String> {

    @Override
    public String convertToDatabaseColumn(Boolean value) {        
        return (value != null && value) ? "Y" : "N";            
        }    

    @Override
    public Boolean convertToEntityAttribute(String value) {
        return "Y".equals(value);
        }
    }