{"id":188,"date":"2007-09-21T15:23:33","date_gmt":"2007-09-21T14:23:33","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=188"},"modified":"2007-09-21T15:23:33","modified_gmt":"2007-09-21T14:23:33","slug":"functions-as-default-values","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=188","title":{"rendered":"Functions as default values…"},"content":{"rendered":"

If you have a program with parameters that have default values and you want the user to be able to change the default values of the parameters, without you having to change the value in the code, then put that data in a table.<\/p>\n

Why shouldn’t you just retrieve the data from the table in the program? One reason could be that the program must also be able to be called with a value different from what’s in the table. That is, if the program is called without the parameter, it uses the default value, which is retrieved from a table. If the program is called with the parameter it can use this value, instead of the default value.<\/p>\n

<\/p>\n<\/p>\n

\n
--<\/span> Create table<\/span>\n<\/span>create<\/span> <\/span>table<\/span> DefaultValues\n(\n  def_id      <\/span>number<\/span>,\n  def_number  <\/span>number<\/span>,\n  def_string  <\/span>varchar2<\/span>(<\/span>4000<\/span>),\n  def_date    date,\n  def_boolean <\/span>number<\/span>(<\/span>1<\/span>)\n)\n;\n<\/span>--<\/span> Add comments to the columns <\/span>\n<\/span>comment <\/span>on<\/span> <\/span>column<\/span> DefaultValues.def_id <\/span>is<\/span> <\/span>'<\/span>Default ID<\/span>'<\/span>;\ncomment <\/span>on<\/span> <\/span>column<\/span> DefaultValues.def_number <\/span>is<\/span> <\/span>'<\/span>Default number<\/span>'<\/span>;\ncomment <\/span>on<\/span> <\/span>column<\/span> DefaultValues.def_string <\/span>is<\/span> <\/span>'<\/span>Default string<\/span>'<\/span>;\ncomment <\/span>on<\/span> <\/span>column<\/span> DefaultValues.def_date <\/span>is<\/span> <\/span>'<\/span>Default date<\/span>'<\/span>;\ncomment <\/span>on<\/span> <\/span>column<\/span> DefaultValues.def_boolean <\/span>is<\/span> <\/span>'<\/span>Default boolean<\/span>'<\/span>;\n<\/span>--<\/span> Create\/Recreate primary, unique and foreign key constraints <\/span>\n<\/span>alter<\/span> <\/span>table<\/span> DefaultValues <\/span>add<\/span> <\/span>constraint<\/span> pk_def_id <\/span>primary<\/span> <\/span>key<\/span> (DEF_ID);<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
Generate and install the QDA<\/a> packages for this new table so they can be used. Then we create a package that retrieves the values from the table.<\/p>\n<\/p>\n
\n
CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>PACKAGE<\/span> defaultvalues_dp\n<\/span>IS<\/span>\n  <\/span>--<\/span> Author  : PBAREL<\/span>\n<\/span>  <\/span>--<\/span> Created : 06\/09\/2007 18:21:14<\/span>\n<\/span>  <\/span>--<\/span> Purpose : Get the default values for functions<\/span>\n<\/span>  <\/span>--<\/span> Public function and procedure declarations<\/span>\n<\/span>  <\/span>FUNCTION<\/span> get_def_number\n    <\/span>RETURN<\/span> defaultvalues_tp.def_number_t;\n\n  <\/span>FUNCTION<\/span> get_def_string\n    <\/span>RETURN<\/span> defaultvalues_tp.def_string_t;\n\n  <\/span>FUNCTION<\/span> get_def_date\n    <\/span>RETURN<\/span> defaultvalues_tp.def_date_t;\n\n  <\/span>FUNCTION<\/span> get_def_boolean\n    <\/span>RETURN<\/span> <\/span>BOOLEAN<\/span>;\n<\/span>END<\/span> defaultvalues_dp;\n<\/span>\/<\/span>\n<\/span>CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>PACKAGE<\/span> <\/span>BODY<\/span> defaultvalues_dp\n<\/span>IS<\/span>\n  <\/span>--<\/span> Private constant declarations<\/span>\n<\/span>  c_true                          <\/span>CONSTANT<\/span> <\/span>PLS_INTEGER<\/span> :<\/span>=<\/span> <\/span>1<\/span>;\n\n  <\/span>--<\/span> Function and procedure implementations<\/span>\n<\/span>  <\/span>FUNCTION<\/span> getdefaultvalues\n    <\/span>RETURN<\/span> defaultvalues_tp.defaultvalues_rt\n  <\/span>IS<\/span>\n    tc_defaultvalues                         defaultvalues_tp.defaultvalues_tc;\n    rc_defaultvalues                         defaultvalues_tp.defaultvalues_rt;\n  <\/span>BEGIN<\/span>\n    tc_defaultvalues   :<\/span>=<\/span> defaultvalues_qp.allrows;\n    rc_defaultvalues   :<\/span>=<\/span> tc_defaultvalues( tc_defaultvalues.FIRST );\n    <\/span>RETURN<\/span> rc_defaultvalues;\n  <\/span>END<\/span> getdefaultvalues;\n\n  <\/span>PROCEDURE<\/span> initialization\n  <\/span>IS<\/span>\n  <\/span>BEGIN<\/span>\n    <\/span>NULL<\/span>;\n  <\/span>END<\/span> initialization;\n\n  <\/span>FUNCTION<\/span> get_def_number\n    <\/span>RETURN<\/span> defaultvalues_tp.def_number_t\n  <\/span>IS<\/span>\n    lv_returnvalue                           defaultvalues_tp.def_number_t;\n    rt_defaultvalues                         defaultvalues_tp.defaultvalues_rt;\n  <\/span>BEGIN<\/span>\n    rt_defaultvalues   :<\/span>=<\/span> getdefaultvalues;\n    lv_returnvalue     :<\/span>=<\/span> rt_defaultvalues.def_number;\n    <\/span>RETURN<\/span> lv_returnvalue;\n  <\/span>END<\/span>;\n\n  <\/span>FUNCTION<\/span> get_def_string\n    <\/span>RETURN<\/span> defaultvalues_tp.def_string_t\n  <\/span>IS<\/span>\n    lv_returnvalue                           defaultvalues_tp.def_string_t;\n    rt_defaultvalues                         defaultvalues_tp.defaultvalues_rt;\n  <\/span>BEGIN<\/span>\n    rt_defaultvalues   :<\/span>=<\/span> getdefaultvalues;\n    lv_returnvalue     :<\/span>=<\/span> rt_defaultvalues.def_string;\n    <\/span>RETURN<\/span> lv_returnvalue;\n  <\/span>END<\/span>;\n\n  <\/span>FUNCTION<\/span> get_def_date\n    <\/span>RETURN<\/span> defaultvalues_tp.def_date_t\n  <\/span>IS<\/span>\n    lv_returnvalue                           defaultvalues_tp.def_date_t;\n    rt_defaultvalues                         defaultvalues_tp.defaultvalues_rt;\n  <\/span>BEGIN<\/span>\n    rt_defaultvalues   :<\/span>=<\/span> getdefaultvalues;\n    lv_returnvalue     :<\/span>=<\/span> rt_defaultvalues.def_date;\n    <\/span>RETURN<\/span> lv_returnvalue;\n  <\/span>END<\/span>;\n\n  <\/span>FUNCTION<\/span> get_def_boolean\n    <\/span>RETURN<\/span> <\/span>BOOLEAN<\/span>\n  <\/span>IS<\/span>\n    lv_returnvalue                           <\/span>BOOLEAN<\/span>;\n    rt_defaultvalues                         defaultvalues_tp.defaultvalues_rt;\n  <\/span>BEGIN<\/span>\n    rt_defaultvalues   :<\/span>=<\/span> getdefaultvalues;\n    lv_returnvalue     :<\/span>=<\/span> rt_defaultvalues.def_boolean <\/span>=<\/span> c_true;\n    <\/span>RETURN<\/span> lv_returnvalue;\n  <\/span>END<\/span>;\n<\/span>BEGIN<\/span>\n  initialization;\n<\/span>END<\/span> defaultvalues_dp;\n\n<\/span><\/div><\/pre>\n
<\/div>\n
Now it’s time for our usage package. <\/p>\n<\/p>\n
\n
CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>PACKAGE<\/span> defaultusage\n<\/span>IS<\/span>\n  <\/span>--<\/span> Author  : PBAREL<\/span>\n<\/span>  <\/span>--<\/span> Created : 06\/09\/2007 18:40:19<\/span>\n<\/span>  <\/span>--<\/span> Purpose : usage of the functions as default parameters<\/span>\n<\/span>\n  <\/span>--<\/span> Public function and procedure declarations<\/span>\n<\/span>  <\/span>PROCEDURE<\/span> shownumber(\n    number_in                      <\/span>IN<\/span>      <\/span>NUMBER<\/span> <\/span>DEFAULT<\/span> defaultvalues_dp.get_def_number );\n\n  <\/span>PROCEDURE<\/span> showstring(\n    string_in                      <\/span>IN<\/span>      <\/span>VARCHAR2<\/span> <\/span>DEFAULT<\/span> defaultvalues_dp.get_def_string );\n\n  <\/span>PROCEDURE<\/span> showdate(\n    date_in                        <\/span>IN<\/span>      <\/span>DATE<\/span> <\/span>DEFAULT<\/span> defaultvalues_dp.get_def_date );\n\n  <\/span>PROCEDURE<\/span> showboolean(\n    boolean_in                     <\/span>IN<\/span>      <\/span>BOOLEAN<\/span> <\/span>DEFAULT<\/span> defaultvalues_dp.get_def_boolean );\n<\/span>END<\/span> defaultusage;\n<\/span>\/<\/span>\n<\/span>CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>PACKAGE<\/span> <\/span>BODY<\/span> defaultusage\n<\/span>IS<\/span>\n  <\/span>--<\/span> Private type declarations<\/span>\n--<\/span>  type <TypeName> is <Datatype>;<\/span>\n<\/span>\n  <\/span>--<\/span> Private constant declarations<\/span>\n--<\/span>  <ConstantName> constant <Datatype> := <Value>;<\/span>\n<\/span>\n  <\/span>--<\/span> Private variable declarations<\/span>\n--<\/span>  <VariableName> <Datatype>;<\/span>\n<\/span>\n  <\/span>--<\/span> Function and procedure implementations<\/span>\n<\/span>  <\/span>PROCEDURE<\/span> initialization\n  <\/span>IS<\/span>\n  <\/span>BEGIN<\/span>\n    <\/span>NULL<\/span>;\n  <\/span>END<\/span> initialization;\n\n  <\/span>PROCEDURE<\/span> shownumber(\n    number_in                      <\/span>IN<\/span>      <\/span>NUMBER<\/span> <\/span>DEFAULT<\/span> defaultvalues_dp.get_def_number )\n  <\/span>IS<\/span>\n  <\/span>BEGIN<\/span>\n    DBMS_OUTPUT.put_line( TO_CHAR( number_in ));\n  <\/span>END<\/span>;\n\n  <\/span>PROCEDURE<\/span> showstring(\n    string_in                      <\/span>IN<\/span>      <\/span>VARCHAR2<\/span> <\/span>DEFAULT<\/span> defaultvalues_dp.get_def_string )\n  <\/span>IS<\/span>\n  <\/span>BEGIN<\/span>\n    DBMS_OUTPUT.put_line( string_in );\n  <\/span>END<\/span>;\n\n  <\/span>PROCEDURE<\/span> showdate(\n    date_in                        <\/span>IN<\/span>      <\/span>DATE<\/span> <\/span>DEFAULT<\/span> defaultvalues_dp.get_def_date )\n  <\/span>IS<\/span>\n  <\/span>BEGIN<\/span>\n    DBMS_OUTPUT.put_line( TO_CHAR( date_in, <\/span>'<\/span>MMDDYYYY<\/span>'<\/span> ));\n  <\/span>END<\/span>;\n\n  <\/span>PROCEDURE<\/span> showboolean(\n    boolean_in                     <\/span>IN<\/span>      <\/span>BOOLEAN<\/span> <\/span>DEFAULT<\/span> defaultvalues_dp.get_def_boolean )\n  <\/span>IS<\/span>\n  <\/span>BEGIN<\/span>\n    <\/span>IF<\/span> boolean_in <\/span>THEN<\/span>\n      DBMS_OUTPUT.put_line( <\/span>'<\/span>TRUE<\/span>'<\/span> );\n    <\/span>ELSE<\/span>\n      DBMS_OUTPUT.put_line( <\/span>'<\/span>FALSE<\/span>'<\/span> );\n    <\/span>END<\/span> <\/span>IF<\/span>;\n  <\/span>END<\/span>;\n<\/span>BEGIN<\/span>\n  initialization;\n<\/span>END<\/span> defaultusage;\n<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
And then of course a small demonstration script of the usage of this functionality.<\/p>\n<\/p>\n
\n
SET<\/span> SERVEROUTPUT <\/span>ON<\/span>\n<\/span>SET<\/span> FEEDBACK <\/span>OFF<\/span>\n<\/span>TRUNCATE<\/span> <\/span>TABLE<\/span> defaultvalues;\n<\/span>INSERT<\/span> <\/span>INTO<\/span> defaultvalues(def_id, def_number, def_string, def_date, def_boolean)\n  <\/span>VALUES<\/span> (<\/span>1<\/span>,<\/span>2912<\/span>,<\/span>'<\/span>Patrick<\/span>'<\/span>,to_date(<\/span>'<\/span>12291972<\/span>'<\/span>,<\/span>'<\/span>MMDDYYYY<\/span>'<\/span>),<\/span>1<\/span>);\n<\/span>COMMIT<\/span>;\n<\/span>--<\/span> first: default usage<\/span>\n<\/span>EXEC<\/span> defaultusage.shownumber;\n<\/span>EXEC<\/span> defaultusage.showstring;\n<\/span>EXEC<\/span> defaultusage.showdate;\n<\/span>EXEC<\/span> defaultusage.showboolean;\n<\/span>--<\/span> next: overridden usage<\/span>\n<\/span>EXEC<\/span> defaultusage.shownumber(<\/span>1<\/span>);\n<\/span>EXEC<\/span> defaultusage.showstring(<\/span>'<\/span>Bar Solutions<\/span>'<\/span>);\n<\/span>EXEC<\/span> defaultusage.showdate(to_date(<\/span>'<\/span>20070101<\/span>'<\/span>,<\/span>'<\/span>YYYYMMDD<\/span>'<\/span>));\n<\/span>EXEC<\/span> defaultusage.showboolean(sys.diutil.int_to_bool(<\/span>0<\/span>));\n<\/span>UPDATE<\/span> defaultvalues\n   <\/span>SET<\/span> def_number <\/span>=<\/span> <\/span>0306<\/span>\n     , def_string <\/span>=<\/span> <\/span>'<\/span>Dana<\/span>'<\/span>\n     , def_date <\/span>=<\/span> to_date(<\/span>'<\/span>03061976<\/span>'<\/span>,<\/span>'<\/span>MMDDYYYY<\/span>'<\/span>)\n     , def_boolean <\/span>=<\/span> <\/span>0<\/span>\n <\/span>WHERE<\/span> def_id <\/span>=<\/span> <\/span>1<\/span>;\n<\/span>COMMIT<\/span>;\n<\/span>--<\/span> again: default usage, but this time with new values<\/span>\n<\/span>EXEC<\/span> defaultusage.shownumber;\n<\/span>EXEC<\/span> defaultusage.showstring;\n<\/span>EXEC<\/span> defaultusage.showdate;\n<\/span>EXEC<\/span> defaultusage.showboolean;\n<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
As you can see, if you don’t provide any value for the parameter then the default value is being used (from the table) which can be anything depending on what’s in the table at that time. But you can still override the values if needed.<\/p>\n
Unfortunately this method cannot be used to define a default on a column in a table, which is kinda strange when you consider that user<\/em> in SQL is actually a function call, as is sysdate<\/em>. Check the standard.sql (or stdspec.sql and stdbody.sql) in the RDBMS\\ADMIN folder. Maybe it’s some kind of special PRAGMA that needs to be set. Something to try out sometime or maybe a restriction Oracle should lift.<\/p>\n","protected":false},"excerpt":{"rendered":"
If you have a program with parameters that have default values and you want the user to be able to change the default values of the parameters, without you having to change the value in the code, then put that data in a table. Why shouldn’t you just retrieve the data from the table in […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,2,5],"tags":[],"class_list":["post-188","post","type-post","status-publish","format-standard","hentry","category-codegen","category-oracle","category-plsql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/188","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=188"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/188\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=188"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=188"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=188"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}