I'm new in PL/SQL. I have trouble with loop within this language. I' d prefer to make loop such as this:

FOR nr IN 1..102 
LOOP
  DBMS_OUTPUT.PUT_LINE(nr);
  IF rec.column_||nr IS NULL
    THEN
    DBMS_OUTPUT.PUT_LINE('test');
  END IF;
END LOOP;

I've produced a cursor. As you can tell I' d prefer to check all column with names column from column_1 to column_102. Regrettably operator doesn't work with this situation. Are you aware some means to fix my problem?

This can be done with dynamic PL/SQL. Make use of an [cde] statement to carry out a string argument as PL/SQL, which you'll constitute with EXECUTE IMMEDIATE because it was intended within the question.

Example:

||

Or you might also assign BEGIN FOR nr IN 1..102 LOOP DBMS_OUTPUT.PUT_LINE(nr); EXECUTE IMMEDIATE 'BEGIN ' || 'IF rec.column.' || nr ||' is null THEN ' || 'DBMS_OUTPUT.PUT_LINE(''test''); ' || 'END IF; ' || 'END; '; END LOOP; END; to some variable making the rec.column.' || nr ||' is null outdoors the PUT_LINE part:

UPDATE: It appears it's not easy to bind EXECUTE IMMEDIATE variables, so I have modified the example to utilize a BOOLEAN.

UPDATE 2: There's a potential efficiency improvement, altough not appropriate within this situation. Make use of a constant NUMBER for that dynamic SQL, and pass in VARCHAR having a binded variable. This really is much more efficient than using native SQL if inside a large loop. I do not think nr would execute as 'rec.column.:arg is null, though.

'rec.column.1 is null

UPDATE 3: Simply because:

  • It's not easy to access DECLARE isnull NUMBER; BEGIN FOR nr IN 1..102 LOOP DBMS_OUTPUT.PUT_LINE(nr); EXECUTE IMMEDIATE 'BEGIN ' || 'IF rec.column.' || nr ||' IS NULL THEN ' || ':x:=1; ' || 'ELSE ' || ':x:=0; ' || 'END IF; ' || 'END; ' USING OUT isnull; IF isnull = 1 THEN DBMS_OUTPUT.PUT_LINE('test'); END IF; END LOOP; END; within the dynamic SQL statement since it is undefined (from scope),

  • It appears difficult to pass through a non-sql type being an argument towards the dynamic statement (record, cursor)

A potential workaround would be to bind some id posts (SQL Type) towards the dynamic statement, and employ a rec clause to discover when the current column is null:

select