0

My codes goal is to cause a Phantom row. I understand the process but I'm struggling to get my first sql script to run 

CREATE OR REPLACE PROCEDURE PHANTOM

IS

DECLARE 
ENAME CHAR;
ENAME EMPLOYEE%ROWTYPE;

BEGIN

SELECT * INTO ENAME
FROM EMPLOYEE
WHERE NAME = 'Albert';

DBMS_OUTPUT.PUT_LINE(ENAME.NAME);

DBMS_LOCK.SLEEP(15);

DBMS_OUTPUT.PUT_LINE(ENAME.NAME);

 END;
 /

This is my first Script in which a variable ENAME of type char is declared and takes on the value of NAME from a table EMPLOYEE where name is equal to Albert. The variable is then called in a DMBS statement in order to return the name. My 2nd Script only contains an update an a commit to complete the phantom process.

When i try and compile this procedure i get two errors

error 1

Error(5,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:     begin function pragma procedure subtype type <an identifier>    <a double-quoted delimited-identifier> current cursor delete    exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.

error 2

Error(21,5): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge json_exists json_value json_query    json_object json_array

how can i fix this issue? Thankyou

Daniel Wong
  • 3
  • 3

1 Answers1

3

You're not too far off here. One problem is a mix-up of syntax between how you'd write an SQL script, and how you'd write a PL/SQL procedure.

If you want a standalone script, ie where the code isn't stored for later re-use, it just runs once, performs an action, then disappears, it's

DECLARE
count_of_apples NUMBER;
BEGIN
...
END;
/

If you want to define a procedure that you can re-run as many times as you like, it's

CREATE OR REPLACE PROCEDURE phantom IS
-- don't need to say DECLARE, it's implied
count_of_apples NUMBER;
BEGIN
...
END phantom; -- good practice to add the name again here
/

And then you'd

exec phantom;

to run that procedure.

The one other problem is that you've declared the same variable twice (ENAME), with two different types, which you can't do. You don't need the CHAR one, though, so you can just delete that line, and you'll be fine.

twit-twoo
  • 46
  • 4