| How to Inherit data default from %TYPE attribute for a variable [message #681397] |
Wed, 15 July 2020 04:36  |
Andrey_R
Messages: 419
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 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
 => 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 #681402 is a reply to message #681399] |
Wed, 15 July 2020 07:09  |
Andrey_R
Messages: 419
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 15 July 2020 13:49Quote: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 #681407 is a reply to message #681397] |
Wed, 15 July 2020 08:58 |
Solomon Yakobson
Messages: 3212
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>
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> |
|
|
|
| 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 |
Andrey_R
Messages: 419
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
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>
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 #681411 is a reply to message #681407] |
Wed, 15 July 2020 10:33 |
 |
Michel Cadot
Messages: 68418
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. |
|
|
|
] 
Search
Help
Members
Register
Login
Home
