• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1471
  • Last Modified:

Execute an oracle Function that returns a reference cursor in C#

Hi Experts,

I am trying to achieve the above but I keep receiving an invalid sql error msg.

I know that there is some problems with the c# code and not the function as other legacy applications uses it.

thanks

The c# code attempts to call the function below
=============================================
CREATE OR REPLACE FUNCTION HE_REPORTING_DATABASE.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');
           
/* discard the first attempts where a second exists */
delete from he_grades
    where a_attemptnumber=1 and a_reference in (select a_reference from he_grades where a_attemptnumber=2);
 


  for r in temp_cursor
    loop
        if (r.a_percentage is not null) and (r.a_attemptnumber=1) then
          if r.a_percentage>=70 then
            update he_grades set a_pass='P', a_grade='A', a_derivedpercentage=a_percentage
            where a_reference=r.a_reference;
          end if;
          if (r.a_percentage>=60) and (r.a_percentage<70) then
            update he_grades set a_pass='P', a_grade='B', a_derivedpercentage=a_percentage
            where a_reference=r.a_reference;
          end if;
          if (r.a_percentage>=50) and (r.a_percentage<60) then
            update he_grades set a_pass='P', a_grade='C', a_derivedpercentage=a_percentage
            where a_reference=r.a_reference;
          end if;
          if (r.a_percentage>=40) and (r.a_percentage<50) then
            update he_grades set a_pass='P', a_grade='D', a_derivedpercentage=a_percentage
            where a_reference=r.a_reference;
          end if;
          if (r.a_percentage<40) then
            update he_grades set a_pass='F', a_grade='E', a_derivedpercentage=a_percentage
            where a_reference=r.a_reference;
          end if;
           
        end if;
       
        if (r.a_percentage is not null) and (r.a_attemptnumber=2) then
          if r.a_percentage>=40 then
            update he_grades set a_pass='P', a_grade='D', a_derivedpercentage=40
            where a_reference=r.a_reference;
          end if;
        end if;
    end loop;  

update he_grades
set a_grade=null, a_percentage=null, a_pass=null
where a_type='S';
 

    for r in gradecalc_cursor
        loop
            if r.a_value=100 then
            update he_grades set "A_PERCENTAGE"=r.percentage
            where he_grades.a_reference=r.a_reference;
            end if;
        end loop;

   
  for r in temp_cursor
    loop
        if (r.a_percentage is not null) and (r.a_attemptnumber=1) then
          if r.a_percentage>=70 then
            update he_grades set a_pass='P', a_grade='A', a_derivedpercentage=a_percentage
            where a_reference=r.a_reference;
          end if;
          if (r.a_percentage>=60) and (r.a_percentage<70) then
            update he_grades set a_pass='P', a_grade='B', a_derivedpercentage=a_percentage
            where a_reference=r.a_reference;
          end if;
          if (r.a_percentage>=50) and (r.a_percentage<60) then
            update he_grades set a_pass='P', a_grade='C', a_derivedpercentage=a_percentage
            where a_reference=r.a_reference;
          end if;
          if (r.a_percentage>=40) and (r.a_percentage<50) then
            update he_grades set a_pass='P', a_grade='D', a_derivedpercentage=a_percentage
            where a_reference=r.a_reference;
          end if;
          if (r.a_percentage<40) then
            update he_grades set a_pass='F', a_grade='E', a_derivedpercentage=a_percentage
            where a_reference=r.a_reference;
          end if;
           
        end if;
       
        if (r.a_percentage is not null) and (r.a_attemptnumber=2) then
          if r.a_percentage>=40 then
            update he_grades set a_pass='P', a_grade='D', a_derivedpercentage=40
            where a_reference=r.a_reference;
          end if;
        end if;
    end loop;  

    insert into he_grades ( a_name, a_value )
        select
        'Points at level '||substr(a_reference,4,1) clevel,
        sum(a_value) value
        from
        he_grades
        where
        a_type='S' and a_pass='P'
        group by substr(a_reference,4,1)
        order by substr(a_reference,4,1) asc;


  OPEN grades_cursor FOR SELECT * FROM he_grades order by a_reference;

   
   RETURN grades_cursor;
