[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3292) Bug Oracle9iDialect Pagination

[Mizael Montenegro (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_30211 ] 

Mizael Montenegro commented on HHH-3292:
----------------------------------------

The string sql generated:

select
        * 
    from
        ( select
            row_.*,
            rownum rownum_ 
        from
            ( select
                ruabean0_.cod_rua as cod1_9_,
                ruabean0_.cod_bairro as cod2_9_,
                ruabean0_.cod_cidade as cod3_9_,
                ruabean0_.cod_uf as cod4_9_,
                ruabean0_.cod_tipo_rua as cod5_9_,
                ruabean0_.cod_usuario_criacao as cod6_9_,
                ruabean0_.nom_rua as nom7_9_,
                ruabean0_.dth_criacao as dth8_9_,
                ruabean0_.dth_alteracao as dth9_9_,
                ruabean0_.cod_usuario_alteracao as cod10_9_,
                ruabean0_.ind_bloqueado as ind11_9_ 
            from
                admin.tb_rua ruabean0_,
                admin.tb_tipo_rua tiporuabea1_ 
            where
                ruabean0_.cod_tipo_rua=tiporuabea1_.cod_tipo_rua 
            order by
                tiporuabea1_.nom_tipo_rua ) row_ 
        where
            rownum <= ?
        ) 
    where
        rownum_ > ?

it is incorrect. Using:

select * from ( SELECT x.*, rownum as r FROM ( select
                ruabean0_.cod_rua as cod1_9_,
                ruabean0_.cod_bairro as cod2_9_,
                ruabean0_.cod_cidade as cod3_9_,
                ruabean0_.cod_uf as cod4_9_,
                ruabean0_.cod_tipo_rua as cod5_9_,
                ruabean0_.cod_usuario_criacao as cod6_9_,
                ruabean0_.nom_rua as nom7_9_,
                ruabean0_.dth_criacao as dth8_9_,
                ruabean0_.dth_alteracao as dth9_9_,
                ruabean0_.cod_usuario_alteracao as cod10_9_,
                ruabean0_.ind_bloqueado as ind11_9_ 
            from
                admin.tb_rua ruabean0_,
                admin.tb_tipo_rua tiporuabea1_ 
            where
                ruabean0_.cod_tipo_rua=tiporuabea1_.cod_tipo_rua 
            order by
                tiporuabea1_.nom_tipo_rua ) x ) where r between ? AND ? 

it's solves the problem. Thank you


> Bug Oracle9iDialect Pagination
> ------------------------------
>
>                 Key: HHH-3292
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.2.6
>         Environment: Java2 6.0, Oracle 10i, Hibernate 3.2.6
>            Reporter: Mizael Montenegro
>            Priority: Critical
>   Original Estimate: 1 hour, 30 minutes
>  Remaining Estimate: 1 hour, 30 minutes
>
> Hi, I found a flaw in the dialect of the oracle, the pagination doesn't work correctly, my English is terrible, then I cannot describe the complete problem here, more I will post the string sql that solves the problem.
> select * from ( SELECT x.*, rownum as r FROM ( "CONSULT SQL" ) x ) where r between ? AND ?
> setInt(1, BEGIN);
> setInt(2, END);
> I altered the dialect and it perfectly worked with that solution. The largest problem is when ordination, the order is used using pagination corresponds never to the exact return of the original consultation.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira