Thursday 8 September 2011

How would I use a & operator to insert different values into FOR-LOOP variables

Question:
Can I use a & operator to insert different values into for-loop variables? Currently when I try that approach, SQL*Plus asks for a value initially and then that value is used for all remaining iterations.

Answer:

PL/SQL is an embedded language, so it does not accept input from a user. Instead, you must rely on the host environment. For example, with SQL*Plus, you can use substitution variables, as in:
BEGIN
my_procedure ('&your_string');
END;
/
It is impossible, however, to place this substitution variable within a PL/SQL loop and have SQL*Plus repeatedly prompt you to provide a new value with each iteration of the loop.
Bottom line: you will need to build a customized front-end in some other language/environment—such as modPLSQL, HTML DB, Java, or Oracle JDeveloper—if you have non-trivial data entry requirements.

No comments:

Post a Comment