Using Variables in WHERE clauses

[anthonycervantes]

OK, I am not a Oracle guy, I know Microsoft, but I am writing a script for an Oracle 8 DB. I want to declare variables, set those variables and then use them in a WHERE clause of a SELECT statement. Here is how I would do it for SQL Server 2K:

DELCARE @myvariable varchar(10)

SELECT *
FROM mytable
where name = @myvarible

GO

Here is what I have so far for my Oracle script:

DECLARE
cp_id INTEGER := 95967;

BEGIN

Select *
FROM mytable
WHERE mytable.COUNTERPARTY_ID = cp_id
END;


This doesn't work. Now what am I missing. This should not be hard to do. It is a very basic thing. Help me out please.

AC

 

[sem]

Using of DECLARE means that you're using pl/sql, that does not support select without INTO clause. You may either open explicit cursor or continue to use SELECT, expecting the result to be a single row, but in any case you should declare some variable to fetch you result to.

But if you try to run some parametrized query from sql*plus you may use either bind or substituting variable. The syntax for bind variable is:

var cp_id number
exec :cp_id := 95967

Select *
FROM mytable
WHERE mytable.COUNTERPARTY_ID = :cp_id
END;

For substituting variable use &:

Select *
FROM mytable
WHERE mytable.COUNTERPARTY_ID = &1
END;

You may pass the value of &1 as a script parameter or enter it interectively.

 

[anthonycervantes]

I could not get the bind variable solution to work. Did you leave out some syntax? That is the one I would like to use. I did however get the substituting variable solution to work. Thanks.

It barked at the "var" in the:

var cp_id number
exec :cp_id := 95967

Here is what I have:


var :cp_id INTEGER
exec :cp_id := 95967
var :trade_date
exec :trade_date := '10/4/2001'
var :trade_hr INTEGER
exec :trade_hr := 2

Select MYTABLE.COUNTERPARTY_ID,
MYTABLE.TRADE_DT,
MYTABLE.POWER_LOCATION_ID,
MYTABLE.TRADE_HOUR_NUM,
MYTABLE.TRADE_QTY,
FROM MYTABLE
WHERE MYTABLE.COUNTERPARTY_ID = :cp_id
AND MYTABLE.TRADE_DT = TO_DATEtrade_date, 'MM/DD/YYYY')
AND MYTABLE.TRADE_HOUR_NUM = :trade_hr
END;

Tell me what I am missing.