Store Procedure Insert / Update?

Dec 7, 2011

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


ADVERTISEMENT

Update Store Procedure (List Of Input)

Jan 10, 2012

The current update store procedure that I have updates a list of input provided, but it there are fields that are left blank, they are being updated as null in the database.

I'm having a trouble creating a store procedure that will just update the provided fields only.

View 1 Replies View Related

PL/SQL :: Update Multiple Records Using Store Procedure?

Feb 27, 2013

i am trying to update multiple records using store procedure but failed to achieve

for example my source is

emp_name sal
abhi 2000
arti 1500
priya 1700

i want to increase salary of emp whose salary is less than 2000 it means rest two salary should get update..using stored procedure only

i have tried following code

create or replace procedure upt_sal(p_sal out emp.sal%type, p_cursor out sys_refcursor)
is
begin
open p_cursor for
select sal into p_sal from emp;
if sal<2000 then
update emp set sal= sal+200;
end i;f
end;

and i have called the procedure using following codes

set serveroutput on
declare
p_sal emp.sal%type;
v_cursor sys_refcursor;
begin
upt_sal(p_sal,v_cursor);
fetch v_cursor into p_sal;
dbms_output.put_line(p_sal);
end;

the program is executing but i should get o/p like this after updating

1700
1900

but i am getting first row only

2000

and record is not updating...

View 15 Replies View Related

SQL & PL/SQL :: Stored Procedure For Insert And Update?

May 27, 2011

i 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

View 5 Replies View Related

SQL & PL/SQL :: How To Grab The Update Values And Insert Into Procedure

Jun 8, 2011

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].....

View 20 Replies View Related

SQL & PL/SQL :: Procedure To Update Or Insert Data In Multiple Tables

Apr 9, 2011

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?

View 15 Replies View Related

SQL & PL/SQL :: Hiding Store Procedure Possible?

Jan 11, 2012

I have a question like, Is it possible to hide the Store Procedure?

Scenario: I have write the SP which contains some logic based on my requirement. Once i developed this i need to implement this to my client page. So i need to hide the logic even the client opened the SP like exe file.

View 2 Replies View Related

SQL & PL/SQL :: Cursors To Update Data And Store It Back To Same Table

Sep 4, 2010

I'm using few cursors to update my data and store it back to the same table. But for some reason the cursor seems to be picking obsolete data.

cursor c1
is
select distinct roles
from table1
where flag = '1';

cursor c2
is
select distinct roles
from table1
where flag = '1';
begin
for i in c1
loop

here im updating the roles im picking to to a suffix and role.

update table1
set role = suffix_role
where 'some condition';
end loop;
commit; -- committing so changes are visible for my next cursor.

for j in c2
loop

here im deleting all the roles that are not part of my comparing table.

delete from table1
where role = 'something';

But in my debugging table i see that its deleting roles present as input for first cursor, whereas it should actually pick data with suffix_roles.

View 12 Replies View Related

SQL & PL/SQL :: Query To Store Results - Update Values In Another Table?

Feb 22, 2010

I have a query like this -

SELECT
FIELD_A,
FN_FUNCTION(CARVE_ID, 1) FIELD_B,
FN_FUNCTION(CARVE_ID, 2) FIELD_C,
FN_FUNCTION(CARVE_ID, 3) FIELD_D,
FN_FUNCTION(CARVE_ID, 4) FIELD_E,
FN_FUNCTION(CARVE_ID, 5) FIELD_F,
FN_FUNCTION(CARVE_ID, 6) FIELD_G
FROM TB_CARVE;

When I execute the query, it returns the data (approx - 40,000 rows) in 1 min.But when I try to insert this data into another table (or create a table of this data) it takes me about 2 hours.

Tried using Materialized view, its again the same the refresh takes 2 hours.Basically here, what I am trying to do is the data from the above query is used to update the values in another table.What ever the procedure I am trying it takes 2 hours.

View 6 Replies View Related

Forms :: Insert And Update Directly Into Table From Pre Update Trigger Of Block?

May 14, 2010

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?

View 4 Replies View Related

SQL & PL/SQL :: Store Procedure To Truncate And Copy Data

Jan 27, 2012

I am very new to oracle and SQL.I am trying to create a store proc that will copy 14 day of data into a table and then truncate the original table. When i compile following code....

CREATE OR REPLACE PROCEDURE STOPROC_TRUNCATE
( dateNum IN NUMBER )
IS
BEGIN
create table AUDIT_14Days as select * from AUDIT where TIMESTAMP >= (SYSDATE - dateNum);
truncate table AUDIT drop storage;
[code]....

View 8 Replies View Related

PL/SQL :: How To Reset Primary Key to 1 Using Store Procedure

Mar 19, 2013

I have created table and i am using sequence to increment primary key value.

Now i need to delete the table content and reset primary key to 1 using store procedure.

View 18 Replies View Related

