Hi all,
Hope doing well,
sir i am executing one function which i translated from sql server to oracle through sql developer.
here is that
/*Global Temporary Tables:1 *//* Translation Extracted DDL For Required Objects*/
CREATE GLOBAL TEMPORARY TABLE tt_v_employees
(
Emp_ID VARCHAR2(8) ,
USER_ID NUMBER(10,0)
);
/
CREATE OR REPLACE PACKAGE FnFetchEmployees_pkg
AS
TYPE tt_v_employees_type IS TABLE OF tt_v_employees%ROWTYPE;
END;
/
CREATE OR REPLACE FUNCTION FnFetchEmployees
(
v_user_id IN NUMBER
)
RETURN FnFetchEmployees_pkg.tt_v_employees_type PIPELINED
AS
--declare @manager as int
v_empid VARCHAR2(20);
v_compid VARCHAR2(20);
v_temp NUMBER(1, 0) := 0;
v_temp_1 SYS_REFCURSOR;
v_temp_2 %ROWTYPE;
BEGIN
BEGIN
SELECT 1 INTO v_temp
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM user_list
WHERE Access_level = 0
AND USER_ID = v_user_id );
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
IF v_temp = 1 THEN
BEGIN
SELECT Emp_ID
INTO v_empid
FROM User_List
WHERE USER_ID = v_user_id;
SELECT Comp_ID
INTO v_compid
FROM Employee
WHERE Emp_ID = v_empid;
INSERT INTO tt_v_employees
( SELECT DISTINCT Emp_id ,
v_user_id
FROM employee
WHERE Comp_ID = v_compid );
END;
END IF;
BEGIN
SELECT 1 INTO v_temp
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM user_list
WHERE Access_level = 2
AND USER_ID = v_user_id );
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
IF v_temp = 1 THEN
BEGIN
INSERT INTO tt_v_employees
( SELECT e.Emp_ID ,
v_user_id
FROM employee e
JOIN user_list ul
ON ul.Emp_ID = e.Emp_ID
WHERE USER_ID = v_user_id );
END;
END IF;
BEGIN
SELECT 1 INTO v_temp
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM user_list
WHERE Access_level = 1
AND USER_ID = v_user_id );
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
IF v_temp = 1 THEN
DECLARE
v_Emp_id VARCHAR2(50);
BEGIN
SELECT e.Emp_id
INTO v_Emp_id
FROM employee e
JOIN user_list ul
ON ul.Emp_ID = e.Emp_ID
WHERE USER_ID = v_user_id;
--insert @employees select e.Emp_id,User_ID from employee e inner join user_list ul on ul.Emp_ID = e.Emp_ID
--where User_ID = @user_id
INSERT INTO tt_v_employees
(
--select Emp_ID,@user_id from employee where Emp_ID = @Emp_id
--union
SELECT Emp_ID ,
v_user_id
FROM employee
WHERE Managerid = v_Emp_id
AND STATUS = 1 );
END;
END IF;
RETURN;--INSERT INTO INSERTID(EMPID)VALUES(EMP_ID)
END;
so here i created this global temporary table and package. but when i am executing this function getting two error
that is :
Error(12,13): PLS-00103: Encountered the symbol "%" when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table long double ref char time timestamp interval date binary national character nchar The symbol "<an identifier>" was substituted for "%" to continue.
Error(69,18): PLS-00103: Encountered the symbol "JOIN" when expecting one of the following: ) , group having intersect minus start union where connect
i am newbie in oracle please help me
thanks