END;
/
==============================================

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Xml.Linq;
//using System.Data.OracleClient;
// ODP.NET Import(s)
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace ConsoleApplication1
{
    class Program
    {
        public static void Main(string[] args)
        {

            OracleConnection con = new OracleConnection();

            con = new OracleConnection("Data Source=**; User ID=**;Password=***;");


            OracleCommand cmd = new OracleCommand();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            cmd.CommandText = " HE_GETGRADES  ";

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

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

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

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


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

        }
    }
}

Open in new window

0
SirReadAlot
Asked:
SirReadAlot
  • 28
  • 19
  • 8
  • +2
1 Solution
 
pivarCommented:
Hi,

I'm not an Oracle expert, but to me it looks like you're getting a return value, in that case use


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

/peter
0
 
HugoHiaslCommented:
I think you can manage it with the information on the following german page:

http://www.fulloracle.de/CONT_EN_c04bf41e-80ea-4a62-b12b-e005bd739c1f.aspx


The description is in german language but you seem to be experienced enough to extract the information you need from the code snippets.

I hope this helps.

0
 
SirReadAlotAuthor Commented:
okay guys i will give it a go
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
SirReadAlotAuthor Commented:
Hi All,

I have tried this code...

but i recieve this error OracleDbType.RefCursor, ParameterDirection

thanks
public static void Main(string[] args)
        {
            OracleConnection con = new OracleConnection();

            con = new OracleConnection("Data Source=***; User ID=****;Password=***");
            OracleCommand cmd = new OracleCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.CommandText = "HE_GETGRADES";
            cmd.CommandType = CommandType.StoredProcedure;

            OracleParameter p1 = cmd.Parameters.Add("grades_cursor", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
            cmd.Parameters.Add("vp_id", OracleDbType.Int32).Value = 1;
            con.Open();
            cmd.ExecuteNonQuery();
            Console.WriteLine("My result is: " + cmd.Parameters["grades_cursor"].Value);
            Console.ReadLine();

        }
        }
    }

Open in new window

0
 
SirReadAlotAuthor Commented:
ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'HE_GETGRADES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
0
 
käµfm³d 👽Commented:
It's my understanding that when you return a refcursor, you have to treat it as a reader (being sure to open it--and leave it open--before returning from the proc. In your code, use ExecuteReader() when you execute the command against the database.
this.cmd.Parameters.Add(new OracleParameter("param_name", OracleDbType.RefCursor, ParameterDirection.Output));

this.reader = this.cmd.ExecuteReader();

Open in new window

0
 
SirReadAlotAuthor Commented:
HI kAUFMED,

is this what you mean

public static void Main(string[] args)
        {
            OracleConnection con = new OracleConnection();

            con = new OracleConnection("Data Source=; User ID=;Password=");
            OracleCommand cmd = new OracleCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.CommandText = "HE_GETGRADES";
            cmd.CommandType = CommandType.StoredProcedure;

          //  OracleParameter p1 = cmd.Parameters.Add("grades_cursor", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
          cmd.Parameters.Add(new OracleParameter("grades_cursor", OracleDbType.RefCursor, ParameterDirection.Output));
             

            cmd.Parameters.Add("vp_id", OracleDbType.Int32).Value = 1;
            con.Open();
            cmd.ExecuteReader();
            Console.WriteLine("My result is: " + cmd.Parameters["grades_cursor"].Value);
            Console.ReadLine();

        }
0
 
SirReadAlotAuthor Commented:
I have the function above,

how do I all it with c#?   all the examples i have googled gets me no where.
0
 
käµfm³d 👽Commented:
A bit more like this:
public static void Main(string[] args)
{
    OracleConnection con = new OracleConnection();
    OracleDataReader reader;

    con = new OracleConnection("Data Source=; User ID=;Password=");
    OracleCommand cmd = new OracleCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = con;
    cmd.CommandText = "HE_GETGRADES";
    cmd.CommandType = CommandType.StoredProcedure;

    //  OracleParameter p1 = cmd.Parameters.Add("grades_cursor", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
    cmd.Parameters.Add(new OracleParameter("grades_cursor", OracleDbType.RefCursor, ParameterDirection.Output));


    cmd.Parameters.Add("vp_id", OracleDbType.Int32).Value = 1;
    con.Open();
    reader = cmd.ExecuteReader();    // Assign the reader

    // Check that reader has something in it
    if (reader != null && reader.HasRows)
    {
        // Loop through reader until it says it doesn't have any more data
        while (reader.Read())
        {
            Console.WriteLine("My result is: " + cmd["name_of_column_selected_in_cursor"].ToString());   // Write the data
        }

        Console.ReadLine();
    }
}

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I'm still concerned about two people calling this at the same time...
 
