OraFAQ Forum: SQL & PL/SQL » How to execute function in sql developer

Home » SQL & PL/SQL » SQL & PL/SQL » How to execute function in sql developer (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

Re: How to execute function in sql developer [message #568157 is a reply to message #568156]

Tue, 09 October 2012 23:46

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

The correct syntax for a pipelined function is:

Code: [Select all] [Show/ hide]

select emp_id from table(fnfetchemployees_pkg.fnfetchemployees(1));

But note that SQL cannot use PL/SQL type.

Regards
Michel

Report message to a moderator

Re: How to execute function in sql developer [message #568158 is a reply to message #568157]

Tue, 09 October 2012 23:50

vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore

Member

Hi sir i used this but getting error that is:

SQL Error: ORA-00904: "FNFETCHEMPLOYEES_PKG"."FNFETCHEMPLOYEES": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:

Report message to a moderator

Re: How to execute function in sql developer [message #568159 is a reply to message #568158]

Tue, 09 October 2012 23:53

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The reason is my last sentence.
You need to create SQL types using CREATE TYPE statement.

Regards
Michel

Report message to a moderator

Re: How to execute function in sql developer [message #568160 is a reply to message #568159]

Tue, 09 October 2012 23:56

vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore

Member

Hi sir,

thanks for your response
sir i am newbie in oracle so could you please explain this create type statement?
thanks

Report message to a moderator

Re: How to execute function in sql developer [message #568161 is a reply to message #568160]

Tue, 09 October 2012 23:57

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I will just give you a link, I think you will be able to find it: Database SQL Reference

Regards
Michel

Report message to a moderator

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

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

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"

Code: [Select all] [Show/ hide]

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?

Report message to a moderator