Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

PL/SQL Feature request: get a record variable initialized with the table defaults (myrec := MYTABLE%DEFAULTVALUES)

qwe23304Nov 15 2023

I don't know it this is the correct channel to submit a suggestion for an enhancement to the PL/SQL syntax, (If someone knows better, I would be grateful if I get pointed to the right direction)

Currently we have these syntaxes:

declare 
   rec MYTABLE%ROWTYPE;
   simpleval MYTABLE.FIELD%TYPE;

I have been working with pl/SQL for two decades and I have always felt we need also to be able to do this:

declare 
   rec MYTABLE%ROWTYPE;
   simpleval MYTABLE.MYFIELD%TYPE;
begin
   rec       := MYTABLE%DEFAULTVALUES;
   simpleval := MYTABLE.MYFIELD%DEFAULTVALUE;
end;

the idea is to be able to be able to initialize a variable (even a record variable) by using the same defaults that have been defined for a column (or for all the columns, in case of a record variable) of a table.

The bigger problem is for record types: not being able to initialize a new record with the table default values makes the “whole record insert” syntax (insert into MYTABLE values rec) dangerous to be used, because whatever code you write to insert a new record is going to break at runtime whenever someone adds a new mandatory field, even if it has a default value, since MYTABLE%ROWTYPE gives you a record whose fields are all null, regardless of the default values declared on the table.

Comments
Post Details
Added on Nov 15 2023
0 comments
103 views