4 Replies Latest reply on Feb 19, 2007 6:23 PM by 437765

    TABLE FUNCTION - Using database view - send parameters to the function.

    437765
      Hi everybody:

      1.- I have a function returning a TABLE OF, and that function recieve 2 parameters of type NUMBER.

      2.- I'm able to call this function as a table function like this:

      SELECT * FROM TABLE(my_function(3,4))

      3.- I want to create a database View, To use this query in diferent places of my app.

      CREATE OR REPLACE VIEW NAME_OF_MY_VIEW AS SELECT * FROM TABLE(my_function(:_idOne,:idTwo))

      4.- My problem is, that I want to send the parameters dinamically to the function, and use those parameters to populate the rows, using a database view, the previous code does not compile... and I don't know another way to do this.

      Please help.

      thnks in advance.

      Alex.
        • 1. Re: TABLE FUNCTION - Using database view - send parameters to the function.
          MichaelS
          One way would be to go via Package Global Variables, another with Session Contexts. I'll show with Package Global Variables:
          SQL> CREATE PACKAGE pkg
          AS
             a_global   VARCHAR2 (30);
          
             FUNCTION f (a VARCHAR2)
                RETURN varchar_tab;
          
             FUNCTION ret_global
                RETURN VARCHAR2;
          END;
          Package created.
          
          SQL> CREATE OR REPLACE PACKAGE BODY pkg
          AS
             FUNCTION f (a VARCHAR2)
                RETURN varchar_tab
             AS
                l_varchar_tab   varchar_tab := varchar_tab ();
             BEGIN
                FOR i IN 1 .. 10
                LOOP
                   l_varchar_tab.EXTEND;
                   l_varchar_tab (i) := a;
                END LOOP;
          
                RETURN l_varchar_tab;
             END f;
          
             FUNCTION ret_global
                RETURN VARCHAR2
             AS
             BEGIN
                RETURN a_global;
             END ret_global;
          END pkg;
          Package body created.
          
          SQL> CREATE OR REPLACE VIEW v
          AS
             SELECT *
               FROM TABLE (pkg.f (pkg.ret_global))
          View created.
          
          SQL> EXEC pkg.a_global := 1
          PL/SQL procedure successfully completed.
          SQL> SELECT *
            FROM v
          
          COLUMN_VALUE                                                                    
          --------------------------------------------------------------------------------
          1                                                                               
          1                                                                               
          1                                                                               
          1                                                                               
          1                                                                               
          1                                                                               
          1                                                                               
          1                                                                               
          1                                                                               
          1                                                                               
          
          
          10 rows selected.
          
          SQL> EXEC pkg.a_global := 5
          PL/SQL procedure successfully completed.
          
          SQL> SELECT *
            FROM v
          
          COLUMN_VALUE                                                                    
          --------------------------------------------------------------------------------
          5                                                                               
          5                                                                               
          5                                                                               
          5                                                                               
          5                                                                               
          5                                                                               
          5                                                                               
          5                                                                               
          5                                                                               
          5                                                                               
          
          10 rows selected.
          • 2. Re: TABLE FUNCTION - Using database view - send parameters to the function.
            437765
            Hello:

            Thnks for your response.

            I notice that using this solution, I always have to execute two statements, one for the setting value to the global variable, and other to populate the values.

            Is there any possibility to do this in one step?

            Sorry if this is something obvious, but I never used this approach before.

            thnks.

            Alex.
            • 3. Re: TABLE FUNCTION - Using database view - send parameters to the function.
              MichaelS
              Yes you can:
              SQL> CREATE PACKAGE pkg
              AS
                 a_global   VARCHAR2 (30);
              
                 FUNCTION f (a VARCHAR2)
                    RETURN varchar_tab;
              
                 FUNCTION ret_global
                    RETURN VARCHAR2;
              
                 FUNCTION set_ret_global (ret_global VARCHAR2)
                    RETURN INTEGER;
              END;
              Package created.
              
              SQL> CREATE OR REPLACE PACKAGE BODY pkg
              AS
                 FUNCTION f (a VARCHAR2)
                    RETURN varchar_tab
                 AS
                    l_varchar_tab   varchar_tab := varchar_tab ();
                 BEGIN
                    FOR i IN 1 .. 10
                    LOOP
                       l_varchar_tab.EXTEND;
                       l_varchar_tab (i) := a;
                    END LOOP;
              
                    RETURN l_varchar_tab;
                 END f;
              
                 FUNCTION ret_global
                    RETURN VARCHAR2
                 AS
                 BEGIN
                    RETURN a_global;
                 END ret_global;
              
                 FUNCTION set_ret_global (ret_global VARCHAR2)
                    RETURN INTEGER
                 AS
                 BEGIN
                    a_global := ret_global;
                    RETURN 1;
                 END set_ret_global;
              END pkg;
              Package body created.
              
              SQL> CREATE OR REPLACE VIEW v
              AS
                 SELECT *
                   FROM TABLE (pkg.f (pkg.ret_global))
              View created.
              
              SQL> SELECT *
                FROM v
               WHERE 1 = pkg.set_ret_global (5)
              
              COLUMN_VALUE                                                                    
              --------------------------------------------------------------------------------
              5                                                                               
              5                                                                               
              5                                                                               
              5                                                                               
              5                                                                               
              5                                                                               
              5                                                                               
              5                                                                               
              5                                                                               
              5                                                                               
              
              
              10 rows selected.
              
              SQL> SELECT *
                FROM v
               WHERE 1 = pkg.set_ret_global (4)
              
              COLUMN_VALUE                                                                    
              --------------------------------------------------------------------------------
              4                                                                               
              4                                                                               
              4                                                                               
              4                                                                               
              4                                                                               
              4                                                                               
              4                                                                               
              4                                                                               
              4                                                                               
              4                                                                               
              
              10 rows selected.
              • 4. Re: TABLE FUNCTION - Using database view - send parameters to the function.
                437765
                Thnks a lot for your quick response.


                This is what I need to do.

                Thnks a lot for your time.


                Alex.