Solved

Calling oracle function with c#

Posted on 2010-08-20
26
539 Views
Last Modified: 2013-12-19
Hi Experts,

How do I call the he_getgrades function from .net so that it fills a gridview control, with the c# code attached. I keep recieving he_getgrades is not a function?

thanks
private void ProcessGrades()
    {
        // Create the connection object
        OracleConnection con = new OracleConnection();
        // Specify the connect string
        con.ConnectionString = "User Id=****;Password=****;Data Source=***;";
        con.Open();
        // Establish a new Oracle Command
        OracleCommand cmd = new OracleCommand("", con);
        // Set the Command text type
        cmd.CommandText = "he_getgrades";
        // Set the Command type
        cmd.CommandType = CommandType.StoredProcedure;
        OracleParameter authorsEmailParam = new OracleParameter("vp_id", OracleDbType.Varchar2);
        authorsEmailParam.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(authorsEmailParam);
        try
        {
            cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
           lblMsg.Text = ex.Message.ToString();
           lblMsg.Visible = true;
        }
        finally
        {
            // DB Connection is placed back into the pool
            con.Dispose();
        }
    }

Open in new window

CREATE OR REPLACE FUNCTION HE.he_getgrades (vp_id IN number)
   RETURN TYPES.ref_cursor
AS
   grades_cursor   TYPES.ref_cursor;
   
   cursor temp_cursor is select * from he_grades for update;
   
   cursor gradecalc_cursor is 
    select  substr(a_reference,0,8) a_reference, 
            sum(a_value) a_value, 
            round(avg(A_DERIVEDPERCENTAGE*a_value/100)*count(*)) percentage,
            max(a_attemptnumber) a_attemptnumber
    from he_grades
    where a_percentage>0 
            and a_type='E' 
    group by substr(a_reference,0,8);
    
    lcl_percentage number;
BEGIN

delete from he_grades;

insert into he_grades ( 
           a_reference,
           a_name,
           a_id,
           a_attemptnumber,
           a_enrolment,
           a_unit,
           a_type,
           a_grade,
           a_pass,
           a_value,
           a_assessmententrydate,
           a_percentage)
  select   t6.a_reference,
           t6.a_name,
           t6.a_id,
           t6.a_attemptnumber,
           t6.a_enrolment,
           t6.a_unit,
           t6.a_type,
           t6.a_grade,
           t6.a_pass,
           t6.a_value,
           trunc (t6.a_assessmententrydate) a_assessmententrydate,
           t6.a_percentage
    from   unite.capd_student@utest t3,
           unite.capd_module@utest t1,
           unite.capd_offering@utest t7,
           unite.capd_moduleenrolment@utest t4,
           unite.capd_assessment@utest t6,
           unite.capd_module@utest t5,
           unite.capd_person@utest t2
   where       t3.s_id(+) = t2.p_id
           and (t6.a_enrolment = t4.e_id)
           and (t4.e_student = t2.p_id)
           and (t2.p_id = vp_id)
           and (t4.e_module = t5.m_id)
           and (t5.m_type = 'H')
           and (t7.o_destination = t5.m_id)
           and (t7.o_source = t1.m_id)
           --and (length (t6.a_reference) > 8)
           and (substr (t6.a_reference, 8, 1) = 'S');

Open in new window

0
Comment
Question by:SirReadAlot
  • 14
  • 7
  • 3
  • +1
26 Comments
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
What are you using ? System.Data.OracleClient or Oracle.DataAccess.Client
0
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
cmd.CommandText = HE.he_getgrades


and if you're going for a grid view:

SqlDataReader dr;

//later on
dr = cmd.ExecuteDataReader();

while (dr.Read())
{
//populate gridview.
gv1["column"] = dr["column"];
}

0
 

Author Comment

by:SirReadAlot
Comment Utility
hi dhaest, i am using
Oracle.DataAccess.Client
0
 

Author Comment

by:SirReadAlot
Comment Utility
hi ged325 that was the link i used but got stuck
0
 

Author Comment

by:SirReadAlot
Comment Utility
i know the functions works but can't understand y it is not being called

