Solved

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

Posted on 2010-08-23
57
1,222 Views
Last Modified: 2013-12-19
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
Comment
Question by:SirReadAlot
  • 28
  • 19
  • 8
  • +2
57 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 33499225
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
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 33499236
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
 

Author Comment

by:SirReadAlot
ID: 33499266
okay guys i will give it a go
0
 

Author Comment

by:SirReadAlot
ID: 33499434
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
 

Author Comment

by:SirReadAlot
ID: 33499492
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
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 33500644
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
 

Author Comment

by:SirReadAlot
ID: 33500839
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
 

Author Comment

by:SirReadAlot
ID: 33500894
I have the function above,

how do I all it with c#?   all the examples i have googled gets me no where.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 33501223
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33501343
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
 

Author Comment

by:SirReadAlot
ID: 33501400
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33501453
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
 

Author Comment

by:SirReadAlot
ID: 33501623
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
 

Author Comment

by:SirReadAlot
ID: 33501667
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33501848
that doesn't look like the code example I posted in http:#a33501343
0
 

Author Comment

by:SirReadAlot
ID: 33501914
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
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 33501930
@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
 

Author Comment

by:SirReadAlot
ID: 33501934
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33501935
>>not sure if i need

Yes you need this.  Your function takes an input parameter doesn't it?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33501952
>>is not safe code anyways since you are not checking if the reader

Yes but it's example code only.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33501959
>>Operation is not valid due to the current state of the object

Please post the code causing this error.
0
 

Author Comment

by:SirReadAlot
ID: 33501983
this line
Console.WriteLine("My result is: " + reader.GetDecimal(0));
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 33502000
>>  Yes but it's example code only.

Better to give a complete example which does not include a subtle bug, methinks  :)
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 33502014
>>  this line
>>  Console.WriteLine("My result is: " + reader.GetDecimal(0));

Ouila!
0
 

Author Comment

by:SirReadAlot
ID: 33502031
??
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 33502095
@SirReadAlot

Not you, sorry :)
0
 

Author Comment

by:SirReadAlot
ID: 33502129
@ kau

do you know what the problem is ?
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 33502213
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33502260
@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
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 33502453
>>  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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33502505
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
 

Author Comment

by:SirReadAlot
ID: 33508619
hi guys,

the code returned....

No Rows Found
0
 

Author Comment

by:SirReadAlot
ID: 33508637
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
 

Author Comment

by:SirReadAlot
ID: 33508825
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33510035
>>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
 

Author Comment

by:SirReadAlot
ID: 33510299
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33510357
Then change the datatype...

 cmd.Parameters.Add("vp_id", OracleDbType.Decimal).Value = 15000028435389;
0
 

Author Comment

by:SirReadAlot
ID: 33510363
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
 

Author Comment

by:SirReadAlot
ID: 33510372
sorry did not see that..

done the decimal as well , it came up with the same error
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33510390
>>but not sure how to changed the cast

you shouldn't have to.

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33510407
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
 

Author Comment

by:SirReadAlot
ID: 33510416

>>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
 

Author Comment

by:SirReadAlot
ID: 33510460
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33510465
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33510470
>>could this be the issue?

It might be but I don't think so....yet...
0
 

Author Comment

by:SirReadAlot
ID: 33510553
VARCHAR2 (20 Byte)
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 33510574
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
 

Author Comment

by:SirReadAlot
ID: 33510637
thannnnnnnnnnnnnnnnnnnks mate!!!!!!!!!!!!!!!!!!!!!!!1
0
 

Author Comment

by:SirReadAlot
ID: 33510696
can i pop a question
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33511044
Glad to help.

I'm not sure what 'pop a question' means.
0
 

Author Comment

by:SirReadAlot
ID: 33511082
was gonna ask a question
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33511100
If you still want to, go ahead.
0
 

Author Comment

by:SirReadAlot
ID: 33511226
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33511248
>>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
 

Author Comment

by:SirReadAlot
ID: 33511666
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
 

Author Comment

by:SirReadAlot
ID: 33511694
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33511722
Can you continue this in the other question since you've already opened it?
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

707 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

15 Experts available now in Live!

Get 1:1 Help Now