Tuesday, October 3. 2006
PL/SQL Developer substitution variables
I like Microsoft Sql Server, I like it a lot. But there are times when you are forced to work with an other RDBMS like Oracle. I had to rewrite a stored procedure for Oracle using PL/SQL developer. One cannot do without this tool when working with Oracle! When I write complicated stored procedures for Sql Server I like to write the query as a select statement first. I can declare variables and use them in my select statement so I can easely convert the query to a stored procedure later:
--declare variable
declare @p_customerID char(5)
--initialize variable
SET @p_customerID = 'ALFKI'
--sql query with parameter
SELECT * FROM Customers
WHERE CustomerID = @p_customerID
I was looking for a similar approach for Oracle with PL/SQL Developer but had a hard time finding one. After some time fiddeling with declare statements and test scripts I found substitution variables. With PL/SQL Developer, in a Sql window, you can use so called substitution variables. By specifying &deptno in the SQL text, you will be prompted for a value for this variable. You can even set default values by specifying &<name="deptno" default="102">.
