In Sql Server, often times when I'm testing the body of a stored procedure, I copy the body into SSMS, DECLARE the variables at the top of the page, set them to some sample values, and execute the body as-is.
For Example, if my proc is
CREATE PROC MySampleProc
@Name VARCHAR(20)
AS
SELECT @Name
Then my test sql would be
DECLARE @Name VARCHAR(20)
SET @Name = 'Tom'
SELECT @Name
What is the Oracle PL/SQL equivalent to this?
This is the closest that I've come up with, but I'm getting "PLS-00428: an INTO clause is expected in this SELECT statement"
DECLARE
myname varchar2(20);
BEGIN
myname := 'Tom';
select myname from DUAL;
END;
This is a better example of what I'm really trying to do:
DECLARE
myname varchar2(20);
BEGIN
myname := 'Tom';
SELECT *
FROM Customers
WHERE Name = myname;
END;
But again, it wants an 'INTO' when really I just want the records printed on the screen, not stored in another table....
RESOLVED:
Thanks to @Allan, I've got it working well enough. Oracle SQL Developer apparently remembers the parameter values you supply it with. PL/SQL Developer, however, wants nothing to do with this....
If you "Run As Script", it will abide by your defaults, but it will only return results as ASCI text, not in a grid/spreadsheet
This question is related to
sql-server
oracle
tsql
plsql
oracle-sqldeveloper
Variables are not defined, but declared.
This is possible duplicate of declare variables in a pl/sql block
But you can look here :
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i27306
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/overview.htm
UPDATE:
Refer here : How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?
In Oracle PL/SQL, if you are running a query that may return multiple rows, you need a cursor to iterate over the results. The simplest way is with a for loop, e.g.:
declare
myname varchar2(20) := 'tom';
begin
for result_cursor in (select * from mytable where first_name = myname) loop
dbms_output.put_line(result_cursor.first_name);
dbms_output.put_line(result_cursor.other_field);
end loop;
end;
If you have a query that returns exactly one row, then you can use the select...into...
syntax, e.g.:
declare
myname varchar2(20);
begin
select first_name into myname
from mytable
where person_id = 123;
end;
Source: Stackoverflow.com