Store Procedure In View With Select Statement

Aug 31, 2012

Need a trigger in view with select statement that means

CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TEST_TABLE;
CREATE OR REPLACE TRIGGER TEST_VIEW_TRG1
INSTEAD OF DELETE ON TEST_VIEW DECLARE
BEGIN
Dbms_Output.Put_Line('STATEMENT TRIGGER.');
END;

i wanted to use select statement instead of delete.How can i get that

View 1 Replies View Related

SQL & PL/SQL :: Create Trigger That Will Update Table When There Is Insert / Update

May 29, 2012

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;
/

View 3 Replies View Related

Oracle Store Procedure - Table Or View Does Not Exist

Nov 21, 2011

I have created this store procedure:

create or replace PROCEDURE INSERT_TESTTABLE
(
PrimaryKey IN NUMBER
,One IN VARCHAR2
,Two IN VARCHAR2
,Three IN VARCHAR2
,Four IN VARCHAR2
[code].......

And I get this error: Error(15,13): PL/SQL: ORA-00942: table or view does not exist

View 1 Replies View Related

SQL & PL/SQL :: How To Pass In Dynamic Variable For Calling Store Procedure

Apr 9, 2012

I have a table that has 10 columns which is used to store the customer information (e.g Gender, Age, Name). And i have wrote a store procedure to compare the before and after value of column since there has a parameter to control which column need/no need to be updated while the value being changed.

For example, master table "CUST" has column (NAME, GENDER, AGE). "CUST_TEMP" is a temporary table to store the input image which has the same table structure as "CUST".

DECLARE
bef_val CUST%ROWTYPE;
aft_val CUST_TEMP%ROWTYPE;
BEGIN
SELECT * INTO bef_val FROM CUST WHERE name = 'ABC';
SELECT * INTO aft_val FROM CUST_TEMP WHERE name = 'ABC';
[code]....

For the above case, i need to type 3 times of "sp_compare_val ( bef_val.xxx, aft_val.xxx )" on the program. And if the table has more than 10 columns, i need to type more than 10 times.Thus, is it possible to pass in a dynamic variable while calling the store procedure. let say, where the 'xxx' can be definable?

View 8 Replies View Related

SQL & PL/SQL :: Creating Store Procedure That Will Accept A Username From A Flat File?

Apr 8, 2012

I'm trying to create a store procedure that will accept a username from a flat file but i don't know how to do read file into store procedure.

Below is a sample store procedure by itself i created to add user which created okay but when i execute I got the error displayed below.

create or replace procedure addUsers(userNam in varchar2)
is
begin
EXECUTE IMMEDIATE 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERS'||'QUOTA "1M" ON USERS'||
'PASSWORD EXPIRE';
end addUsers;
/

[code].....

View 21 Replies View Related

SQL & PL/SQL :: Store All Rows Of Columns Into Single Variable / Use In Inside Of Stored Procedure

Mar 6, 2012

i want to store all rows of columns into single variable and then use in inside of SP

declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....

it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g

View 4 Replies View Related

Insert Or Update On A Table

Jul 30, 2009

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]....

View 6 Replies View Related

SQL & PL/SQL :: Trigger To Update And Insert Together?

May 7, 2010

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.

View 4 Replies View Related

Insert If Not Exists Else Update

Aug 10, 2007

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).

View 4 Replies View Related

Sqlplus Copy Insert / Update

May 3, 2013

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

View 3 Replies View Related

SQL & PL/SQL :: Insert And Update Through A Select Statements

Jul 7, 2010

how can i insert and update to a table in oracle database 10g through a select statement. not using merge.

View 2 Replies View Related

Forms :: Insert / Update Data

Oct 12, 2010

check 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.

View 5 Replies View Related

SQL & PL/SQL :: Combine Update And Insert Command?

Jul 23, 2012

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].....

View 3 Replies View Related

SQL & PL/SQL :: Insert Or Update Trigger On Table B

Jun 2, 2011

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.

View 13 Replies View Related

SQL & PL/SQL :: Update Table In After Insert Trigger

Feb 1, 2011

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.

View 3 Replies View Related

Forms :: How To Insert Or Update A Number

Jun 11, 2013

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 Related

PL/SQL :: Update / Insert History Table?

Jun 15, 2012

I'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)?

View 2 Replies View Related

SQL & PL/SQL :: Procedure Insert Into?

Mar 12, 2010

, I'm trying to make a stored procedure in Oracle insertcion of records, but before you insert has to get the most code and generate a new one generated more than everyone else, I'm using Max, but as I assign to a variable as in SQL is:

Declare @ IDMax numeric
Select @ IDMax = Max (Code) From Members

Then I would make a:Insert into Users (Code, Name) values (@ IDMax, 'Victor');As serious for Oracle to perform com from declaring a parameter as the Code for me to store the value (Code Maximo)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved