CODEWITHSUNDEEP
databaseplsqlvariable-declaration
Henry Chong
Henry Chong

Reputation: 3

Compilation error when declaring variables in BEGIN..END procedure PL/SQL

I have a compile error that unable to declare variables in procedure PL/SQL which trying solutions from other sources. Can anybody help to check what is the problem?

CREATE OR REPLACE PROCEDURE add_vaccine
(p_id IN VARCHAR, d_id IN VARCHAR, p_vdate IN DATE, p_vaccinated IN VARCHAR)

AS
    
    
BEGIN

    DECLARE counter NUMBER(MAX);

    --Prompt user to input vaccination and visit detail
    DBMS_OUTPUT.PUT_LINE('Patient ID = ' || p_id);
    DBMS_OUTPUT.PUT_LINE('Doctor ID = ' || d_id);
    DBMS_OUTPUT.PUT_LINE('Date = ' || p_vdate);
    DBMS_OUTPUT.PUT_LINE('Vaccinated = ' || p_vaccinated);
    
    

END;
/

The code is unfinished but I'm stuck at the variable declaration.

Upvotes: 0

Views: 591

Answers (2)

T.kowshik Yedida
T.kowshik Yedida

Reputation: 195

Whenever you have a DECLARE section, you must have a BEGIN and END with some code.

If you don't need to declare inside, you can always declare them after AS.

CREATE OR REPLACE PROCEDURE STH()
AS
<DECLARE VARIABLES HERE>
BEGIN

DECLARE
<YOU CAN DECLARE HERE AS WELL>
BEGIN 
    DBMS_OUTPUT.PUT_LINE('HELLO');
END;
-- Do something here
END;

Upvotes: 2

Connor McDonald
Connor McDonald

Reputation: 11596

There is an implict DECLARE before the first BEGIN in your procedure hence

SQL> CREATE OR REPLACE PROCEDURE add_vaccine
  2  (p_id IN VARCHAR, d_id IN VARCHAR, p_vdate IN DATE, p_vaccinated IN VARCHAR)
  3  AS
  4
  5      counter NUMBER;
  6  BEGIN
  7      --Prompt user to input vaccination and visit detail
  8      DBMS_OUTPUT.PUT_LINE('Patient ID = ' || p_id);
  9      DBMS_OUTPUT.PUT_LINE('Doctor ID = ' || d_id);
 10      DBMS_OUTPUT.PUT_LINE('Date = ' || p_vdate);
 11      DBMS_OUTPUT.PUT_LINE('Vaccinated = ' || p_vaccinated);
 12  END;
 13  /

Procedure created.

Upvotes: 0

Related Questions