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;
Add a comment
|
2 Answers
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.
2 Comments
SouravM
Please provide me the script in Sql Navigator
Justin Cave
@SouravM - Does SQL Navigator support the SQLPlus substitution variable syntax? If not, does it have it's own syntax? Many GUIs will support at least a subset of SQLPlus syntax.
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;
