Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

put count(*) into variable

Posted on 2014-01-23
4
Medium Priority
?
599 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

688 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