Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

put count(*) into variable

Posted on 2014-01-23
4
Medium Priority
?
607 Views
Last Modified: 2014-01-23
Hello,

When I execute the script :
DECLARE
        num NUMBER;
begin
select count(*) into num from dba.table T1;
select num from dual;
end;
/


ORA-06550: line 5, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

How can I resolve it?

Thanks
0
Comment
Question by:bibi92
  • 3
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39803828
This line puts the value into a variable.

       select count(*) into num from dba.table T1;


This line is illegal, you must put your returned values somewhere
      select num from dual;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39803837
I assume you're trying to see value of "num" by doing the second select.  Unless you're in 12c you can't have implicit result sets.

try this...

set serveroutput on
DECLARE
        num NUMBER;
begin
select count(*) into num from dba.table T1;
dbms_output.put_line(num);
end;
/

Open in new window



if viewing the result is not the intent, then please explain what you want to have happen and that will drive how to resolve it.
0
 

Author Comment

by:bibi92
ID: 39803846
This line is illegal, you must put your returned values somewhere : ok, how?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39803862
same way you did in the previous line


DECLARE
        num NUMBER;
        num2 NUMBER;
begin
select count(*) into num from dba.table T1;
select num into num2 from dual;
end;
/


however, if you simply want to copy a variable you should not use sql to do it, simply assign the variable directly



DECLARE
        num NUMBER;
        num2 NUMBER;
begin
select count(*) into num from dba.table T1;
num2 := num;
end;
/
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question