StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You appear to have some confusion about the differences between <em>bind variables</em> in Oracle and <em>substitution variables</em> in SQL*Plus.</p> <p>Let's start with substitution variables. Substitution variables are unique to SQL*Plus and are not part of the database. They won't work if you try to use them with JDBC, for example.</p> <p>Substitution variables can only hold a piece of text. If SQL*Plus encounters a substitution variable in a line of input, it will replace the variable with its text contents:</p> <pre> SQL> define subvar=X SQL> select * from dual where dummy = &subvar; old 1: select * from dual where dummy = &subvar new 1: select * from dual where dummy = X select * from dual where dummy = X * ERROR at line 1: ORA-00904: "X": invalid identifier </pre> <p>Note that SQL*Plus replaced our substitution variable with its text value with no regard for whether it gave us valid SQL. In the example above, we omitted the single quotes around <code>&subvar</code> and it gave us invalid SQL, so we got an error.</p> <p>The lines beginning <code>old</code> and <code>new</code> show us the line we entered before and after SQL*Plus applied the substitution variables. The <code>new</code> line is the line the database tried to run.</p> <p>You can enable or disable the display of the <code>old</code> and <code>new</code> lines using <code>SET VERIFY ON</code> and <code>SET VERIFY OFF</code>. You can also turn the replacement of substitution variables on or off by using <code>SET DEFINE ON</code> and <code>SET DEFINE OFF</code>.</p> <p>If we want to run the above query using the substitution variable, we must put quotes around it:</p> <pre> SQL> select * from dual where dummy = '&subvar'; old 1: select * from dual where dummy = '&subvar' new 1: select * from dual where dummy = 'X' D - X </pre> <p>If <code>&subvar</code> happens to contain a string that was a valid number (e.g. <code>5</code>), then we can get away without using the quotes, but that's only because taking out the text <code>&subvar</code> and replacing it with the text <code>5</code> happens to give us valid SQL.</p> <p>For example, suppose we have a table called <code>test</code> with the following data in it:</p> <pre> A ---------- 1 2 3 4 5 </pre> <p>Then we can do</p> <pre> SQL> define subvar=5 SQL> select * from test where a = &subvar; old 1: select * from test where a = &subvar new 1: select * from test where a = 5 A ---------- 5 </pre> <p>Bind variables, on the other hand, have types. They are not simple text values. Their values are sent to the database, and the database can also set their values.</p> <pre> SQL> variable bindvar varchar2(1); SQL> exec :bindvar := 'X'; PL/SQL procedure successfully completed. </pre> <p>You don't put quotes around a bind variable when you want to use it:</p> <pre> SQL> select * from dual where dummy = :bindvar; D - X SQL> select * from dual where dummy = ':bindvar'; no rows selected </pre> <p>In the second example above, we got no rows returned because the <code>DUAL</code> table has no rows with the <code>DUMMY</code> column containing the text <code>:bindvar</code>.</p> <p>You'll get an error if you attempt to assign a value of the wrong type to a bind variable:</p> <pre> SQL> variable bindvar number; SQL> exec :bindvar := 'X'; BEGIN :bindvar := 'X'; END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1 </pre> <p>Bind variables are a standard part of the database, and you can use them with JDBC or whichever method of connecting to the database you choose.</p> <hr> <p>Finally, <code>variable num1 number</code> and <code>var num1 number</code> both mean the same thing. They both define a bind variable <code>num1</code> of type <code>number</code>. <code>var</code> is just an abbreviation for <code>variable</code>.</p>

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