Is field name in record.FIELD required to be literals in a cursor loop?

Question

I am preparing a procedure which will extract data from several tables and output a .csv file, but have an unsurmountable problem to streamline the code. The following is an illustraion of the problem.

This code works out straightforeward:

declare
    cursor cur_company is
       select company_name, no_employees
       from company;
    line_out varchar2(200);
 begin
    for rec in cur_company loop
       line_out := '';
       line_out := line_out || rec.company_name || ',';
       line_out := line_out || rec.no_employees;
       dbms_output.put_line(line_out);
    end loop;
end;
/

PL/SQL procedure successfully completed.

USCABLES,250
KODAK,2500
KLM,5000
HAMBRO,2000
FORD,8000
CITYBANK,3000
DISNEY,2500

In the example each record has only two fields. But the procedure I am working on deals with dozens of fields, say 60, and thus the same statement of concatenation will repeat 60 times:

  for rec in cur_company loop
     line_out := '';
     line_out := line_out || rec.field1 || ',';
     line_out := line_out || rec.field2 || ',';
     ...
     line_out := line_out || rec.field60;
     ...
  end loop;

The code is therefore not sightly. I thought of embedding another loop to cycle through all the fields. The following is the code, having a lot fewer lines of code than 60, but it incurred error:

declare
   cursor cur_company is
      select company_name, no_employees
      from company;
   line_out varchar2(200);
   headerset varcharlist := varcharlist('COMPANY_NAME', 'NO_EMPLOYEES');
begin
   for rec in cur_company loop
      line_out := '';
      for ctr in 1..headerset.count loop
         if ctr = headerset.count then
            line_out := line_out || rec.headerset(ctr);
         else
            line_out := line_out || rec.headerset(ctr) || ', ';
         end if;
      end loop;
      dbms_output.put_line(line_out);
   end loop;
end;
/

...
ORA-06550: line 12, column 41:
PLS-00302: component 'HEADERSET' must be declared
...

Could there be a a problem with the HEADERSET array? The following shows the array is OK:

declare
   headerset varcharlist := varcharlist('COMPANY_NAME', 'NO_EMPLOYEES');
begin
   for ctr in 1..headerset.count loop
      dbms_output.put_line(headerset(ctr));
   end loop;
end;
/

PL/SQL procedure successfully completed.

COMPANY_NAME
NO_EMPLOYEES

Or is it due to the direct use of the array for naming the fields? I tried putting the array element in a varchar2 variable before using it. But it incurred the same kind of error:

declare
   cursor cur_company is
      select company_name, no_employees
      from company;
   line_out varchar2(200);
   headerset varcharlist := varcharlist('COMPANY_NAME', 'NO_EMPLOYEES');
   col_name varchar2(20);
begin
   for rec in cur_company loop
      line_out := '';
      for ctr in 1..headerset.count loop
         col_name := headerset(ctr);
         if ctr = headerset.count then
            line_out := line_out || rec.col_name;
         else
            line_out := line_out || rec.col_name || ', ';
         end if;
      end loop;
      dbms_output.put_line(line_out);
   end loop;
end;
/

...
ORA-06550: line 14, column 41:
PLS-00302: component 'COL_NAME' must be declared
...

Now it seems to me that, in looping through the fields of a cursor record, only literals are accepted by PL/SQL for the field name, and variables are not recognized. Is that true? Is there a way to get around this limitation to reduce the clutter of code?

Just FYI, you can concatenate multiple column values in one line, e.g. `line_out := line_out || rec.company_name || ',' || rec.no_employees || ',';` — David Faber, Jun 06 '18 at 15:51
It is true, yes. You could do [something like this](https://stackoverflow.com/a/42419223/266304) (possibly a duplicate question too?), but there are always trade-offs - such as readability, maintainability, not seeing syntax errors until runtime... Are you sure you really need the procedure, and can't use built-in formatting options in a client (e.g. `sqlformat` in SQL Developer or SQLcl)? — Alex Poole, Jun 06 '18 at 16:47
Thank you @Alex. The biggest takeaway is the confirmation that it does require the field names to be literals. I wrote the first such data report I was asked to in sql*plus, no cursor, just the query, with the output saved in a .csv file through SPOOL. But that script had to be discarded because it was required all such code be written in stored procedures. Hence all this fun of trying to be as terse as sql*plus script. — newman, Jun 07 '18 at 16:36
The sqlformat is nice, with the csv format right there on the screen. But it seems to be a SQL Developer session setting, and can not be part of a stored procedure (please let me know if this is not true). In my case, all such code are to be run at night as scheduled jobs. SQLlc is new to me, and I just now downloaded it, and it seems to be a modernized sql*plus. I wonder and will find out if scripts written in SQLlc can be saved as a database object and the code always a click away in the object tree in SQL Developer. — newman, Jun 07 '18 at 16:36
The 60 lines of code is still neat and simple, but just a bit too many lines. If it takes that long way with increased complexity to just decrease the number of lines of simple code, the trade off does not look like worthwhile. Just wish that future versions of Oracle will lift the literal-only restriction. — newman, Jun 07 '18 at 16:37

score 0 · Answer 1 · answered Jun 06 '18 at 23:43

You can't refer to a query like that. You could use Dynamic SQL, but that's probably a bad idea. I'd suggest just listing out the column names in your aggregation. If you want to speed it up, you can query from ALL_TAB_COLS to get a list of the columns in your table.

Is field name in record.FIELD required to be literals in a cursor loop?

1 Answers1