StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can do this with <a href="http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm" rel="nofollow">dynamic PL/SQL</a>. Use an <a href="http://download.oracle.com/docs/cd/B12037_01/appdev.101/b10807/13_elems017.htm" rel="nofollow"><code>EXECUTE IMMEDIATE</code></a> statement to execute a string argument as PL/SQL, which you can make up with <code>||</code> as it was intended in the question.</p> <p>Example: </p> <pre><code>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; </code></pre> <p>Or you could also assign <code>rec.column.' || nr ||' is null</code> to a variable and make the <code>PUT_LINE</code> outside the <code>EXECUTE IMMEDIATE</code> part: </p> <p><strong>UPDATE</strong>: It seems it is not possible to bind <code>BOOLEAN</code> variables, so I've modified the example to use a <code>NUMBER</code>.</p> <p><strong>UPDATE 2:</strong> There is a possible efficiency improvement, altough maybe not suitable in this case. Use a constant <code>VARCHAR</code> for the dynamic SQL, and pass in <code>nr</code> with a binded variable. This is even more efficient than using native SQL if in a large loop. I don't think <code>'rec.column.:arg is null</code> would execute as <code>'rec.column.1 is null</code>, though.</p> <pre><code> 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; </code></pre> <hr> <p><strong>UPDATE 3</strong>: Seeing that: </p> <ul> <li><p>It is not possible to access <code>rec</code> inside the dynamic SQL statement because it is undefined (out of scope), </p></li> <li><p>It seems not possible to pass a non-sql type as an argument to the dynamic statement (record, cursor) </p></li> </ul> <p>A possible workaround is to bind some id columns (SQL Type) to the dynamic statement, and use a <code>select</code> clause to find out if the current column is null: </p> <pre><code>DECLARE isnull NUMBER; rec_id NUMBER; -- Identifier of the fetched record BEGIN rec_id := rec.id; FOR nr IN 1..102 LOOP DBMS_OUTPUT.PUT_LINE(nr); EXECUTE IMMEDIATE 'SELECT 1 FROM my_table WHERE id = :idarg ' || ' AND column_' || nr || ' IS NULL' INTO isnull USING rec_id; IF isnull = 1 THEN DBMS_OUTPUT.PUT_LINE('test'); END IF; END LOOP; END;</code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload