9 Replies Latest reply: Oct 6, 2005 12:43 PM by 24208 RSS

    Help - Oracle function w/RETURN VIEW_NAME%ROWTYPE

    452123
      I have a fairly complex Oracle function with a signature like this:

      FUNCTION get_some_data
      (
      in_param_one VARCHAR2
      , in_param_two VARCHAR2
      )RETURN VIEW_NAME%ROWTYPE

      (The internal logic of the function is such that it will return at most one row.)

      I have sample code from MSDN that outlines calling functions in .Net, like this:

      // create the command for the function
      OracleCommand cmd = new OracleCommand();
      cmd.Connection = conn;
      cmd.CommandText = "GET_EMPLOYEE_EMAIL";
      cmd.CommandType = CommandType.StoredProcedure;

      // add the parameters, including the return parameter to retrieve
      // the return value
      cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;
      cmd.Parameters.Add("p_email", OracleType.VarChar, 25).Direction =
      ParameterDirection.ReturnValue;

      // execute the function
      conn.Open();
      cmd.ExecuteNonQuery();
      conn.Close();

      // output the result
      Console.WriteLine("Email address is: " + cmd.Parameters["p_email"].Value);


      I don't see an available OracleType to fit the '...%ROWTYPE' return value.

      I think the Oracle code is doing inferred 'table inlining' but it may, instead, be using an inferred refcursor (clearly I'm no DBA). Everything I've been able to find online when googling for how to handle this in .Net doesn't seem to match this situation.

      How can I call this function and get the data back in C#? Please note that I don't have the authority to modify the package to make it explicitly return a refcursor.

      Thank you for any light you can shed!

      MarFarMa
        • 1. Re: Help - Oracle function w/RETURN VIEW_NAME%ROWTYPE
          24208
          I don't see an available OracleType to fit the '...%ROWTYPE' return value.
          Unfortunately ODP.NET does not support this at this time.

          If you search the forum for "%rowtype" you'll find some threads where this is discussed:

          output parameter rowtype problem

          Re: Table support in Beta

          Are a couple...

          - Mark
          • 2. Re: Help - Oracle function w/RETURN VIEW_NAME%ROWTYPE
            452123
            OK - not the answer I wanted, but - resolution is good.

            in an interactive SQL window, I attempted to run the following:

            select get_some_data('paramval1','paramval2') from dual;

            it doesn't run. How would I "wrap this function" in PL/SQL, as one of the referred threads suggests, so that I can get the data into something .Net readable?

            MarFarMa
            • 3. Re: Help - Oracle function w/RETURN VIEW_NAME%ROWTYPE
              24208
              You said you don't have the authority to modify the current package to return a ref cursor... do you have the ability/authority to create a new package/procedure etc?

              Thanks,

              - Mark
              • 4. Re: Help - Oracle function w/RETURN VIEW_NAME%ROWTYPE
                452123
                Not really.

                If it's the only solution then I'll have to present it to the client's DBA - but the plan was that I work against their existing package.

                Isn't there some way of executing the function a block of PS/SQL from an command, where it returns a rowset? Kind of like:

                cmd.commandtext = "declare ...... begin ...[necessary wrapper code].. end"
                dataset = cmd.execute();

                If that's wildly inefficient, as I suspect it might be - then that will be the motivation for them to provide me with the ccess to build something that makes better sense.

                Thanks,

                MarFarMa
                • 5. Re: Help - Oracle function w/RETURN VIEW_NAME%ROWTYPE
                  24208
                  Here's a "quick and dirty" example using the HR sample schema that I think might be enough to get you going...

                  Pl/SQL (used to mimic your function that returns %rowtype):
                  create or replace function get_country_row (p_id in varchar2) return countries%rowtype as
                    cursor emp_cur is select * from countries where country_id = p_id;
                    emp_rec emp_cur%rowtype;
                  begin
                    open emp_cur;
                    fetch emp_cur into emp_rec;
                    close emp_cur;
                    return emp_rec;
                  end;
                  C# Code:
                  using System;
                  using System.Data;
                  using System.Text;
                  using Oracle.DataAccess.Client;
                  using Oracle.DataAccess.Types;
                  
                  namespace RowTypeTest
                  {
                    /// <summary>
                    /// Summary description for Class1.
                    /// </summary>
                    class Class1
                    {
                      /// <summary>
                      /// The main entry point for the application.
                      /// </summary>
                      [STAThread]
                      static void Main(string[] args)
                      {
                        // create and open connection
                        string constr = "User Id=hr; Password=hr; Data Source=oranet; Pooling=false";
                        OracleConnection con = new OracleConnection(constr);
                        con.Open();
                  
                        // build anonymous pl/sql block to get the data
                        StringBuilder sbSQL = new StringBuilder();
                        sbSQL.Append("declare ");
                        sbSQL.Append("  l_country_row countries%rowtype; ");
                        sbSQL.Append("begin ");
                        sbSQL.Append("  l_country_row   := get_country_row(:p_id); ");
                        sbSQL.Append("  :p_country_id   := l_country_row.country_id; ");
                        sbSQL.Append("  :p_country_name := l_country_row.country_name; ");
                        sbSQL.Append("  :p_region_id    := l_country_row.region_id; ");
                        sbSQL.Append("end;");
                  
                        // input parameter for country id
                        OracleParameter p_id = new OracleParameter();
                        p_id.OracleDbType = OracleDbType.Varchar2;
                        p_id.Size = 2;
                        p_id.Value = "UK";
                        p_id.Direction = ParameterDirection.Input;
                  
                        // input/output parameter for country id
                        // this is redundant but we are selecting * from the table...
                        OracleParameter p_country_id = new OracleParameter();
                        p_country_id.OracleDbType = OracleDbType.Varchar2;
                        p_country_id.Size = 2;
                        p_country_id.Direction = ParameterDirection.InputOutput;
                  
                        // input/output parameter for country name
                        OracleParameter p_country_name = new OracleParameter();
                        p_country_name.OracleDbType = OracleDbType.Varchar2;
                        p_country_name.Size = 40;
                        p_country_name.Direction = ParameterDirection.InputOutput;
                  
                        // input/output parameter for region id
                        OracleParameter p_region_id = new OracleParameter();
                        p_region_id.OracleDbType = OracleDbType.Decimal;
                        p_region_id.Direction = ParameterDirection.InputOutput;
                  
                        // create command object
                        OracleCommand cmd = con.CreateCommand();
                        cmd.CommandText = sbSQL.ToString();
                  
                        // add parameters to command
                        cmd.Parameters.Add(p_id);
                        cmd.Parameters.Add(p_country_id);
                        cmd.Parameters.Add(p_country_name);
                        cmd.Parameters.Add(p_region_id);
                  
                        // get the data
                        cmd.ExecuteNonQuery();
                  
                        // display data retrieved
                        Console.WriteLine("{0}, {1}, {2}", p_country_id.Value, p_country_name.Value, p_region_id.Value);
                        
                        // clean up objects
                        p_region_id.Dispose();
                        p_country_name.Dispose();
                        p_country_id.Dispose();
                        p_id.Dispose();
                        cmd.Dispose();
                        con.Dispose();
                      }
                    }
                  }
                  Output:
                  UK, United Kingdom, 1
                  This only works if the stored function returns a single row and obviously has no error handling, etc. but I hope it helps a bit...

                  - Mark
                  • 6. Re: Help - Oracle function w/RETURN VIEW_NAME%ROWTYPE
                    452123
                    Mark,

                    Wow! Above and beyond!!! Just the PL/SQL and a sentence about using parameters to access the ':' prefaced identifiers would have been 'enough to get me going'

                    Thank you so much!

                    MarFarMa
                    • 7. Re: Help - Oracle function w/RETURN VIEW_NAME%ROWTYPE
                      452123
                      Mark, (or anyone)

                      Got approval from the client to create wrapper functions / stored procedures for this implementation. Can you help me again? Given the same function, returning a rowtype object - how do I get a wrapper PL/SQL to return return the equivalent data as a refcursor?

                      I'm thinking I'd need to declare the result parameter (l_country_row countries%rowtype) as some kind of inline table(?) and then select * on it? If you could give me the official PL/SQL terms for what I need to do, I'll look up how to do it. But I'm a bit out to sea right now.

                      thanks!

                      MarFarMa
                      • 8. Re: Help - Oracle function w/RETURN VIEW_NAME%ROWTYPE
                        452123
                        Mark, (or anyone)

                        Got approval from the client to create wrapper functions / stored procedures for this implementation. Can you help me again? Given the same function, returning a rowtype object - how do I get a wrapper PL/SQL to return return the equivalent data as a refcursor?

                        I'm thinking I'd need to declare the result parameter (l_country_row countries%rowtype) as some kind of inline table(?) and then select * on it? If you could give me the official PL/SQL terms for what I need to do, I'll look up how to do it. But I'm a bit out to sea right now.

                        thanks!

                        MarFarMa
                        • 9. Re: Help - Oracle function w/RETURN VIEW_NAME%ROWTYPE
                          24208
                          MarFarMa,

                          I'm working from a location where I don't have access to Visual Studio, so this is all done in SQL*Plus. Also, I don't know if you will be able to use this solution since you need to create two new types to make this work.

                          Of course, if anyone else has a better way to do this feel free to add to this!

                          OK, here we go...

                          using the scott schema and the dept table:
                          SQL> connect scott/tiger
                          Connected.
                          SQL> -- function that returns %rowtype and a single row
                          SQL> create or replace function get_dept(p_in dept.deptno%type) return dept%rowtype
                            2  as
                            3    l_dept dept%rowtype;
                            4  begin
                            5    select * into l_dept from dept where deptno = p_in;
                            6    return l_dept;
                            7  end;
                            8  /
                          
                          Function created.
                          
                          SQL> -- object type to represent the dept%rowtype
                          SQL> create or replace type dept_type as object
                            2  (
                            3    deptno number(2),
                            4    dname  varchar2(14),
                            5    loc    varchar2(13)
                            6  );
                            7  /
                          
                          Type created.
                          
                          SQL> -- table of dept_type object type
                          SQL> create or replace type dept_list as table of dept_type;
                            2  /
                          
                          Type created.
                          
                          SQL> -- function to return the %rowtype as a ref cursor
                          SQL> create or replace function get_dept_rc(p_in dept.deptno%type) return sys_refcursor
                            2  as
                            3    -- call function to get the dept%rowtype in local variable
                            4    l_dept_row dept%rowtype := get_dept(p_in);
                            5
                            6    -- this is the trick...
                            7    -- use the %rowtype returned from get_dept to construct a dept_type object
                            8    -- the dept_type object is then used for the dept_list type
                            9    l_list dept_list := dept_list(dept_type(l_dept_row.deptno, l_dept_row.dname, l_dept_row.loc));
                          
                           10    l_rc sys_refcursor;
                           11  begin
                           12    -- use the table function to create a table from the l_list variable
                           13    open l_rc for select * from table(l_list);
                           14    return l_rc;
                           15  end;
                           16  /
                          
                          Function created.
                          
                          SQL> variable v_rc refcursor
                          SQL> exec :v_rc := get_dept_rc(20);
                          
                          PL/SQL procedure successfully completed.
                          
                          SQL> print v_rc
                          
                              DEPTNO DNAME          LOC
                          ---------- -------------- -------------
                                  20 RESEARCH       DALLAS
                          
                          1 row selected.
                          
                          SQL>
                          And there you have it. I have not tested this etc. so I am not sure how efficient this approach is and so forth. Your mileage may vary, yadda yadda yadda

                          I hope this is helpful,

                          - Mark