Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

put count(*) into variable

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
bibi92
Asked:
bibi92
  • 3
1 Solution
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
bibi92Author Commented:
This line is illegal, you must put your returned values somewhere : ok, how?
0
 
sdstuberCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now