CODEWITHSUNDEEP
sqloracle-database
SouravM
SouravM

Reputation: 209

Using Variable declared in Declare section(Anonymous Block)

I want to use a declared variable, that I declare in Declare section ,in Begin End section of anonymous block with out using into statement.Is that possible?? E.g

Declare
   week number :=6;
Begin
   select col1,col2+week from table;
end;

Upvotes: 0

Views: 4739

Answers (2)

Alaa
Alaa

Reputation: 575

It's impossible to use select in an oracle pl-sql block without using INTO
because oracle doesn't accept select statement without use of into statement so, your block after modify will become like this :

Declare
   week number :=6;
   Column1 table.col1%type;
   Column2 table.col2%type;
Begin
   select col1,col2+week into Column1, Column2 from table;
end;

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231681

Since an anonymous block cannot return any data to the caller, what would you want to happen if this block was executed? You couldn't return a cursor to the caller so if you're not selecting the data into local variables, I'm not sure what you want to happen.

Potentially, you just want a SQL*Plus script, i.e.

SQL> variable week number;
SQL> exec :week := 6;

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1* select empno, hiredate+:week from emp
SQL> /

     EMPNO HIREDATE+
---------- ---------
      7369 23-DEC-80
      7499 26-FEB-81
      7521 28-FEB-81
      7566 08-APR-81
      7654 04-OCT-81
      7698 07-MAY-81
      7782 15-JUN-81
      7788 25-APR-87
      7839 23-NOV-81
      7844 14-SEP-81
      7876 29-MAY-87

     EMPNO HIREDATE+
---------- ---------
      7900 09-DEC-81
      7902 09-DEC-81
      7934 29-JAN-82
      1234

15 rows selected.

Upvotes: 3

Related Questions