CREATE OR REPLACE FUNCTION  GET_EMP_SERIAL
(EMP_ID   NUMBER)
RETURN  VARCHAR2
IS
V_length(employee_id)  NUMBER(10);
V_first_name VARCHAR2(20);
V_last_name VARCHAR2(20);
V_commission NUMBER(10);
V_months DATE,
V_name  VARCHAR2(60);
V_hire  DATE,
V_SAL NUMBER(20);


BEGIN
select employee_id ||
length(employee_id) || 
substr(lower(first_name),1,2)||
substr(upper(last_name),-2)||
nvl2(commission_pct,'c','nc') ||
trunc((months_between(sysdate,hire_date))/12)||
nvl(nullif(length(first_name) , length(last_name)),0)
||to_char(hire_date,'d')||to_char(hire_date,'ddd') || 
to_char(next_day(hire_date,'monday'),'yyyymmdd')||case
when salary between 0 and 5000 then 'D'
when salary between  5001 and 10000 then 'C'
when salary between 10001 and 20000 then  'B'
else'A' end 
INTO
EMP_ID,
V_length(employee_id),V_first_name,V_last_name,V_commission,V_months,V_name,V_hire,V_SAL
from employees
WHERE   employee_id=EMP_ID
RETURN
(EMP_ID ||
V_length(employee_id)||V_first_name||V_last_name||V_commission||V_months||V_name||V_hire||V_SAL);


END  GET_EMP_SERIAL;
/
show error 
---------------------------------------------------
LINE/COL ERROR 5/9 PLS-00103: Encountered the symbol "(" when expecting one of the f ollowing: constant exception <an identifier> <a double-quo ted delimited-identifier> table LONG_ double ref char time tim estamp interval date binary national character nchar

تم تعديل إظهار تنسيق الكود 

الكود غير مفهوم 

وما هي فائدته ..  ولماذا تمت كتابته ....

السلام عليكم

جرب الكود بعد التعديل:

 

CREATE OR REPLACE FUNCTION GET_EMP_SERIAL (EMP_ID NUMBER)
   RETURN VARCHAR2
IS
   V_length       NUMBER (10);
   V_FIRST_NAME   VARCHAR2 (20);
   V_LAST_NAME    VARCHAR2 (20);
   V_COMMISSION   NUMBER (10);
   V_MONTHS       DATE;
   V_NAME         VARCHAR2 (60);
   V_HIRE         DATE;
   V_SAL          NUMBER (20);
BEGIN
   SELECT    EMPLOYEE_ID
          || LENGTH (EMPLOYEE_ID)
          || SUBSTR (LOWER (FIRST_NAME), 1, 2)
          || SUBSTR (UPPER (LAST_NAME), -2)
          || NVL2 (COMMISSION_PCT, 'c', 'nc')
          || TRUNC ( (MONTHS_BETWEEN (SYSDATE, HIRE_DATE)) / 12)
          || NVL (NULLIF (LENGTH (FIRST_NAME), LENGTH (LAST_NAME)), 0)
          || TO_CHAR (HIRE_DATE, 'd')
          || TO_CHAR (HIRE_DATE, 'ddd')
          || TO_CHAR (NEXT_DAY (HIRE_DATE, 'monday'), 'yyyymmdd')
          || CASE
                WHEN SALARY BETWEEN 0 AND 5000 THEN 'D'
                WHEN SALARY BETWEEN 5001 AND 10000 THEN 'C'
                WHEN SALARY BETWEEN 10001 AND 20000 THEN 'B'
                ELSE 'A'
             END
     INTO EMP_ID,
          V_LENGTH,
          V_FIRST_NAME,
          V_LAST_NAME,
          V_COMMISSION,
          V_MONTHS,
          V_NAME,
          V_HIRE,
          V_SAL
     FROM EMPLOYEES
    WHERE EMPLOYEE_ID = EMP_ID;


   RETURN (   EMP_ID
           || V_LENGTH
           || V_FIRST_NAME
           || V_LAST_NAME
           || V_COMMISSION
           || V_MONTHS
           || V_NAME
           || V_HIRE
           || V_SAL);
END GET_EMP_SERIAL;
/