TOP with index variable oracle

user_10344380 0
Viewed 80

I was trying to use the variable 'ind' to get the ROWNUM on my select, but everytime I try to use the variable there I get a error like:

ORA-01008: not all variables bound

or these two:

ORA-01403: no data found

ORA-06512: at line 10

    DECLARE 
texto VARCHAR2(255);
ind NUMBER := 0;
BEGIN
   LOOP
     ind := ind + 1;
     IF ind > 3 THEN
      EXIT;
     END IF;
     SELECT TEXTO_LOG 
     INTO texto
      from table WHERE REGEXP_LIKE(TEXTO_LOG, 'Alteração') AND ROWNUM >= :ind AND ROWNUM <= :ind ;
      dbms_output.put_line(substr(trim(texto), 1, instr(texto, ' ')));
      dbms_output.put_line(substr(texto, 0, 100));
     
    END LOOP;
END;
/

I searched and found someone telling that not all variables bound is a bug, I'm not sure if it's. I tried the ROWNUM with different opperators. Any suggestions?

plsql oracle
3 Answers

The error is that you are referring to :ind as if it were a bind variable, when it is not. It is in fact a variable declared in your DECLARE section.

You might try this

** Update **

As your query looks like it is faling, the reason perhaps is in the query itself. Run this and get the output and running separately

set serveroutput on size unlimited 
DECLARE 
texto VARCHAR2(255);
ind    NUMBER := 0;
BEGIN
  for r in 1..4 
   LOOP
     ind := r + 1;
   dbms_output.put_line ( q'[SELECT TEXTO_LOG 
         INTO texto
          from table WHERE REGEXP_LIKE(TEXTO_LOG, 'Alteração') AND ROWNUM <= ind ;
          dbms_output.put_line(substr(trim(texto), 1, instr(texto, ' ')));
          dbms_output.put_line(substr(texto, 0, 100));]');
   exit when ind > 3;
   END LOOP;
END;
/

Although you can build it like this which is easier

DECLARE 
texto VARCHAR2(255);
ind    NUMBER := 0;
BEGIN
  for r in 1..4 
   LOOP
     ind := r + 1;
     dbms_output.put_line(ind);
     SELECT TEXTO_LOG 
     INTO texto
      from table WHERE REGEXP_LIKE(TEXTO_LOG, 'Alteração') AND ROWNUM >= ind AND ROWNUM <= ind ;
      dbms_output.put_line(substr(trim(texto), 1, instr(texto, ' ')));
      dbms_output.put_line(substr(texto, 0, 100));
     exit when ind > 3;
   END LOOP;
END;
/

EXample

SQL> DECLARE
texto VARCHAR2(255);
ind    NUMBER := 0;
BEGIN
  for r in 1..4
   LOOP
     ind := r + 1;
     dbms_output.put_line(ind);
         exit when ind > 3;
   END LOOP;
END;
/  2    3    4    5    6    7    8    9   10   11   12
2
3
4

PL/SQL procedure successfully completed.

SQL>
user_137555380

Usage of rownum is the problem here apart from other issues already answered. a predicate like rownum>=2 and rownum <=2 (and so on...till exit point) yield no result and thus no_data_found error

But as workaround put the rownum inside from clause and restrict it outside should work,

DECLARE
   texto VARCHAR2(255);
   ind   NUMBER := 0;
BEGIN
   LOOP
      ind := ind + 1;
      IF ind > 3
      THEN
         EXIT;
      END IF;
      SELECT texto_log 
      INTO texto
      FROM   (SELECT texto_log
                    ,rownum myrownum
              FROM   TABLE
              WHERE  regexp_like(texto_log
                                ,'Alteração'))
      WHERE  myrownum >= ind
      AND    myrownum <= ind;
      dbms_output.put_line(substr(TRIM(texto)
                                 ,1
                                 ,instr(texto
                                       ,' ')));
      dbms_output.put_line(substr(texto
                                 ,0
                                 ,100));
   
   END LOOP;
END;
/
user_138780230

With implicit cursor

DECLARE 
-- texto VARCHAR2(255);
ind NUMBER := 0;
BEGIN
   
    FOR i IN (SELECT TEXTO_LOG FROM table WHERE REGEXP_LIKE(TEXTO_LOG, 'Alteração'))
     LOOP  
         ind:=ind+1;
         EXIT WHEN ind >3;
         DBMS_OUTPUT.PUT_LINE(SUBSTR(TRIM(i.TEXTO_LOG), 1, INSTR(i.TEXTO_LOG, ' ')));
         DBMS_OUTPUT.PUT_LINE(SUBSTR(i.TEXTO_LOG, 0, 100));
     END LOOP;
END;
Admin1
Related