1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

error reporting, i don't know what's going wrong

Discussion in 'SQL PL/SQL' started by artistic21, Apr 14, 2014.

  1. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Code (SQL):
    CREATE OR REPLACE PROCEDURE add_employee33(pempno IN employees.employee_id%TYPE,
    pename IN EMPLOYEES.FIRST_NAME%TYPE,plast_name IN EMPLOYEES.LAST_NAME%TYPE,email IN EMPLOYEES.EMAIL%TYPE,
    pnr IN EMPLOYEES.PHONE_NUMBER%TYPE ,phiredate IN EMPLOYEES.HIRE_DATE%TYPE ,pjob IN EMPLOYEES.JOB_ID%TYPE,
    psal IN EMPLOYEES.SALARY%TYPE,pcomm IN EMPLOYEES.COMMISSION_PCT%TYPE,pmgr IN EMPLOYEES.MANAGER_ID%TYPE,pdep IN EMPLOYEES.DEPARTMENT_ID%TYPE)
    IS
    emp_rec employees%ROWTYPE;
       MngrRepeated NUMBER;
       managerName varchar2(40);
         mngr NUMBER;
         maxId NUMBER;
         id_mng NUMBER;
       BEGIN
      SELECT MAX(employee_id) INTO maxId FROM employees;
       SELECT manager_id,COUNT(*) INTO id_mng,MngrRepeated
       FROM EMPLOYEES GROUP BY manager_id ;
       --WHERE manager_id=employee_id;
      IF MngrRepeated<4 THEN
      --    insert into emp values (FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,  DEPARTMENT_ID);
      INSERT INTO emp_rec
      VALUES maxId+1, pename,plast_name,pemail,pnr,sysdate, pjob,psal,pcomm, pmgr,pdep;
      DBMS_OUTPUT.PUT_LINE('its ok'||emp_rec.first_name);
     ELSE
      SELECT FIRST_NAME  INTO managerName FROM EMPLOYEES
      WHERE EMPLOYEE_ID=id_mng;
           DBMS_OUTPUT.PUT_LINE('Manager :'||managerName||'have to employees!');
      END IF;
      END;
    Error(20,17): PL/SQL: ORA-00933: SQL command not properly ended
    Error(19,3): PL/SQL: SQL Statement ignored


    i'd really appreciate your help. Thanks in advance.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your VALUES statement needs () around the value list:

    VALUES(maxId+1, pename,plast_name,pemail,pnr,sysdate, pjob,psal,pcomm, pmgr,pdep);


    Also you did not declare pemail; you declared it as email. You will need to correct that, too.
     
  3. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Thanks.
    i edited, but it shows me two another errors...
    Error(19,3): PL/SQL: SQL Statement ignored
    Error(19,15): PL/SQL: ORA-00942: table or view does not exist
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    emp_rec is not a table.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    some specifications:

    Code (SQL):

    CREATE OR REPLACE PROCEDURE add_employee33(
                                                pempno IN employees.employee_id%TYPE,
                                                pename IN EMPLOYEES.FIRST_NAME%TYPE,
                                                plast_name IN EMPLOYEES.LAST_NAME%TYPE,
                                                email IN EMPLOYEES.EMAIL%TYPE,
                                                pnr IN EMPLOYEES.PHONE_NUMBER%TYPE ,
                                                phiredate IN EMPLOYEES.HIRE_DATE%TYPE ,
                                                pjob IN EMPLOYEES.JOB_ID%TYPE,
                                                psal IN EMPLOYEES.SALARY%TYPE,
                                                pcomm IN EMPLOYEES.COMMISSION_PCT%TYPE,
                                                pmgr IN EMPLOYEES.MANAGER_ID%TYPE,
                                                pdep IN EMPLOYEES.DEPARTMENT_ID%TYPE
                                                )
    IS
        emp_rec  employees%ROWTYPE;
        MngrRepeated NUMBER;
        managerName varchar2(40);
        mngr NUMBER;
        maxId NUMBER;
        id_mng NUMBER;
    BEGIN

    -- it is better not to use such request as it can lead to performance concerns
        SELECT MAX(employee_id) INTO maxId FROM employees;
       
        --  if not  used  WHERE ,then  can be   exception    TO_MANY_ROWS

        SELECT
            manager_id,COUNT(*)
        INTO
            id_mng,MngrRepeated
        FROM
            EMPLOYEES
        --WHERE manager_id=employee_id
       
        GROUP BY manager_id ;
       
       
      IF MngrRepeated<4 THEN
      --    insert into emp values (FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,  DEPARTMENT_ID);
    INSERT INTO employees
      VALUES (maxId+1, pename,plast_name,pemail,pnr,sysdate, pjob,psal,pcomm, pmgr,pdep);
     
      -- here NULL as you don't initialize the variable  emp_rec
      DBMS_OUTPUT.PUT_LINE('its ok'||emp_rec.first_name);
     
     ELSE
     
      SELECT
            FIRST_NAME  INTO managerName
      FROM EMPLOYEES
      WHERE
            EMPLOYEE_ID=id_mng;
            DBMS_OUTPUT.PUT_LINE('Manager :'||managerName||'have to employees!');
      END IF;
    END;
     
    Try this :

    Code (SQL):


    CREATE OR REPLACE PROCEDURE add_employee33(
                                                pempno IN employees.employee_id%TYPE,
                                                pename IN EMPLOYEES.FIRST_NAME%TYPE,
                                                plast_name IN EMPLOYEES.LAST_NAME%TYPE,
                                                email IN EMPLOYEES.EMAIL%TYPE,
                                                pnr IN EMPLOYEES.PHONE_NUMBER%TYPE ,
                                                phiredate IN EMPLOYEES.HIRE_DATE%TYPE ,
                                                pjob IN EMPLOYEES.JOB_ID%TYPE,
                                                psal IN EMPLOYEES.SALARY%TYPE,
                                                pcomm IN EMPLOYEES.COMMISSION_PCT%TYPE,
                                                pmgr IN EMPLOYEES.MANAGER_ID%TYPE,
                                                pdep IN EMPLOYEES.DEPARTMENT_ID%TYPE
                                                )
    IS
        --emp_rec  employees%ROWTYPE;
        MngrRepeated NUMBER;
        managerName varchar2(40);
        mngr NUMBER;
        maxId NUMBER;
        id_mng NUMBER;
    BEGIN
     
    -- it is better not to use such request as it can lead to performance concerns
        SELECT MAX(employee_id) INTO maxId FROM employees;
     
        --  if not  used  WHERE ,then  can be   exception    TO_MANY_ROWS
     
        SELECT
            manager_id,first_name,COUNT(*)
        INTO
            id_mng,managerName ,MngrRepeated
        FROM
            EMPLOYEES
       
        WHERE manager_id=pmgr
     
        GROUP BY manager_id,firts_name ;
     
     
      IF MngrRepeated<4 THEN
      --    insert into emp values (FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,  DEPARTMENT_ID);
    INSERT INTO employees
      VALUES (maxId+1, pename,plast_name,pemail,pnr,sysdate, pjob,psal,pcomm, pmgr,pdep);
     
      -- here NULL as you don't initialize the variable  emp_rec
      --DBMS_OUTPUT.PUT_LINE('its ok'||emp_rec.first_name);
      DBMS_OUTPUT.PUT_LINE('its ok'||pename);
     
     ELSE
     
      /*SELECT
            FIRST_NAME  INTO managerName
      FROM EMPLOYEES
      WHERE
            EMPLOYEE_ID=id_mng;
            DBMS_OUTPUT.PUT_LINE('Manager :'||managerName||'have to employees!');
      END IF;
      */

      DBMS_OUTPUT.PUT_LINE('Manager :'||managerName||'have to employees!');
    END;