How do I populate a user-defined record that has default values?

Question

TL; DR:

How can I declare a user-defined record type so that if I don't populate one of the fields, that field will honor its DEFAULT?

---

Details:

In my package spec, I define the following record and table types:

/* set up a custom datatypes that will allow us to pass an array of values into CCD_UI procedures and functions */
TYPE RECORD_OPTION_ATTRIBUTES IS RECORD(
    option_name             VARCHAR2(200)   NOT NULL DEFAULT 'INVALID NAME"', /* default intentionally breaks HTML */
    option_value            VARCHAR2(200)   NOT NULL DEFAULT 'INVALID VALUE"', /* default intentionally breaks HTML */
    option_selected_ind     NUMBER(1)       NOT NULL DEFAULT '0',
    option_class            VARCHAR2(200)   DEFAULT NULL,
    option_attributes       VARCHAR2(200)   DEFAULT NULL
);

TYPE TABLE_OPTION_ATTRIBUTES IS TABLE OF RECORD_OPTION_ATTRIBUTES
    INDEX BY BINARY_INTEGER;

In the package body, I have functionality very similar to this:

PROCEDURE populate_user_defined_table()
AS

    v_criteria_pairs        TABLE_OPTION_ATTRIBUTES;

BEGIN

    SELECT some_column1 AS option_name, some_column2 AS option_value, some_column3 AS selected_ind,
        some_column4 AS option_class
    BULK COLLECT INTO v_criteria_pairs
    FROM Some_Table
    WHERE some_column='whatever';

END;

The sharp eye will notice that I am not inserting any values into the option_attributes field; I am populating only 4 of the 5 available fields.

When I attempt to compile this package, I receive the following error from the package body:

PL/SQL: ORA-00913: too many values

If I drop the option_attributes field from the RECORD_OPTION_ATTRIBUTES declaration, the package will compile.

How can I declare the record type so that if I don't specify a value for option_attributes, that field will default to NULL?

Answer 1

Here is another option with object type which is not PL/SQL record but has same behaviour plus more options for initialization with default values in constructor (use expressions and PL/SQL functions):

Define new type with constructor:

CREATE OR REPLACE TYPE RECORD_OPTION_ATTRIBUTES AS OBJECT(
  option_name             VARCHAR2(200),
  option_value            VARCHAR2(200),
  option_selected_ind     NUMBER(1),
  option_class            VARCHAR2(200),
  option_attributes       VARCHAR2(200),
  constructor function RECORD_OPTION_ATTRIBUTES(
    in_option_name             VARCHAR2    DEFAULT 'INVALID NAME"', /* default intentionally breaks HTML */
    in_option_value            VARCHAR2    DEFAULT 'INVALID VALUE"', /* default intentionally breaks HTML */
    in_option_selected_ind     NUMBER      DEFAULT '0',
    in_option_class            VARCHAR2    DEFAULT NULL,
    in_option_attributes       VARCHAR2    DEFAULT NULL
  )
  return self as result
);

In constructor use default values and can use complex initialization logic. Please bear in mind you can have multiple constructors.

create or replace type body RECORD_OPTION_ATTRIBUTES 
as
  constructor function RECORD_OPTION_ATTRIBUTES(
    in_option_name             VARCHAR2    DEFAULT 'INVALID NAME"', /* default intentionally breaks HTML */
    in_option_value            VARCHAR2    DEFAULT 'INVALID VALUE"', /* default intentionally breaks HTML */
    in_option_selected_ind     NUMBER      DEFAULT '0',
    in_option_class            VARCHAR2    DEFAULT NULL,
    in_option_attributes       VARCHAR2    DEFAULT NULL
  )
  return self as result
  as
  begin
    self.option_name         := in_option_name;
    self.option_value        := in_option_value;
    self.option_selected_ind := in_option_selected_ind;
    self.option_class        := in_option_class;
    self.option_attributes   := in_option_attributes;  
    return;
  end;
end;
/  

Let's run test sql:

select RECORD_OPTION_ATTRIBUTES(table_name, tablespace_name, ini_trans) 
  from all_tables
 where owner = 'SYS'
   and rownum <= 10;

Check results:

