Question: generating multiple columns from dual table

Question

generating multiple columns from dual table

Answers 3
Added at 2017-01-02 12:01
Tags
Question

I need to generate multiple columns from dual table. Number of columns to be generated gets decided by user input. If input is 3 then three times 'ABC'. If it is 4 then 4 times 'ABC' should be selected from dual. I tried union all but I am trying to find out more efficient way of doing it.

DECLARE
    v_value varchar2(10):='ABC'
    v_count number:=3;
    VAR varchar2(4000)
BEGIN
    select 'ABC','ABC','ABC' INTO VAR FROM DUAL;
END;
Answers to

generating multiple columns from dual table

nr: #1 dodano: 2017-01-02 12:01

Is that what you mean?

select RPAD('ABC',length('ABC')*3,'ABC') from dual

ABCABCABC


DECLARE
    v_value varchar2(10):='ABC'
    v_count number:=3;
    VAR varchar2(4000)
BEGIN
    select RPAD(v_value,length(v_value)*v_count,v_value) INTO VAR FROM DUAL;
END;
nr: #2 dodano: 2017-01-02 12:01

Your question is not that clear.

The fact that you are using a VARCHAR2(4000) variable for your result value makes me think that you need to get a single string composed by the concatenation of a string n times; if this is the case, you don't need a select ... from DUAL and can simply do:

DECLARE
    v_value varchar2(10):='ABC';
    v_count number:=4;
    VAR varchar2(4000);
BEGIN
    VAR :=  rpad(v_value, length(v_value) * v_count, v_value);
    dbms_output.put_line(VAR);
END;
/
ABCABCABCABC

But you say you tried UNION, and this makes me think you need to get n rows with the same value; in this case, you may try:

DECLARE
    type yourResultType is table of varchar2(10);    
    v_value varchar2(10):='ABC';
    v_count number:=4;
    VAR yourResultType;
BEGIN
    select v_value
    bulk collect into VAR
    from dual
    connect by level <= v_count;
    --
    for i in VAR.first .. VAR.last loop
        dbms_output.put_line(VAR(i));
    end loop;
END;
/
ABC
ABC
ABC
ABC
nr: #3 dodano: 2017-01-02 13:01

Try this. Here you can input the number of times 'ABC' needed as column at runtime:

Code:

declare
  user_input number := &num;

  var   varchar2(10) := '''ABC''';
  var1  varchar2(2000);
  v_sql varchar2(1000);
  v_res varchar2(4000);
begin

  var1 := var;

  for i in 1 .. user_input - 1 loop
    var1 := var1 || ',' || var;
  end loop;

  var1 := LTRIM(RTRIM(var1, ','), ',');
  --dbms_output.put_line(var1);
  v_sql := 'select :var1 from dual';
  --dbms_output.put_line(v_sql);

  Execute immediate v_sql
    into v_res
    using var1;

  dbms_output.put_line(v_res);

end;

Demo:

SQL> declare
  2  
  3    user_input number := &num;
  4  
  5    var   varchar2(10) := '''ABC''';
  6    var1  varchar2(2000);
  7    v_sql varchar2(1000);
  8    v_res varchar2(4000);
  9  begin
 10  
 11    var1 := var;
 12  
 13    for i in 1 .. user_input - 1 loop
 14      var1 := var1 || ',' || var;
 15    end loop;
 16  
 17    var1 := LTRIM(RTRIM(var1, ','), ',');
 18  
 19    --dbms_output.put_line(var1);
 20  
 21    v_sql := 'select :var1 from dual';
 22  
 23    --dbms_output.put_line(v_sql);
 24  
 25    Execute immediate v_sql
 26      into v_res
 27      using var1;
 28  
 29    dbms_output.put_line(v_res);
 30  
 31  end;
 32  /
Enter value for num: 2
old   3:   user_input number := &num;
new   3:   user_input number := 2;
'ABC','ABC'

PL/SQL procedure successfully completed.

SQL> /
Enter value for num: 5
old   3:   user_input number := &num;
new   3:   user_input number := 5;
'ABC','ABC','ABC','ABC','ABC'

PL/SQL procedure successfully completed.

SQL> /
Enter value for num: 7
old   3:   user_input number := &num;
new   3:   user_input number := 7;
'ABC','ABC','ABC','ABC','ABC','ABC','ABC'

PL/SQL procedure successfully completed.

SQL> /
Enter value for num: 6
old   3:   user_input number := &num;
new   3:   user_input number := 6;
'ABC','ABC','ABC','ABC','ABC','ABC'

PL/SQL procedure successfully completed.

SQL> /
Enter value for num: 9
old   3:   user_input number := &num;
new   3:   user_input number := 9;
'ABC','ABC','ABC','ABC','ABC','ABC','ABC','ABC','ABC'

PL/SQL procedure successfully completed.
Source Show
◀ Wstecz