SQL & PL/SQL :: Insert Values Through View?
Mar 30, 2012how to insert values through view.
View 5 Replies
ADVERTISEMENT
INSERT Values In A VIEW With INNER JOIN
Aug 5, 2011I have a question related with VIEWS
I would like INSERT data in a VIEW with a INNER JOIN, like this example:
CODECREATE VIEW MYVIEW (order_id,list_price,customer_id) AS
SELECT order_id, list_price, customer_id FROM ORDERS o
INNER JOIN PRODUCT_INFO p ON (o.order_id= p.pdt_id);
INSERT INTO MYVIEW VALUES (4,500,10); /* will cause an error*/
But when I try to execute the insert statement, the "SQL Developer" returns a error:
ORA-01779 - "cannot modify a column which maps to a non key-preserved".
Why can't I do it? Are there some way to do it?
SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table
Dec 23, 2010My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
How To Mimic Insert Through A View
Jul 9, 2010The functionality I want to accomplish is accomplished at the database level by creating a select * view of a table in a remote database (through a database link) and inserting a record in the view and getting the update written to the remote table.
The issue is that the call must be made from an application using JDBC and the database is required to run dedicated not shared. If I can change either of those, I can get it to work but those are not acceptable solutions. Materialized views would be fine except that the remote system will not implement any master site requirements (they will not make any non-data changes).
The error is ORA-24777: use of non-migratable database link not allowed.
The current focus is on a stored procedure that the application would call that would write the insert/update/delete locally.
SQL & PL/SQL :: Insert Data Into View
Mar 27, 2010I have created a view in that the primary key column is not included,so that we cannot insert data into that view,but i want to insert data into that view without using Instead of Triggers.
CM: swapped version and title so they're the right way round.
PL/SQL :: Insert Using Trigger And View
Jun 13, 2012I'm trying to insert data in my_second_table using a trigger and a view when I insert the data in my_first_table but there's something wrong in the code.
CREATE OR REPLACE TRIGGER my_trigger
AFTER INSERT ON my_first_table
FOR EACH ROW
BEGIN
[Code]...
I'm suspecting that the problem is in the :new.cod_emp
P.S.: I'm using: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production on Enterprise Linux Server release 5.5 (Carthage)
SQL & PL/SQL :: No Entries In Materialized View Log For Insert Append?
Aug 9, 2010I've created a materialized view log on table with the following statement:
CREATE MATERIALIZED VIEW LOG ON table_a
WITH ROWID, SEQUENCE (column_a, column_b, column_c)
INCLUDING NEW VALUES;
The insert is done with the following statement:
INSERT
/*+ APPEND PARALLEL("table_a") */
INTO
"table_a"
("column_a",
"column_b",
"column_c",
"column_d_sum")
(select
column_a",
"column_b",
"column_c",
"column_d_sum"
from table_B)
But the Log is empty when the insert is finished. When I insert rows without the APPEND hint, rows are created in the log table. So, doesn't the log record bulk loads?
How To Insert Values In Database
Dec 10, 2006i want to create database. i have created the schema & done all activity like decide foreign & primary key of table all things on paper? when i open oracle 8i & what should i write at front of sql prompt. For enter the value into database should i give the set path? How to store value into particular directory or folder in hard disk? should i directly start with create database?
I know proper syntax. Suppose i want to store the database & values in folder " D:apurva " what should i do. After entering oracle 8i it show sql> so what i do to store database in D:apurva . should i directly start with create table command in front of sql>
PL/SQL :: Insert Values Into EMP Table
Jul 26, 2013I have a table EMP having columns User_Id, ENO,Org_ID, Dept_ID.Now I would like to insert values into this EMP table using below conditions.Insert into EMP
(user_seq.nextval,(select empno from employees where empno in(....(empnumbers), (select org_id from organizations where org_name=' XXXXXXXXXX'), (select dept_id from DEPT where dname in ('MANAGER','ANALYST','SALESMAN') ))
provide me the query for the above requirement.
INSERT INTO Table (dateadded) VALUES (NOW())?
Apr 24, 2007In MySQL I can insert the current timestamp by using MySQL's proprietary NOW() function.
Does Oracle have a similar function or method for adding a current timestamp with an INSERT?
SQL & PL/SQL :: Getting Error When Try To Insert Values Into Database
Nov 25, 2011I am trying to insert values into my database but it keeps coming up with the same error message: 'not enough values'
Here's what am I trying to do:
This is the insert I am having trouble with.
INSERT INTO Customers
VALUES (000120, 'Andy', 'Finnigan', 'M', '23/JUL/1978',
Address_varray_type
(Address_type('Home', 76, 'Fun Way', 'Semilong', 'NORTHAMPTON', 'NN3 8YF'),
Address_type('Work', 54, 'Region Street', 'Halfache', 'OXFORDSHIRE', 'OX4 7EG'),
Contact_table_type
(Contact_type('Non_work hours', '07659485743', '02084731131', 'AFinnigan@yahoo.com'),
Contact_type('Work_hours', '07795052846', '0768543323', NULL))));
Here are the Object tables.
CREATE TYPE Address_type AS OBJECT(
Location VARCHAR2(20),
House_number NUMBER(6),
Street_name VARCHAR2(30),
Town VARCHAR2(20),
County VARCHAR2(20),
Postcode VARCHAR2(20));
/
CREATE TYPE Contact_type AS OBJECT(
Contact_type VARCHAR2(30),
Home_no VARCHAR2(20),
Mobile_no VARCHAR2(20),
Email VARCHAR2(50));
/
CREATE TABLE Customers(
Customer_id NUMBER(6) NOT NULL,
Firstname VARCHAR2(20),
Familyname VARCHAR2(20),
Gender CHAR DEFAULT 'M',
DOB DATE,
Address address_varray_type,
Contact_details contact_table_type)
NESTED TABLE Contact_details
STORE AS customer_contact_table;
Forms :: Insert Values In A Table Through Ref
Aug 26, 2011Table Structure:
Name of Table: info
Column: ID, Name, Cell,Cell1,Ref
i want to insert these values in a table through ref (key next item).
-----------------------------------------------------------
Key next Item Triger
DECLARE
A NUMBER;
B NUMBER;
BEGIN
SELECT :CELL,:CELL1 INTO A,B FROM DUAL;
[code].......
-------------------------------------------------------
but when i press enter(ref Key_next_item) it will return error:
FRM-40735: KEY_NEXT_ITEM RAISED UNHANLEDED EXCEPTION ERROR: ORA 40102
Insert Values Into A Nested Table?
May 15, 2013Insert values into a table. The table is called PurchaseOrder_objtab. Here is the type for the table:
CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT
(
PONo NUMBER,
CUST_ref REF Customer_objtyp,
OrderDate DATE,
ShipDate DATE,
[code]...
The LineItemList_ntab is a nested table.
Here is the create code for LineItemList_ntabtyp:
CREATE TYPE LineItem_objtyp AS OBJECT (
LineItemNo NUMBER,
Stock_ref REF StockItem_objtyp,
Quantity NUMBER,
Discount NUMBER
)
[code]...
In the above code, the LineItemList_ntab is an empty LineItemList_ntabtyp. I am wanting to add values to this nested table rather than it being empty in the INSERT INTO code.
Here is some of the code I have tried to insert values with:
INSERT INTO PurchaseOrder_objtab
SELECT 1008, REF(C),
SYSDATE, '12-MAY-1999',
LineItemList_ntabtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004,
NULL
[code]...
The first insert statement above produces the following error:
SQL Error: ORA-00933: SQL command not properly ended
The second insert statement above produces the following error:
SQL Error: ORA-00936: missing expression
successfully insert values into the LineItemList_ntab nested table?
Database Link To Fetch Values From View?
Dec 8, 2008Here is the information about database I need to connect from my view, as the underlying table for my view is in this database
Database instance : orcl
Machine name : contentm (not sure, if this needs to be used)
so in my schema, I tried the following
CREATE DATABASE LINK product_lnk2 USING 'orcl'
select * from cont20.V_FB_PRODUCT_NM@product_lnk2
where cont20 is the schema on orcl
But it gives me this error ORA-12154: TNS:could not resolve the connect identifier specified
SQL & PL/SQL :: View With Column Based On Row Values Of Another Table
Apr 13, 2011I have table with values :
PROV_IDMEASURE_IDPERCENTAGE
Z0000221P114 45
Z0000135P115 68
For the column all possible values are ( P102,P101,P103 etc toP124). I want to create a view ( if possible ) from the above with data output as :
PROV_ID P101 P102 P103 ............................P124
z000234 23 45 60 72
basically this view has columns based on the previous tables column ( MEASURE_ID) values and the values will be corresponding value in column Percentage.
JDeveloper, Java & XML :: Design Dynamic Table And Insert In A View
Feb 25, 2011I'm in a situation where i have to design dynamical XML table that gonna get inserted in a view:
late'si consider the following table:
CREATE TABLE FB_XML
(
ID NUMBER(10) NOT NULL,
FB_ID NUMBER(10),
XMLCONTENT CLOB
)
the XML content field have content like the following:
XMLROOT(XMLELEMENT (
"Form",
XMLELEMENT ("elements",
(SELECT XMLAGG (
[code].......
so late consider for the first row i insert for the
* id:1
* fb_id : 25
* xmlcontent:
XMLROOT(XMLELEMENT (
"Form",
[code]........
the code works "fine" here but the xml in the fb_xml table is not EXECUTED it simply escaped by oracle , i know i should add something so that the XML have to execute and generate.
Insert Rows Based On Text Values?
Mar 22, 2007I am an Oracle beginner and I am having some trouble with the following insert query.
I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.
INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338);
INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);
The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"
SQL & PL/SQL :: Insert All Rows Values Fetched By Cursor?
Aug 12, 2010I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.
what parameter or code I should used ?
I have marked with color row as below.
DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;
[Code]....
SQL & PL/SQL :: How To Grab The Update Values And Insert Into Procedure
Jun 8, 2011how can i make this script into a function or procedure which instead of user change the crime_id :=4 or 5 directly it actually can grab the id from a update statment like
UPDATE CRIME_STATUS SET CRIME_STATUS = 'open'
WHERE CRIME_ID = 9;
pick 9 and insert into the above statment so it runs as normal
[Code].....
SQL & PL/SQL :: Create SQL Insert Statement With Multiple Values?
Jun 3, 2010I need to create a SQL insert statement with mutipleValues and an select statement I am trying this it works -
insert into uwa_crew_ids(crew_ID, CREATION_DATE, CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_NAME, first_name )
values
( uwa_crew_ids_s.nextVal,sysdate, 1767,sysdate, 1767,'TEST1', 'NITA')
This statement does not work (with or without keyword) Is there any alternate syntax
insert into uwa_crew_ids(crew_ID, CREATION_DATE, CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_NAME, first_name )
( uwa_crew_ids_s.nextVal,sysdate, 1767,sysdate, 1767,(select last_name, first_name from uwa_crew_ids where guid = '8795EAAFBE694F8EE0440003BA2AEC90' ))
Forms :: How To Insert Values Through Blocks Using Button (save)
Dec 15, 2010I HAVE 2 BLOCKS E.G. MASTER-DETAIL AND i have created it manually then how to insert values thrugh these blocks using a button called "save" using "commit_form;" suppose form fields are
master-block:
emp_id
emp name
age
detail block:
address
city
where emp_id is primary key as well foreign key in detail table
how these values will be inserted in both tables
Forms :: How To Insert Column Values Into Text Items
Dec 30, 2011I have created the Textitem with five instances.
i am trying to assign column values to text item through query.
if my table contain one record,it shown in textitems.But if my table contains more records it is showing ora-1422 err.how to solve it.
SQL & PL/SQL :: Insert Into Oracle Table Of UNIX Script Values
Dec 24, 2012I want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table.
E.g. : insert into tbl_daily_status values(STAT,ENDTIME);
#!/bin/ksh
joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det"
for i in $joblist
do
STAT=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $6}'`
ENDTIME=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $4" " $5}'`
echo "$STAT"
echo "$ENDTIME"
done
PL/SQL :: Insert Values Into A Nested Table Or Array With A Loop
Oct 8, 2013How to create this pl/sql process to add elements to a nested table or varray within a loop. Here's the scenario: I have an apex package that has some pl/sql processes and some stored procedures. I am dealing with Inspection Areas. An Inspection Area has several sectors. I already have the loop that lists all the Inspection Areas and a loop inside that loop that lists all the sectors. There is an if statement that determines whether or not the sector name gets stored in the varray or table. I am not sure how to correctly do this and am not sure whether to use a nested table or varray. I've posted somewhat of a pseudo coded example below
If (you_belong_in_table) then
variable := store_me_in_varray /* OR */
variable := array_type(sector.sector_name)
i := i + 1;
end if;
/* Now we output our varray or table */
start loop
output(sector names one by one)
end loop I hope this makes sense. I more so just need the syntax to be able to continually added values to a table or varray while I'm already inside a loop; and also how to output those values end the end as well.
Forms :: Hierarchical Tree - Retrieve Values And Insert Into Table
Apr 19, 2010My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.
I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.
SQL & PL/SQL :: Insert Values Fetched From Object Type Into Empty Table?
May 4, 2012I need to insert values fetched from an object type into an empty table.
-- >> table creation:
create table ASSET
(
ASSETID NUMBER(5),
ASSETTYPE VARCHAR2(20));
-- >> created an object_type
SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20),
2 v_ASSETTYPE varchar2(20));
3 /
Type created
--- >> create the package
SQL> create or replace package test_arr is
2 type nest_tab is table of obj1;
3 procedure insert_data(t_nest IN nest_tab);
4 end test_arr;
5 /
Package created
SQL> create or replace package body test_arr is
2 procedure insert_data(t_nest IN nest_tab) is
3 begin
4 for i in t_nest.first..t_nest.last loop
5 insert into asset(ASSETID,
6 ASSETTYPE) values (t_nest(i).v_ASSETID,
[code]....
-- >> a block to execute the above package:
SQL> declare
2 type ref_tab is table of obj1;
3 ref_tab1 ref_tab;
4 begin
5 ref_tab1 := ref_tab(1,'a');
6 test_arr.insert_data(ref_tab1);
7 end;
8 /
But I am getting the below error when executing the package:
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 1:
[code]....
SQL & PL/SQL :: How To Get Left Padded Sequence Values In Variable To Insert It In Table For ID Creation
Feb 22, 2010I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :
CREATE SEQUENCE AAA_FOC.TRAINEE_ID INCREMENT BY 1 MINVALUE 0 MAXVALUE 999 NOCACHE NOCYCLE NOORDER
I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......
I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........
The code is given below:
CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
(sTraineeNo IN NUMBER,
nReturned_O OUT NUMBER)
IS
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
[code]......
Client Tools :: Providing Bind Variables As Values In Insert Statement?
Aug 23, 2011I executed the following PL/SQL block in SqlDeveloper :
VARIABLE max_dept_no NUMBER
DECLARE
v_dept_name VARCHAR2(30) := '&p_dept_name';
v_max NUMBER(4,0);
BEGIN
SELECT MAX(department_id) INTO v_max FROM departments;
:max_dept_no := v_max + 20;
INSERT INTO departments VALUES (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/
And it gave the error : Quote:Error report:
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"
The same code when executed in iSqlPlus gave no error.
Application Express :: Insert Into History Table When Update Action Is Performed On Tabular Form View Or Table
Aug 24, 2013My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
Forms :: Create View Based On Values Selected In Forms
Jun 7, 2013I am working on a simple form which will get the user to fetch required columns of employee master.
For that i have created a form which will display the column names and select option. When user clicks on query, the form will display the selected columns in a block. After going thru if required the user can download it to excel.
As of now I do not have filtering option. For this I thought of creating view based on the selection and then fetch records from the view and display it in form.
To achieve this I would like to know How I create or replace a view dynamically based on values selected. I remember doing this sort of thing long back, but could not recollect it.