How to populate a user record that has default values?

Question

TL DR:

How to declare a custom record type so that if I do not fill out one of the fields, this field will execute it DEFAULT ?

---

Details:

In my package specification, I define the following types of records and tables:

 /* 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 keen eye will notice that I do not insert any values ​​into the option_attributes field; I fill out only 4 of the 5 available fields.

When I try to compile this package, I get the following error from the package body:

PL / SQL: ORA-00913: Too Many Values

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

How to declare a record type so that if I do not specify a value for option_attributes , this field will default to NULL ?

Answer 1

AFAIK according to Oracle doc , "To set all fields in a record by default, assign 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; / 

Answer 2

You cannot use select [bulk collect] into syntax. In the comment you said:

It would be crazy if both of these statements are true: 1) user records allow you to define default values, and 2) you must fill in each field of the user record.

The first statement is true; the second - only if you have assigned the entire record from the query.

The documentation says :

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

So, if you create a record variable, the default values ​​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 default values ​​by individually setting the field values.

If you select a record variable , then

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

It clearly does not indicate that the list of choices cannot have less values ​​than the type of record, but it follows from the second sentence that; you must have added your extra field at the end of the record, but there was nothing to prevent you from putting it at the beginning, which would be more clearly violated. There is no mechanism to indicate which column in the selection list matches which field in the record, so you need to specify 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 default values. You cannot specify a field value. (Even if your query evaluates the column to null, this still overrides the default if your query made SELECT null AS option_name, ... you would get a digital or significant error ORA-06502 because the field is non-null ) Thus, none of your defaults apply when using select into , with or without bulk collect .

Unfortunately, you will either add new types of records and tables with an additional field (which you cannot pass to procedures that expect the original types, so this is probably impractical, maybe you can add translation functions, but it just makes the situation worse), or, as @MartinSchapendonk suggested, take a hit and modify existing code.

You may not need to change anything that only handles the collection or records, as they simply won’t look at the new field — although you’ll probably make some changes or not have the field at all. And you don’t need to change anything that directly creates the records, as they will get the default value even if it is in the cursor loop (which is not included in the record variable). You only (!) Have to change how the collection / record is populated from SQL queries using select into , select bulk collect into or fetch into .

Answer 3

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

Here is another option with an object type that is not a PL / SQL record, but has the same behavior and more parameters to initialize with default values ​​in the constructor (use PL / SQL expressions and functions):

Define a 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 ); 

The constructor uses default values ​​and can use complex initialization logic. Please keep in mind that you may have several 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 run the sql test:

 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, the last 2 columns have a default value (null in this case). Compared to the original sql query in your question, all you have to do is wrap the selected columns with RECORD_OPTION_ATTRIBUTES ().