CODEWITHSUNDEEP
sqloracle-databaseplsql
user1630812
user1630812

Reputation: 51

Creating an SQL procedure gives compilation error even though code block runs fine when executed separately

I'm trying to run this block of code in a procedure.

DECLARE sharedpool FLOAT;
BEGIN
select bytes/1024/1024 into sharedpool from v$sgastat where pool='shared pool' and name like '%free memory';
insert into tempstats1(stat,cdate) values(sharedpool,sysdate);
commit;
END;

When run like this it executes successfully and the table is updated.I want to add this block to a procedure and schedule a job to run it periodically.

CREATE OR REPLACE PROCEDURE temp_insert1 IS
DECLARE sharedpool FLOAT;
BEGIN
select bytes/1024/1024 into sharedpool from v$sgastat where pool='shared pool' and name like '%free memory';
insert into tempstats1(stat,cdate) values(sharedpool,sysdate);
commit;
END;

If I run this, it shows a warning that the procedure was created with compilation errors.Why isn't it compiling properly?Can someone please explain where I've gone wrong?

Upvotes: 0

Views: 429

Answers (1)

Iswanto San
Iswanto San

Reputation: 18569

Try to remove DECLARE from your stored procedure:

CREATE OR REPLACE PROCEDURE temp_insert1
IS
   sharedpool   FLOAT;
BEGIN
   SELECT bytes / 1024 / 1024
     INTO sharedpool
     FROM v$sgastat
    WHERE pool = 'shared pool' AND name LIKE '%free memory';

   INSERT INTO tempstats1 (stat, cdate)
        VALUES (sharedpool, SYSDATE);

   COMMIT;
END;

From @DavidAldridge comment:

You can remove your variable declaration like this:

CREATE OR REPLACE PROCEDURE temp_insert1
    IS      
    BEGIN

       INSERT INTO tempstats1 (stat, cdate)
       SELECT bytes / 1024 / 1024, SYSDATE
         FROM v$sgastat
        WHERE pool = 'shared pool' AND name LIKE '%free memory';

       COMMIT;
    END;

Upvotes: 1

Related Questions