gkaindia.com

Home > > How to check for null value of output parameter?

How to check for null value of output parameter?

Advertisement
Hi guys, I get a test procedure with 2 output parameters and do nothing:
CREATE OR REPLACE PACKAGE BODY p_parameters_test AS
  PROCEDURE p_null_output_basetype(p1 OUT NUMBER,p2 OUT VARCHAR2)
  AS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('DO NOTHING');
  END p_null_output_basetype;
END;And I have below C# code:
cmd.CommandText = "p_parameters_test.p_null_output_basetype";
OracleParameter p1 = new OracleParameter("p1", OracleDbType.Decimal, System.Data.ParameterDirection.Output);
OracleParameter p2 = new OracleParameter("p2", OracleDbType.Varchar2, System.Data.ParameterDirection.Output);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
try
    conn.Open();
    cmd.ExecuteNonQuery();
    if (p1.Value==null)
        Console.WriteLine("p1.Value==null");
    else if (Convert.IsDBNull(p1.Value))
        Console.WriteLine("Convert.IsDBNull(p1.Value)");
    else
        Console.WriteLine("p1 else "+p1.Value);
    if (p2.Value==null)
        Console.WriteLine("p2.Value==null");
    else if (Convert.IsDBNull(p2.Value))
        Console.WriteLine("Convert.IsDBNull(p2.Value)");
    else
        Console.WriteLine("p2 else "+p2.Value);
    Console.WriteLine("finished");
