Good Morning you all
I am writing a sql procedure but I keep getting errors from the compiler and I really can't understand why.
I have edited the code multiple times but without success.
Can you please point me where is the issue here ?
CREATE OR REPLACE PROCEDURE gio_pro
IS
n_counter NUMBER := 1;
BEGIN
WHILE n_counter <= 5 LOOP
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ': Wait 60 seconds please.. ');
dbms_lock.sleep(60);
-- Checking conditions
IF(NOT EXISTS(SELECT 1 FROM control_proc)) THEN
CONTINUE;
IF control_proc.command = 'PRINT' THEN
dbms_output.put_line(control_proc.valuex);
IF control_proc.command = 'EXIT' THEN EXIT;
END IF;
--Empty main table
TRUNCATE TABLE control_proc;
dbms_output.put_line(to_char('The table has been successfully empty! ');
END LOOP;
END;
the procedure just does a couple of checks and it empties the table IF the condition just above is not satisfied, otherwise if ‘EXIT’ is found inside the “command” column then the procedure should stop the loop and exit the procedure.
On top of that, the line where I use the TRUNCATE operator shows error as it doesn't expect “table” after “truncate”. It doesn't really make any sense to me.
Here the DML script for the table:
DROP TABLE control_proc;
CREATE TABLE control_proc(
command VARCHAR2(16),
valuex VARCHAR2(64)
);
commit;
EDIT: For some reason the editor of this post deleted all indentation. I don't know why, but code is indented as it should be. Cheers.