Using Variables

[Bonediggler1]

Hi-

I learned SQL on MS SQL Server and am trying to declare and use variables now in Oracle. I am using Toad as an interface. This should be extremely simple but I cannot get it to work...below is an example of what I am trying to do:

VARIABLE VAR1 NUMBER
:VAR1 = 1
SELECT * FROM "TRANSACTION" TT WHERE TT.BLLNG_ID =:VAR1

Thank you!

 

[LKBrwnDBA]

Try this:

VARIABLE VAR1 NUMBER;
EXEC :VAR1 := 1;
SELECT *
  FROM "TRANSACTION" tt
 WHERE tt.bllng_id = :var1;

And, execute "as script"!
.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

...and since "EXEC" is a SQL*Plus command, and since SQL*Plus commands do not require a ";" as a terminator,

EXEC :VAR1 := 1

...works, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Bonediggler1]

Thank you - this doesn't throw any errors, but does prompt the user to define VAR1...even though it's already defined in the code.

What next?

 

[SantaMufasa]

...it's already defined in the code.

How are you defining it in the code? What is the actual name of the variable that you defined? If it is really defined in the code (with a value), then you should be able to successfully execute this code:

Select :varname from dual;

...where :varname is the actual name of your variable.


Let us know.




Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Bonediggler1]

Upon closer examination, Oracle is actually disregarding the first statement (VARIABLE...) and just executing the SELECT statement, as running this by itself bring up the "define bind variable" box as well. Running the VARIABLE etc. chunk of code by itslef throws an "Invalid SQL Statement" error then brings up the "define bind variable" window.

Looks like I'm back to square 1! Still don't know how to define/declare a variable and then use it in a subsequent select statement...

 

[Bonediggler1]

Thanks Mufasa. I sent the last string at exactly the same time you sent your response. Perhaps I should start over: how do I define and/or declare a variable in Oracle and then use it in a select statement?

Thanks again!!

 

[Bonediggler1]

Perhaps this is unnecessary but in SQL Server I used to do something like this:

DECLARE @VAR AS NUMBER
SET @VAR = '1'

SELECT * FROM TABLE1 WHERE TABLE1.ID = @VAR

I am trying to do the same for Oracle...

 

[SantaMufasa]

how do I define and/or declare a variable in Oracle and then use it in a select statement?

I've never encountered a case where the code that LKBrwnDBA posts doesn't work, so you can trust it. As a proof of concept, I'll execute the code that you/he posted, showing results:

SQL> desc transaction
 Name                    Null?    Type
 ----------------------- -------- ------------
 ID                      NOT NULL NUMBER(7)
 BLLNG_ID                NOT NULL NUMBER(7)
 DATE_ORDERED                     DATE
 DATE_SHIPPED                     DATE
 SALES_REP_ID                     NUMBER(7)
 TOTAL                            NUMBER(11,2)
 PAYMENT_TYPE                     VARCHAR2(6)
 ORDER_FILLED                     VARCHAR2(1)

select * from transaction;

 ID   BLLNG_ID DATE_ORDE DATE_SHIP SALES_REP_ID      TOTAL PAYMEN O
--- ---------- --------- --------- ------------ ---------- ------ -
100          4 31-AUG-92 10-SEP-92           11     601100 CREDIT Y
101          5 31-AUG-92 15-SEP-92           14     8056.6 CREDIT Y
102          6 01-SEP-92 08-SEP-92           15       8335 CREDIT Y
103          8 02-SEP-92 22-SEP-92           15        377 CASH   Y
104          8 03-SEP-92 23-SEP-92           15      32430 CREDIT Y
105          9 04-SEP-92 18-SEP-92           11    2722.24 CREDIT Y
106         10 07-SEP-92 15-SEP-92           12      15634 CREDIT Y
107         11 07-SEP-92 21-SEP-92           15     142171 CREDIT Y
108         12 07-SEP-92 10-SEP-92           13     149570 CREDIT Y
109         13 08-SEP-92 28-SEP-92           11    1020935 CREDIT Y
110         14 09-SEP-92 21-SEP-92           11    1539.13 CASH   Y
111          4 09-SEP-92 21-SEP-92           11       2770 CASH   Y
 97          1 28-AUG-92 17-SEP-92           12      84000 CREDIT Y
 98          2 31-AUG-92 10-SEP-92           14        595 CASH   Y
 99          3 31-AUG-92 18-SEP-92           14       7707 CREDIT Y
112         10 31-AUG-92 10-SEP-92           12        550 CREDIT Y

(Your and LK's code follows exactly as LK originally posted):

VARIABLE VAR1 NUMBER;
EXEC :VAR1 := 1;
SELECT *
  FROM "TRANSACTION" tt
 WHERE tt.bllng_id = :var1;

 ID   BLLNG_ID DATE_ORDE DATE_SHIP SALES_REP_ID      TOTAL PAYMEN O
--- ---------- --------- --------- ------------ ---------- ------ -
 97          1 28-AUG-92 17-SEP-92           12      84000 CREDIT Y

Let us know if you have additional questions.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Bonediggler1]

Hmmm. Must be related somehow to Toad. Guess I'll check with them next.

Thanks for the help!!

 

[LKBrwnDBA]

Thats is why I stated "execute as script". In TOAD there is a button that has a lightning icon -- that is the one you need to click on for toad to execute everything in the editor window as a script.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb