Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Unable to Insert into temp db(HSQL) VIEW.

 
kapil shukla
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I am facing this issue with HSQL DB. Even if i try to create a simple view, i am unable to insert into it. Following are the steps I am trying:

1. create table test_emp(emp_id int, emp_name varchar, emp_age varchar, emp_sal varchar);
2. insert into test_emp values (4, 'rakesh', 29, 35000);//inserted some values into the table
3. CREATE VIEW emp_view as select emp_id,emp_name, emp_age, emp_sal from test_emp;// creating a view displays the values which are available in table.
4. INSERT INTO EMP_VIEW (emp_id,emp_name, emp_age, emp_sal) VALUES (9, 'naresh', '41', '42000');//this line gives error
[Error Code: -55, SQL State: S0005] Not a Table: EMP_VIEW in statement [INSERT INTO EMP_VIEW]

It seems it will accept only a table name here.
I am using Temp DB so all the tables, view and even the db gets deleted once session is completed.

Can anybody help me out in resolving this issue?

Thanks,
Kapil
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can't insert into a view (usually), a view is a read only way of looking at the database.

A view can select from many tables and have a complicated where clause with outer joins and inner joins, unions and subselects. How can the database know what to insert where, how would it handle mandatory columns that were on the table but not in the view?

 
kapil shukla
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
we can insert in a DB VIEW, provided some conditions. You can go through this link for that http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9014.htm#i2080505

probably I could use INSTEAD OF trigger, but it is also giving me error.


CREATE TRIGGER EMP_Trigger on EMP_VIEW
INSTEAD OF INSERT
AS
BEGIN
--Build an INSERT statement ignoring inserted.ID and
--inserted.ComputedCol.
INSERT INTO TEST_EMP
SELECT emp_id,emp_name, emp_age, emp_sal
FROM EMP_VIEW
END;

ERROR: Unexpected Token INSTEAD, requires ON in statement

is there something wrong with this?
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
are you using HSQLdb (hyper SQL) or oracle, as you are linking to the oracle database syntax not HSQL db syntax
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic