Create var inside a for loop PLSQL ORACLE 11G

Question

i have a function that puts a number in a var (var1). if var1 = 3 then create 3 new variables and assign a value for later send that values in an out type. I don't know how can i do, my code is something like this where "pos" acts like an INSTR() that i know its each 13 chars. And "newvar||var1" is because i want my vars names be newvar1, newvar2, newvar3, etc.:

FOR n IN 1..var1
LOOP
  pos          NUMBER(4):= 0;
  newvar||var1 NUMBER(3);
  newvar||var1 := SUBSTR(TO_NUMBER(numberInsideString), 1, 1+pos);
  pos := pos+13;
END LOOP;

My question is, how a person who really know PLSQL would do that, im learning PLSQL please help. thank you.

Oracle 11g is ancient, time to upgrade. Instead of attempting to dynamically create variables, you should just use an array, start here: docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/… — Andrew Sayer
– Andrew Sayer, Commented May 3, 2022 at 23:22
PL/SQL code gets compiled, it doesn't have an ability to dynamically generate itself. It looks like an XY problem, so please describe what do you want to achieve and why you cannot preallocate (declare) as much variables as you need. — astentx
– astentx, Commented May 3, 2022 at 23:52

Barbaros Özhan · Accepted Answer · 2022-05-04 05:47:39Z

2

What you want to try to do suggests that you need a one dimensional array in order repeatedly to assign new values for each iteration within the loop. One of the types in OWA package such as vc_arr(composed of VARCHAR2 data type values) is handy to define an array.

Coming to the code, you can start with moving the variables into the declaration section, and write such a simple code as a sample exercise

SET serveroutput ON

DECLARE
  pos                INT := 0;
  numberInsideString VARCHAR2(100):='as12df34sdg345apl8976gkh4f11öjhöh678u7jgvj';
  newvar             OWA.vc_arr;
BEGIN
  FOR n IN 1..3
  LOOP    
    newvar(n) := SUBSTR(numberInsideString, 1, 1+pos);
    pos := pos+5;
    Dbms_Output.Put_Line(newvar(n));
  END LOOP;  
END;
/
a
as12df
as12df34sdg

answered May 4, 2022 at 5:47

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

Create var inside a for loop PLSQL ORACLE 11G

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related