The following was tested using 10.2.0.3 (very similar to the code above.

Given the Following Oracle table and data:
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 sys_refcursor
is
myresult sys_refcursor;
begin
open myresult for select * 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=username;Password=pwd;Data Source=ORCL");

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

			cmd.Parameters.Add("myResult", OracleDbType.RefCursor);

			cmd.Parameters["myResult"].Direction = ParameterDirection.ReturnValue;

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

			con.Open();


      		OracleDataReader reader;

	        reader = cmd.ExecuteReader();

        	reader.Read();

			Console.WriteLine("My result is: " + reader.GetDecimal(0));

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

	}

}

Open in new window

0
 
SirReadAlotAuthor Commented:
Hi,
This is what I have arrived at using the code



ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'HE_GETGRADES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

when I CHANGE
cmd.CommandType = CommandType.Stordprocedure;

to

cmd.CommandType = CommandType.text;

i get invalid sql
public static void Main(string[] args)
        {
            OracleConnection con = new OracleConnection();
            OracleDataReader reader;

            con = new OracleConnection("Data Source=***; User ID=***;***");
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = con;
            cmd.CommandText = "HE_GETGRADES";
            cmd.CommandType = CommandType.Stordprocedure;
            cmd.Parameters.Add(new OracleParameter("grades_cursor", OracleDbType.RefCursor, ParameterDirection.Output));
                      
            con.Open();
            reader = cmd.ExecuteReader();    // Assign the reader

            // Check that reader has something in it
            if (reader != null)
            {
                // Loop through reader until it says it doesn't have any more data
                while (reader.Read())
                {
                    Console.WriteLine("My result is: " + cmd.Parameters["vp_id"].ToString());   // Write the data
                }

                Console.ReadLine();
            }
        }
    }
}

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Check out my code.  

We sort of touched on this in your other question:  
I believe this error is due to "ParameterDirection.Output" in the way you are calling the function.
0
 
SirReadAlotAuthor Commented:
Hi Slightwv,

I have tried this

but it errors on the   OracleDataReader reader line.

==========
ORA-06550: line 1, column 8:
PLS-00306: wrong number or types of arguments in call to 'HE_GETGRADES'
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
===========
public static void Main(string[] args)
        {

            OracleConnection con = new OracleConnection();
            OracleDataReader reader;


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

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

            cmd.Parameters.Add("grades_cursor", OracleDbType.Int32);
            cmd.Parameters.Add(new OracleParameter("grades_cursor", OracleDbType.RefCursor, ParameterDirection.Output));


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

            con.Open();
            reader = cmd.ExecuteReader();    // Assign the reader

            // Check that reader has something in it
            if (reader != null)
            {
                // Loop through reader until it says it doesn't have any more data
                while (reader.Read())
                {
                    Console.WriteLine("My result is: " + cmd.Parameters["vp_id"].ToString());   // Write the data
                }

                Console.ReadLine();
            }
        }
    }
}

Open in new window

0
 
SirReadAlotAuthor Commented:
Hi

not sure if this helps but the funtion is meant to recieve an id (vp_id), which will bring up all data relevant to the record. I am not sure if it will work without passing an id to it??
0
 
slightwv (䄆 Netminder) Commented:
that doesn't look like the code example I posted in http:#a33501343
0
 
SirReadAlotAuthor Commented:
sorry,

was looking at something else..........almost there

not sure if i need
  //  cmd.Parameters.Add("vp_id", OracleDbType.Int32).Value = 1;

but i got this error

ORA-06550: line 1, column 16:
PLS-00306: wrong number or types of arguments in call to 'HE_GETGRADES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
public static void Main(string[] args)
        {

            OracleConnection con = new OracleConnection();

            con = new OracleConnection("Data Source=***; User ID=***;Password=**");

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

            cmd.Parameters.Add("grades_cursor", OracleDbType.RefCursor);

            cmd.Parameters["grades_cursor"].Direction = ParameterDirection.ReturnValue;

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

            con.Open();


            OracleDataReader reader;

            reader = cmd.ExecuteReader();

            reader.Read();

            Console.WriteLine("My result is: " + reader.GetDecimal(0));

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

        }
    }
}

