Oracle PL/SQL select into variables

Question

I am trying to run the following query in SQL Developer, but I am receiving an error. I am trying to declare two local variables (var_num1 and payDate) and then set the variables. Does anyone know what I can be doing incorrectly? I know Oracle SQL is a little different than SQL Server.

DECLARE
  var_num1 number; 
  payDate date;
 BEGIN 
  var_num1 := 100; 
  payDate := '10/1/2013'
  BEGIN 
    SELECT * FROM Paycode WHERE PaycodeID = var_num1 and PaycodeDate = payDate;
  End;
END;

Error report:
ORA-06550: line 6, column 2:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

That's a PL/SQL block, not a query.

Jeffrey Kemp
– Jeffrey Kemp

2013-12-10 08:22:35 +00:00
Commented Dec 10, 2013 at 8:22 — Jeffrey Kemp
– Jeffrey Kemp, Commented Dec 10, 2013 at 8:22

tvm · Accepted Answer · 2013-12-09 15:58:29Z

7

You cannot use SELECT without INTO clause in PL/SQL. The output of the SELECT must be stored somewhere. Eg. table or variable or perhaps a record. See example below how to store result of the SELECT statement into record.

DECLARE
  var_num1 number; 
  payDate date;
  v_result Paycode%ROWTYPE;
BEGIN 
  var_num1 := 100; 
  payDate := '10/1/2013';

    SELECT * INTO v_result
    FROM Paycode
    WHERE PaycodeID = var_num1 and PaycodeDate = payDate;

END;

edited Dec 9, 2013 at 15:58

answered Dec 9, 2013 at 15:52

tvm

3,45929 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user3083460 Over a year ago

THank you for your response. Is "v_result" a variable or a table? I do not want to create a table.

tvm Over a year ago

It is basically a row stored inside variable (some might call that object). It is common way to store content of whole row when selecting all columns in the table (using *), see oracle.com/technetwork/issue-archive/2012/12-may/…

user3083460 Over a year ago

Oh great, so it is not actually creating a physical row or table? This is just stored in memory while the query is being executed, correct? Thanks for your help.

user3083460 Over a year ago

When I try this, the result is "annoymous block completed", it doesn't actually select the result.

GriffeyDog Over a year ago

@user3083460 It ran the select statement. You just didn't do anything useful with the result.

|

Tomás · Accepted Answer · 2013-12-09 21:39:38Z

0

If you want to get a resultset back in SQL Developer with this code block you will need to open a ref cursor for the query e.g.

DECLARE
  refCur REF CURSOR;
  refc refCur;
  var_num1 number; 
  payDate date;
  v_result Paycode%ROWTYPE;
BEGIN 
  var_num1 := 100; 
  payDate := '10/1/2013';

OPEN refc FOR
    SELECT * 
    FROM Paycode
    WHERE PaycodeID = var_num1 and PaycodeDate = payDate;

END;

answered Dec 9, 2013 at 21:39

Tomás

5657 silver badges23 bronze badges

Collectives™ on Stack Overflow

Oracle PL/SQL select into variables

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related