Solved

put count(*) into variable

Posted on 2014-01-23
4
590 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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

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 …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.

751 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