Open in new window

0
 
käµfm³d 👽Commented:
@slightwv

Of course the code you posted in 33501343 is not safe code anyways since you are not checking if the reader has rows before you call GetDecimal()--a recipe for a NullReferenceException I'm sure  :)

Yes, you would need to pass in the vp_id as a parameter. The code I posted would have required you to change your SP to take an additional parameter of type REFCURSOR and use it as an OUT parameter. I'm not sure if you can return Refcursor as a return value--I'll let the others speak to that. My initial thought is that you cannot, but I can't remember what I read.  :)
0
 
SirReadAlotAuthor Commented:
when

  cmd.Parameters.Add("vp_id", OracleDbType.Int32).Value = 1; is included
i get

Operation is not valid due to the current state of the object
0
 
slightwv (䄆 Netminder) Commented:
>>not sure if i need

Yes you need this.  Your function takes an input parameter doesn't it?
0
 
slightwv (䄆 Netminder) Commented:
>>is not safe code anyways since you are not checking if the reader

Yes but it's example code only.
0
 
slightwv (䄆 Netminder) Commented:
>>Operation is not valid due to the current state of the object

Please post the code causing this error.
0
 
SirReadAlotAuthor Commented:
this line
Console.WriteLine("My result is: " + reader.GetDecimal(0));
0
 
käµfm³d 👽Commented:
>>  Yes but it's example code only.

Better to give a complete example which does not include a subtle bug, methinks  :)
0
 
käµfm³d 👽Commented:
>>  this line
>>  Console.WriteLine("My result is: " + reader.GetDecimal(0));

Ouila!
0
 
SirReadAlotAuthor Commented:
??
0
 
käµfm³d 👽Commented:
@SirReadAlot

Not you, sorry :)
0
 
SirReadAlotAuthor Commented:
@ kau

do you know what the problem is ?
0
 
käµfm³d 👽Commented:
ATM, no. The queries I have done have always been queries, not calls to functions; I cannot immediately tell what the issue is. Before I surmise, I will try to make a test function and call it--unless slightwv beats me to it  :)
0
 
slightwv (䄆 Netminder) Commented:
@kauf,
I think its the 'feature' you mentioned.  My guess is the reader isn't returning any rows.

ODP.Net doesn't appear to have a HasRows.

@SirReadAlot,
Give this a try.  After you execute the reader, check the first read to see it it has a row returned.
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=username;Password=pwd;Data Source=ORCL");

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

			cmd.Parameters.Add("myResult", OracleDbType.RefCursor);

			cmd.Parameters["myResult"].Direction = ParameterDirection.ReturnValue;

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

			con.Open();


      		OracleDataReader reader;

	        reader = cmd.ExecuteReader();
	        
	        	if (reader.Read()) {
					Console.WriteLine("My result is: " + reader.GetDecimal(0));
				} else {
					Console.WriteLine("No Rows Found.");
				}

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

	}

}

Open in new window

0
 
käµfm³d 👽Commented:
>>  ODP.Net doesn't appear to have a HasRows.

It most certainly does:  http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledatareader.hasrows.aspx
0
 
slightwv (䄆 Netminder) Commented:
It wouldn't compile for me using the 10g ODAC.

You sure that link isn't for the MSoft data provider and not Oracle's?
0
 
SirReadAlotAuthor Commented:
hi guys,

the code returned....

No Rows Found
0
 
SirReadAlotAuthor Commented:
the legacy application which calls this function uses this code

<%
Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Command2__vp_id=request.querystring("id")
set Command2=Server.CreateObject("ADODB.Command")
Command2.ActiveConnection=MM_whstudents_STRING
Command2.CommandText="he_getgrades"
Command2.CommandType=4
Command2.CommandTimeout=0
Command2.Prepared=true
Command2.Parameters.Append Command2.CreateParameter("vp_id", 200, 1, 100, Command2__vp_id)<<<<<
set recordset2=command2.execute
%>
0
 
