I got a table table1 with 3 columns: id, name, value
im trying to create a procedure to update the table.
create or replace
PROCEDURE TEST1 (
x IN varchar,
y IN varchar,
z IN varchar
) AS
BEGIN
update table1 set value=x where name=y and id=z;
commit;
END TEST1;
that doesnt seem to work
I have three tables fixtures, fixture_teams and team_tbl
fixtures consists of:
create table Fixture_tbl(
fixt_id varchar2(8),
fixt_date date,
fixt_time varchar2(10),
fixt_location location_t,
umpire_id varchar2(8),
player_of_match player_of_match,
home_team varchar2(20),
away_team varchar2(20),
[code]....
creating a stored procedure that updates the points column in the teams_tbl , the value that is updated in to the points column will be retrieved from the fixture_team table. so if team a has more goals than team b then the points column for team a will be increased by 6 else if the scores are equal they get 4 points each.
create a procedure that will update the column status from active to closed after every two years.
View 4 Replies View Relatedi 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;
/
I have table test1(id,name) and table test2(id,,name)
Now when I update name column of a row on test1 I want the same value to be updated for the same id in test2.
So I wrote this trigger but its not working
create trigger test_trigger after update on test1 for each row
begin
update test2 set name=new.name where test2.id=id
end
/
We have one table which is updated by some program but we are not able to identify the procedure, package or script which update it.
View 2 Replies View Relatedi have created a trigger on a table after update. i am using if condition if the condition is true i am passing a value and then inserting into audit table.
if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;
Then i am inserting
Insert into audit_table(change_type.....)
values(..L_change_type)...)
If i want to skip the insert statement for particular condition wt i have to do.
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies View Relatedi have two table:
QUOTE create table test1(
num number(4),
name varchar2(50));
create table test2(
num1 number(4),
name1 varchar2(50));
then i want to create a trigger before update on table test1 that i want to insert in table test2 the old values in table test1:
for example
QUOTE insert into test1(1,'cocol');
QUOTE update test1
set name='oracle' where num=1;
so i want the trigger to insert 1 and cocol into the table test2.
how can i create this query on a procedure:
Insert into COMPUSOFT.PESAJE@DB_2
(PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA,
PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD,
USR_COD, PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO)
select /*+ FULL(Tbl1) */
[code]..
plus i would like to insert also that when it runs the query also change a value in table pesaje column dmn_cod to "yes" default "no" in db_2
I am trying to create a procedure that inserts parameters into a table and then returns the number of rows inserted back to calling block. the procedure is compiling fine but is not returning the number of rows inserted. My code is as follows;
HOST VARIABLE DECLARATION
VARIABLE g_CarMasterInsertCount NUMBER;
STORED PROCEDURE
CREATE OR REPLACE PROCEDURE CarMasterInsert_sp (
registration IN VARCHAR2,
model_name IN VARCHAR2,
car_group_name IN VARCHAR2,
date_bought IN DATE,
cost IN NUMBER,
miles_to_date IN NUMBER,
miles_last_service IN NUMBER,
status IN CHAR,
rowsInserted OUT NUMBER)
[code]....
I think im close just that my syntax is off.
How to create procedure for getting number of records of a table
View 24 Replies View RelatedI've to create a table every time a procedure is run, initially the table should be dropped and then created every time. From this procedure, the table is neither created nor dropped.
I cant track the error. Why is it so?
CREATE OR REPLACE PROCEDURE TESTPROC IS
S_SQL VARCHAR2(1000);
BEGIN
S_SQL := 'DROP TABLE MYTEST PURGE';
EXECUTE IMMEDIATE S_SQL;
[code]........
I need to create PROCEDURE to create user in oracle
CREATE OR REPLACE PROCEDURE "CREATE_USER_ORACLE8"
(
USER_ID in VARCHAR2,
PASSWORD in VARCHAR2,
ROLES in VARCHAR2,
nReturnCode OUT NUMBER
)
BEGIN
[code].......
Compilation errors for PROCEDURE NOG.CREATE_USER_ORACLE8
Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
; is with authid deterministic parallel_enable as
Line: 9
Text: BEGIN
i want that the customer execute PROCEDURE (user_id,password,PROCEDURE )
DB version:11g
can we create a procedure inside a procedure...if not is there any alternative??
in db2 it is allowed so do oracle support this????
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?
What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
PROCEDURE split_name (
full_name IN VARCHAR2,
name1 OUT VARCHAR2,
name2 OUT VARCHAR2,
name3 OUT VARCHAR2,
name4 OUT VARCHAR2,
name5 OUT VARCHAR2
)
the above is the procedure declaration. i want to use this function in updating the records in a table. this procedure is working fine in forms and it is splitting the names perfectly and i dont know how to use this in update statement as it is not returning anything like functions do.
In procedure "update_emp", i am updating a row based on p_empno and if it is not present i.e. SQL%ROWCOUNT = 0, then I am inserting that row into emp table.
where as in procedure "update_emp1" , first I am checking whether any row with that p_empno is present or not,if presentthen update the row, else raise an exception to insert the row.
In both procedure, I am doing the same thing, But I am unable to understand which one is good and why
create or replace procedure update_emp( p_empno int) is
begin
update emp set ename='raj' where empno=p_empno;
[code]....
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
View 4 Replies View RelatedStore 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 RelatedOne of the procedures that am working on is failing with ORA-0000: normal, successful completion error.
The procedure has got several update and delete statements and have logging enabled after each step. The problem with that again is, each time the log table gets updated thereby losing the history of until what point the procedure ran successfully.I have this issue only in production environment and unable to simulate it in dev environment which limits my options of troubleshooting the procedure code. I was using SQLERRM in the code.
Is there a way I can identify the bad records/ record causing this issue? Am very new to PL/SQL and do not know how to proceed with this.How do you debug this sort of issues??(where one procedure internally invokes another one which again invokes other one etc)
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
In a table i have some duplicate rows
I can get it through this query : select PARTY_ID from XXWFS_CUSTOMER_EXT group by PARTY_ID having count (PARTY_ID) > 1;
Now for the records which i got for each duplicate row i want to update the second row with a specific value.. so that duplicate rows does not exist anymore
Ex: I got party id's 12, 14, 16, 18 two times each
Now as 12 is two times.. i want to update the second row of 12 with some x value same is the case for other values like 14,16, etc
how can i write a procedure for this
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.
I need to write a procedure to be able to reuse it to clean up subcategory sequence. Here is a problem: two tables: Category and subcategory:
create table Category
(category_id number,
Name varchar2(20))
/
create table Sub_Category
(sub_category_id number,
category_id number,
sequence number)
/
In the ideal world each subcategory of a single category would have unique sequence so if there are 3 subcategory for the same category then each of them would have 1,2,3 in sequence, if there are 5 subcategories then 1,2,3,4,5 for each of them etc.
Problem I'm facing is that some of the subcategories sequences for the same category has the same values . For instance for 4 subcategories of the same category, each of them has 1 (1,1,1,1) in a sequence.
So ideal world is :
Insert into Category values (123 ,'Category1');
Insert into Category values (234 ,'Category2');
Insert into Category values (345 ,'Category3');
Insert into Category values (456 ,'Category4');
Insert into Category values (567 ,'Category5');
Insert into Sub_Category values (1,123,1);
Insert into Sub_Category values (2,123,2);
Insert into Sub_Category values (3,123,3);
But I've also bad rows like this:
Insert into Sub_Category values (4,234,1);
Insert into Sub_Category values (5,234,1);
Insert into Sub_Category values (6,234,1);
Insert into Sub_Category values (7,345,1);
Insert into Sub_Category values (8,345,1);
[code].....
Fix for this and my goal is to select all such cases where subcases have mixed up sequences as above and give them randomly numbers starting from 1. So if there are 3 subcategories like for CATEGORY 2 then just apply random number to the sequence of the subCATEGORIES like 1,2,3. For CATEGORY 3 : 1,2,3 to 7.
I was thinking to write two procedures one selecting all the categories and passing category ID to the other procedure that would actually update sequence, like this:
CREATE OR REPLACE PROCEDURE SCHEMA.SELECT_CATEGORY
IS
CURSOR c1
IS
select category_ID from category where ...;
BEGIN
FOR a IN c1 LOOP
UPDATE_SUBCATEGORY(a.Category_id);
COMMIT;
END LOOP;
END;
/
And the actual procedure updating subcategory:
CREATE OR REPLACE PROCEDURE SCHEMA.UPDATE_SUBCATEGORY
BEGIN
............
END;
/
write PROCEDURE SCHEMA.SELECT_CATEGORY cursor to not miss any of the categories ID having mixed up subcategory. There can be any of the doubled sequences like doubled 1 value (this is majority) but there can be any other doubles (or at least I need to make sure that there aren't any other doubles 2 values or 3 values in sequence etc.)
And how to write SCHEMA.UPDATE_SUBCATEGORY to loop through rows of subcategory and update sequence with values starting from 1 ?
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].....
i want to ask to all that, i have three table
(1)stock (2)issuance(3)recieve
i want to create as procedure when i issue in issuance table then update + stock in stock table. and when i recieve in recieve table then minus- stock in stock table.
hows can i create these procedure
i m using in oracle 9i and form 6i.
I want to update records which returns more than 1 row using store procedure. i tried with ref_cursor but failed to update,
View 1 Replies View Relatedi 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...