| How to execute function in sql developer [message #568156] |
Tue, 09 October 2012 23:42  |
 |
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
|
Member |
|
|
Hi all,
Hope doing well,
sir i am new member of this website.
sir i have converted one function from sql to oracle through sql developer. so it's created with package name
i execute package name and function also
here is my package name and function:
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);
v_temp_1 SYS_REFCURSOR;
--v_temp_2 %ROWTYPE;
BEGIN
BEGIN
v_temp:=0;
End;
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;
it's executed successfully.
when i am executing this function like this:
select emp_id from fnfetchemployees_pkg.fnfetchemployees(1) from dual;
getting error: sql command is not properly ended;
please help me.
thanks in advance.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to execute function in sql developer [message #568163 is a reply to message #568161] |
Wed, 10 October 2012 00:25  |
|
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
|
Member |
|
|
Hi sir i checked this link and i created type there
here is the type
CREATE OR REPLACE TYPE myrec AS OBJECT
( col1 VARCHAR2(10),
col2 VARCHAR2(10)
)
after that i executed this query
CREATE OR REPLACE TYPE myrectable AS TABLE OF myrec;
now i used some modification in function
that is here:
create or replace
FUNCTION FnFetchEmployees
(
v_user_id IN NUMBER
)
RETURN myrectable PIPELINED
As
--declare @manager as int
v_empid VARCHAR2(20);
v_compid VARCHAR2(20);
v_temp NUMBER(1, 0);
v_temp_1 SYS_REFCURSOR;
--v_temp_2 %ROWTYPE;
BEGIN
BEGIN
v_temp:=0;
End;
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;
and i run it like this:
select emp_id from fnfetchemployees(1) from dual;
but getting the same error.
sql command is not properly ended.
have i done right?
thanks
--
Edit: No you haven't done right. I've added the [code] tags for you, please do it yourself in future. jw.
[Updated on: Wed, 10 October 2012 01:26] by Moderator Report message to a moderator |
|
|
|
| Re: How to execute function in sql developer [message #568167 is a reply to message #568163] |
Wed, 10 October 2012 01:19 |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
Cant confirm the "sql command is not properly ended." error.
But you
1) don't have a RETURN value
RETURN;--INSERT INTO INSERTID(EMPID)VALUES(EMP_ID)
2)do DML in a function (INSERT), this could lead to "ORA-14551 cannot perform a DML operation inside a query".
3) use the "bad"
EXCEPTION
WHEN OTHERS THEN
NULL;
that will hide all errors in the function - have a look in the diskussions to this topic in this forum.
And please have a look at the first(upper) thread here:
How to use [code] tags and make your code easier to read?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
