how 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].....
Store procedure code, I want to insert data in a database in this fashion,I want to check first if the record exist, if not Insert or else Update.
View 2 Replies View Relatedi Have Write A SP But Show me Error when i Compile It.
Create or Replace Procedure PREPAIDEXPENSE(v_OperationType varchar2(1))
v_ATTM_TXN_TYPES_CODE ACC_TXN_TYPES_MST.ATTM_TXN_TYPES_CODE%TYPE;
v_ATTM_TXN_TYPES_DESC ACC_TXN_TYPES_MST.ATTM_TXN_TYPES_DESC%TYPE;
v_ATTM_STATUS ACC_TXN_TYPES_MST.ATTM_STATUS%TYPE;
v_ATSM_STAGE_ID ACC_TXN_TYPES_MST.ATSM_STAGE_ID%TYPE;
v_PP_ACCOUNT_GL ACC_TXN_TYPES_MST.PP_ACCOUNT_GL%TYPE;
[code]....
Error:SQL command not properly ended
I'm writing a Procedure which Updates or Inserts data in Multiple tables. Selected fields of 10 tables need to be updated or Inserted. For this I created a table which comprises of fields related to all 10 tables. Then I write Procedure. Under this I create a Cursor which uploads the data from the newly created table which contains different fields of 10 tables. Then I write Update and Insert statements one by one for all 10 tables.
Sample Procedure below.
-------------------------------------------
Create or replace procedure p_proc as
spidm spriden.spriden_pidm%type;
cursor mycur is select * from mytable;
begin
for rec in mycur
[code]......
----------
Note: I created table on my server because data is coming from different server. They will upload the data in the table from there I pick and update the tables. Is updating or Inserting data in different tables one by one is correct?
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
I'm having an issue pulling an ID from a report column.
I have a report based on the query:
select Inspection_Name, Activation_Status, Activate, Deactivate, Activation_Date, Inspection_Sector_Id from Inspections
where Event_Id = :P0_Event_IdThis report is based on a select list at the top of the page. The user selects an event from the select list and the report is populated.
The query for the select list is: select event_name, event_id
from Water_EventsSimple enough so far. Each row is identified by the Inspection_Sector_Id. I want to be able to turn each row under the 'Activate' and the 'Deactivate' columns into links. I have accomplished this by turning this field into a link. The link needs to navigate to a page that has that rows Inspection_Sector_Id, but am not sure how to grab that Id. I am navigating via URL on the link, i.e. f?p=application_id:page_id:session......
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/
My 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]...
I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.
Eg:-
SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
[Code]....
The above table contains 14 records. Now i would like to update the salary column with different values like
EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000
How to update above values with single update query.
i have 2 identical tables....the trick is with regards to one column say column A,the first table TableA is constantly having data inserted and data updated, what im trying to do is create a before insert or update trigger that looks at column A and if column A=20 it changes this to 5 and inserts this into the tableB, everything else with the exception of Column A will be the same:-
this trigger i have done works but doesnt change the value of column A (INPUT_NADI) as i dont know how to do this:-
CREATE OR REPLACE TRIGGER UPDATE_TRG_NP_NE1_T0
BEFORE INSERT ON TESTNSS.NP_NE1_T1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO NP_NE1_T0
VALUES
[code]....
I have to write a trigger where when the table is updated then one column named 'Status' should be updated as 'U' and if arow is inserted in the table then the column 'Status' needs to be inserted with value 'I'.
Create table test_trig
(
vempno number,
vempname varchar2(20),
status char
)
New to triggers....how to go with both insert and update conditions together.
Can we write a trigger which takes care of both insert and update. I have used Merge statement where I can write conditions based on insert/update done.
I have a single table with a TOTAL_TIME column which I want to increment by a certain amount every time I get a request from a specific user. If the row for that user does not exist, it should be created and the TOTAL_TIME column should be set to the value that just came in. Otherwise, if it does exist, it should be incremented by the value passed in.
How can I accomplish this in oracle? I don't want to just first do a select, then insert, because that can cause race conditions. I want something that'll do the check and insert/update in one statement (locked).
i 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>
how to insert values through view.
View 5 Replies View RelatedI 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.
I have two identical tables from different DB's and I need to copy a row from db1 and insert into db2 if it does not exist or update it if it does(on the tables pk).
This is what i was trying until i realized replace drops the whole table where i thought it dropped the row and inserted the new.
copy
from db1
to db2
replace table2
using
select *
from table1
where x = blah
how can i insert and update to a table in oracle database 10g through a select statement. not using merge.
View 2 Replies View Relatedcheck my query and correct it basically I want to insert/update data from one user to other therefore I write this coding at my form button, when user press button first time its insert data successfully but if user press button again then it should update because data have been inserted in first step.
Actually it is detail table so it can have more then one record against any master. My query fails in updation, it inserts a new record instead of update.
find the attached file for checking QUERY.
i have a given pl/sql program that first deletes records out of a table and afterwards inserts new rows. now for example 2 rows out of 10 have a foreign constraint and can not be deleted that easily anymore. so i delete the ones i am able to (with the where not exists clause).
now i want to update the records who have a foreign key constraint and the rest with a regular insert. how would i do this the easiest way. i thought i could use insert with a where clause!!
here i have some part from the original
declare
procedure add(
i_id pls_integer,
[Code].....
Select * from Table A where emp=1;
Emp AID
1 111
select * from Table B where emp=1 ;
----------
AID, emp, start_dt, End_dt
111 1 01-jan-2011 31-dec-2011
112 1 01-jan-2011 31-dec-2011
113 1 01-jan-2011 31-dec-2011
I have After insert or update trigger on Table B. This will update AID column on Table B.
after insert update on table B
update A
set AID=:new.AID where emp=:new.emp;
if i end date any record on table B i wanted to update max(AID) on Table A.
EX update table b
set end_dt= sysdate-1
where aid=111;
So I wrote the trigger on Table A as below
before insert or update on trigger A
declare
v_aid number;
v_strt_dt date;
v_end_dt date;
begin
select max(AID) from table B
where emp=:new.emp;
select v_end_dt from table B where aid=:new.Aid;
if v_end_dt<sysdate then
:new.aid:=v_AID;
end if;
But the problem here is its giving mutating error.Then i tried with autonomus transaction but it willreturn old value when it fiers.
So how can i achive both the task at a time.That means i have to endate Table A , Same time i have to update active max(AID) to table B.
I am calling an after insert Trigger on table1.
In the trigger I am calling a procedure that returns an error if there is any error returned from procedure. I have to update the table table1's column error_desc (for the same new inserted record for which the trigger was called) with the error received by OUT parameter of procedure called in trigger. I have to update the same record on whose insert this trigger was called.
how to insert or update a number which is having value like 08,09 in forms 6i while insert its inserting only 8 and 9 not 08 or 09 the first digit zero is not inserting,,,i want to use time file which is 24 hrs format, so i am giving value like 0835 (08:35) but 835 only saving.
View 3 Replies View RelatedI've a one history table in which I'm putting approval history data.
For any transaction id you may have more than one record with approval status APPROVED,REJECTED,ERRORED,OVERLIMIT etc
Another program selects records from this table by passing transaction_id. For transaction id, it needs to check the most recent approved record exist or not ? If it's there then updating the record by adding comments to comments field of the same record and then delete all other records for the same transaction id.
If it does not exist then delete all other records and create one with approved status?
What's the simple and best approach to do this (sql or pl/sql)?
I 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?
In 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?
I 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;
Table 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 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?
updating the column values.
Test data:
CREATE TABLE test_at(nr_a NUMBER, nr_id NUMBER) TABLESPACE ECLP_DATA01
INSERT INTO test_att VALUES(90270091,NULL);
INSERT INTO test_att VALUES(90270091,NULL);
[Code].....
I need to update "nr_id" column of test_at table with the values from test_d table. They can be udpated in any order.
I tried to update using rownum but it failed with "single row subquery returns more than 1 row...."
I have a table called 'price' and want to update it with a table called 'update'. See workbook in the attachment.
The idea is to update the table 'price' with values from 'update' as long as the values from 'update' are different then the values in 'price' or the value in price with 'enddat' empty is not present in 'update'. you can still follow this...
Example:
Enddat for df_location 4 from 'price' is empty and df_location 4 is not present in 'update' so 'enddat' for this df_location should be filled with any date from 'update' (there is always going to be just one distinct date).
I have tried something like this:
proc sql;
insert into work.price (enddat) where enddat = ''
select (date) where df_location not in (select
df_location from work.update);
;
quit;
Well this obviously doesn't work. I'm trying to pass 2 criteria (when enddat is empty and df_location is not present in 'update') but I'm not sure where to put them...