Using declare variables in Oracle

Question

I am having a syntax issue with the declaration in Oracle. I am using these variables on MS SQL server and they work just fine; however, how do I declare these in Oracle?

Use in MS SQL server:

DECLARE @FROM_DT DATETIME
DECLARE @END_DT  DATETIME
DECLARE @LOCATION  VARCHAR(100)

SET @FROM_DT = '04/01/2011'
SET @END_DT =  '05/09/2011'
SET  @LOCATION ='VA'

user330315 · Accepted Answer · 2011-05-11 19:01:14Z

4

You cannot declare variables outside of a PL/SQL block.

The format of variable declarations inside a PL/SQL block is described very detailed in the manual (including examples):

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/fundamentals.htm#CIHGGIAH

answered May 11, 2011 at 19:01

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Álvaro González Over a year ago

Simple but informative. «Cannot declare variables outside of a PL/SQL block» is the important bit most tutorials omit. Also, other documents explain the VARIABLE syntax and do not mention it's a SQL*Plus internal command.

Fırat Küçük · Accepted Answer · 2012-09-13 11:07:00Z

0

You may pass variables to other sql files. It's not exactly what you want but it helps me to solve some problems without using blocks.

For Instance my config.sql file:

@drop.sql 'MY_USER'

my drop.sql file:

DROP USER "&1" CASCADE;

answered Sep 13, 2012 at 11:07

Fırat Küçük

6,0632 gold badges55 silver badges57 bronze badges

Collectives™ on Stack Overflow

Using declare variables in Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related