Question How to call stored function

[Deliverables]

Hello guys,

Firstly I must mention that am new to both vb.net and oracle. I come from a VB 6.0 and Microsoft SQL server background. I am building an online payment system which is external to our production database so I need to insert records into one table in the production database then call a stored function in oracle from vb.net which is supposed to process the records I inserted. I don't have permission to edit anything in the database so I cant change the function to a procedure like some help on other forums was suggesting. The developers of the database and core system which uses the database only game me the function name and parameters of the function to call. However, I am running into the error below when I call the function from vb.net:
-----------------------
vb.net: ORA-06550: line 1, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

-------------------

My code is as below:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


Dim Connection52 As OracleConnection

Connection52 = New OracleConnection("Data Source=Source;Persist Security Info=True;User ID=ID;PWD=pwd;Unicode=True")

Connection52.Open()

Dim cmd As New OracleCommand



cmd.Connection = Connection52
cmd.CommandText = "depks_upload.fn_batch_upload"
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("result", OracleType.Number).Value = DBNull.Value
cmd.Parameters("result").Direction = ParameterDirection.ReturnValue


cmd.Parameters.Add("p_branch", OracleType.VarChar).Value = "027"
cmd.Parameters.Add("p_appldate", OracleType.DateTime).Value = "31-Jul-2009"
cmd.Parameters.Add("p_curr_userid", OracleType.VarChar).Value = "SYSTEM"

cmd.Parameters.Add("p_batchno", OracleType.VarChar).Value = "5619"
cmd.Parameters.Add("p_source_code", OracleType.VarChar).Value = "LSKCLG"

cmd.Parameters.Add("p_ovd", OracleType.VarChar).Value = "N"
cmd.Parameters.Add("p_lcy", OracleType.VarChar).Value = "ZMK"
cmd.Parameters.Add("p_lang", OracleType.VarChar).Value = "ENG"
cmd.Parameters.Add("p_mis_required", OracleType.VarChar).Value = "N"
cmd.Parameters.Add("p_auto_auth", OracleType.VarChar).Value = "Y"
cmd.Parameters.Add("p_udf_upload", OracleType.VarChar).Value = "N"

cmd.Parameters.Add("p_gl_offset_entry", OracleType.VarChar).Value = "032"
cmd.Parameters.Add("p_txn_code", OracleType.VarChar).Value = "CAW"

cmd.Parameters.Add("p_offset_gl", OracleType.VarChar).Value = "032"

cmd.Parameters.Add("p_total_ent", OracleType.Number).Value = DBNull.Value
cmd.Parameters("p_total_ent").Direction = ParameterDirection.Output

cmd.Parameters.Add("p_ent_uploaded", OracleType.Number).Value = DBNull.Value
cmd.Parameters("p_ent_uploaded").Direction = ParameterDirection.Output

cmd.Parameters.Add("p_ent_rejected", OracleType.Number).Value = DBNull.Value
cmd.Parameters("p_ent_rejected").Direction = ParameterDirection.Output

cmd.Parameters.Add("p_error_code", OracleType.VarChar).Value = DBNull.Value
cmd.Parameters("p_error_code").Size = 50
cmd.Parameters("p_error_code").Direction = ParameterDirection.Output

cmd.Parameters.Add("p_error_parameter", OracleType.VarChar).Value = DBNull.Value
cmd.Parameters("p_error_parameter").Size = 50
cmd.Parameters("p_error_parameter").Direction = ParameterDirection.Output

' execute the function

cmd.ExecuteNonQuery()

End Sub

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

 

[Hack]

How do I call Oracle Stored Procedure from ASP.NET?

 

[Deliverables]

Thanks Hack.
I somehow managed to convince the developers of the system to change the function to return 0 or 1 and not true or false and its working now. Thanks anyway. Regards.

 

[Hack]

No problem...always happy to help.

 

[cjard]

Thanks Hack.
I somehow managed to convince the developers of the system to change the function to return 0 or 1 and not true or false and its working now. Thanks anyway. Regards.

Functions that return PLSQL booleans are not usable outside PLSQL
Either create a wrapper function that returns the value as another type, or convert the function to a procedure (it seems to be a DML function anyway, and it's not really advisable for functions to perform data manipulation because you have to start an autonomous transaction for them, which can really screw up your rollbacks) and don't bother to return a value

To know if a procedure succeeded, your dba should use exceptions rather than return values; if it throws an exception, it didnt work