Oracle Rank Function Stub

  • Alex

    Alex - 2012-10-30

    Hello All.

    I have an issue which has been driving me to distraction all day.

    I'm working on a project which uses an Oracle XE database back end, and am in the process of porting the existing unit tests (which currently hit a live database) to use HSQL in memory.

    There are a number of stored procedures which are working fine, but several queries use the Rank() function.
    I cannot make changes to the SQL (the use of rank is pretty essential to the result pagination), and my efforts to mock up the Rank function have led to nothing but headaches.

    All I require in this instance is a mock function. It doesn't need to perform any real data processing, just needs to be there to stop the tests erroring when I compile the project.

    I've mocked up the function in Java, but the tests are still failing with the error "user lacks privilege or object not found: RANK".

    My question is : In a maven project, where abouts would the Java segment of a stored procedure need to live such that an alias can be created for it?

    Alternatively, is it possible to create an empty stored procedure using just SQL? I have tried with an ATOMIC block that contained no content. but that just gave a bad SQL grammar exception.

    Any help that anybody might be able to give would be hugely appreciated!

    Regards, Alex

  • Fred Toussi

    Fred Toussi - 2012-10-30

    The RANK() function is used with an OVER(… ORDER BY … ) clause. therefore even if you create a stub for RANK(), you may not be able to use it in a SELECT, but give it a try, as currently OVER() is ignored.

    Anyway, you can use SQL to create the function that simply returns a value.

    create function rank() returns int 
    begin atomic
    return 1;
  • Alex

    Alex - 2012-10-31

    Hi Fred.

    That's superb! Thank you for a prompt reply!
    It's exactly what I was looking for, but you are indeed right, it is failing in a select as our PL/SQL as run in Oracle does require the OVER clause.

    I'm going to just bite the bullet I think and implement both functions properly. It will save time in the future.

    Many thanks again!!

  • Fred Toussi

    Fred Toussi - 2012-10-31

    It's not going to be easy to implement this function, as its hidden parameters are the in the result set.

    I had a quick check and noticed it is possible to mock this function. See org.hsqldb.ParserDQL  and look for Tokens.ROW_NUMBER. We translate ROW_NUMVER OVER() into ROWNUM. You can add the case for RANK to ROW_NUMBER to return the same value.

  • Alex

    Alex - 2012-10-31

    Hi Fred.

    I've looked into mocking the function as you described, and while it is potentially viable as a long term solution for testing, I'm running up fast on a deadline and I think I shall have to go down another route.

    One possibility I've considered is trying to alias Rank() to ROW_NUMBER, and simply have the result from that returned.

    Do you know if this is possible using just SQL code, or would this need to be implemented in Java as a static method?

  • Fred Toussi

    Fred Toussi - 2012-10-31

    You can't do that because RANK() OVER () is one expression. It's very easy to do what I suggested. Just add a switch case for Tokens.RANK next to Tokens.ROW_NUMBER and compile the jar.

  • Anonymous - 2013-07-27

    Hi Guys,

    It's been a while on this, but i'm really interested in it. However, i'm still not in the right phase of putting all systems together.
    The HSQL dialect in hibernate does not work using the rank, and from my understanding this thread is discussing a similar topic.

    The question is: how to add the suggested functions and procedures to HSQL, i couldn't find anywhere, is there a file to write those procedures within? is there a java class to handle this by accepting a string that includes the procedure? what is the drill here


Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.

No, thanks