PL/SQL loop through variables

Question

I have a tool which output is a sql query results. Depending on the user choice query is using subqueries. In order to get all subqueries to my final query I use strings and at the final stage I concat them to one big query - vSQL.

Subqueries are saved in string like vSQL1 .. vSQL14 Since not every subquery is used - user choice - some of them are omitted.

Now it looks like this:

if LENGTH(vSQL1) > 1 then
set_vSQL(vSQL, vSQL1, t);
end if;

..

if LENGTH(vSQL14) > 1 then
set_vSQL(vSQL, vSQL14, t);
end if;

Is it possible to put it into loop so only variable would change?
I tried something like this but this is not working.

for x in 1 .. 14
loop 
    if LENGTH(vSQL || x) > 1 then
        set_vSQL(vSQL, vSQL || x, t);
    end if;
end loop

You have to tell us what set_vSQL is about to do? You say "Subqueries are saved in string like vSQL1 .. vSQL14" - Why can't it be concatenated while saving? — Kaushik Nayak
– Kaushik Nayak, Commented Mar 25, 2019 at 9:38

lowak · Accepted Answer · 2019-03-25 10:58:43Z

1

In a meantime I have found a solution.

I have added a new type and a variable:

TYPE vSQLs_table IS TABLE OF VARCHAR2(32000);
vSQLs vSQLs_table;

With this I could input all my variables into one:

vSQLs := vSQLS_table(vSQL1, vSQL2, vSQL3, vSQL4, vSQL5, vSQL6, vSQL7, vSQL8, vSQL9, vSQL10, vSQL11, vSQL12);

With this I could loop through all of them:

for x in 1 .. 12
    loop
        if LENGTH(vSQLs(x)) > 1 then
            set_vSQL(vSQL, vSQLs(x), t);
        end if;
    end loop;

answered Mar 25, 2019 at 10:58

lowak

1,2942 gold badges19 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hotfix · Accepted Answer · 2019-03-25 09:06:42Z

0

you can use dynamic SQL. something like that( the code ist not tested)

declare
  l number;
begin

  for x in 1 .. 14
  loop 
      execute immediate 'select LENGTH(vSQL'||to_char(x)||')  from dual' into l;
      if l > 1 then
        execute immediate 'set_vSQL(vSQL, vSQL'||to_char(x)||', t)' ;
      end if;
  end loop
end;

answered Mar 25, 2019 at 9:06

hotfix

3,39624 silver badges36 bronze badges

Collectives™ on Stack Overflow

PL/SQL loop through variables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related