Dynamic SQL - with CONCAT function [message #650277] |
Thu, 21 April 2016 07:20 |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
Hi,
Am using dynamic SQL way to form a insert statement as given below.
when i EXECUTE IMMEDIATE its giving error. As i couldn't able to copy full script.
I have given my sample script
as
ORA-00984: column not allowed here
ORA-06512: at "SS_REPO.PKG_SS_LOAD_ALL_TRANS", line 539
ORA-06512: at "SS_REPO.PKG_SS_LOAD_ALL_TRANS", line 42
ORA-06512: at line 6
create table t1 ( c1 varchar2(100), c2 varchar2(100), c3 varchar2(100))
insert into t1 a values ('ashok','kumar',CONCAT(c1,c2))
|
|
|
Re: Dynamic SQL - with CONCAT function [message #650279 is a reply to message #650277] |
Thu, 21 April 2016 07:31 |
|
Littlefoot
Messages: 21808 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Should beinsert into t1 a values ('ashok','kumar',CONCAT('ashok', 'kumar'))
Or, you could create a database trigger which would do the concatenation.
Or, you could use a virtual column, such as
create table t1
( c1 varchar2(100),
c2 varchar2(100),
c3 varchar2(200) generated always as (concat(c1, c2)) virtual
);
insert into t1 (c1, c2) values ('ashok','kumar');
|
|
|
|
|
|
|
|
|
|
Re: Dynamic SQL - with CONCAT function [message #650316 is a reply to message #650295] |
Fri, 22 April 2016 06:18 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
ORAGENASHOK wrote on Thu, 21 April 2016 22:47Yes , I want to know whenever DML operation happen this virtual column should have current values.
Again, not when DML operation happens but when virtual column is referenced:
SQL> create table t1
2 ( c1 number,
3 c2 number,
4 c3 number generated always as (c1 / c2) virtual
5 );
Table created.
SQL> insert into t1(c1,c2) values(1,0);
1 row created.
SQL> select c1,c2 from t1;
C1 C2
---------- ----------
1 0
SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
SY.
|
|
|