CODEWITHSUNDEEP
oracle-databaseplsqlsql-order-by
Yaswanth Gelli
Yaswanth Gelli

Reputation: 69

Passing order by columns as argument to pl sql

I am passing csv of column names as an argument for pl/sql procedure which in turn will be used in order by clause of sql command, but its being ignored.

SET serverOutput ON SIZE unlimited;
SET linesize 32767;
declare 
test1 varchar2(30);
begin
test1 := 'PARAMETER_NAME,DESCRIPTION'; -- This will be passed as input parameter
for rCursor in (select * from configurations order by test1 asc) loop
 dbms_output.put_line(rCursor.parameter_name || '-=-' || rCursor.Description);
 -- Output is not ordered by any of these columns
end loop;
end;

Any inputs?

Upvotes: 1

Views: 1436

Answers (1)

Aleksej
Aleksej

Reputation: 22949

You are using a variable to order a static cursor, so the result will be the same than

select * from configurations order by 'PARAMETER_NAME,DESCRIPTION'

If you need to use your variable to dynamically change the ordering of your cursor, you may need something like this:

declare 
    test1 varchar2(30);
    rcursor SYS_REFCURSOR;   /* define a cursor */
    vConfiguration configurations%ROWTYPE;  /* define a variable to host the result of your query */
begin
    test1 := 'PARAMETER_NAME,DESCRIPTION'; -- This will be passed as input parameter
    open rCursor for 'select * from configurations order by ' || test1; /* open a dynamic cursor */
    loop
        fetch rCursor into vConfiguration; /* fetch the cursor into a variable */
        exit when rCursor%NOTFOUND;        /* check if the cursor has rows */
        dbms_output.put_line(vConfiguration.parameter_name || '-=-' || vConfiguration.Description);
    end loop;
end;

Upvotes: 3

Related Questions