Geek Answers Handbook

In the oracle database, how can I specify a default value for a parameter that is a user-defined type?

PACKAGE PKG_DEVICE AS TYPE STR_ASSOC_ARR is table of VARCHAR(255) index by BINARY_INTEGER; procedure proc_create_device ( in_deviceid in raw , in_devicecert in clob , in_status in number , in_caps in STR_ASSOC_ARR , in_vals in STR_ASSOC_ARR );

is a stored procedure declaration. I would like to specify a default value for the in_caps and in_vals parameters. Is it possible? I cannot specify default null as it does not work. My goal is not to pass these two parameters (or pass null) from C # when they are not available. If there is a way for odp.net to do the same, it will work too.
Using oracle db 11g.

+4
source share
2 answers

You need to specify NULL as a custom type. Try the following:

  PACKAGE PKG_DEVICE AS TYPE STR_ASSOC_ARR is table of VARCHAR(255) index by BINARY_INTEGER; procedure proc_create_device ( in_deviceid in raw , in_devicecert in clob , in_status in number , in_caps in STR_ASSOC_ARR DEFAULT CAST(NULL AS STR_ASSOC_ARR) , in_vals in STR_ASSOC_ARR DEFAULT CAST(NULL AS STR_ASSOC_ARR) );

Now you do not need to specify values ​​for in_caps or in_vals. If values ​​are not passed, they default to null values ​​of type STR_ASSOC_ARRAY.

And, of course, you will need to update the procedure declaration in the package body to match these changes.

+9
source

If you want to pass the default value from C # side, you can do this using

  var sParam1 = new OracleParameter("in_param1_array", OracleDbType.Varchar2, ParameterDirection.Input); sParam1.CollectionType = OracleCollectionType.PLSQLAssociativeArray; if (list.Count == 0) { sParam1.Size = 1; sParam1.Value = new string[1] { "" };//Or initialize as default value. } else { sParam1.Size = list.Count; sParam1.Value = list.ToArray(); } parameters.Add(sParam1);
-1
source

More articles:


All Articles