Declaring and using variables in PL-SQL

Question

I am new to PL-SQL. I do not understand why I am getting the error "PLS-00428: an INTO clause is expected in this SELECT statement"

What I'm trying to accomplish is to create a variable c_limit and load it's value. I then want to use that variable later to filter data.

Basically I am playing around in the demo db to see what I can/can't do with PL-SQL.

The code worked up to the point that I added "select * from demo_orders where CUSTOMER_ID = custID;"

declare 
c_limit NUMBER(9,2);
custID INT;

BEGIN
  custID := 6;

  -- Save the credit limit
  select credit_limit INTO c_limit
  from  demo_customers cust
  where customer_id = custID;

  select * from demo_orders where CUSTOMER_ID = custID;

  dbms_output.Put_line(c_limit);
END;

You have to select * from demo_orders where CUSTOMER_ID = custID; into something, as you did it in the first query. It looks like you don't need this query anyway, so just comment or delete it and this PL/SQL block will work. — stee1rat
– stee1rat, Commented Jan 18, 2016 at 16:08

Stephen Reindl · Accepted Answer · 2016-01-18 23:28:27Z

1

If you are using a SQL SELECT statement within an anonymous block (in PL/SQL - between the BEGIN and the END keywords) you must select INTO something so that PL/SQL can utilize a variable to hold your result from the query. It is important to note here that if you are selecting multiple columns, (which you are by "SELECT *"), you must specify multiple variables or a record to insert the results of your query into.

for example:

SELECT 1 
INTO v_dummy 
FROM dual;

SELECT 1, 2 
INTO v_dummy, v_dummy2 
FROM dual;

It is also worth pointing out that if your SELECT * FROM.... will return multiple rows, PL/SQL will throw an error. You should only expect to retrieve 1 row of data from a SELECT INTO.

edited Jan 18, 2016 at 23:28

Stephen Reindl

5,8292 gold badges36 silver badges40 bronze badges

answered Jan 18, 2016 at 22:46

Amy Grange

2551 silver badge6 bronze badges

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

Comments

Gunjan Shakya · Accepted Answer · 2016-01-18 19:27:14Z

0

Looks like the error is from the second select query.

select * from demo_orders where CUSTOMER_ID = custID;

PL-SQL won't allow a standalone sql select query for info.

http://pls-00428.ora-code.com/

You need to do some operation with the second select query

edited Jan 18, 2016 at 19:27

answered Jan 18, 2016 at 16:59

Gunjan Shakya

1941 silver badge10 bronze badges

Collectives™ on Stack Overflow

Declaring and using variables in PL-SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related