SQL & PL/SQL :: Create Stored Procedure To Update Columns In Table
Dec 8, 2010
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.
View 13 Replies
ADVERTISEMENT
Aug 27, 2007
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
View 3 Replies
View Related
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
Feb 27, 2013
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 Related
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
Mar 8, 2007
I am trying to create a stored procedure in TOAD. I've found example code on the Internet to write a stored procedure, but it doesn't seem to work with Toad. I tried to write the procedure and execute it to save it to the DB, and instead I keep getting error messages like "an INTO clause is expected in this SELECT statement" when I used the
CREATE OR REPLACE PROCEDURE testing
AS
BEGIN
SELECT *
FROM CPK;
END;
or "bad bind variable 'CPKS'" when I tried the following instead:
CREATE OR REPLACE PROCEDURE testing
AS
BEGIN
SELECT *
FROM CPK;
Into :Cpks;
END;
post some example code here with short explanations for me on how to write a stored procedure.
View 4 Replies
View Related
May 14, 2013
How to create a list in oracle stored procedure and that list contains two columns.
View 1 Replies
View Related
Oct 11, 2010
Iam using oracle10g . when i created a simple stored procedure,got an error
PLS-00428: an INTO clause is expected in this SELECT statement
here is my code
create or replace procedure sp_TrialLiswt
as begin
select * from mivrs_studyinfo;
end;
View 1 Replies
View Related
Feb 22, 2012
I want to create a stored procedure where tables of two different databases are to be used.
View 4 Replies
View Related
Apr 30, 2010
How to create Data Block Using Stored Procedure in Forms10G ?
View 1 Replies
View Related
Jul 14, 2011
how to create stored procedure with an array as an output parameter.I mean when we need to return multiple rows...
View 7 Replies
View Related
Nov 9, 2009
I would like to create a trigger that will execute a stored procedure when a package/function/procedure is compiled. I tried creating an update trigger on user_objects, but it statues aI cannot create that trigger tyoe on views.
View 1 Replies
View Related
Nov 21, 2012
I have to develop an application for a store, where some material In and Out on daily basis. and i want to see current status any time.
How to do that means coding of stored procedure, on whose basis i can create material IN and OUT form.
View 0 Replies
View Related
Mar 10, 2011
create a procedure that will update the column status from active to closed after every two years.
View 4 Replies
View Related
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
Jul 15, 2013
I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table. I created the script but when they try to run in on the server it wont run.
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
[code]........
View 1 Replies
View Related
Nov 18, 2010
How to take a stored procedure last run without using DBA_AUDIT_TRAIL sys table.
View 6 Replies
View Related
Jul 15, 2013
I have been developing in MS SQL for about 15. So I'm still getting use to the syntax and features within Oracle.I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.I created the script but when they try to run in on the server it wont run.
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
AAAA.1D1D
[code].....
View 7 Replies
View Related
Jul 15, 2013
I'm still getting use to the syntax and features within Oracle. I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.I created the script but when they try to run in on the server it wont run.
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP IS BEGIN CREATE GLOBAL TEMPORARY TABLE myAAAA AS (SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777, DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C, AAAA.1D1D FROM mySchema.FFFF_07 FFFF RIGHT OUTER JOIN mySchema.EEEE EEEE ON FFFF.9999 = EEEE.1B1B RIGHT OUTER JOIN
[code].........
View 10 Replies
View Related
Oct 16, 2012
i am running one store procedure and checking the value if it is there then it's storing in one variable. but by checking through breakpoint i got that if value is not there which condition it's checking it's stuck there.
is it not possible if value is not there it should ignore and don't store value in variable.
my query is like this:
BEGIN
SELECT 1 Into v_temp FROM MASTERPROCESSDAILYDATA WHERE Emp_ID = v_ReadEmpID AND PDate = v_ReadPDate AND STATUS='A';
IF v_temp = 1 THEN
BEGIN
DELETE MASTERPROCESSDAILYDATA WHERE Emp_ID = v_ReadEmpID AND PDate = v_ReadPDate;
END;
END IF;
End;
View 14 Replies
View Related
May 31, 2011
I have one stored proc with three in parameters as
number, varchar and table of number
what statement I need to write in pl/sql to execute it ...
execute getdetails(1,'xyz', ????????????)
View 5 Replies
View Related
Jan 3, 2012
I dropped HR schema from database by mistake.
How to create HR Schema and its all tables using oracle stored scripts.
My operating system is Windows XP and oracle version is 10g.
View 9 Replies
View Related
Jun 14, 2012
I've a table with fields:
create table test
( f1 varchar2(10),
f2 varchar2(10),
f3 varchar3(10)
)
insert into test values ('d1','d2','d3');
insert into test values ('d10','d20','d30');
I want to update the fields of the table as per need i.e update only one field leaving all the data of the fields as it is. Suppose I want to update only f1 (from d1 to x1) field leaving f2, and f3 as it is. I've written stored procedure to update all the fields but do not know how to do it?
Quote:CREATE OR REPLACE PROCEDURE UPDATE_TEST
( U_F1 TEST.F1%TYPE,
U_F2 TEST.F2%TYPE,
[Code]....
View 7 Replies
View Related
Jun 15, 2013
I have 2 tables order_items and items.
Order_items Items
Item_id Item_id
Quantity Price
In normal sql statement: select sum(order_items.quantity*items.price) sales_price
from order_items,items
where order.item_id=items.item_id;
I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.
View 2 Replies
View Related
Feb 7, 2013
we couldnt access the dba_segments table from the named stored proc however its working fine if accessing from unnamed SP.
SQL> declare
2 cnt number;
3 begin
4 select count(*) into cnt from dba_segments;
5 dbms_output.put_line('cnt='||cnt);
6 end;
7 /
cnt=6746
PL/SQL procedure successfully completed.SQL> create or replace procedure test
2 is
3 cnt number;
4 begin
5 select count(*) into cnt from dba_segments;
6 dbms_output.put_line('cnt='||cnt);
7 end;
8 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
5/31 PL/SQL: ORA-00942: table or view does not exist
SQL>oracle version
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
View 4 Replies
View Related
Feb 6, 2011
I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:
Query 1
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
Query 2
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)
When i execute these two above queries, it keeps executing indefinitely.
View 4 Replies
View Related
Jul 21, 2011
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
/
View 9 Replies
View Related
Feb 6, 2012
This procedure is not working properly.
create or replace procedure bank_search_sp
(
p_tablename in varchar2,
p_searchname in varchar2,
p_bankcode out varchar2,
p_bankname out varchar2,
p_dist_code out number
)
as
v_tem varchar2(5000);
begin
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'';
execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
commit;
end bank_search_sp;
the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated
ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
ORA-06512: at line 1
View 1 Replies
View Related
Mar 15, 2010
I have two table and trying to update tableA with data from tableB but gives an error. My TableA has columns - colA,colB,colC,colX and table B has columns - colA,colB,colX. Sample data looks like this:
tableA:
XXYY, local,3/19,48
XXYY,ixc,3/19,24
XXYYlocal,3/20,48
XXYY,local,3/21,48
Table B:
XXYY,local,48
XXYY,ixc,24
PPQQ,local,72
this is the query I wrote
update tableA a
set (a.colX)=(select (b.colX) from tableB b where a.colA=b.colA and a.colB=b.colB);
When i run it gives this error:single row subquery returns more than one row.
tableB has only 1 value for colX for each colA,colB record but tableA has mutiple colA and colB repeated but for all of thsoe in table A i want to update the tableB.colX value for matching colA and colB.
View 4 Replies
View Related
Dec 13, 2011
how to update the middle of plenty rows in the middle of the columns
sample_data
id name state REGION LOC
1 v A.p 1 1
2 a
3 g K.A 0 3
4 y
5 i T.N 1 0
6 l M.P 0 1
7 c U.P
This is sample data,and i have this kind of large data and i need to fill the rows which are empty. In three columns state,region,loc with data like 0,web_intimation,1,
View 8 Replies
View Related
