Initializing the pl / sql Record Type

Question

In PL / SQL, a varray can be initialized at creation time as follows:

 TYPE colour_tab IS VARRAY(3) OF VARCHAR2(20); french_colours colour_tab := colour_tab('RED','WHITE','BLUE'); 

Is there an equivalent initialization method for PL / SQL record types?

 type location_record_type is record ( street_address varchar2(40), postal_code varchar2(12), city varchar2(30), state_province varchar2(25), country_id char(2) not null := 'US' ); 

Answer 1

No no. You must assign each value explicitly. Link to the documentation here .

Answer 2

Use the function to act as a kind of constructor function (see f () function):

 DECLARE TYPE ty_emp IS RECORD( id INTEGER, name VARCHAR(30), deptcode VARCHAR(10) ); TYPE ty_tbl_emp IS TABLE OF ty_emp; tbl_emp ty_tbl_emp; FUNCTION f ( -- <============== id INTEGER, name VARCHAR, deptcode VARCHAR) RETURN ty_emp IS e ty_emp; BEGIN e.id := id; e.name := name; e.deptcode := deptcode; RETURN e; END f; BEGIN tbl_emp := ty_tbl_emp( f(1, 'Johnson', 'SALES'), f(2, 'Peterson', 'ADMIN')); Dbms_Output.put_line(tbl_emp(2).name); END; 

Answer 3

Record types are really intended for storing rows from SELECT statements.

  .... type location_record_type is record ( street_address varchar2(40), postal_code varchar2(12), city varchar2(30), state_province varchar2(25), country_id char(2) not null := 'US' ); type location_record_nt is table of location_record_type; loc_recs location_record_nt; begin select street_name , pcode , city , region , country_code bulk collect into loc_recs from t69 where .... 

Obviously, in cases where the query is not SELECT * FROM from the same table (because in this case we can use %ROWTYPE .

Answer 4

You can create a function that returns this type of record.

See code example:

 DECLARE type location_record_type is record ( street_address varchar2(40), postal_code varchar2(12), city varchar2(30), state_province varchar2(25), country_id char(2) not null := 'US'); v_loc_rec location_record_type; FUNCTION new_loc_rec RETURN location_record_type IS v_new_loc_rec location_record_type; BEGIN return v_new_loc_rec; END; BEGIN v_loc_rec := new_loc_rec; v_loc_rec.state_province := 'SomeState'; v_loc_rec.country_id := 'SU'; dbms_output.put_line('State: '||v_loc_rec.state_province||'; Country_ID: '||v_loc_rec.country_id); v_loc_rec := new_loc_rec; dbms_output.put_line('State: '||v_loc_rec.state_province||'; Country_ID: '||v_loc_rec.country_id); END; 

Answer 5

Oracle 18c allows you to initialize a record using qualified expressions :

 declare type location_record_type is record ( street_address varchar2(40), postal_code varchar2(12), city varchar2(30), state_province varchar2(25), country_id char(2) not null := 'US' ); --Oracle 18c Qualified Expression: v_location location_record_type := location_record_type('1234 Fake Street', '90210', 'Springfield', 'KY', 'US'); begin dbms_output.put_line('It worked!'); end; / 

You can run the above code example in Oracle Live SQL here . (Unfortunately, this site requires a login. Since 18c is not available for download, there are several convenient ways to test new features.)