| timestamp question [message #651416] |
Tue, 17 May 2016 10:03  |
desmond30
Messages: 41
Registered: November 2009
|
Member |
|
|
I have a table, where i created a virtual column and hoped to get the month out of it
create table test(x number, TIMESTAMP TIMESTAMP(6) NOT NULL,
mymonth generated always as (to_number(to_char("TIMESTAMP",'MM'))));
problem is in my PLSQL, when i have a code that says my_rec test%rowtype, I get the error ORA-54013: INSERT operation disallowed on virtual columns
when i try to insert
so its obvious when using virtual column, i cannot do a %rowtype and insert....
is there a way i can have mymonth get the month out of the current timestamp while creating the table ?
something in the lines of
create table (x number,
mymonth --- get the month from sysdate)
|
|
|
|
| Re: timestamp question [message #651417 is a reply to message #651416] |
Tue, 17 May 2016 10:05  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
desmond30 wrote on Tue, 17 May 2016 08:03I have a table, where i created a virtual column and hoped to get the month out of it
create table test(x number, TIMESTAMP TIMESTAMP(6) NOT NULL,
mymonth generated always as (to_number(to_char("TIMESTAMP",'MM'))));
problem is in my PLSQL, when i have a code that says my_rec test%rowtype, I get the error ORA-54013: INSERT operation disallowed on virtual columns
when i try to insert
so its obvious when using virtual column, i cannot do a %rowtype and insert....
is there a way i can have mymonth get the month out of the current timestamp while creating the table ?
something in the lines of
create table (x number,
mymonth --- get the month from sysdate)
solution is to CREATE VIEW which includes the month
|
|
|
|
| Re: timestamp question [message #651422 is a reply to message #651417] |
Tue, 17 May 2016 10:26 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You are basically asking how to not do the thing you want to do.
A calculated field in a table is a virtual column, it can't be anything else
You can either modify your inserts to reference the columns properly, which is generally best practice, or as BS suggests use a view.
|
|
|
|
| Re: timestamp question [message #651436 is a reply to message #651416] |
Tue, 17 May 2016 18:04 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can do things the old-fashioned way, with a trigger, like we did before virtual columns existed. Please see the reproduction of problem and possible solution below. I would also recommend using something other than "TIMESTAMP" for your column name, as it is an Oracle data type. It also would help, in the future, if you provide a copy and paste of a run of your code from SQL*Plus, including the error, as I have done below. Otherwise, we can't be sure if we have the exact problem, which makes it hard to provide an accurate solution.
-- reprodution of problem:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test
2 (x NUMBER,
3 "TIMESTAMP" TIMESTAMP(6) NOT NULL,
4 mymonth GENERATED ALWAYS AS (TO_NUMBER (TO_CHAR ("TIMESTAMP", 'MM'))))
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO test (x, "TIMESTAMP") select 1, SYSTIMESTAMP from dual
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> COLUMN timestamp FORMAT A30
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test
2 /
X TIMESTAMP MYMONTH
---------- ------------------------------ ----------
1 17-MAY-16 03.58.02.386000 PM 5
1 row selected.
SCOTT@orcl_12.1.0.2.0> DECLARE
2 my_rec test%ROWTYPE;
3 BEGIN
4 SELECT * INTO my_rec FROM test WHERE ROWNUM = 1;
5 INSERT INTO test VALUES my_rec;
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
ORA-06512: at line 5
-- possible solution:
SCOTT@orcl_12.1.0.2.0> DROP TABLE test
2 /
Table dropped.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test
2 (x NUMBER,
3 timestamp TIMESTAMP(6) NOT NULL,
4 mymonth NUMBER)
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TRIGGER test_mymonth_biur
2 BEFORE INSERT OR UPDATE ON test
3 FOR EACH ROW
4 BEGIN
5 :NEW.mymonth := TO_NUMBER (TO_CHAR (:NEW."TIMESTAMP", 'MM'));
6 END test_mymonth_biur;
7 /
Trigger created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO test (x, timestamp) select 1, SYSTIMESTAMP from dual
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> COLUMN "TIMESTAMP" FORMAT A30
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test
2 /
X TIMESTAMP MYMONTH
---------- ------------------------------ ----------
1 17-MAY-16 03.58.02.811000 PM 5
1 row selected.
SCOTT@orcl_12.1.0.2.0> DECLARE
2 my_rec test%ROWTYPE;
3 BEGIN
4 SELECT * INTO my_rec FROM test WHERE ROWNUM = 1;
5 INSERT INTO test VALUES my_rec;
6 END;
7 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test
2 /
X TIMESTAMP MYMONTH
---------- ------------------------------ ----------
1 17-MAY-16 03.58.02.811000 PM 5
1 17-MAY-16 03.58.02.811000 PM 5
2 rows selected.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