see this
static void Main(string[] args)
        {
            using (OracleConnection objConn = new OracleConnection("Data Source=****; User ID=***;Password=****;"))
           {
                OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandText = "he_getgrades";
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.Add("vp_id", OracleDbType.NVarchar2).Value = 2;
                objCmd.Parameters.Add("var_count", OracleDbType.NVarchar2).Direction = ParameterDirection.ReturnValue;

                try
                {
                    objConn.Open();
                    objCmd.ExecuteNonQuery();
                    System.Console.WriteLine("Number of employees in department 20 is {0}", objCmd.Parameters["var_count"].Value);

                }

                catch (Exception ex)
                {
                    System.Console.WriteLine("Exception: {0}", ex.ToString());
                    Console.ReadLine();

                }

                objConn.Close();

            }

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
I can't test it, but watch the comment on this thread:
http://www.velocityreviews.com/forums/t385408-anyone-know-how-to-call-an-oracle-function-from-c.html

However, it only works with System.Data.OracleClient NOT Oracle.DataAccess.Client
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
In oracle you typically 'select' a function.

Check out:
http://forums.oracle.com/forums/thread.jspa?threadID=387950

I'm also confused about what the function is actually doing.  First there's no 'return'.

What happens if two people execute the code at the same time?

once the Oracle function actually returns a cursor you don't want ExecuteNonQuery.  you likely want something like a datareader to bind to the gridview.
0
 

Author Comment

by:SirReadAlot
Comment Utility
I'm also confused about what the function is actually doing.  First there's no 'return'.

What happens if two people execute the code at the same time?

once the Oracle function actually returns a cursor you don't want ExecuteNonQuery.  you likely want something like a datareader to bind to the gridview

i only pasted some of the function code
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
Not an oracle guy so I can't help you there, but it looks like the function is returning a ref_cursor:  (which I'm assuming is oracle's way of saying a table)
RETURN TYPES.ref_cursor

0
 

Author Comment

by:SirReadAlot
Comment Utility
am out of ideas
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>i only pasted some of the function code

That accounts for the 'return' question but not the rest of the questions.

You should be able to create a datareader or dataset.  execute a generic sql select: 'select he_getgrades (1) from dual' and bind the reader/dataset to the gridview.
0
 

Author Comment

by:SirReadAlot
Comment Utility
hi slightwv,

don't understand.. but at this point i am really puzzles as to why I can't call the function.

I am also using
 using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:SirReadAlot
Comment Utility
I am suppose to run this
select he_getgrades (1) from dual

....I did but i received an 06553 error
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Again, in Oracle you typically 'select' a function.

you are telling .Net to expect you execute a procedure not a function:

cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();


When you tried the select, did you change these parameters?

Is there a specific reason you created on Oracle function to return the cursor and not a stored procedure with an output parameter?
0
 

Author Comment

by:SirReadAlot
Comment Utility
r u suggesting to change it to

cmd.Connection=cnn
cmd.CommandType = CommandType.Text ?


>Is there a specific reason you created on Oracle function to return the cursor and not a stored procedure with an output parameter?

function was created a couple of years ago
0
 

Author Comment

by:SirReadAlot
Comment Utility
would it be easier to change it to a stored procedure?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>r u suggesting to change it to

Yes.  Then you will likely use ExecuteScalar not ExecuteNonQuery.

>>function was created a couple of years ago

Then I doubt you can change it since it is probably used elsewhere in code.

>would it be easier to change it to a stored procedure?

In this case, I feel a stored procedure would be easier.  I've never created a function that returned a cursor.  I've always done it with a stored procedure.

But based on the above statement, I probably wouldn't change it since it might break code somewhere else.

I just went back and looked at the code you posted.  You will also have issues with your parameters:

OracleParameter authorsEmailParam = new OracleParameter("vp_id", OracleDbType.Varchar2);

I might be confusing my .Net data providers but I believe you are using ODP.Net.  There are numerous code examples that come with that data provide under %ORACLE_HOME%\ODP.NET\samples.

If memory serves, there is a ref cursor example in there.
       
The return value of the function is a ref cursor, not a varchar2...
0
 

Author Comment

by:SirReadAlot
Comment Utility
ok,

I was just doing a sense check but this time using using System.Data.OracleClient; and the codes below

CREATE OR REPLACE function HE_REPORTING_DATABASE.testme(pin_deptno number)
 return number
is
 var_count number;
begin
 select count(*) into var_count
 from demo
 where studid=pin_deptno;
 return var_count;
end testme


it returns zero records but i know i have 99 records
using (OracleConnection objConn = new OracleConnection("Data Source=; User ID=;Password=;"))
            {

                OracleCommand objCmd = new OracleCommand();

                objCmd.Connection = objConn;

                objCmd.CommandText = "HE_REPORTING_DATABASE.testme";

                objCmd.CommandType = CommandType.StoredProcedure;

                objCmd.Parameters.Add("pin_deptno", OracleType.Number).Value = 20;

                objCmd.Parameters.Add("return_value", OracleType.Number).Direction = ParameterDirection.ReturnValue;



                try
                {

                    objConn.Open();

                    objCmd.ExecuteNonQuery();

                    System.Console.WriteLine("Number of employees in department 20 is {0}", objCmd.Parameters["return_value"].Value);
                    Console.ReadLine();

                }

                catch (Exception ex)
                {

                    System.Console.WriteLine("Exception: {0}", ex.ToString());
                    Console.ReadLine();

                }



                objConn.Close();

            }

        }

    }

}

