CODEWITHSUNDEEP
oracle11g
MGL
MGL

Reputation: 145

Oracle 11 g Declare error

I'm learning to create a stored procedure in Oracle 11g and I'm using SQL Developer. When I ran the statement separately they work but when I group them together into a stored procedure and added some looping I got an error that I can't understand. I'm not in luck when trying to search for the solution in the net so hoping anyone in this forum can offer some advice. Thank you!

create or replace PROCEDURE InputAddr AS   
  DECLARE v_TotalCount INT;

  BEGIN
     -- this insert statement is correct and ran just fine
     INSERT INTO table1 (column1,colum2,colum3,...)
     SELECT column11,column12,column13,...
     FROM table2

     -- counting how many records have been inserted into table1
     select count(*) into v_totalcount
     from table1

     -- loop the process 
     IF v_totalcount >= 1000
     THEN
        WHILE v_TotalCount >= 1000
        LOOP
          DECLARE 
          P_NAME VARCHAR2(200);
          P_DESCRIPTION VARCHAR2(200);
          P_COUNTRY VARCHAR2(200);

        BEGIN 
          P_NAME := 'TEST FILE';
          P_DESCRIPTION := 'TEST FILE DESC';
          P_COUNTRY := 'USA';

          INSTANTADDRESS.SENDADDRESSES ( P_NAME, P_DESCRIPTION, P_COUNTRY );
          COMMIT; 
        END;            
        END LOOP;              
     ELSE
         -- less then 1000 run the same codes once
          DECLARE 
          P_NAME VARCHAR2(200);
          P_DESCRIPTION VARCHAR2(200);
          P_COUNTRY VARCHAR2(200);            
        BEGIN 
          P_NAME := 'TEST FILE';
          P_DESCRIPTION := 'TEST FILE DESC';
          P_COUNTRY := 'USA';

          INSTANTADDRESS.SENDADDRESSES ( P_NAME, P_DESCRIPTION, P_COUNTRY );
          COMMIT; 
        END;          
     END IF
  END;

Here is the error after compiling this procedure:

Error(3,4): 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

Upvotes: 0

Views: 815

Answers (2)

Boneist
Boneist

Reputation: 23588

In addition to Ramana's answer, you're also missing some semi-colons. Welcome to semi-colon hell, I mean, PL/SQL! *{:-)

I've added them in here, for you:

create or replace procedure inputaddr
as   
  v_totalcount int;
begin
  -- this insert statement is correct and ran just fine
  insert into table1 (column1,colum2,colum3,...)
  select column11,column12,column13,...
  from   table2; -- MISSING ; ADDED!

  -- counting how many records have been inserted into table1
  select count(*)
  into   v_totalcount
  from   table1; -- MISSING ; ADDED!

  -- loop the process 
  if v_totalcount >= 1000 then
    while v_totalcount >= 1000
    loop
      declare 
        p_name varchar2(200);
        p_description varchar2(200);
        p_country varchar2(200);
      begin 
        p_name := 'TEST FILE';
        p_description := 'TEST FILE DESC';
        p_country := 'USA';

        instantaddress.sendaddresses ( p_name, p_description, p_country );
        commit; 
      end;            
    end loop;              
  else
    -- less then 1000 run the same codes once
    declare 
      p_name varchar2(200);
      p_description varchar2(200);
      p_country varchar2(200);            
    begin 
      p_name := 'TEST FILE';
      p_description := 'TEST FILE DESC';
      p_country := 'USA';

      instantaddress.sendaddresses ( p_name, p_description, p_country );
      commit; 
    end;          
  end if; -- MISSING ; ADDED!
end inputaddr;
/

Upvotes: 0

ramana_k
ramana_k

Reputation: 1933

You don't need DECLARE on line 2.

"AS" acts as DECLARE here.

Upvotes: 1

Related Questions