Forum Stats

  • 3,781,161 Users
  • 2,254,485 Discussions
  • 7,879,597 Comments

Discussions

Assign values to variables in while loop

managed BEAN
managed BEAN Member Posts: 948 Silver Badge
edited Oct 15, 2015 12:51PM in SQL & PL/SQL

Hello experts,

I am creating a package with procedures/functions.

One of my issues was to create the below code:

DECLARE
    comma_pos      PLS_INTEGER;
    p_list         VARCHAR2 (500) := '10000000C445652B1057C738561F6862A891B80C,10000000CC6BA1CE146331BCE33637106C31F049,10000000FE7FC766360C28BE5AD1BE266A83C173';
    remaining_txt  VARCHAR2 (500) := p_list;
    token          VARCHAR2 (500);
    Part1          VARCHAR2 (500);
    Part2          VARCHAR2 (500);
    Part3          VARCHAR2 (500);
        
BEGIN
    WHILE  remaining_txt  IS NOT NULL
    LOOP
        comma_pos := INSTR ( remaining_txt || ',' , ','  ); 
        token := SUBSTR ( remaining_txt , 1 , comma_pos - 1 );
        dbms_output.put_line (token );
        remaining_txt := SUBSTR ( remaining_txt, comma_pos + 1 )
    END LOOP;
END;
/

Now, i would like to assign each of the value (it´s3) that the 'token' has which showns in output.

It should be assigned in the end the following:

Part1: 10000000C445652B1057C738561F6862A891B80C

Part2: 10000000CC6BA1CE146331BCE33637106C31F049

Part3: 10000000FE7FC766360C28BE5AD1BE266A83C173

This could be dinamicaly to be scalabe (imagine that tomorrow will be 4 parts, for example) if necessary, not required.

Looking forward for your valuable answers,

Regards,

Carlos

