In SQL* Plus it's the ampersand sign "&" by default.ĭrop table mytest create table mytest( my_id number ,my_name varchar2(10)) insert into mytest values (1, 'John') insert into mytest values (2, 'Jane') insert into mytest values (3, 'Dave') insert into mytest values (4, 'Mike') insert into mytest values (5, 'Pete') commit select my_name from mytest where my_id = &my_param I'd assume there's character that is used as a "substitution" character. ToolTimeTabor wrote:So, in SQL Developer, what would I have to do to make this statement work where Q a variable?SELECT * FROM gl_periods WHERE QUARTER_NUM = Q How do I declare, set and use the Q in SQL Developer? In particular, they are NOT terminated by semicolon. Note that both the VARIABLE command and the EXEC command are SQL*Plus (scripting language) commands, not SQL and not PL/SQL. Then, if you must execute the query again for another value of the variable, you only need to run an EXEC command again, similar to the one above. I should have made that comment OUTSIDE the "code" area in my reply. Variable q1 numberexec :q1 := 1 - or whatever value you must use as inputselect * from gl_periods where quarter_num = :q1 ĮDIT: The comment after the EXEC command should not be copied and pasted (SQL*Plus doesn't support comments preceded by two dashes).
In the editor, before you select and execute the query, you will have to define the variable and give it a value. If you execute as a "script" (F5, or the second little icon at the top), you will likely get an error, telling you that the variable is not defined. In SQL Developer, if you execute this as a "Statement" (CTRL + ENTER, or the first little icon at the top), you will be prompted for a value for Q1. Select * from gl_periods where quarter_num = :q1 (I won't go into this - a long discussion with one simple conclusion.) SUBSTITUTION variables (as defined and used in SQL*Plus, and supported by SQL Developer) are part of the scripting language (SQL*Plus), they are replaced with the supplied value in the text of the query before the query is ever seen by the database.įor many reasons, it is best to use bind variables, not substitution variables. Strictly speaking, SQL only understands BIND variables. 1.7K Training / Learning / Certificationįirst you need to decide what kind of variable you want to use.165.3K Java EE (Java Enterprise Edition).7.9K Oracle Database Express Edition (XE).3.8K Java and JavaScript in the Database.