Functions as default values…

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 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.

-- Create table create table DefaultValues ( def_id number, def_number number, def_string varchar2(4000), def_date date, def_boolean number(1) ) ; -- Add comments to the columns comment on column DefaultValues.def_id is 'Default ID'; comment on column DefaultValues.def_number is 'Default number'; comment on column DefaultValues.def_string is 'Default string'; comment on column DefaultValues.def_date is 'Default date'; comment on column DefaultValues.def_boolean is 'Default boolean'; -- Create/Recreate primary, unique and foreign key constraints alter table DefaultValues add constraint pk_def_id primary key (DEF_ID);

Generate and install the QDA packages for this new table so they can be used. Then we create a package that retrieves the values from the table.

CREATE OR REPLACE PACKAGE defaultvalues_dp IS -- Author : PBAREL -- Created : 06/09/2007 18:21:14 -- Purpose : Get the default values for functions -- Public function and procedure declarations FUNCTION get_def_number RETURN defaultvalues_tp.def_number_t; FUNCTION get_def_string RETURN defaultvalues_tp.def_string_t; FUNCTION get_def_date RETURN defaultvalues_tp.def_date_t; FUNCTION get_def_boolean RETURN BOOLEAN; END defaultvalues_dp; / CREATE OR REPLACE PACKAGE BODY defaultvalues_dp IS -- Private constant declarations c_true CONSTANT PLS_INTEGER := 1; -- Function and procedure implementations FUNCTION getdefaultvalues RETURN defaultvalues_tp.defaultvalues_rt IS tc_defaultvalues defaultvalues_tp.defaultvalues_tc; rc_defaultvalues defaultvalues_tp.defaultvalues_rt; BEGIN tc_defaultvalues := defaultvalues_qp.allrows; rc_defaultvalues := tc_defaultvalues( tc_defaultvalues.FIRST ); RETURN rc_defaultvalues; END getdefaultvalues; PROCEDURE initialization IS BEGIN NULL; END initialization; FUNCTION get_def_number RETURN defaultvalues_tp.def_number_t IS lv_returnvalue defaultvalues_tp.def_number_t; rt_defaultvalues defaultvalues_tp.defaultvalues_rt; BEGIN rt_defaultvalues := getdefaultvalues; lv_returnvalue := rt_defaultvalues.def_number; RETURN lv_returnvalue; END; FUNCTION get_def_string RETURN defaultvalues_tp.def_string_t IS lv_returnvalue defaultvalues_tp.def_string_t; rt_defaultvalues defaultvalues_tp.defaultvalues_rt; BEGIN rt_defaultvalues := getdefaultvalues; lv_returnvalue := rt_defaultvalues.def_string; RETURN lv_returnvalue; END; FUNCTION get_def_date RETURN defaultvalues_tp.def_date_t IS lv_returnvalue defaultvalues_tp.def_date_t; rt_defaultvalues defaultvalues_tp.defaultvalues_rt; BEGIN rt_defaultvalues := getdefaultvalues; lv_returnvalue := rt_defaultvalues.def_date; RETURN lv_returnvalue; END; FUNCTION get_def_boolean RETURN BOOLEAN IS lv_returnvalue BOOLEAN; rt_defaultvalues defaultvalues_tp.defaultvalues_rt; BEGIN rt_defaultvalues := getdefaultvalues; lv_returnvalue := rt_defaultvalues.def_boolean = c_true; RETURN lv_returnvalue; END; BEGIN initialization; END defaultvalues_dp;

Now it’s time for our usage package.

CREATE OR REPLACE PACKAGE defaultusage IS -- Author : PBAREL -- Created : 06/09/2007 18:40:19 -- Purpose : usage of the functions as default parameters -- Public function and procedure declarations PROCEDURE shownumber( number_in IN NUMBER DEFAULT defaultvalues_dp.get_def_number ); PROCEDURE showstring( string_in IN VARCHAR2 DEFAULT defaultvalues_dp.get_def_string ); PROCEDURE showdate( date_in IN DATE DEFAULT defaultvalues_dp.get_def_date ); PROCEDURE showboolean( boolean_in IN BOOLEAN DEFAULT defaultvalues_dp.get_def_boolean ); END defaultusage; / CREATE OR REPLACE PACKAGE BODY defaultusage IS -- Private type declarations -- type <TypeName> is <Datatype>; -- Private constant declarations -- <ConstantName> constant <Datatype> := <Value>; -- Private variable declarations -- <VariableName> <Datatype>; -- Function and procedure implementations PROCEDURE initialization IS BEGIN NULL; END initialization; PROCEDURE shownumber( number_in IN NUMBER DEFAULT defaultvalues_dp.get_def_number ) IS BEGIN DBMS_OUTPUT.put_line( TO_CHAR( number_in )); END; PROCEDURE showstring( string_in IN VARCHAR2 DEFAULT defaultvalues_dp.get_def_string ) IS BEGIN DBMS_OUTPUT.put_line( string_in ); END; PROCEDURE showdate( date_in IN DATE DEFAULT defaultvalues_dp.get_def_date ) IS BEGIN DBMS_OUTPUT.put_line( TO_CHAR( date_in, 'MMDDYYYY' )); END; PROCEDURE showboolean( boolean_in IN BOOLEAN DEFAULT defaultvalues_dp.get_def_boolean ) IS BEGIN IF boolean_in THEN DBMS_OUTPUT.put_line( 'TRUE' ); ELSE DBMS_OUTPUT.put_line( 'FALSE' ); END IF; END; BEGIN initialization; END defaultusage;

And then of course a small demonstration script of the usage of this functionality.

SET SERVEROUTPUT ON SET FEEDBACK OFF TRUNCATE TABLE defaultvalues; INSERT INTO defaultvalues(def_id, def_number, def_string, def_date, def_boolean) VALUES (1,2912,'Patrick',to_date('12291972','MMDDYYYY'),1); COMMIT; -- first: default usage EXEC defaultusage.shownumber; EXEC defaultusage.showstring; EXEC defaultusage.showdate; EXEC defaultusage.showboolean; -- next: overridden usage EXEC defaultusage.shownumber(1); EXEC defaultusage.showstring('Bar Solutions'); EXEC defaultusage.showdate(to_date('20070101','YYYYMMDD')); EXEC defaultusage.showboolean(sys.diutil.int_to_bool(0)); UPDATE defaultvalues SET def_number = 0306 , def_string = 'Dana' , def_date = to_date('03061976','MMDDYYYY') , def_boolean = 0 WHERE def_id = 1; COMMIT; -- again: default usage, but this time with new values EXEC defaultusage.shownumber; EXEC defaultusage.showstring; EXEC defaultusage.showdate; EXEC defaultusage.showboolean;

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.

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 in SQL is actually a function call, as is sysdate. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *