CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqloracle11g
Adarsh D
Adarsh D

Reputation: 581

PLSQL statement executes but not getting output

I have this procedure to create a table 'circle' and insert some radius and corresponding area to it, this is my code

create or replace procedure table1
 is
BEGIN
  execute immediate'drop table circle';
  execute immediate'create table circle (r int, a int)';

end;

declare 
r int;
ar float;

begin   
   for r in 3 .. 7 loop 
        ar:=3.14*r*r;
        INSERT INTO circle VALUES(r,ar);
   end loop;
   execute immediate 'select * from circle';
end;

But when I run this I get this warning

Warning: Procedure created with compilation errors.

and when I try to find the table I get

 SQL> select * from circle;
 select * from circle
          *
 ERROR at line 1:
 ORA-00942: table or view does not exist

what is wrong in my code?

Upvotes: 1

Views: 72

Answers (2)

Namandeep_Kaur
Namandeep_Kaur

Reputation: 378

In the code mentioned above, you're just creating a procedure. It also needs to be executed successfully before using the table in anonymous block. I've created your procedure here and the procedure is created successfully.

Just when you try to execute it, handle the exception for when the table circle doesn't exist in your procedure's code and then execute (or call) it. Further, you could use the anonymous block to insert the values in your table.

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74710

If the table doesn't exist, attempting to drop it will fail. The rest of the code won't run, so your table is never created

Carry out your drop attempt and catch the error, then proceed

This answer has more info: Oracle: If Table Exists

Upvotes: 0

Related Questions