CODEWITHSUNDEEP
c#oracle-databaseentity-frameworkstored-proceduresplsql
Liquid Core
Liquid Core

Reputation: 1

PL/SQL - Oracle: Stored procedure returning dataset from various tables

What I need: In PLS/SQL on an Oracle DB, create a stored procedure or function with parameters, which given a declared table of , where is a ROW of a table (with all the fields), returns the resultset following the conditions given in the parameters. After, I need to call them from Microsoft Entity Framework with edmx file.

Basically the need is to being able to provide a quick report of the table contents into a pdf, matching some filters, with an oracle db.

The mantainer must be able, provided a script I give, to create and add new reports, so this needs to be dynamic.

Here's what I've got so far:

CREATE OR REPLACE type THETABLEIWANTTYPE  as table of THETABLEIWANT%TYPE

create function
  SCHEMA.THETABLEIWANT_FUNCTION(PARAM_GR in number default 1)
  return THETABLEIWANTTYPE
  PIPELINED
  as
  result_table THETABLEIWANTTYPE
  begin
     SELECT S.id, S.idg, S.sta, S.tab
      Bulk collect into result_table
      from SCHEMA.THETABLEIWANT S
      WHERE IDGR = PARAM_GR

      IF result_table.count > 0 THEN
        for i in result_table.FIRST .. result_table.LAST loop 
          pipe row (result_table(i))
          end loop
           end if
           return
  end;

But it's not working. It gives errors.

Running CREATE TYPE I get:

Compilation errors for TYPE SCHEMA.THETABLEIWANT

Error: PLS-00329: schema-level type has illegal reference to SCHEMA.THETABLEIWANT

The mantainer will launch the script creating a TYPE of the row of the table I need, then the function should return a table with the records.

Then calling it from Entity Framework I should be able to execute it like I'm calling a normal select from my table, IE:

``_dbContext.THETABLEIWANT.Where(x => x.IDGR = Param_gr).ToList();

The problem is that mantainers should be able to generate new kind of reports with any select inside without the need of my intervention on the software code.

Any hint?

It's ok also to bulk all the select result into a temp table but it has to be dynamic as column will be changing

Upvotes: 1

Views: 3318

Answers (1)

Liquid Core
Liquid Core

Reputation: 1

I ended up to write a PLS/SQL procedure that returns a cursor and managing it from C# code with Oracle.ManagedDataAccess Library.

Here's the procedure, for anyone interested:

CREATE OR REPLACE PROCEDURE SCHEMA.PROC_NAME(
PARAM_1 VARCHAR2,
RESULT OUT SYS_REFCURSOR)
IS
BEGIN
 OPEN RESULT FOR
SELECT A, V, C AS MY_ALIAS from SCHEMA.TABLE WHERE FIELD = PARAM_1 AND FIELD_2 = 'X';
END;

And here's the C# code for calling and getting the result:

OracleConnection conn = new OracleConnection("CONNECTIONSTRING");

     try
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            List<OracleParameter> parametri = new List<OracleParameter>()
                    {
                        new OracleParameter
                        {
                            ParameterName = nameof(filter.PARAM_1),
                            Direction = ParameterDirection.Input,
                            OracleDbType = OracleDbType.NVarchar2,
                            Value = filter.PARAM_1
                        }
                    };


            OracleCommand cmd = conn.CreateCommand();
            cmd.Parameters.AddRange(parametri.ToArray());

            OracleParameter cursor = cmd.Parameters.Add(
                new OracleParameter
                {
                    ParameterName = "RESULT",
                    Direction = ParameterDirection.Output,
                    OracleDbType = OracleDbType.RefCursor
                }
            );

            cmd.CommandText = procedureName;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
            using (OracleDataReader reader = ((OracleRefCursor)cursor.Value).GetDataReader())
            {
                if (reader.HasRows)
                    while (reader.Read())
                    {
                        //Iterate the result set
                    }
            }
    }
    catch(Exception ex)
    {
        //Manage exception
    }

Upvotes: 1

Related Questions