jcon.no: Oracle blog

For et par dager siden utførte jeg et kode review på en SQL pakke. Denne koden kom definitivt ikke igjennom nåløyet. Koden bygde opp diverse SQL strenger dynamisk, og gjorde blant annet ikke bruk av bind variabler. Dvs det var bind noen steder, og andre steder ikke. En av årsakene til at dynamisk SQL ble benyttet var behovet for å sortere på ulike kolonner på bakgrunn av input-parametere. I etterkant av dette har jeg prøvd å google for å se om det finnes løsninger på å løse denne type sorteringer uten bruk av dynamisk SQL. Det eneste fornuftige jeg fant var diverse linker som pekte mot det faktum at man kan bruke subqueries i order by klausulen. Derfor bestemte jeg meg for å prøve ut en løsning på egen hånd.

Jeg har laget en liten test case som jeg ønsker å utforske litt videre. Hvis noen av dere har kommentarer, eller har erfaring med noe lignende, så kommenter gjerne på denne artikkelen.

Test:

-- create test table, and insert some data
drop table t;
create table t as select object_id id, object_name  my_text from all_objects where rownum<=10;
insert into t values (200,'TEST');
commit;

var setting varchar2(30)
exec :setting:='id'

select * from t
order by
(case :setting when 'id' then to_char(lpad(t.id,17,0))
when 'my_text'  then T.TEKST
else null end) ;

ID MY_TEXT
---------- ------------------------------
100 ORA$BASE
116 DUAL
117 DUAL
200 TEST
280 MAP_OBJECT
365 SYSTEM_PRIVILEGE_MAP
367 SYSTEM_PRIVILEGE_MAP
368 TABLE_PRIVILEGE_MAP
370 TABLE_PRIVILEGE_MAP
371 STMT_AUDIT_OPTION_MAP
373 STMT_AUDIT_OPTION_MAP

11 rows selected.

exec :setting:='my_text'

select * from t
order by
(case :setting when 'id' then to_char(lpad(t.id,17,0))
when 'my_text'  then T.TEKST
else null end) ;

ID MY_TEXT
---------- ------------------------------
116 DUAL
117 DUAL
280 MAP_OBJECT
100 ORA$BASE
371 STMT_AUDIT_OPTION_MAP
373 STMT_AUDIT_OPTION_MAP
367 SYSTEM_PRIVILEGE_MAP
365 SYSTEM_PRIVILEGE_MAP
368 TABLE_PRIVILEGE_MAP
370 TABLE_PRIVILEGE_MAP
200 TEST

11 rows selected.

exec :setting:=''

select * from t
order by
(case :setting when 'id' then to_char(lpad(t.id,17,0))
when 'my_text'  then T.TEKST
else null end) ;

ID MY_TEXT
---------- ------------------------------
100 ORA$BASE
116 DUAL
117 DUAL
280 MAP_OBJECT
365 SYSTEM_PRIVILEGE_MAP
200 TEST
368 TABLE_PRIVILEGE_MAP
370 TABLE_PRIVILEGE_MAP
371 STMT_AUDIT_OPTION_MAP
373 STMT_AUDIT_OPTION_MAP
367 SYSTEM_PRIVILEGE_MAP

11 rows selected.

Er det grunner til at du ikke bør løse sorterings problemet på denne måten? Eller finnes det andre bedre måter å løse dette problemet på?  Gi gjerne en kommentar!

Den orginale koden utførte noe lignende:

set serveroutput on
declare
  my_sql varchar2(2000);
  type rcur_type is ref cursor;
  rcur rcur_type;
  myrec t%rowtype;
  setting varchar2(10);
begin
  setting:='id';
  my_sql:='select * from t';

  if (setting='id') then
    my_sql:=my_sql ||' order by id';
  else
    if (setting='my_text') then
      my_sql:=my_sql ||' order by my_text';
    end if;
  end if;
  dbms_output.put_line('SQL> '|| my_sql);

  open rcur for my_sql;
  loop
    fetch rcur into myrec;
    exit when rcur%notfound;
    dbms_output.put_line(lpad(myrec.id,10,' ') || ' : '|| myrec.my_text);
  end loop;
  close rcur;
end;
/

Når spørringer blir komplekse liker jeg definitivt ikke å bygge opp spørringene på denne måten.
Dynamisk SQL øker sjansen for bugs i koden, og jeg ønsker derfor å finne alternative måter å gjøre dette på.