Open in new window

0
 

Author Comment

by:SirReadAlot
Comment Utility
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>is   var_count a temp table

I don't understand that last question.  You declared it in your function:  var_count number;

>>it returns zero records but i know i have 99 records

The code looks fine.  I would verify you are selecting the rows you think you are.

I found almost the exact same code here:
http://msdn.microsoft.com/en-us/library/ms971506.aspx

If I get some time later I'll try to st up a working test case.

0
 

Author Comment

by:SirReadAlot
Comment Utility
hi,
this is what makes up demo table (but actually its a view)

 SELECT   SURNAME,
            FORENAMES,
            STUDREF,
            STATUS,
            M_REFERENCE,
            COURSETITLE,
            STUDID
     FROM   STUDENT_ENROLMENTS
    WHERE   M_REFERENCE = 'S653D1F-09';
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
I feel silly... are you ready?

A small blurb in the link I posted in http:#a33484710 hold the key:

"For some reason only knows to Larre, your returnvalue must be the FIRST parameter"

Given the following Oracle table/function:
---------------------------------
drop table tab1 purge;
create table tab1(col1 number);

insert into tab1 values(1);
insert into tab1 values(2);
insert into tab1 values(2);
commit;

create or replace function myFunc (inputID number)
return number
is
myresult number;
begin
    select count(*) into myresult from tab1 where col1=inputID;
   
    return myresult;
end;
/



using System;
using System.Data;

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

public class Bob
{

	public static void Main(string[] args)
	{

			OracleConnection con = new OracleConnection();

			con = new OracleConnection("User Id=bud;Password=bud;Data Source=bud");

			OracleCommand cmd = new OracleCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection = con;
			cmd.CommandText = " myFunc ";

			cmd.Parameters.Add("myResult", OracleDbType.Int32);
			cmd.Parameters["myResult"].Direction = ParameterDirection.ReturnValue;

			cmd.Parameters.Add("myInput", OracleDbType.Int32).Value = 1;

			con.Open();
			cmd.ExecuteNonQuery();

			Console.WriteLine("My result is: " + cmd.Parameters["myResult"].Value);


			con.Close();
			con.Dispose();

	}

}

Open in new window

0
 

Author Comment

by:SirReadAlot
Comment Utility
Thanks slightwv,

i will try this out on monday
0
 

Author Comment

by:SirReadAlot
Comment Utility
hi slightwv,

you awake??

could you please explain why when i toggle between
      cmd.Parameters.Add("myInput", OracleDbType.Int32).Value = 1;

      cmd.Parameters.Add("myInput", OracleDbType.Int32).Value = 2;
or
      cmd.Parameters.Add("myInput", OracleDbType.Int32).Value = 1;

myResult value in
Console.WriteLine("My result is: " + cmd.Parameters["myResult"].Value);
always returns 1

Contents of Tab1 is
Col1
1
2
3
4

thanks

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>you awake??
Almost...

>>always returns 1

If you are running my test code, I select count(*).  Based on the data in your table there is only ever 1 value.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
visual Basic Net vs c# net 8 54
asp.net bundle 8 34
Duplicate a row 2 29
How to use xmlReader and idatareader  ? 4 44
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now