SirReadAlotAuthor Commented:
I think if i pass the vp_id when calling the function, we can see some results---- but not sure

 public static void Main(string[] args)
        {
            string sqlStr = "15000028435389";          ==============>here  
            OracleConnection con = new OracleConnection();
            con = new OracleConnection("Data Source=WH1; User ID=he_reporting_database;Password=PASSWORD");
            OracleCommand cmd = new OracleCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.CommandText = "HE_GETGRADES";
            cmd.Parameters.Add("grades_cursor", OracleDbType.RefCursor,sqlStr);=================Error       cannot convert from 'string' to 'System.Data.ParameterDirection'      

            cmd.Parameters["grades_cursor"].Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add("vp_id", OracleDbType.Int32).Value = 1;
            con.Open();
            OracleDataReader reader;
            reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                Console.WriteLine("My result is: " + reader.GetDecimal(0));
                Console.ReadLine();
            }
            else
            {
                Console.WriteLine("No Rows Found.");
                Console.ReadLine();
            }
            con.Close();
            con.Dispose();
0
 
slightwv (䄆 Netminder) Commented:
>>the code returned....No Rows Found

This means the Oracle function didn't have any rows for the vp_id you passed in.

>> string sqlStr = "15000028435389";          ==============>here  
>>cmd.Parameters.Add("grades_cursor", OracleDbType.RefCursor,sqlStr);=================Error      

I have NO IDEA what you are trying to do here..

If 15000028435389 is an actual vp_id then this needs to be the input parameter.  Put the code back as it was and do:

 cmd.Parameters.Add("vp_id", OracleDbType.Int32).Value = 15000028435389;
0
 
SirReadAlotAuthor Commented:
Value was either too large or too small for an Int32

using
 this code



 public static void Main(string[] args)
        {
         

            OracleConnection con = new OracleConnection();
            con = new OracleConnection("Data Source=@@@; User ID=@@@;Password=@@@");
            OracleCommand cmd = new OracleCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.CommandText = "HE_GETGRADES";
            cmd.Parameters.Add("grades_cursor", OracleDbType.RefCursor);
            cmd.Parameters["grades_cursor"].Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add("vp_id", OracleDbType.Int32).Value = 15000028435389;
            con.Open();
            OracleDataReader reader;
            reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                Console.WriteLine("My result is: " + reader.GetDecimal(0));
                Console.ReadLine();
            }
            else
            {
                Console.WriteLine("No Rows Found.");
                Console.ReadLine();
            }
            con.Close();
            con.Dispose();
0
 
slightwv (䄆 Netminder) Commented:
Then change the datatype...

 cmd.Parameters.Add("vp_id", OracleDbType.Decimal).Value = 15000028435389;
0
 
SirReadAlotAuthor Commented:
i have changed it to
    cmd.Parameters.Add("vp_id", OracleDbType.Int64).Value = 15000028435389;

but not sure how to changed the cast
  Console.WriteLine("My result is: " + reader.GetDecimal(0)); >>>special cast required
0
 
SirReadAlotAuthor Commented:
sorry did not see that..

done the decimal as well , it came up with the same error
0
 
slightwv (䄆 Netminder) Commented:
>>but not sure how to changed the cast

you shouldn't have to.

0
 
slightwv (䄆 Netminder) Commented:
I tried it with Int64 and Decimal and the code ran for me.

Here's the Database side I used:
--------------------
drop table tab1 purge;
create table tab1(col1 number);

insert into tab1 values(15000028435389);
insert into tab1 values(25000028435389);
insert into tab1 values(25000028435389);
commit;

create or replace function myFunc (inputID number)
return sys_refcursor
is
myresult sys_refcursor;
begin
open myresult for select * 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.RefCursor);

			cmd.Parameters["myResult"].Direction = ParameterDirection.ReturnValue;

			cmd.Parameters.Add("myInput", OracleDbType.Int64).Value = 25000028435389;

			con.Open();


      		OracleDataReader reader;

	        reader = cmd.ExecuteReader();
	        
	        	if (reader.Read()) {
					Console.WriteLine("My result is: " + reader.GetDecimal(0));
				} else {
					Console.WriteLine("No Rows Found.");
				}

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

	}

}

Open in new window

0
 
SirReadAlotAuthor Commented:

>>but not sure how to changed the cast

>>you shouldn't have to.

I didn't, same code

