Sort Nested table based on dynamic information

Question

I am having trouble sorted a nested table based on some dynamic information that would be in the order by clause.

Here is a sample of what I have found (https://technology.amis.nl/2006/05/31/sorting-plsql-collections-the-quite-simple-way-part-two-have-the-sql-engine-do-the-heavy-lifting/)

The only difference here is I need to dynamically define the column and direction in the order by clause

SELECT CAST(MULTISET(SELECT * 
                       FROM TABLE(table_a) 
                      ORDER BY P_SORT_COLUMN P_DIRECTION 
                     ) as table_typ) 
  INTO table_b
  FROM dual;

So to get around think I thought of using dynamic SQL and put it in a proc as forms cannot do this dynamically

loc_sql_stmt VARCHAR2(500);



BEGIN

    loc_sql_stmt := 'SELECT CAST(MULTISET(SELECT * ' ||
                                           'FROM TABLE(P_TABLE_A) ' ||
                                          'ORDER BY P_COLUMN P_DIRECTION || ) as table_typ) ' || 
                            'INTO P_TABLE_B' || 
                            'FROM dual;';


    EXECUTE IMMEDIATE loc_sql_stmt
             USING IN P_TABLE_A, P_COLUMN, P_DIRECTION, P_TABLE_B;

END;

There error I get from the EXECUTE IMMEDIATE line is "ORA-00936 missing expression

So is there a better way to sort a nest table by any given column and the direction or how do I get this dynamic SQL to work?

Here is a sample:

create this in DB:

  CREATE OR REPLACE TYPE table_obj AS OBJECT(
                    column1       VARCHAR2(20),
                    column2     VARCHAR2(20));

  CREATE OR REPLACE TYPE table_typ AS TABLE OF table_obj;  

and then a sample run:

DECLARE
    table_a           table_typ := table_typ ();
   table_b           table_typ := table_typ ();
   loc_idx           NUMBER;
   loc_sort_column   INTEGER := 1;
   loc_desc          VARCHAR2 (4);
   P_SORT_COLUMN     VARCHAR2 (100) := 'column1';
   P_DIRECTION       VARCHAR2 (4) := 'DESC';
   loc_sql_stmt      VARCHAR2 (500);
BEGIN
   FOR i IN 1 .. 5
   LOOP
      loc_idx := table_a.COUNT + 1;
      table_a.EXTEND;
      table_a (loc_idx) := table_obj (NULL, NULL);

      table_a (loc_idx).column1 := TO_CHAR (loc_idx);
      table_a (loc_idx).column2 := TO_CHAR (loc_idx);
   END LOOP;

   --
   loc_sql_stmt :=
     'SELECT CAST(MULTISET(SELECT * ' || 
                            'FROM TABLE(' || table_a || ') ' || 
                           'ORDER BY ' || P_SORT_COLUMN || ' '|| P_DIRECTION || 
                        ' ) as table_typ) ' || 
       'INTO :table_b' || 
       'FROM dual';

  EXECUTE IMMEDIATE loc_sql_stmt USING IN OUT table_a, table_b;

 FOR i IN 1 .. table_b.COUNT
 LOOP
  DBMS_OUTPUT.PUT_LINE (table_b (i).rx_number);
 END LOOP;
END; 

Answer 1

To pass variable to native dynamic SQL use : before parameter name, to build dynamic statement use concatenation, like this

loc_sql_stmt VARCHAR2(500);

BEGIN

    loc_sql_stmt := 'SELECT CAST(MULTISET(SELECT * ' ||
                                           'FROM TABLE('|| P_TABLE_A || ') ' ||
                                          'ORDER BY ' ||  P_COLUMN || ', ' || P_DIRECTION || ' ) as table_typ) ' || 
                            'INTO :P_TABLE_B' || 
                            'FROM dual;';


    EXECUTE IMMEDIATE loc_sql_stmt
             USING OUT P_TABLE_B;
END;

EDITED version:

Now seeing your code I understand what you need. To make it work we need to use dynamic PL/SQL block, not Native SQL here is working code of your sample, and pay attention to what is variable and what is concatenated literal

DECLARE
   table_a table_typ := table_typ();
   table_b table_typ := table_typ();
   loc_idx NUMBER;
   loc_sort_column INTEGER := 1;
   loc_desc VARCHAR2(4);
   P_SORT_COLUMN VARCHAR2(100) := 'column1';
   P_DIRECTION VARCHAR2(4) := 'desc';
   loc_sql_stmt VARCHAR2(500);
BEGIN
   FOR i IN 1 .. 5
   LOOP
      loc_idx := table_a.COUNT + 1;
      table_a.EXTEND;
      table_a(loc_idx) := table_obj(NULL, NULL);

      table_a(loc_idx).column1 := TO_CHAR(loc_idx);
      table_a(loc_idx).column2 := TO_CHAR(loc_idx);
   END LOOP;

   --
   loc_sql_stmt := 'begin SELECT CAST(MULTISET(SELECT * ' ||
                   'FROM TABLE(:table_a ) ORDER BY ' || P_SORT_COLUMN || ' ' ||
                   P_DIRECTION || ' ) as table_typ ) ' || ' INTO :table_b ' ||
                   'FROM dual; end;';

   EXECUTE IMMEDIATE loc_sql_stmt
      USING table_a, IN OUT table_b;

   FOR i IN 1 .. table_b.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE(table_b(i).column1);
   END LOOP;
END;

Answer 2

If you have limited column/direction choices, try a case statement in your order by, simple example:

select * from tab
order by case when :order = 'c1_asc'  then c1 else null end asc
      ,  case when :order = 'c1_desc' then c1 else null end desc
      ,  case when :order = 'c2_asc'  then c2 else null end asc
      ,  case when :order = 'c2_desc' then c2 else null end desc
/* ... */
;