PLSQL statement executes but not getting output

Question

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?

Your code is creating a procedure, not a table. Once you create your procedure successfully, execute it and then you should see the table. An IDE like SqlDeveloper should give better info about your compilation error. — grinder22, Nov 02 '18 at 19:10

score 1 · Answer 1 · answered Nov 03 '18 at 05:36

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.

score 0 · Answer 2 · answered Nov 02 '18 at 19:10

0

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

answered Nov 02 '18 at 19:10

Caius Jard

72,509
5
49
80

PLSQL statement executes but not getting output

2 Answers2