«1

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    edited Oct 15, 2015 10:37AM

    Store them in a table.

  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited Oct 15, 2015 10:41AM

    Hi,

    here's one approach to loop tokens

    set serveroutput on
    declare
      l_list varchar2(200) := '10000000C445652B1057C738561F6862A891B80C,10000000CC6BA1CE146331BCE33637106C31F049,10000000FE7FC766360C28BE5AD1BE266A83C173';
    begin
      for r in (
        select 
          regexp_substr(l_list, '[^,]+', level) token
        from dual
        connect by level <= regexp_count(l_list,',') + 1
      )
      loop
        dbms_output.put_line(r.token);
      end loop;
    end;
    
    
    PL/SQL procedure successfully completed.
    
    
    10000000C445652B1057C738561F6862A891B80C
    0000000C445652B1057C738561F6862A891B80C
    000000C445652B1057C738561F6862A891B80C
    
    
  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited Oct 15, 2015 10:56AM

    i might have been hasty with my first answer but here's perhaps what you seek.

    set serveroutput on
    declare
      l_list varchar2(200) := '10000000C445652B1057C738561F6862A891B80C,10000000CC6BA1CE146331BCE33637106C31F049,10000000FE7FC766360C28BE5AD1BE266A83C173';
      type l_table is table of varchar2(40);
      l_parts l_table;
    begin
      select 
        regexp_substr(l_list, '[^,]+', 1, level) token
      bulk collect into l_parts 
      from dual
      connect by level <= regexp_count(l_list,',') + 1;
      -- just debugging varible values
      for i in 1..l_parts.last
      loop
        dbms_output.put('part');
        dbms_output.put(i);
        dbms_output.put(':');
        dbms_output.put_line(l_parts(i));
      end loop;
    end;
    
    
    PL/SQL procedure successfully completed.
    
    
    part1:10000000C445652B1057C738561F6862A891B80C
    part2:10000000CC6BA1CE146331BCE33637106C31F049
    part3:10000000FE7FC766360C28BE5AD1BE266A83C173
    

    edited: fixed a bug

  • managed BEAN
    managed BEAN Member Posts: 948 Silver Badge
    edited Oct 15, 2015 10:52AM

    Hello SomeoneElse,

    Thanks for the reply but i don´t need to create a table and store those values, especially for security reasons.

    I only need these values at runtime to make one thing and after that they are disposable.

    Is there any other way?

    Regards,

    Carlos

  • managed BEAN
    managed BEAN Member Posts: 948 Silver Badge
    edited Oct 15, 2015 10:54AM

    Hello Jarkko,

    Not what i need.

    The result must be:

    pastedImage_0.png

    and each must be on a variable, which i can work with.

    Thanks for the reply.

    Do you have any other ideias?

    Regards,

    Carlos

  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited Oct 15, 2015 10:59AM
    managed BEAN kirjoitti:
    
    Hello Jarkko,
    
    Not what i need.
    The result must be:
    
    pastedImage_0.png

    and each must be on a variable, which i can work with.

    Thanks for the reply.

    Do you have any other ideias?

    Regards,

    Carlos

    it is in the variable l_parts just refer correct one with index

    like this l_parts(i)

    if you need separate variables then just declare them but that's not dynamic.

    l_part1 : = l_parts(1);

    l_part2 : = l_parts(2);

    l_part3 : = l_parts(3);

  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited Oct 15, 2015 11:04AM

    now i see why you are baffled.

    post #2 is not correct because it had a bug in regexp_substr parameter. I fixed that in post #3.

  • managed BEAN
    managed BEAN Member Posts: 948 Silver Badge
    edited Oct 15, 2015 12:11PM

    I´m sorry, i didn´t understand, would you mind to reformulate?

    So far i tried like this, and it worked, but is not dinamicaly (altought is not a requirement):

    DECLARE
        comma_pos      PLS_INTEGER;
        p_list         VARCHAR2 (500) := '10000000C445652B1057C738561F6862A891B80C,10000000CC6BA1CE146331BCE33637106C31F049,10000000FE7FC766360C28BE5AD1BE266A83C173';
        remaining_txt  VARCHAR2 (500) := p_list;
        token          VARCHAR2 (500);
        Part1          VARCHAR2 (500);
        Part2          VARCHAR2 (500);
        Part3          VARCHAR2 (500);
        loop_num       PLS_INTEGER;
            
        BEGIN
        
        loop_num := 1;
            WHILE  remaining_txt  IS NOT NULL
            LOOP
                comma_pos := INSTR ( remaining_txt || ',' , ','  ); -- encontra a primeira ocorrencia da ',' na string remaining_txt
                token := SUBSTR ( remaining_txt , 1 , comma_pos - 1 ); -- String temporaria para a substring até á primeira ocorrencia da ','
                dbms_output.put_line (token ); -- imprime a String temporaria
                
                  if loop_num = 1 then
                    Part1 := SUBSTR ( remaining_txt , 1 , comma_pos - 1 ); -- obter a substring até á primeira ocorrencia da ','
                  elsif loop_num = 2 then
                    Part2 := SUBSTR ( remaining_txt , 1 , comma_pos - 1 ); -- obter a  substring até á primeira ocorrencia da ','
                  elsif loop_num = 3 then
                    Part3 := SUBSTR ( remaining_txt , 1 , comma_pos - 1 ); -- obter a substring até á primeira ocorrencia da ','
                  end if;
                
                loop_num := loop_num + 1;
                
                remaining_txt := SUBSTR ( remaining_txt, comma_pos + 1 ); -- actualiza a string remaining_txt com o restante após a primeira ','
            END LOOP;
            
        dbms_output.put_line ('Part1--> ' || Part1 );
        dbms_output.put_line ('Part2--> ' || Part2 );
        dbms_output.put_line ('Part3--> ' || Part3 );
    END;
    /
    

    Is there any other way better for this use case?

    Regards,

    Carlos

  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited Oct 15, 2015 12:17PM

    what is your requirement? What variable you need and for what?

  • managed BEAN
    managed BEAN Member Posts: 948 Silver Badge
    edited Oct 15, 2015 12:31PM

    From the String i pass to the variable p_list, each exctraction (the ',' is where the string must be separated) must be placed in a variable (part1, part2, part3) to be manipulated by the caller:

    PROCEDURE procedure (  <...In parameters...> , part1 OUT VARCHAR2, part2 OUT VARCHAR2, part3 OUT VARCHAR2) IS ...

    Carlos

This discussion has been closed.