Improve company productivity with a Business Account.Sign Up

x
?
Solved

put count(*) into variable

Posted on 2014-01-23
4
Medium Priority
?
612 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

595 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