==================
 public static void Main(string[] args)
        {
            //string sqlStr = "15000028435389";        

            OracleConnection con = new OracleConnection();
            con = new OracleConnection("Data Source=; User ID=;Password=");
            OracleCommand cmd = new OracleCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.CommandText = "HE_GETGRADES";
            cmd.Parameters.Add("grades_cursor", OracleDbType.RefCursor);
            cmd.Parameters["grades_cursor"].Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add("vp_id", OracleDbType.Decimal).Value = 15000028435389;
            con.Open();
            OracleDataReader reader;
            reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                Console.WriteLine("My result is: " + reader.GetDecimal(0));
                Console.ReadLine();
            }
            else
            {
                Console.WriteLine("No Rows Found.");
                Console.ReadLine();
            }
            con.Close();
            con.Dispose();
=============error given
{System.InvalidCastException: Specified cast is not valid.
   at Oracle.DataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
   at ConsoleApplication1.Program.Main(String[] args) in C:\Visual Studio 2008\Projects\ConsoleApplication1\ConsoleApplication1\Program.cs:line 36
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()}
0
 
SirReadAlotAuthor Commented:
okay, will try your code but from your experience...

we use oracle 9.2 and u mentioned yours was 10g.

could this be the issue?
0
 
slightwv (䄆 Netminder) Commented:
What is the datatype of the first column being returned in the cursor?

From sqlplus please post the output of:
SQL> desc he_grades
0
 
slightwv (䄆 Netminder) Commented:
>>could this be the issue?

It might be but I don't think so....yet...
0
 
SirReadAlotAuthor Commented:
VARCHAR2 (20 Byte)
0
 
slightwv (䄆 Netminder) Commented:
You need to match the data types the reader is expecting with the data types being returned in the cursor.

In this case since you are returning a Varchar2:

Console.WriteLine("My result is: " + reader.GetString(0));
0
 
SirReadAlotAuthor Commented:
thannnnnnnnnnnnnnnnnnnks mate!!!!!!!!!!!!!!!!!!!!!!!1
0
 
SirReadAlotAuthor Commented:
can i pop a question
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.

I'm not sure what 'pop a question' means.
0
 
SirReadAlotAuthor Commented:
was gonna ask a question
0
 
slightwv (䄆 Netminder) Commented:
If you still want to, go ahead.
0
 
SirReadAlotAuthor Commented:
sorry to be a pain...................

the function returns 16 columns
i would like to read all of them whether they are null or not


public static void Main(string[] args)
        {


            OracleConnection con = new OracleConnection();
            con = new OracleConnection("Data Source=**; User ID=**;Password=**");
            OracleCommand cmd = new OracleCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;

            cmd.CommandText = "HE_GETGRADES";
            cmd.Parameters.Add("grades_cursor", OracleDbType.RefCursor);
            cmd.Parameters["grades_cursor"].Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add("vp_id", OracleDbType.Decimal).Value = 15000028435389;

            con.Open();
            OracleDataReader reader;
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine("\t{0}\t{1}", reader.GetName(0),
                    reader.GetName(1));

                while (reader.Read())
                {
                    Console.WriteLine("\t{0}\t{1}", reader.GetString(0),
                        reader.GetString(1));
                }
                reader.NextResult();
     
       
                con.Close();
                con.Dispose();
            }
        }
    }
}
0
 
slightwv (䄆 Netminder) Commented:
>>sorry to be a pain...................

no problem.  That's why we're here.

I think this should be covered in your other question.  I've already posted over there.

I'll see if I can add to my example and post it there.
0
 
SirReadAlotAuthor Commented:
hiya,

been reviewing the soln you posted. but i can't really get it to work. how can i tweak this

while (reader.Read() || (reader.IsDBNull(1)))
            {
                Console.WriteLine("\t{0}\t{1}", reader.GetName(0),
                    reader.GetName(1));

                while (reader.Read())
                {
                    Console.WriteLine("\t{0}\t{1}", reader.GetString(0),
                        reader.GetString(1));

                }
0
 
SirReadAlotAuthor Commented:
sorry meant this, but it still give the same error

while (reader.Read() || (Convert.IsDBNull(reader[1])))
            {
                Console.WriteLine("\t{0}\t{1}", reader.GetName(0),
                    reader.GetName(1));

                while (reader.Read())
                {
                    Console.WriteLine("\t{0}\t{1}", reader.GetString(0),
                        reader.GetString(1));
0
 
slightwv (䄆 Netminder) Commented:
Can you continue this in the other question since you've already opened it?
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 28
  • 19
  • 8
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now