Home » SQL & PL/SQL » SQL & PL/SQL » How to Inherit data default from %TYPE attribute for a variable (Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production)
How to Inherit data default from %TYPE attribute for a variable [message #681397] Wed, 15 July 2020 04:36 Go to next message
Andrey_R
Messages: 335
Registered: January 2012
Location: Israel
Senior Member

Hi all,
I have a test table, with a default value for a column.


SQL> create table test ( col1 varchar2 (10) default 'defval1' not null ) ;

Table created.

Then I am trying to create a procedure that will use %TYPE attribute to inherit the properties of the column name implicitly.
Then I want to insert my variable into it, and if not set, I want it to be inserted with 'defval1":

SQL>
SQL> declare
  2  v_col1 test.col1%TYPE;
  3  begin
  4    INSERT INTO TEST VALUES ( V_Col1 ) ;
  5    end;
  6    /
declare
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MYUSER"."TEST"."COL1")
ORA-06512: at line 4


I did read some documentation saying I can't do that in here Smile => https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/type_attribute.htm#LNPLS01352

And it says Quote:
"The referencing item inherits the default value only if the referencing item is not a database column and does not have the NOT NULL constraint."
The question is, is there a way to do it ?
I mean, if I don't provide a value, use implicitly the data default ?

The real-life case I am aiming at has tens of columns, so the idea is to avoid writing code to go to USER_TAB_COLS and such for every one of the columns...

Many thanks in advance!

Andrey R

[Updated on: Wed, 15 July 2020 04:38]

Report message to a moderator

Re: How to Inherit data default from %TYPE attribute for a variable [message #681399 is a reply to message #681397] Wed, 15 July 2020 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The question is, is there a way to do it ?
I mean, if I don't provide a value, use implicitly the data default ?
The problem is that with your variable (even not set) you provide a value which is NULL.
The only direct way to not provide a value for a column is to not put this later one in the statement (or use the keyword DEFAULT) and so have 2 statements in your PL/SQL block.

The other way is to add a before insert (and update) trigger to the table.

Re: How to Inherit data default from %TYPE attribute for a variable [message #681402 is a reply to message #681399] Wed, 15 July 2020 07:09 Go to previous messageGo to next message
Andrey_R
Messages: 335
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Wed, 15 July 2020 13:49
Quote:
The question is, is there a way to do it ?
I mean, if I don't provide a value, use implicitly the data default ?
The problem is that with your variable (even not set) you provide a value which is NULL.
The only direct way to not provide a value for a column is to not put this later one in the statement (or use the keyword DEFAULT) and so have 2 statements in your PL/SQL block.

The other way is to add a before insert (and update) trigger to the table.

Ok, so the first way means to set a default per variable in addition to the %TYPE bit ?
Will this require me to set the default manually in my code, like this ?

declare
v_col1 test.col1%TYPE := 'defval1';
begin
  INSERT INTO TEST VALUES ( V_Col1 ) ;
  end;
/

If the answer is yes - this will require manually setting defaults per variable,
and not inheriting nothing but the datatype and precision/character length, right ?

If this is the case, then we are still litteralizing the parameter list, and missing the point of trying to dynamically make the defaults be inherited from the data dictionary



I'll be happy to hear from you about this, did I understand correctly ?

As per the trigger option, I'll conduct a testcase and will present it soon here for knowledge sharing/discussion.

Thanks
Andrey
Re: How to Inherit data default from %TYPE attribute for a variable [message #681403 is a reply to message #681402] Wed, 15 July 2020 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
If the answer is yes - this will require manually setting defaults per variable,
and not inheriting nothing but the datatype and precision/character length, right ?

Yes.

Quote:
If this is the case, then we are still litteralizing the parameter list, and missing the point of trying to dynamically make the defaults be inherited from the data dictionary

As I said you can put it in 2 statements:
SQL> declare
  2    v_col1 test.col1%TYPE;
  3  begin
  4    if v_col1 is null then
  5      INSERT INTO TEST VALUES (default);
  6    else
  7      INSERT INTO TEST VALUES ( V_Col1 ) ;
  8    end if;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select * from test;
COL1
----------
defval1
You can also manually get the default value from data dictionary, DATA_DEFAULT column:
SQL> desc user_tab_columns
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 TABLE_NAME                       NOT NULL VARCHAR2(30)
 COLUMN_NAME                      NOT NULL VARCHAR2(30)
 DATA_TYPE                                 VARCHAR2(106)
 DATA_TYPE_MOD                             VARCHAR2(3 CHAR)
 DATA_TYPE_OWNER                           VARCHAR2(30)
 DATA_LENGTH                      NOT NULL NUMBER
 DATA_PRECISION                            NUMBER
 DATA_SCALE                                NUMBER
 NULLABLE                                  VARCHAR2(1 CHAR)
 COLUMN_ID                                 NUMBER
 DEFAULT_LENGTH                            NUMBER
 DATA_DEFAULT                              LONG
 NUM_DISTINCT                              NUMBER
 LOW_VALUE                                 RAW(32)
 HIGH_VALUE                                RAW(32)
 DENSITY                                   NUMBER
 NUM_NULLS                                 NUMBER
 NUM_BUCKETS                               NUMBER
 LAST_ANALYZED                             DATE
 SAMPLE_SIZE                               NUMBER
 CHARACTER_SET_NAME                        VARCHAR2(44 CHAR)
 CHAR_COL_DECL_LENGTH                      NUMBER
 GLOBAL_STATS                              VARCHAR2(3 CHAR)
 USER_STATS                                VARCHAR2(3 CHAR)
 AVG_COL_LEN                               NUMBER
 CHAR_LENGTH                               NUMBER
 CHAR_USED                                 VARCHAR2(1 CHAR)
 V80_FMT_IMAGE                             VARCHAR2(3 CHAR)
 DATA_UPGRADED                             VARCHAR2(3 CHAR)
 HISTOGRAM                                 VARCHAR2(15 CHAR)
Re: How to Inherit data default from %TYPE attribute for a variable [message #681407 is a reply to message #681397] Wed, 15 July 2020 08:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, do you really have one column table with default value? Default in such case is meaningless since the only way for a column to get default value is to omit it in list of columns and list of values and Oracle doesn't support syntax like:

SQL> insert into test() values();
insert into test() values()
                 *
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into test(col1) values();
insert into test(col1) values()
                              *
ERROR at line 1:
ORA-00936: missing expression


SQL>
So you must have at least two columms (and you can make second column invisible if you want to). Then:

SQL> create table test ( col1 varchar2 (10) default 'defval1' not null,dummy varchar2(1) invisible) ;

Table created.

SQL> declare
  2      v_col1 test.col1%TYPE;
  3  begin
  4      if v_col1 is null
  5        then
  6          INSERT INTO TEST(dummy) VALUES(null);
  7        else
  8          INSERT INTO TEST VALUES (v_col1) ;
  9      end if;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select  *
  2    from  test
  3  /

COL1
----------
defval1

SQL> declare
  2      v_col1 test.col1%TYPE := 'XYZ';
  3  begin
  4      if v_col1 is null
  5        then
  6          INSERT INTO TEST(dummy) VALUES(null);
  7        else
  8          INSERT INTO TEST VALUES (v_col1) ;
  9      end if;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select  *
  2    from  test
  3  /

COL1
----------
defval1
XYZ

SQL>
SY.
Re: How to Inherit data default from %TYPE attribute for a variable [message #681408 is a reply to message #681407] Wed, 15 July 2020 09:04 Go to previous messageGo to next message
Andrey_R
Messages: 335
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Wed, 15 July 2020 15:49

Quote:
If the answer is yes - this will require manually setting defaults per variable,
and not inheriting nothing but the datatype and precision/character length, right ?
Yes.

Quote:
If this is the case, then we are still litteralizing the parameter list, and missing the point of trying to dynamically make the defaults be inherited from the data dictionary
As I said you can put it in 2 statements:
SQL> declare
  2    v_col1 test.col1%TYPE;
  3  begin
  4    if v_col1 is null then
  5      INSERT INTO TEST VALUES (default);
  6    else
  7      INSERT INTO TEST VALUES ( V_Col1 ) ;
  8    end if;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select * from test;
COL1
----------
defval1
You can also manually get the default value from data dictionary, DATA_DEFAULT column:
SQL> desc user_tab_columns
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 TABLE_NAME                       NOT NULL VARCHAR2(30)
 COLUMN_NAME                      NOT NULL VARCHAR2(30)
 DATA_TYPE                                 VARCHAR2(106)
 DATA_TYPE_MOD                             VARCHAR2(3 CHAR)
 DATA_TYPE_OWNER                           VARCHAR2(30)
 DATA_LENGTH                      NOT NULL NUMBER
 DATA_PRECISION                            NUMBER
 DATA_SCALE                                NUMBER
 NULLABLE                                  VARCHAR2(1 CHAR)
 COLUMN_ID                                 NUMBER
 DEFAULT_LENGTH                            NUMBER
 DATA_DEFAULT                              LONG
 NUM_DISTINCT                              NUMBER
 LOW_VALUE                                 RAW(32)
 HIGH_VALUE                                RAW(32)
 DENSITY                                   NUMBER
 NUM_NULLS                                 NUMBER
 NUM_BUCKETS                               NUMBER
 LAST_ANALYZED                             DATE
 SAMPLE_SIZE                               NUMBER
 CHARACTER_SET_NAME                        VARCHAR2(44 CHAR)
 CHAR_COL_DECL_LENGTH                      NUMBER
 GLOBAL_STATS                              VARCHAR2(3 CHAR)
 USER_STATS                                VARCHAR2(3 CHAR)
 AVG_COL_LEN                               NUMBER
 CHAR_LENGTH                               NUMBER
 CHAR_USED                                 VARCHAR2(1 CHAR)
 V80_FMT_IMAGE                             VARCHAR2(3 CHAR)
 DATA_UPGRADED                             VARCHAR2(3 CHAR)
 HISTOGRAM                                 VARCHAR2(15 CHAR)

That looks like a perfect solution:

SQL> drop table test purge;

Table dropped.

SQL> create table test
  2  (
  3  col1 varchar2 (10) default 'defval1' not null,
  4  col2 number(5) default 42 not null
  5  ) ;

Table created.

SQL>
SQL>
SQL> declare
  2  v_col1 test.col1%TYPE;
  3  begin
  4    INSERT INTO TEST VALUES ( default,default ) ;
  5    end;
  6    /

PL/SQL procedure successfully completed.

SQL> select * from test;

COL1             COL2
---------- ----------
defval1            42

SQL>
I'm a little confused by the documentation says I can't do this, or to the least this is what I understood in the first place,
Before you showed me I actually can...


Anyhow, this works well. Thank you very much!

Andrey R.
Re: How to Inherit data default from %TYPE attribute for a variable [message #681409 is a reply to message #681408] Wed, 15 July 2020 09:05 Go to previous messageGo to next message
Andrey_R
Messages: 335
Registered: January 2012
Location: Israel
Senior Member

Mission accomplished.
Thank you both.


Andrey R
Re: How to Inherit data default from %TYPE attribute for a variable [message #681411 is a reply to message #681407] Wed, 15 July 2020 10:33 Go to previous message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
the only way for a column to get default value is to omit it in list of columns
Not correct, since version 9.0 you can use the keyword DEFAULT to tell Oracle you want to insert the default column value:
SQL> create table test ( col1 varchar2 (10) default 'defval1' not null ) ;

Table created.

SQL> insert into test values (default);

1 row created.

SQL> select * from test;
COL1
----------
defval1

1 row selected.
Previous Topic: REGEXP_LIKE to match string in a list
Next Topic: creating a materialized view throws "ORA-01031: insufficient privileges"
Goto Forum:
  


Current Time: Fri Sep 18 03:51:08 CDT 2020