[SOLVED] How to iterate over binary string in Oracle?

How to iterate over binary string in Oracle?

declare

str varchar2(2000) := :inputstr;
v_len number;
currChar CHAR(1);

begin
                v_len := length(str);
                for i in 1..v_len
                Loop
                        currChar  := substr(str,i,1);
                        if currChar = 1 then
              dbms_output.put_line('curr index' || i);
                        end if;
                End loop;
end;

When I pass '000111000' as input to IN_STRING variable , it trims the string and behaves very unusually.Please suggest some good approaches to iterate over binary strings like this.I am expecting output as 4,5,6 from above operation.

EDIT1: Please don't directly input the string as str varchar2(2000) := '000111000'; Instead input it from bind variable as I mentioned above.

Solution

Your code works so long as you pass in a VARCHAR2 data type (and not a NUMBER).

You can also tidy up the code passing in the bind variable only once and using CONSTANTs to hold the values that are constant:

VARIABLE in_string VARCHAR2;

DECLARE
  c_string CONSTANT VARCHAR2(200) := :in_string;
  c_length CONSTANT PLS_INTEGER := LENGTH(c_string);
  v_out    CHAR(1);
BEGIN
  FOR i IN 1..c_length
  LOOP
    v_out := SUBSTR(c_string,i,1) ;
    DBMS_OUTPUT.PUT_LINE(v_out);
  END LOOP;
END;
/

Which outputs:

db<>fiddle here