How do I declare and use variables in PL/SQL like I do in T-SQL?

Question

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

Draper

This is the closest I found to my answer, but they don't show how to use the variable within the select list of the query, and that keeps throwing me errors. plsql-tutorial.com/plsql-variables.htm – Draper

There are two problems in your last piece of code. 1) There's no colon needed in front of variable, neither in the assignment nor in the SELECT statement. 2) The result of the SELECT statement needs to go somewhere, e.g. you declare a second variable and write SELECT Name INTO Name2 FROM DUAL. – Goodoh

What if I don't want the results to go somewhere, I just want them to show up in a grid in PL/SQL Developer? – Draper

That's not possible (unless you use rather advanced features). T-SQL and PL/SQL are rather different in that aspect. If you want to test a stored procedure, then you might want to use DBMS_OUTPUT.PRINT_LINE to write your result in a way that most Oracle tools (such as TOAD, SQL Developer, PL/SQL Developer) can catch and display. – Goodoh

I have struggled with that too in the past. You have to use select into and print the output with dbms_output.put_line(myname); Oracle uses cursors to return results, SQL Server doesn't use that. – Bowles

@tunmisefasipe, does that work for multiple field select statements? Do you have an example? – Draper

Answer 1

Revised Answer

If you're not calling this code from another program, an option is to skip PL/SQL and do it strictly in SQL using bind variables:

var myname varchar2(20);

exec :myname := 'Tom';

SELECT *
FROM   Customers
WHERE  Name = :myname;

In many tools (such as Toad and SQL Developer), omitting the var and exec statements will cause the program to prompt you for the value.

---

Original Answer

A big difference between T-SQL and PL/SQL is that Oracle doesn't let you implicitly return the result of a query. The result always has to be explicitly returned in some fashion. The simplest way is to use DBMS_OUTPUT (roughly equivalent to print) to output the variable:

DECLARE
   myname varchar2(20);
BEGIN
     myname := 'Tom';

     dbms_output.print_line(myname);
END;

This isn't terribly helpful if you're trying to return a result set, however. In that case, you'll either want to return a collection or a refcursor. However, using either of those solutions would require wrapping your code in a function or procedure and running the function/procedure from something that's capable of consuming the results. A function that worked in this way might look something like this:

CREATE FUNCTION my_function (myname in varchar2)
     my_refcursor out sys_refcursor
BEGIN
     open my_refcursor for
     SELECT *
     FROM   Customers
     WHERE  Name = myname;

     return my_refcursor;
END my_function;

Five

I think your third example is what I'm looking for. How would I run that in PL/SQL Developer? If I use that syntax, I get "invalid SQL statement" on the var line? Or do I just need to use Toad? – Draper

I left a colon out of the exec statement initially. With the colon, if you "Run Script" it should work in SQL Developer, but the results will be output to the Script Output pane, rather than the Query Result grid. Given limited experimentation, the only way I've found to get it to the grid is to omit the var and exec statements and let SQL Developer prompt you for a value. – Five

I keep just getting "ORA-01008: not all variables bound". Should I be running this as a "SQL Window" or a "Test Window" or some other window? – Draper

Exactly what tool are you using? I was under the impression that you were using Oracle's free SQL Developer, but that doesn't jibe with your last comment. – Five

I was using PL/SQL Developer, not Oracle Developer. Installed that just now, and it prompted for the parameters just like you described. I'd still like to know how to use script defined passwords, but this is good enough. – Draper

In Toad Run As Second mouse button -> "Execute Script" so It not promt for you for value. – Sakai

This works, and I'm sincerely thankful for this answer. However, Oracle is way too finicky compared to T-SQL and Postgresql. It's also sad that I had to read through SEVERAL questions and answers just to find one that worked on my system. – Unload

Answer 2

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;

Outbrave

Answer 3

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?

Irritative

Maybe I'm not asking the right question. Based on your link, I get how to assign the value, but how do I select it out and show it in a result set? – Draper

What you mean by show it in a result set? you want to select value as a function? – Irritative

Updated the tail of the question with a better example of what I am trying to do / answer your followup question. – Draper

Then you need to create function instead of procedure and do SELECT MY_FUNC() FROM DUAL; Function can return result set.See my update – Irritative

Are you saying there is no way to run test code that uses variables without committing a function or procedure back to the oracle database? – Draper

No, I'm saying that if you want to use reusable code, you should create function that return result set. – Irritative

Ah, see I don't want re-usable code, that's my point. I want to know how to re-use the body of what was a procedure (and had parameters) in what is now just a 1-run PL/SQL Developer window, with statically defined/assigned variables. – Draper

well, if you copy the body of an Oracle procedure, it will have the same "constraint" in a 1-run pl-sql developer window... So it will ask for select into, or cursors... U have a "real" example of working procedure you wanna test ? – Thoracotomy