CODEWITHSUNDEEP
oracle-databaseplsqlpls-00103
R. Sharma
R. Sharma

Reputation: 1

PLS-00103: Encountered the symbol when expecting one of the following:

I can't remove error

ERROR at line 18: PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exception table LONG_ double ref char time timestamp interval date binary national character nchar

My procedure:

CREATE OR REPLACE PROCEDURE create_schedule( id IN VARCHAR2,ac1st IN ARCHAR2,ac2nd IN VARCHAR2,ac3rd IN VARCHAR2 ,sleeper IN VARCHAR2)
AS 
BEGIN
DECLARE
   query_str   VARCHAR2(512);
   ac1   VARCHAR2(10);
   ac2   VARCHAR2(10);
   ac3   VARCHAR2(10);
   slp   VARCHAR2(10);
   ac1 := 'ac1st'||id;
   ac2 := 'ac2nd'||id;
   ac3 := 'ac3rd'||id;
   slp := 'sleeper'||id;

  query_str := 'alter table schedule 
  add( :1   number default ( :2) not null  ,
    :3 number default ( :4 ) not null  ,
    :5 number default ( :6) not null  ,
    :7 number default ( :8) not null    )';
  EXECUTE IMMEDIATE query_str using ac1,ac1st,ac2,ac2nd,ac3,ac3rd,slp,sleeper ;
commit;
END create_schedule ;
/

Upvotes: -2

Views: 4039

Answers (1)

Aleksej
Aleksej

Reputation: 22949

You have some mistakes ( ARCHAR2 parameter, the position of BEGIN, the unneeded DECLARE):

CREATE OR REPLACE PROCEDURE create_schedule( id IN VARCHAR2,ac1st IN VARCHAR2,ac2nd IN VARCHAR2,ac3rd IN VARCHAR2 ,sleeper IN VARCHAR2)
AS 
   query_str   VARCHAR2(512);
   ac1   VARCHAR2(10);
   ac2   VARCHAR2(10);
   ac3   VARCHAR2(10);
   slp   VARCHAR2(10);
begin   
   ac1 := 'ac1st'||id;
   ac2 := 'ac2nd'||id;
   ac3 := 'ac3rd'||id;
   slp := 'sleeper'||id;

  query_str := 'alter table schedule 
  add( :1   number default ( :2) not null  ,
    :3 number default ( :4 ) not null  ,
    :5 number default ( :6) not null  ,
    :7 number default ( :8) not null    )';
  EXECUTE IMMEDIATE query_str using ac1,ac1st,ac2,ac2nd,ac3,ac3rd,slp,sleeper ;
commit;
END create_schedule ;
/

Upvotes: 2

Related Questions