4 Replies Latest reply on Oct 8, 2012 8:32 AM by BluShadow

    Error while executing function

    966743
      hiii all,

      hope doing welll

      iam getting error while executing the function and error is

      ORA-00932: inconsistent datatypes: expected NUMBER got AMPLEX_GRAND1.SYS_PLSQL_75185_9_1
        • 1. Re: Error while executing function
          Chanchal Wankhade
          Hi,

          Please past create tabel script or insert into table or code of your function.
          • 2. Re: Error while executing function
            966743
            hii sir this is my function


            create or replace
            FUNCTION FnFetchEmployeesforjobsheet
            (
            v_user_id IN NUMBER
            )
            RETURN FnFetchEmployeesforjobshee_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 TT_V_EMPLOYEES%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 @employees select  Emp_ID from User_List where User_Id=@user_id
            INSERT INTO tt_v_employees
            ( Emp_ID )
            VALUES ( v_Emp_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;
            OPEN v_temp_1 FOR
            SELECT *
            FROM tt_v_employees;

            LOOP
            FETCH v_temp_1 INTO v_temp_2;
            EXIT WHEN v_temp_1%NOTFOUND;
            PIPE ROW ( v_temp_2 );
            END LOOP;
            END;


            and my table is


            CREATE GLOBAL TEMPORARY TABLE tt_v_employees
            (
            Emp_ID VARCHAR2(8) ,
            USER_ID NUMBER(10,0)
            );

            and i am executing this function ,like this

            select FnFetchEmployeesforjobshee_pkg.FnFetchEmployeesforjobsheet('1') from dual; and getting this error

            SQL Error: ORA-00904: "FNFETCHEMPLOYEESFORJOBSHEE_PKG"."FNFETCHEMPLOYEESFORJOBSHEET": invalid identifier
            00904. 00000 - "%s: invalid identifier"
            • 3. Re: Error while executing function
              Billy~Verreynne
              The code looks ugly.. and seems to be an Oracle hack that is based on a SQL-Server T-SQL procedure.

              PL/SQL is nothing like T-SQL. Oracle is nothing like SQL-Server.

              I suggest that you trash this code. It belongs in the recycle bin. You then determine WHAT the requirements are, and then look at how to CORRECTLY address these requirements in Oracle.
              • 4. Re: Error while executing function
                BluShadow
                Duplicate thread:

                How to Execute Table Valued function using query in pl/Sql

                Answers, probably better over on that one (as I've formatted his code over there)