CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
user10777723
user10777723

Reputation: 25

How to get the data for the dynamic column name in oracle APEX

I would like to take the column names dynamically.

declare
  rColNames varchar2(500);
  rColumns varchar2(4000);
  rC varchar2(4000);

BEGIN
  for i in (
    select  name, amount,days -- (More columns will be there)
    from trans_e 
    where name = 'A'
  )
  loop
    --dynamically selected column names 
    --:P2_COLS -- NAME,AMOUNT,DAYS etc...
    for colNames in
    (
      select regexp_substr(:P2_COLS,'[^,]+',1,level) rCol from dual
      connect by level <= length(regexp_replace(:P2_COLS,'[^,]+')) + 1
    )
    loop
      rColNames :=  LOWER(colNames.rCol);
      select 'i.' || rColNames into rColumns from dual; 
      rC := rC ||  rColumns ; 
    end loop;
    
    
   

  end loop;
END;

So I need the data from i.days in rColumns. But I am getting the rColumns as i.days instead of i.days value 10.

If I give i.days directly, I am getting the value 10. But if I am giving 'i.' || rColNames, then I get i.days as an output, not the value 10.

How to get the dynamic data value from the i loop?

Upvotes: 0

Views: 484

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

The simplest approach may be to enumerate the columns in a case expression:

loop
  -- append a comma if this isn't the first column
  rC := case when rC is not null then rC || ',' end ||
    case lower(colNames.rCol)
      when 'name' then i.name
      when 'amount' then to_char(i.amount)
      when 'days' then to_char(i.days)
    end;          
end loop;

If you're expecting multiple rows back from the outer cursor then you need to clear rC before each row:

declare
  rC varchar2(4000);
begin
  for i in (
    select  name, amount,days -- (More columns will be there)
    from trans_e 
    where name = 'A'
  )
  loop
    -- clear result from previous row
    rC := null;

    --dynamically selected column names 
    --:P2_COLS -- NAME,AMOUNT,DAYS etc...
    for colNames in
    (
      select regexp_substr(:P2_COLS,'[^,]+',1,level) rCol from dual
      connect by level <= length(regexp_replace(:P2_COLS,'[^,]+')) + 1
    )
    loop
      -- append a comma if this isn't the first column
      rC := case when rC is not null then rC || ',' end ||
        case lower(colNames.rCol)
          when 'name' then i.name
          when 'amount' then to_char(i.amount)
          when 'days' then to_char(i.days)
        end;          
    end loop;
  end loop;
end;
/

Upvotes: 1

Related Questions