Question Returning DBNull when executing the oracle procedure using oracle client

[santi]

Hi,

I have written an oracle procedure which has been succesfully compiled in Toad. When I try to run the procedure from vb.net, it is returning DBNull value in place of 'Out' parameter. Below is my code.

Dim oraclecommand AsNew OracleClient.OracleCommand("INB.SPROCSTATEDTLS", con)
oraclecommand.CommandType = CommandType.StoredProcedure
oraclecommand.Parameters.Add("IC_CODE", OracleClient.OracleType.Number).Value = 1
oraclecommand.Parameters.Add("IC_CATEGORY", OracleClient.OracleType.VarChar).Value = "'ST'"
oraclecommand.Parameters.Add("C_CODE1", OracleClient.OracleType.VarChar, 100).Direction = ParameterDirection.Output
oraclecommand.ExecuteNonQuery()
ccodelist = oraclecommand.Parameters(2).Value

I am receiving DBNull in the last line.

Please advise.

Thanks.

 

[jmcilhinney]

What are we supposed to say? The only conclusion that we can draw from that is that you are either assigning NULL or else not assigning anything to that parameter in the procedure. You haven't provided any information, e.g. the procedure, to refute that.

 

[santi]

Here is the proc code:
"
create or replace PROCEDURE SPROCSTATEDTLS (
IC_CODE IN NUMBER,
IC_CATEGORY IN VARCHAR2,
C_CODE1 OUT VARCHAR2)
IS
BEGIN
FOR X IN (SELECT CR.CODE1 AS ccode
FROM CODE C
JOIN CODE_RELATED CR
ON C.C_CODE = CR.CODE2
JOIN CODE_CATEGORY CC
ON CC.C_CATEGORY = CR.C_CATEGORY
WHERE C.C_CODE = IC_CODE AND CC.C_CATEGORY = IC_CATEGORY)
LOOP
C_CODE1 := C_CODE1 || ',' || X.CCODE;
END LOOP;
END;
"
Result when executed from sqldeveloper tool..
"C_CODE1 = ,2,3,4,5,6,7
Process exited."

 

[santi]

can some one please advise if there is anything wrong in my procedure code which is the cause of returning DBNull..!

 

[cjard]

Show us the code used to call it from SQL developer

Change the stored procedure code to have this as a final line:
IF c_code1 IS NULL THEN
C_code1 := 'no results for ic_code:='||ic_code||' and ic_category:='||ic_category;
END IF;

I bet it's because you are putting apostrophes in the value of ic_category when calling from vb

 

[santi]

apostrophes were the issue. The code is returning values as expected when apostrophes are removed.. Thanks cjard..!

 

[cjard]

When working with database parameterized queries of any kind (e.g. SELECT * FROM dual WHERE dummy = :dummyparam, or EXECUTEmyProcArg1, :myProcArg2) we must never put things in the parameter value that we put in the query tool as control characters.. e.g. the apostrophes delimit the strings in the query tool.. they are not needed in code because the code knows where the string starts and stops