Usage of Variable in the sql Prompt

[motto]

Hi,<br><br>&nbsp;&nbsp;&nbsp;How to declare a variable in the<br>SQL&gt;Prompt and use the same in any PL/SQL<br>Block.Hope got my question.<br><br>Thanks in advance<br>Motto

 

[Guest7112023]

If you mean from within SQL*Plus:<br>'SET DEFINE ON' and then prefix your variable names with '&', eg.<br>&nbsp;&nbsp;&nbsp;SELECT column1<br>&nbsp;&nbsp;&nbsp;FROM&nbsp;&nbsp;&nbsp;table<br>&nbsp;&nbsp;&nbsp;WHERE&nbsp;&nbsp;column2 = '&my_variable'<br>SQL*Plus will prompt you for the value every time it runs.<br>If you use '&&' as a prefix the variable will be kept for the<br>remainder of the SQL*Plus session.<br><br>I hope this answers your question.

 

[yaffle]

The use of & or && as variable markers may not be what you want here. These will simply perform text substitution within a SQL*Plus script. If you want to reference your value from within a PL/SQL stored procedure for example, you'll need to declare a bind variable.<br>To declare a PL/SQL variable in SQL*Plus, use the variable command, then reference it using a colon, e.g.<FONT FACE=monospace><br>SQL&gt; variable a number;<br>SQL&gt; execute :a := 2;<br><br>PL/SQL procedure successfully completed<br><br>SQL&gt; print a<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A<br>---------<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2<br><br>SQL&gt; select :a * 4 from sys.dual;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:A*4<br>---------<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8<br><br>SQL&gt;</font><br>I hope this helps.