catch......The output of it is:
p1 else null
p2 else null
Does anyone have any idea why it always goes to the 'else' of the condition-branching, and how can I check if the output parameter is null?
Thanks in advance.
Advertisement
Morven... I ran into similar problems. Maybe you've found a solution of your own by now, but here's what I've learned...
The Value property of output parameters, like p1 and p2 in your code, actually varies, according to (I think) the OracleDbType of the parameter. You've got OracleDbType.Decimal for p1 and OracleDbType.Varchar2 for p2. These look about right, since they match the parameter types in your actual stored procedure.
After cmd.ExecuteNonQuery() executes, the respective Value properties of p1 and p2 are actually of different types. For p1, it's going to be "OracleDecimal" and for p2 it's "OracleString". Keep in miind that these are the types of the Value property of the OracleParameter objects, not the OracleParameter objects themselves.
OracleDecimal and OracleString (and some other types like OracleDate, etc.) have an "IsNull" property you can use if you cast the Value property to its runtime type...
if ((OracleDecimal)cmd.Parameters["p1"].Value).IsNull) { …do something… }
else { …do something else… }
Or maybe something like this...
Decimal p1val = ((OracleDecimal)cmd.Parameters["p1"].Value).IsNull ? 0 : ((OracleDecimal)cmd.Parameters["AVG_SALARY"].Value).Value;
I'll admit that expressions like this: ((OracleDecimal)cmd.Parameters["AVG_SALARY"].Value).Value look a little weird. But the "Value" of the "OracleDecimal" property is a regular .NET decimal type (System.Decimal). So, it's a "Value" of the "Value" property of the OracleParameter class.
Even when the stored procedure returns a null, the Value property is still populated. In the case of p1, it's populated with an OracleDecimal object (actually a struct) where IsNull is true. That's why "p1.Value==null" tests false.
From what I can see, OracleDecimal, OracleString, etc. will never be typed as DbNull, or DBNull.Value. So, that would be why Convert.IsDBNull(p1.Value)) always returns false. btw, it appears that these are Value types. That would suggest that coding something like like this, should be avoided…
OracleString p2val = ((OracleString)cmd.Parameters["p2"].Value;
if (p2val.IsNull) { …do something… }
else { …do something else… }
By assigning the value to another variable, you’d be actually creating an entire copy of the OracleString structure, which is pointless.
I hope that helps
Edited by: 897674 on Jan 3, 2012 10:44 AM
Edited by: 897674 on Jan 3, 2012 10:46 AM
Read the other 4 answers
I want to check for null and empty values on input source node. If there exists a null or empty in the input source node, I should not pass that to output destination node. Does the following attachment work? I want to check for null or empty in the
Hello all, Can anyone tell me the best procedure to check for null and empty for an arraylist in a jsp using JSTL. I'm trying something like this; <c:if test="${!empty sampleList}"> </c:if> Help is greatly appreciated. Thanks, Greesh
I have 3 Page Validations which all fire on "When Button Pressed" = "Submit". <br><br> Number 1 is of type "Item specified is NOT NULL" for "P199_BUSINESS_UNIT1". <br> Number 2 is of type "Ite
Hi, I have the below JScript: /*Function to convert the US Date format to UK date format */ function parseDate(input) { var parts = input.split('/'); // new Date(year, month [, date [, hours[, minutes[, seconds[, ms]]]]]) return new Date(parts[2], pa
I Want to apply a view criteria to check for null or not null on a column , i see examples on how to set for value like vcRow.setAttribute("Sal", "> 2500")but i need to check for Sal is null or not null , tried vcRow.setAttribute(&q
Hello all, How does APEX check for null values in Text Fields on the forms? This might sound trivial but I have a problem with a PL/SQL Validation that I have written. I have one select list (P108_CLUSTER_ID) and one Text field (P108_PRIVATE_IP). I m
Hello Guys, I have a problem. I have an external webservice to which I have to post my request. My task is to create an Webservice and Service Assembly to which others would post request and get response. I have to create SA to deploy onto the bus. T
Hi i have an excel file which i i am reading into an arraylist row by row but not necesarrily that all columns in the row mite be filled. So how do i check for null values in the array list. try                     int cellCount = 0;                 
Hi, I have a variable of type char(8). I want to check if this field is empty. This is the logic I use right now: condense lv_date no-gaps. if lv_date eq ''. *then some default value endif. Is this the correct approach or there is a way to directly c
Hi, I am trying to configure oracle bsd on AIX and got the below error, # sh /ldap/db-4.6.21.NC/dist/configure checking build system type... powerpc-ibm-aix5.3.0.0 checking host system type... powerpc-ibm-aix5.3.0.0 checking if building in the top-le
Hey everyone. Trying my hand at a text based game but I'm encountering many problems: I have this method in one class that (as you can see) has a conditional that checks to see if 'check' is null, this throws a null pointer exception whenever it is c
I have a string variable and I need to check for null. What is the best way to do this? Thanks.You can also do it like this: } catch (NullPointerException e) {or like this: s==null ? <...> : <...>Read other 5 answers
I am looking forward an example that call a stored function/procedure with a REF CURSOR output parameter and get the result. In other words, I have a stored function/procedure that execute a SELECT statement using the OCI library and then it could ge
How can I return and see what is in my output parameter from HTML_DB using a procedure?I am not sure what your saying. I am using a PLSQL anonymous block as my source type for my page item. I am also using (DISPLAY AS TEXT BASED ON PLSQL,DOES NOT SAV
I have a VB6 app on XP that uses OLE DB and ADO to access data from Oracle. I am trying to create and append and output parameter to my SQL command. The output parameter is of type VarChar in Oracle. I tried to choose adChar, adVarChar, adLongVarChar
Hi, I want to develop custom action with xml type input and/or output parameter. Is there sample code for java side. How is the definition of input and/or output parameter and set/get methods? does it need special .jar file to develop custom action l
HI All, From long days i am working on this but i unable to solve it. Even i have studied so many forums in SAP but i didn't find the solution. I am calling Oracle Store procedure with 3 inputs and 1 output without cursor. Store Procedure:- CREATE OR
Hello, I'm developing an C# Application that is showing a datagrid with results from a PL/SQL procedure inside a Package. The .net project is in .net 4 and the DB is an oracle 10g. I have read the odp.net sample about the associative array. I could s
In Process flow, how do we pass an Output parameter from a mapping (generated by a post mapping operator) into an external process (shell script)? I have a mapping that generates an output parameter and I would like to pass this parameter to an exter