13 Replies Latest reply: Oct 9, 2012 2:50 AM by BluShadow RSS

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

    966743
      Hi All,

      hope doing well

      i have created a function in oracle 11g and i want to execute it in my application using query
      i am posting that 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;
      now i want to execute this function using following query

      "SELECT Emp_ID FROM Employee where status=1 and Emp_ID IN (select FnFetchEmployeesforjobsheet (" & Session("UserId") & ") from dual)"

      while executing it is giving error

      AMPLEX_GRAND1.SYS_PLSQL_75185_9_1()

      Edited by: BluShadow on 08-Oct-2012 09:03
      added {noformat}
      {noformat} for readability, please read {message:id=9360002} and learn to do this yourself.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        • 1. Re: How to Execute Table Valued function using query in pl/Sql
          BluShadow
          963740 wrote:
          while executing it is giving error

          AMPLEX_GRAND1.SYS_PLSQL_75185_9_1()
          That's not an error message.
          What tool are you running your query through?

          Error messages from SQL typically start ORA-xxxxx where xxxxx is a number.
          • 2. Re: How to Execute Table Valued function using query in pl/Sql
            BluShadow
            Looks as though your code could be optimized somewhat... e.g.
            CREATE OR REPLACE FUNCTION FnFetchEmployeesforjobsheet(v_user_id IN NUMBER) RETURN FnFetchEmployeesforjobshee_pkg.tt_v_employees_type PIPELINED AS
              v_temp_1  SYS_REFCURSOR;
              v_temp_2  TT_V_EMPLOYEES%ROWTYPE;
            BEGIN
              INSERT INTO tt_v_employees
                SELECT emp_id, v_user_id
                FROM (
                      SELECT DISTINCT Emp_id
                      FROM employee 
                      WHERE Comp_ID = (select comp_id
                                       from employee
                                       where empid = (select emp_id
                                                      from   user_list
                                                      where  user_id = v_user_id
                                                      and    access_level = 0
                                                     )
                                      )
                      UNION ALL
                      SELECT e.Emp_ID
                      FROM employee e JOIN user_list ul
                                      ON   ul.Emp_ID = e.Emp_ID
                                      AND  ul.user_id = v_user_id
                                      AND  ul.access_level = 2
                      UNION ALL
                      SELECT Emp_ID
                      FROM employee 
                      WHERE Managerid = (SELECT e.Emp_id
                                         FROM   employee e JOIN user_list ul
                                                           ON   ul.Emp_ID = e.Emp_ID
                                                           AND  ul.USER_ID = v_user_id
                                                           AND  ul.access_level = 1
                                        )
                      AND STATUS = 1
                     );
            
              INSERT INTO tt_v_employees (Emp_ID) 
                SELECT e.Emp_id
                FROM   employee e JOIN user_list ul
                                  ON   ul.Emp_ID = e.Emp_ID
                                  AND  ul.USER_ID = v_user_id
                                  AND  ul.access_level = 1;
            
              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;
              RETURN;
            END;
            Obviously this is untested as I don't have your tables or know exactly what your output should be, but you do seem to be including a lot of redundant code in what you're doing.
            • 4. Re: How to Execute Table Valued function using query in pl/Sql
              BluShadow
              Billy  Verreynne  wrote:
              DO NOT ASK THE SAME QUESTION TWICE!

              Error while executing function
              He asked this one first, and then went obscure on the other thread. sigh
              • 5. Re: How to Execute Table Valued function using query in pl/Sql
                966743
                sir
                its giving following error

                SQL Error: ORA-00904: "FNFETCHEMPLOYEESFORJOBSHEE_PKG"."FNFETCHEMPLOYEESFORJOBSHEET": invalid identifier
                • 6. Re: How to Execute Table Valued function using query in pl/Sql
                  BluShadow
                  963740 wrote:
                  sir
                  its giving following error

                  SQL Error: ORA-00904: "FNFETCHEMPLOYEESFORJOBSHEE_PKG"."FNFETCHEMPLOYEESFORJOBSHEET": invalid identifier
                  As I said, it wasn't tested because I don't have your environment.

                  From what I can see you are trying to return a PL/SQL type (declared in your package) from your pipelined function. You cannot use pl/sql types for that, they have to be types declared on the database, otherwise the receiving SQL statement won't know about the type (remember SQL and PL/SQL are different 'engines' so SQL cannot access PL/SQL types).

                  Declare the appropriate return types on the database for SQL to use and alter your function to use those types.
                  • 7. Re: How to Execute Table Valued function using query in pl/Sql
                    966743
                    sir i am using oracle sql developer and executing this
                    • 8. Re: How to Execute Table Valued function using query in pl/Sql
                      BluShadow
                      963740 wrote:
                      sir i am using oracle sql developer and executing this
                      Thanks for the extra information.

                      Now, have you tried creating the types in SQL as suggested?
                      • 9. Re: How to Execute Table Valued function using query in pl/Sql
                        21205
                        BluShadow wrote:
                        From what I can see you are trying to return a PL/SQL type (declared in your package) from your pipelined function. You cannot use pl/sql types for that, they have to be types declared on the database, otherwise the receiving SQL statement won't know about the type (remember SQL and PL/SQL are different 'engines' so SQL cannot access PL/SQL types).

                        Declare the appropriate return types on the database for SQL to use and alter your function to use those types.
                        fyi: this is one of the things that will change with Oracle 12c, as presented last week at Oracle Open World.

                        <shameless plug>
                        http://nuijten.blogspot.nl/2012/10/oow-2012-is-plsql-still-alive.html
                        </shameless plug>
                        • 10. Re: How to Execute Table Valued function using query in pl/Sql
                          BluShadow
                          Alex Nuijten wrote:
                          BluShadow wrote:
                          From what I can see you are trying to return a PL/SQL type (declared in your package) from your pipelined function. You cannot use pl/sql types for that, they have to be types declared on the database, otherwise the receiving SQL statement won't know about the type (remember SQL and PL/SQL are different 'engines' so SQL cannot access PL/SQL types).

                          Declare the appropriate return types on the database for SQL to use and alter your function to use those types.
                          fyi: this is one of the things that will change with Oracle 12c, as presented last week at Oracle Open World.

                          <shameless plug>
                          http://nuijten.blogspot.nl/2012/10/oow-2012-is-plsql-still-alive.html
                          </shameless plug>
                          Good to know.

                          Think I may wait for 12cR2 for such changes to stablize though... it usually takes them a while to iron out the bugs. ;)
                          • 11. Re: How to Execute Table Valued function using query in pl/Sql
                            rp0428
                            >
                            i have created a function in oracle 11g and i want to execute it in my application using query
                            >
                            That won't work.

                            DML is NOT allowed in functions that are used in SQL and you have this in your function
                                  INSERT INTO tt_v_employees . . .
                            Even if you fix the other errors mentioned your function will not work unless you remove the DML statements.
                            • 12. Re: How to Execute Table Valued function using query in pl/Sql
                              rp0428
                              >
                              From what I can see you are trying to return a PL/SQL type (declared in your package) from your pipelined function. You cannot use pl/sql types for that, they have to be types declared on the database, otherwise the receiving SQL statement won't know about the type (remember SQL and PL/SQL are different 'engines' so SQL cannot access PL/SQL types).
                              . . .
                              Declare the appropriate return types on the database for SQL to use and alter your function to use those types.
                              >
                              Well yes, the types have to be SQL types but you can let Oracle create them for you automagically by putting them in a package. ;)

                              See Solomon's classic solution in this thread from Jun 27, 2012 5:42 pm
                              urgent help
                              >
                              Or simply take advantage of package pipelined functions where record type is automatically mapped to object and object table types:
                              . . .
                              >
                              Padders showed a similar example in the Sep 6, 2012 7:17am reply in this thread
                              Re: Can we write pipelined function without creating Type objects.

                              When the PL/SQL types are declared in a package Oracle will create corresponding SQL types (hidden in 11g) when you compile the package.
                              • 13. Re: How to Execute Table Valued function using query in pl/Sql
                                BluShadow
                                rp0428 wrote:
                                >
                                From what I can see you are trying to return a PL/SQL type (declared in your package) from your pipelined function. You cannot use pl/sql types for that, they have to be types declared on the database, otherwise the receiving SQL statement won't know about the type (remember SQL and PL/SQL are different 'engines' so SQL cannot access PL/SQL types).
                                . . .
                                Declare the appropriate return types on the database for SQL to use and alter your function to use those types.
                                >
                                Well yes, the types have to be SQL types but you can let Oracle create them for you automagically by putting them in a package. ;)

                                See Solomon's classic solution in this thread from Jun 27, 2012 5:42 pm
                                urgent help
                                >
                                Or simply take advantage of package pipelined functions where record type is automatically mapped to object and object table types:
                                . . .
                                >
                                Padders showed a similar example in the Sep 6, 2012 7:17am reply in this thread
                                Re: Can we write pipelined function without creating Type objects.

                                When the PL/SQL types are declared in a package Oracle will create corresponding SQL types (hidden in 11g) when you compile the package.
                                Interesting, and good examples. Thanks... now noted in the Flash Brain Memory. ;)