C# OleDb Oracle function and Table Types

Question

i'm trying to invoke an Oracle function that returns a type encapsulated in a Table

Type Object

create or replace
type Z_TBL_STRUCTURE_CODE
AS OBJECT
(
    PROJ_ID varchar2(50 BYTE)
);

Type table

create or replace
type Z_TABLE_STRUCTURE_CODE AS TABLE of Z_TBL_STRUCTURE_CODE;

Oracle Function

create or replace
FUNCTION Z_TESTE_IN_FUNC
(
    var_teste in varchar2
)
return Z_TABLE_STRUCTURE_CODE
AS
tab Z_TABLE_STRUCTURE_CODE;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT 
    CAST(
    MULTISET(
      select count(*) into num from structure 
      where structure_code in ('|| var_teste ||')) as Z_TABLE_STRUCTURE_CODE)
    into tab
    from dual;';
  dbms_output.put_line(var_teste);
  return tab;
 END Z_TESTE_IN_FUNC;

NOTE: PLEASE IGNORE THE EXECUTE IMMEDIATE, ITS JUST FOR A TEST.

NOTE2: I KNOW THERE IS AN OPTION TO AVOID TYPES (USING CURSORS) BUT I HAVE NOT YET BEEN ABLE TO UNDERSTAND THE TOPIC SO I WOULD ASK YOU TO IGNORE CURSOS WHEN ANSWERING, UNLESS ITS THE ONLY OPTION AVAILABLE.

C# Code

public DataTable getTaskStartFinish()
{
    OleDbConnection con = null;
    OleDbDataReader reader = null;
    try{
        con = new OleDbConnection(ConfigurationManager.ConnectionStrings["OracleBD"].ConnectionString);

        OleDbCommand cmd = new OleDbCommand("", con);
        cmd.CommandText = "Z_TESTE_IN_FUNC";
        cmd.CommandType = CommandType.StoredProcedure;

        OleDbParameter retval = new OleDbParameter("retval", OleDbType.VarChar, 10);
        retval.Direction = ParameterDirection.ReturnValue;

        OleDbParameter inval = new OleDbParameter("inval", OleDbType.Variant, 50);
        inval.Direction = ParameterDirection.Input;
        inval.Value = "1";

        cmd.Parameters.Add(retval);
        cmd.Parameters.Add(inval);
        con.Open();

        reader = cmd.ExecuteReader();

        DataTable dt = new DataTable();
        dt.Load(reader);

        return dt;
    }
    catch(Exception ex)
    {
        throw new Exception("getTaskStartFinish error: " + ex.Message);
    }
    finally
    {
        if(con != null)
            con.Close();
        if(reader != null || !reader.IsClosed)
            reader.Close();
    }
}

The current Error i get is the following: ORA-06550: line 1, column 13: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored

I have seen in the net similar examples but all using ExecuteScalar() or some code for procedures or function returning singular values but not tables.

Xelom · Answer 1 · 2013-04-11T18:44:19.890

0

Well if you want to return a single value my suggestion is(Edited for multiple rows):

        OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["OracleBD"].ConnectionString);
        OleDbCommand cmd = new OleDbCommand("Z_TESTE_IN_FUNC", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@procValueName", "myValue");
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        DataTable dt = new DataTable();
        conn.Open();
        try
        {
            da.Fill(dt);
        }
        catch (Exception excp)
        {
            //Handle Exception
        }
        finally
        {
            conn.Close();
        }

Edit 2: Try this for function

OleDbConnection con = new OleDbConnection(cntStr);
con.Open();
OleDbCommand cmd = new OleDbCommand("F_TESTFUNC", con); 
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OleDbParameter("retVal", OleDbType.VarChar, 11,ParameterDirection.ReturnValue, true, 0, 0, "retVal", DataRowVersion.Current,null);
cmd.Parameters.Add("ID", strID);
cmd.ExecuteScalar();
con.Close();

edited Apr 11 '13 at 18:44

answered Apr 11 '13 at 09:58

Xelom

1,615
1
13
23

Hi Xelom, that would be ok if i wanted a single value yes. but i pretend to obtain multiple values and multiple rows. I have a way of doing this but it is insecure and prone to sql injection (using the basic way of setting my sql and variables in a string and send the string in the cmd.CommandText). Since i want to avoid doing this i tried the way i showed but its not working. – FEST Apr 11 '13 at 10:08
Editing my answer for multiple rows then – Xelom Apr 11 '13 at 12:49
Hi Xelom. So i tried you code and i got the following error: {System.Data.OleDb.OleDbException: ORA-06550: line 1, column 7: PLS-00221: 'Z_TESTE_IN_FUNC' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored. Do i need to do something else because i want to execute a function? can i user whatever name i want in the addwithParameter? – FEST Apr 11 '13 at 17:54

C# OleDb Oracle function and Table Types

1 Answers1