CODEWITHSUNDEEP
oracle-databaseplsql
Kashif Khan
Kashif Khan

Reputation: 695

How to sort with dynamic column and dynamic sort direction in pl/sql

How to write a pl/sql with dynamic column and dynamic sort direction for example:-

select * from table order by "dynamic column" "dynamic sort order";

Upvotes: 0

Views: 1180

Answers (2)

doberkofler
doberkofler

Reputation: 10421

You basically need to construct your sql command as a string and then execute it dynamically.

DECLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  emp_record      employees%ROWTYPE;
  v_stmt_str      VARCHAR2(200);
  v_order         VARCHAR2(30);
  v_e_job         employees.job%TYPE;
BEGIN
  -- Dynamic SQL statement with placeholder:
  v_order := 'emplid DESC';
  v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j ORDER BY ' || v_order;

  -- Open cursor & specify bind argument in USING clause:
  OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';

  -- Fetch rows from result set one at a time:
  LOOP
    FETCH v_emp_cursor INTO emp_record;
    EXIT WHEN v_emp_cursor%NOTFOUND;
  END LOOP;

  -- Close cursor:
  CLOSE v_emp_cursor;
END;
/

Upvotes: 1

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

You can use dynamic SQL & REF CURSORS to build queries dynamically

DECLARE
    TYPE cur_typ IS ref CURSOR;
    emp_rec_cursor CUR_TYP;
    v_query_str    VARCHAR2(2000);
    v_sort_order   VARCHAR2(10) := 'DESC';
    v_column       VARCHAR2(20) := 'NAME';
    v_empid        emp.empid%TYPE;
    v_name         emp.name%TYPE;
    v_salary       emp.salary%TYPE;
BEGIN
    query_str := 'SELECT empid, name, salary FROM emp order by '
                 || v_column   -- reassign v_column as needed
                 || '  '
                 || v_sort_order; -- reassign v_sort_order as needed

    OPEN emp_rec_cursor FOR query_str;

    LOOP
        FETCH emp_rec_cursor INTO v_empid, v_name, v_salary;

        EXIT WHEN emp_rec_cursor %NOTFOUND;
    -- process row here
    END LOOP;

    CLOSE emp_rec_cursor;
END;

Upvotes: 0

Related Questions