Allow all comparisons for row values

@user-jens.bjerg_9122150

Using JSON_OBJECT won't work because the string comparison does not provide the same result: CHANNEL_ID 11 would be less than CHANNEL_ID 9.

@user-jens.bjerg_9122150 ,

Suppose CHANNEL_DESC contains 'abcde' compared to 'abcde!' ? Now '!' is going to be compared to '"' and it will come first, which is incorrect.

Are you going to RPAD(CHANNEL_DESC, ' ', 4000) ?

What if JSON_OBJECT tries to return more than 4000 bytes? It will fail, and comparing CLOBs is not supported.

Is this really less complicated than the standard (though tedious) logic?

@Thorsten Kettner ,

You misread my post.

You quoted me as saying "Is this really less complicated than the standard (though tedious) logic?"

Did you notice that I was addressing @user-jens.bjerg_9122150 ?

I was objecting to his alternative approach. I agree that the word "standard" is misleading: I did not mean the SQL standard but the ordinary logic you are tired of using.

I was supporting your request by dismissing a clumsy and error-prone alternative.

By the way, are you also a moderator of this forum? Am I supposed to follow your rules?

There is also a OFFSET in the row limiting clause, so you can do the

SELECT * 
FROM programs
ORDER BY version, subversion, subsubversion
FETCH NEXT 50 ROWS ONLY;

save the sql%rowcount to a variable and then in the next call do a

SELECT * 
FROM programs
ORDER BY version, subversion, subsubversion
OFFSET var_rowcount_from_previous_select
FETCH NEXT 50 ROWS ONLY;

or more general with two variables page_no and rows_per_page

SELECT * 
FROM programs
ORDER BY version, subversion, subsubversion
OFFSET ((page_no - 1) * rows_per_page)
FETCH NEXT rows_per_page ROWS ONLY;

An alternate - but complicated way - is to create an object type with an ORDER (or MAP) method to support sorting and then use that type as a replacement for the JSON_OBJECT() mentioned earlier.

The disadvantage is that you must declare such a type for each different combination of datatypes in the tuple you want to use, e.g.:

create or replace type t_compare_vnv as object (
  v1 varchar2(100), n1 number, v2 varchar2(100),
  order member function order_by (p_other in t_compare_vnv) return integer
) not final instantiable;
/
create or replace type body t_compare_vnv as
  order member function order_by (p_other in t_compare_vnv) return integer is
  begin
    return (case
              when (self.v1 < p_other.v1) then -1
              when (self.v1 > p_other.v1) then +1
              when (self.n1 < p_other.n1) then -1
              when (self.n1 > p_other.n1) then +1
              when (self.v2 < p_other.v2) then -1
              when (self.v2 > p_other.v2) then +1
              else 0
            end);
  end order_by;
end;
/