RECORD_OPTION_ATTRIBUTES(TABLE_NAME,TABLESPACE_NAME,INI_TRANS)(OPTION_NAME, OPTI
--------------------------------------------------------------------------------
RECORD_OPTION_ATTRIBUTES('WRR$_REPLAY_CALL_FILTER', 'SYSAUX', 1, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$EXPRESS', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$AWMD', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$AWCREATE', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$AWCREATE10G', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$AWXML', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('AW$AWREPORT', 'SYSAUX', 4, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('DUAL', 'SYSTEM', 1, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('SYSTEM_PRIVILEGE_MAP', 'SYSTEM', 1, NULL, NULL)
RECORD_OPTION_ATTRIBUTES('TABLE_PRIVILEGE_MAP', 'SYSTEM', 1, NULL, NULL)

10 rows selected.

As you can see last 2 columns have default (null in this case) value. Comparing with original sql query in your question all you need to do is wrap selected columns with RECORD_OPTION_ATTRIBUTES().

Answer 2

TYPE RECORD_OPTION_ATTRIBUTES IS RECORD(
    option_name             VARCHAR2(200)   NOT NULL DEFAULT 'INVALID NAME"', /* default intentionally breaks HTML */
    option_value            VARCHAR2(200)   NOT NULL DEFAULT 'INVALID VALUE"', /* default intentionally breaks HTML */
    option_selected_ind     NUMBER(1)       NOT NULL DEFAULT '0',
    option_class            VARCHAR2(200)   DEFAULT NULL,
    option_attributes       VARCHAR2(200)   DEFAULT NULL
);

TYPE TABLE_OPTION_ATTRIBUTES IS TABLE OF RECORD_OPTION_ATTRIBUTES
    INDEX BY BINARY_INTEGER;



PROCEDURE populate_user_defined_table()
AS
  CURSOS cur IS -- cursor selecting values without last column
  SELECT some_column1 AS option_name, some_column2 AS option_value,some_column3 AS selected_ind, some_column4 AS option_class
   FROM Some_Table
  WHERE some_column='whatever';

 TYPE t_tmp_arr IS TABLE OF cur%rowtype index by pls_integer;

 v_tmp_arr               t_tmp_arr;
 v_criteria_pairs        TABLE_OPTION_ATTRIBUTES;

BEGIN
  open cur;
  fetch cur bulk collect into v_tmp_arr;
  close cur;
  for i in 1..v_tmp_arr.count loop
    -- it's better to wrap it into a function which accepts one type of record and returns another one        
    v_criteria_pairs(i).option_name := v_tmp_arr(i).option_name;
    v_criteria_pairs(i).option_value := v_tmp_arr(i).option_value;
    v_criteria_pairs(i).option_selected_ind := v_tmp_arr(i).option_selected_ind;
    v_criteria_pairs(i).option_class := v_tmp_arr(i).option_class;
  end loop;
END;

Answer 3

You can't when using the select [bulk collect] into syntax. In a comment you said:

It would be madness if both of these statements are true: 1) user-defined records allow you to define default values, and 2) you must populate every field of a user-defined record.

The first statement is true; the second is only true if you assign the entire record from a query.

The documentation says:

For a record variable of a RECORD type, the initial value of each field is NULL unless you specify a different initial value for it when you define the type.

So if you create a record variable the defaults are set:

declare
  v_rec RECORD_OPTION_ATTRIBUTES;
begin
  dbms_output.put_line(v_rec.option_name ||':'|| v_rec.option_value
    ||':'|| v_rec.option_selected_ind ||':'|| v_rec.option_class
    ||':'|| v_rec.option_attributes);
end;
/

INVALID NAME":INVALID VALUE":0::

PL/SQL procedure successfully completed.

You can then override the defaults by individually setting the field values.

If you select into the record variable then

For each column in select_list, the record variable must have a corresponding, type-compatible field. The columns in select_list must appear in the same order as the record fields.

It doesn't explicitly say that you can't have fewer values in the select list than the record type, but the second sentence sort of implies that; you happen to have added your extra field at the end of the record but there was nothing stopping you putting it at the start, which would more clearly have violated that. There is no mechanism to specify which column in the select list maps to which field in the record, so you have to supply exactly the same number, of the same type, in the same order.

The values from the query are used to populate the record, always overwriting the defaults. You can't not provide a field value. (Even if your query evaluates a column value to null, that still overrides the default; if your query did SELECT null AS option_name, ... you'd get an ORA-06502 numeric or value error because the field is not-null). So none of your defaults apply when using select into, with or without bulk collect.

Unfortunately, you will either add new record and table types with the extra field (which you won't be able to pass to procedures expecting the original types, so that probably isn't practical; you could maybe add translation functions but that's just making things worse), or as @MartinSchapendonk suggested, take the hit and modify your existing code.

You may not need to change anything that only processes the collection/records as they will just not look at the new field - though presumably you will be making some modifications or there would be no point having the field at all. And you don't need to change anything that constructs the records directly, as those will get the default null value, even if that is in a cursor loop (that doesn't fetch into the record variable). You only (!) need to change how a collection/record is populated from SQL queries, with select into, select bulk collect into, or fetch into.

Answer 4

AFAIK as per Oracle doc, "To set all the fields in a record to default values, assign to it an uninitialized record of the same type", and this is their example:

DECLARE
   TYPE RecordTyp IS RECORD (field1 NUMBER, 
                         field2 VARCHAR2(32) DEFAULT 'something');
   rec1 RecordTyp;
   rec2 RecordTyp;
BEGIN
-- At first, rec1 has the values you assign.
   rec1.field1 := 100; rec1.field2 := 'something else';
-- Assigning an empty record to rec1
-- resets fields to their default values.
-- Field1 is NULL and field2 is 'something'
-- due to the DEFAULT clause
   rec1 := rec2;
   DBMS_OUTPUT.PUT_LINE
     ('Field1 = ' || NVL(TO_CHAR(rec1.field1),'<NULL>') || ',
      field2 = ' || rec1.field2);
END;
/