Error: PLS-00103: Encountered the symbol "/"

Question

I am new to Pl Sql. I am trying to run procedure and getting these errors:

Encountered the symbol "Create" with begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge:

I have tried searching for what causes these errors and for examples similar to this, but results were not sufficient. And this is my script:

 CREATE OR REPLACE PROCEDURE CommandScript IS
BEGIN 
  EXECUTE IMMEDIATE 'DROP TABLE sf_tmp'; 
  EXCEPTION
     WHEN OTHERS THEN IF SQLCODE != -942 
       THEN RAISE; 
       END IF; 

create table sf_tmp(smcard varchar2(17),p_line varchar2(1000));  

INSERT INTO dec_tmp(DECSCNR,DECSCPSNR)
     SELECT TRIM (SUBSTRB (qinputstring, INSTR (qinputstring, '|', 1, 2) + 1,11)) dec_nr,
        decoders.decscpsnr FROM bgqueue, decoders WHERE qworktype = 10
        AND decoders.decscpsnr > 0 AND decoders.decscnr =
        TRIM (SUBSTRB (qinputstring,INSTR (qinputstring, '|', 1, 2) + 1,11))
        AND LENGTH (TRIM (SUBSTRB (qinputstring,INSTR (qinputstring, '|', 1, 2) + 1,11))) = 11;

commit;


declare 
 CURSOR smcard_cursor  IS 
     (select d.DECSCNR,d.DECSCPSNR from dec_tmp d);                  


 CURSOR prod_cursor  (v_pslink number )  IS
     (select  p.psdecscpsnrlink, c.CPCSIENTITLEMENTS
        from csiprod c, prodsubs  p
         where p.psdecscpsnrlink = v_pslink
            and c.CPIBSPRODUCTNR = p.psproductnr
            and c.CPCONDITIONNR =1
            and c.cpcsientitlements<>'NILDRA'
            and p.psstatus = 'A' ) ;

   m_decno  varchar2(17) ;
   v_decno  varchar2(17) ;
   prod_line VARCHAR2(1000) ;
   prod_full VARCHAR2(1000) ;
   comma_line VARCHAR2(1000) ;

   prod_len  number ;
   prod_diff number ;
   new_prodct varchar2(300) ;

   L_Size NUMBER ;
   new_size number ;
   s_link number ;
   space_con number ;
   sw number ; 
BEGIN


FOR smcard_record IN smcard_cursor LOOP
   prod_line  := '' ;
   prod_full  := ''  ;
   comma_line := ''  ;
   prod_len   :=0 ;
   prod_diff  :=0  ;
   L_Size :=0 ;
   new_size := 0 ;

   space_con := 0;  
   new_prodct := '' ;


   sw := 0 ; 
   S_link := smcard_record.decscpsnr ;
   v_decno:= substr(smcard_record.decscnr,1,10) ;
   m_decno:= substr(smcard_record.decscnr,1,11) ;


 FOR prod_record IN prod_cursor (s_link) LOOP
    SW := 1 ;
    prod_len := length(prod_record.CPCSIENTITLEMENTS) ;
    prod_diff := mod(prod_len,6) ;

  if prod_diff = 0   THEN 
       new_prodct := prod_record.CPCSIENTITLEMENTS ; 
     else 
      space_con :=prod_len+6-prod_diff ;
      new_prodct := rpad(prod_record.CPCSIENTITLEMENTS, space_con,' ') ;
  end if ;       

  prod_line := prod_line || new_prodct ;
  end loop ;



if sw = 1 then 
 L_size := length(prod_line) ;
 comma_line := comma_line || substr(prod_line,1,6);

for I in 1..L_size  LOOP 
    IF MOD(I,6) = 0 THEN                             
       comma_line := comma_line ||',' || substr(prod_line,(I+1),6) ; 
    END IF;
end loop ;  

 new_size := length(comma_line) - 1 ;
 comma_line := substr(comma_line,1,new_size) ;



prod_FULL :='SOFULL'||','||'002000,'|| v_decno||',EGY,'||'EG,'||'NONE      ,'||comma_line ;

 insert into sf_tmp  (smcard,p_line) values (m_decno,prod_full) ;
 commit ;

end if ; 

end loop ;  
 END;
END; 

Answer 1

You can't execute DDL in a procedure natively. Wrap it in an EXECUTE IMMEDIATE

create table sf_tmp(smcard varchar2(17),p_line varchar2(1000));  

You don't end your begin statement, which mean your procedure sees

create procedure begin ... declare ... begin ... end ... end

You don't need a declare in compiled code so this should be:

create procedure ... begin ... end ... begin ... end

The second begin ... end being what you want to execute on compilation (i.e. the creating and dropping of your tables)

You can't reference non-existent tables in a procedure. Given that you're allowing for your table to not exist at the beginning you need to wrap this in an EXECUTE IMMEDIATE.

insert into sf_tmp  (smcard,p_line) values (m_decno,prod_full) ;

Please start using the ANSI join syntax, rather than joining in your WHERE clause

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

LISTAGG() should help you significantly.

A global temporary table should remove the need to perform DDL in your procedure at all.

Without investigating too hard, your entire procedure looks like it can be done in a single INSERT statement...

Lastly, it looks like you're generating partial SQL to be executed later. This can be a valid approach in very specific circumstances, but I'd seriously reconsider whether it is before continuing.

Answer 2

I beleive you don't need '/' at line 10.