PL/SQL - dynamic sql formatting comes out as literal varchar

i have a reference table that stores various columns with values for each record such as LPAD( pl_sql_variablex 10)

the idea is that data in the table (ie the number of padding can be changed from 10 to 15, or change the relevant variable) can change without needing to change any PL/SQL.

i have a cursor that goes

    CURSOR C IS SELECT columna, columnb, columnc, columnd
                  FROM REFerence
                  ORDER BY FIELDORDER;  

then i want to print out the formatted string but 开发者_如何学JAVAwith my current syntax it is printing literally the text 'LPAD' instead of actually padding the values

  FOR R IN C LOOP
     UTL_FILE.PUT(FILEP, R.COLUMNA||R.COLUMNb||','||
      R.COLUMNc||','||R.COLUMND);
      dbms_output.put_line(R.COLUMNA||R.COLUMNb||','||
      R.COLUMNc||','||R.COLUMND);
  END LOOP;

---

You can use execute immediate (I've replaced your explicit cursor with an implicit one for brevity):

create table reference(
  columna varchar2(100), 
  columnb varchar2(100), 
  columnc varchar2(100), 
  columnd varchar2(100));

insert into reference(columna, columnb, columnc, columnd) 
  values(':x', 'lpad(:x, 5, ''_'')', 'trim(:x)', 'substr(:x, 1, 5)');    

declare
  a varchar2(100);
  b varchar2(100);
  c varchar2(100);
  d varchar2(100);  
  l_SQL varchar2(4000);
  inval varchar2(100) default ' hello world ';
begin
  for cur in (select * from reference)
  loop
    l_SQL := 'select ' || cur.columna || ',' || cur.columnb ||',' || cur.columnc || ',' || cur.columnd || ' from dual';
    dbms_output.put_line(l_SQL);
    execute immediate l_SQL into a,b,c,d using inval, inval, inval, inval;
    dbms_output.put_line(a);
    dbms_output.put_line(b);
    dbms_output.put_line(c);
    dbms_output.put_line(d);
  end loop;
end;

Of course, you'll have to watch out for SQL injection attacks, invalid SQL statements etc.