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

How to skip null columns with oracle data reader

Hi experts,
the code below works but how do i get it to include null columns instead of through an exception
"Column contains NULL data"

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.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();

                //if (reader.Read())

                //{
                //    //Console.WriteLine("My result is: " + reader.GetString(1));
                //    Console.WriteLine("{0}\t{1}", reader.GetString(0),
                //        reader.GetString(1));

                //    Console.ReadLine();

                //}
                //else
                //{
                //    Console.WriteLine("No Rows Found.");
                //    Console.ReadLine();
                //}
                con.Close();
                con.Dispose();
            }
        }
    }
}

Open in new window

0
SirReadAlot
Asked:
SirReadAlot
  • 7
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
2 options:
* either change the procedure to return non-nulls only
* change the code to check each column if it's null, and react accordingly (aka use a replacement value)
0
 
slightwv (䄆 Netminder) Commented:
At the .Net code layer check out:
http://forums.oracle.com/forums/thread.jspa?threadID=598612

Convert.IsDBNull(dr[1]))
0
 
SirReadAlotAuthor Commented:
orry 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
Why do you have two while loops?  You only need one.

I'll work on expanding my test from your previous question.  I'll post it soon.
0
 
slightwv (䄆 Netminder) Commented:
Here's what I came up with.  If there's still problems, please add/subtract to my test SQL so I can reproduce the error on my end.

Given the database objects:
-----------------------------------
drop table tab1 purge;
create table tab1(col1 number, someColumn varchar2(20), thirdColumn char(1));

insert into tab1 values(15000028435389,'Hello','Z');
insert into tab1 values(25000028435389,'World','Z');
insert into tab1 values(25000028435389,null,'Z');
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;
			bool haveRows = false;

	        reader = cmd.ExecuteReader();
	        
	        	while (reader.Read()) {
					Console.WriteLine("=========================");
					Console.WriteLine("Column1 has: " + (Convert.IsDBNull(reader.GetValue(0))?"null":reader.GetDecimal(0).ToString()));
					Console.WriteLine("Column2 has: " + (Convert.IsDBNull(reader.GetValue(1))?"null":reader.GetString(1)));
					Console.WriteLine("Column3 has: " + (Convert.IsDBNull(reader.GetValue(2))?"null":reader.GetString(2)));
					haveRows = true;
				}


				if(!haveRows) {
					Console.WriteLine("No Rows Found.");
				}

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

	}

}

Open in new window

0
 
SirReadAlotAuthor Commented:
ok  2sec
0
 
SirReadAlotAuthor Commented:
HI,

no errors with your sql,

but i received Specified cast is not valid. in line 1

            >>    Console.WriteLine("Column1 has: " + (Convert.IsDBNull(reader.GetValue(0)) ? "null" : reader.GetDecimal(0).ToString()));

thanks
0
 
SirReadAlotAuthor Commented:
DONE THIS

 Console.WriteLine("Column1 has: " + (Convert.IsDBNull(reader.GetValue(0)) ? "null" : reader.GetString(0).ToString()));
                Console.WriteLine("Column2 has: " + (Convert.IsDBNull(reader.GetValue(1)) ? "null" : reader.GetString(1).ToString()));
                Console.WriteLine("Column3 has: " + (Convert.IsDBNull(reader.GetValue(2)) ? "null" : reader.GetString(2).ToString())); >>ERRORS HERE so i will check thre datatype
                haveRows = true;
0
 
SirReadAlotAuthor Commented:
hi
Console.WriteLine("Column1 has: " + (Convert.IsDBNull(reader.GetValue(0)) ? "null" : reader.GetString(0).ToString()));
                Console.WriteLine("Column2 has: " + (Convert.IsDBNull(reader.GetValue(1)) ? "null" : reader.GetString(1).ToString()));
                Console.WriteLine("Column3 has: " + (Convert.IsDBNull(reader.GetValue(2)) ? "null" : reader.GetOracleDecimal(2).ToString()));

works now, so to view the rest of the colums I will have to keep adding
Console.WriteLine("Column1 has: " + (Convert.IsDBNull(reader.GetValue(0)) ? "null" : reader.GetString(0).ToString()));
                Console.WriteLine("Column2 has: " + (Convert.IsDBNull(reader.GetValue(1)) ? "null" : reader.GetString(1).ToString()));
                Console.WriteLine("Column3 has: " + (Convert.IsDBNull(reader.GetValue(2)) ? "null" : reader.GetOracleDecimal(2).ToString()));

right
0
 
slightwv (䄆 Netminder) Commented:
Correct.  You need to make sure the column offset is correct in the GetValue and Get<DataType> calls and the data types match.

So the next column would have offset (3).

Console.WriteLine("Column4 has: " + (Convert.IsDBNull(reader.GetValue(3)) ? "null" : reader.GetOracleDecimal(3).ToString()));

0
 
SirReadAlotAuthor Commented:
many thanks once again
0
 
SirReadAlotAuthor Commented:
what a cool teacher!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now