How can I solve this error on plsql-oracle-CodePudding

create or replace procedure sp_crud_docente (
p_opcion    varchar2,
p_id_Docente NUMBER,
p_nombre VARCHAR2(30),
p_apellido VARCHAR2(30),
p_cedula NUMBER,
p_titulo VARCHAR2(100),
p_observaciones VARCHAR2(200),
p_estado VARCHAR2(10),
p_mensaje out varchar) 
as
v_valor int;
v_row Docente%rowtype;
    begin  
        if (p_opcion = 'I') then             
              begin
             Select  MAX(id_Docente)  1  
            into v_valor
            from Docente;
            if v_valor is null then 
            v_valor := 1;
              p_mensaje:= 'Registro inserted...';  
             DBMS_OUTPUT.PUT_LINE(p_mensaje);   
            insert into Docente
            values (p_opcion,v_valor,p_id_Docente,p_nombre,p_apellido,p_cedula,p_titulo,p_observaciones,p_estado);
        end if;
        end;

          else if (p_opcion = 'U') then 
          
            update Docente set  nombre=p_nombre,
                                apellido=p_apellido,
                                cedula=p_cedula,
                                titulo=p_titulo,
                                observaciones=p_observaciones,
                                estado=p_estado
                           where idperiodo=p_idperiodo;
             p_mensaje:= ('Registro updated...');   
               DBMS_OUTPUT.PUT_LINE(p_mensaje);
          else if (p_opcion = 'S') then   
           begin
            Select 
            opcion,
            id_Docente
            nombre,
            apellido,
            cedula,
            titulo,
            observaciones,
            estado                
            into v_row 
            from Docente;
        p_mensaje := ('Ok');
                      
DBMS_OUTPUT.PUT_LINE(p_mensaje||' --> '||v_row.opcion||'|'||v_row.id_Docente||'|'||v_row.nombre||'|'||v_row.apellido||'|'||v_row.cedula||'|'||v_row.titulo||'|'||v_row.observaciones||'|'||v_row.estado);
         end;
               
             else if (p_opcion = 'D') then  
              
                Delete from Docente where id_Docente=p_id_Docente;
                
                    p_mensaje := ('Proceso ejecutado correctamente');
                    
    DBMS_OUTPUT.PUT_LINE(p_mensaje);
    commit;
            
          end if;
           end if;
           end if;  
            end if;  
         
         EXCEPTION  
           WHEN OTHERS then 
           p_mensaje := ('ERROR. No se pudo ejecutar el proceso');
           rollback;
          end;

Errors:

PROCEDURE SP_CRUD_DOCENTE Line/Col: 4/18 PLS-00103: Encountered the symbol "(" when expecting one of the following:

:= . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

Line/Col: 5/20 PLS-00103: Encountered the symbol "(" when expecting one of the following:

:= . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

Line/Col: 7/18 PLS-00103: Encountered the symbol "(" when expecting one of the following:

:= . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

Line/Col: 8/25 PLS-00103: Encountered the symbol "(" when expecting one of the following:

:= . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

Line/Col: 9/18 PLS-00103: Encountered the symbol "(" when expecting one of the following:

:= . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

CodePudding user response：

Take out the length of the incoming parameters:

create or replace procedure sp_crud_docente (
p_opcion    varchar2,
p_id_Docente NUMBER,
p_nombre VARCHAR2,
p_apellido VARCHAR2,
p_cedula NUMBER,
p_titulo VARCHAR2,
p_observaciones VARCHAR2,
p_estado VARCHAR2,
p_mensaje out varchar2)

See 8.7.1 Formal and Actual Subprogram Parameters section of the 19c manual "Database PL/SQL Language Reference"

Bobby

CodePudding user response：

Apart from the fact that varchar2 parameters can't have size, this is practically unsolvable without you posting the docente table description.

Bad habits kick; you're wrongly relying on the fact that you know what you're doing, but it turns out you don't.

I can't figure out how many nor which columns docente contains, and in which order.

insert into docente should explicitly mention all columns you're inserting into, one-by-one. Code you wrote suggests that table contains 9 columns (opcion, id_docente, nombre, apellido, cedula, titulo, observaciones, estado).

update, on the other hand, updates only 6 of them (nombre, apellido, cedula, tutlo, observaciones, estado) which doesn't collide with insert; it is OK if you don't update all columns.

However, p_opcion = 'S' selects only 7 columns into v_row which is declared as docente%rowtype. Out of those 7 columns, I believe you "forgot" a comma between id_docente and nombre - those are two columns, it's not that nombre is id_docente's alias, right? Suppose it is 8 columns that are selected after all. Now, that collides with number of columns you used in insert (9 of them). If you're selecting into %rowtype, you must select ALL columns in EXACT ORDER.

I suggest you review code you wrote, pay attention about what I said and fix those errors.