How to DECLARE variables in PL/SQL queries?

fabricator_296 · July 10, 2009, 3:49pm

Forgive me if this has been previously addressed; I could not find a forum search form (EDIT: oddly, one showed up AFTER I posted but was not there bwfore…?).

I have just started using both TDA and PL/SQL. My experience up to now has been T-SQL and Microsoft Query.

I have created a PL/SQL query in our Toad (2.1) environment and it runs successfully, returning exactly what I expect. However, there are repeated segments that could best be handled using variables.

Problem is, I cannot get common syntax to work. Variables seem to be DECLAREd okay but fail when I try to assign a value; I receive a syntax error at the := operator.

Are PL/SQL variables just not supported, or is my noobness showing?

EDIT: the code structure others have provided from similar questions (DECLARE then BEGIN/END;) is not working and I can’t figure out why not. No matter what I try I receive errors…

Message was edited by: fabricator_296

Debbie_Peabody · July 13, 2009, 6:22pm

Can you post your code or send in email to me?

Debbie

Here is some goofing around code with variables in a PL/SQL block. I like to use dbms_output.put_line to validate what is occurring.

declare
val VARCHAR2(10);
val1 VARCHAR2(10) := 'Before';
val2 VARCHAR2(10) := 'X';
BEGIN
val := 'Hello Deb';
dbms_output.put_line(val);

select val1
into val1
from dual
where DUMMY = val2;
dbms_output.put_line('execute sql 1');
dbms_output.put_line(val1);

val1 := 'After';
select val1
into val1
from dual
where DUMMY = val2;
dbms_output.put_line('execute sql 2');
dbms_output.put_line(val1);

END;