end-of-file error occur while creating a procedure in oracle

Question

I am new to oracle database. I use oracle database 19c. I try to create a procedure by declaring a cursor. But i get end of file error due to syntax error. Also the procedure is invalid during execution. Thanks in advance !

Code:

kIsH@Xhydra<>create or replace procedure sw as
        cursor cevent
        IS
                select sw.sid,sw.seq#,sw.event,sw.p1text,
                         sw.p1,sw.p1raw,sw.p2text,sw.p2,
                         sw.p2raw,sw.p3text,sw.p3,sw.p3raw,
                         sw.wait_time,sw.seconds_in_wait,
                         sw.state,s.serial#,s.username,
                         s.osuser,s.paddr,s.logon_time,
                         s.process,s.sql_hash_value,s.saddr,
                         s.module,s.row_wait_obj#,s.row_wait_file#,
                         s.row_wait_block#,s.row_wait_row#
                from v$session_wait sw, v$session s
                where sw.sid = s.sid
                and s.username is not null
                and s.type <> 'BACKGROUND';
        /
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17
Warning: Procedure created with compilation errors.

Execution:

kIsH@Xhydra<>exec sw;
BEGIN sw; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SYS.SW is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Error:

LINE/COL ERROR
-------- -----------------------------------------------------------------
16/29    PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         begin function pragma procedure subtype type <an identifier>
         <a double-quoted delimited-identifier> current cursor delete
         exists prior

SYS.SW is invalid Usually not a good idea to create stored procedures in SYS schema. — Abra
– Abra, Commented Sep 11, 2022 at 6:01

doberkofler · Accepted Answer · 2022-09-11 05:43:18Z

Independently from the fact that the procedure currently has no functionality, the body of the procedure is not complete. You must at least have an empty begin/end block.

create or replace procedure sw as
cursor cevent
        IS
                select sw.sid,sw.seq#,sw.event,sw.p1text,
                         sw.p1,sw.p1raw,sw.p2text,sw.p2,
                         sw.p2raw,sw.p3text,sw.p3,sw.p3raw,
                         sw.wait_time,sw.seconds_in_wait,
                         sw.state,s.serial#,s.username,
                         s.osuser,s.paddr,s.logon_time,
                         s.process,s.sql_hash_value,s.saddr,
                         s.module,s.row_wait_obj#,s.row_wait_file#,
                         s.row_wait_block#,s.row_wait_row#
                from v$session_wait sw, v$session s
                where sw.sid = s.sid
                and s.username is not null
                and s.type <> 'BACKGROUND';
begin
   null;
end;
/

Collectives™ on Stack Overflow

end-of-file error occur while creating a procedure in oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related