CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Ispirer SQLWays Migrations
Ispirer SQLWays Migrations

Reputation: 1058

How to get sorted record in dynamic sql oracle

My issue is how to sort records, which must be sorted when the procedure is called. For example, I have record like:

TYPE rec1_ex_TP IS RECORD
    (
      rec_col1 CHAR(1),
      rec_col2 CHAR(1),
      rec_col3 CHAR(1)
    );
TYPE rec1_ex_ARRAY IS VARRAY(12) OF rec1_ex_TP;
TYPE rec1_TP IS RECORD
    (
      rec1_ex rec1_ex_ARRAY := rec1_ex_ARRAY()
    );
rec1 rec1_TP;

Values are inserted to the record:

rec1.rec1_ex(1).rec_col1 := 'X';
rec1.rec1_ex(1).rec_col2 := 'A';
rec1.rec1_ex(1).rec_col3 := 'A';
rec1.rec1_ex(2).rec_col1 := 'M';
rec1.rec1_ex(2).rec_col2 := 'B';
rec1.rec1_ex(2).rec_col3 := 'A';
rec1.rec1_ex(3).rec_col1 := 'A';
rec1.rec1_ex(3).rec_col2 := 'C';
rec1.rec1_ex(3).rec_col3 := 'D';

How to get sorted record by 'rec_col1' from dynamic sql?

I tried to create table and insert this record, but I don't know what to do next. Thank you for help!

Upvotes: 1

Views: 94

Answers (1)

Grzegorz Kazior
Grzegorz Kazior

Reputation: 401

Depending on details here are some clues:

  1. Put the records in order into the table
  2. When returned in SQL ex. PIPELINE use ORDER BY
  3. If processed in PLSQL put them into table index by VARCHAR2 which keeps the order like this

Upvotes: 1

Related Questions