SQL & PL/SQL :: Procedure For Update / Insertion?

Jan 5, 2012

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

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: User Defined Exceptions For Insertion Update Queries

Sep 7, 2012

how to define user defined exceptions for cases like, ==> when anyone tries to insert string values without using single quotation marks " '...' "? ==> update the column which is not present in table.

how can I define user defined exceptions for such cases?

View 3 Replies View Related

SQL & PL/SQL :: Can A Procedure Be Used In Update Statement

Aug 2, 2011

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.

View 4 Replies View Related

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 View Related

Create A Procedure To Update Status?

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

SQL & PL/SQL :: Procedure Has Got Several Update And Delete Statements

Oct 23, 2013

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

View 27 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

Create A Procedure To Update The Table?

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

PL/SQL :: Procedure To Update Duplicate Rows?

Mar 14, 2013

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

View 3 Replies View Related

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

SQL & PL/SQL :: Procedure To Update Double Subcategory Sequence

Dec 6, 2010

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 ?

View 17 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 :: Update Stock Procedure And Debit Credit?

Dec 21, 2010

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.

View 7 Replies View Related

PL/SQL :: How To Update Multiple Records Using Stored Procedure

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

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 :: Table Update - Unable To Identify Procedure

Feb 21, 2011

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 Related

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

Slow Insertion In Particular TABLE

Apr 17, 2012

The issue is slow insertion in particular table(i.e A Table) it means insertion in all other tables(i.e B, C, D tables) in same schema is going properly but only when i am trying to insert in one particular table(i.e A table) in same schema it takes long time to complete insertion. Daily insertion is 6000 rows.

I have check all the details like Tablespace size, Analyzing of table, Analyzing of indexes and all. There is no any error alertlog file.

View 1 Replies View Related

Forms :: Insertion In Database

Jan 22, 2013

i have a simple insert statement in oracle form, which is sucessfully run in oracle database(sql). but it is in oracle form trigger: WHEN BUTTON PRESSED as in this format:

Declare
cnt number;
begin
select count(*) into :control.cnt from ol_lcy_ndc where aan=:control.aan and event_id= 'ACL';
if cnt = 0 then
insert into ol_lcy_ndc (form_no, aan, regno, event_id, doev, status, edt, ludt, username)
values (12345, 255257,10030661,'ACL', SYSDATE, 'DRAFT', SYSDATE, SYSDATE, ' ');
else
update ol_lcy_ndc set LUDT= to_date('09-09-2009','DD-MM-YYYY') where aan=:control.aan and event_id= 'ACL';
end if;
end;

but after giving count in cnt, it is not doing anything like insert or update from oracle form, but both the statements are correctly execute in oracle database. may problem is linked with some properties of property palette, upto my knowledge i checked: insertion allowed--> yes.

View 9 Replies View Related

SQL & PL/SQL :: Insertion In Clob Column?

Jul 3, 2012

i have another problem with clob column , when i try to insert data in it through the stored procedure then it shows an

error- 'ORA-01460: unimplemented or unreasonable conversion requested clob' and this error especially arise when data to e inserted in clob column , have more than 4000 characters.

sql>exec pi_test(1,'sysdate','text of clob column'); ORA-01460: unimplemented or unreasonable conversion requested clob

where is the error.

View 8 Replies View Related

Insertion Of Any Character After Writing 2 Words

May 3, 2006

How can i insert any character or symbol automatically after writing 2 words

E.g.

i want to enter date 20-may-06 , in this i mean it that when i enter 20 then automatically - is inserted.

is there any query in Oracle. Or Oracle Hasn't created this type of query.

View 3 Replies View Related

Forms :: Checkbox Value Insertion In Table

Nov 4, 2010

I am using oracle form builder 6i and oracle database 10g

1:I have table named 'info' column name 'InfoId'and some other.And another table named 'Handing' with column names HtId, Value1 and value2.

2:I made form that consist of three data blocks, first block takes criteria and second block display record against that criteria from table 'info'.

3:i want checkboxes agianst that display record,and want that when I select some checkboxes against 'InfoId' these selected 'InfoIds'

should save in another table named 'handing' in column 'HtId'.and in same table data in column value1 and value2 will be inserted through textboxes that are in the third datablock of the thae same form .

View 2 Replies View Related

SQL & PL/SQL :: Insertion / Data Loading To A Table

Apr 10, 2012

We are getting the below error frequently from the application while doing insertion/dataloading to a table. The mentioned error is in the Primary key index

Error: 'ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state'.

I set the value SKIP_UNUSABLE_INDEXES = TRUE using the command 'ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = TRUE' to avoid this. Again we are getting the same error and Every time Iam rebuilding('alter index INDEX_NAME rebuild') the index and doing the DML Operation.

View 22 Replies View Related

Forms :: CLOB Insertion And Updation?

Feb 13, 2012

I simply want to insert data in CLOB column but the size of the data is almost 1 MB. (Copying from a MS word document)

View 2 Replies View Related

SQL & PL/SQL :: Insertion Of Data From One To Other Schema Table In Same Database

Aug 9, 2011

I need to insert data from one schema table to other schema's table in same database.The thing is columns are not equal.so when I am trying to use insert statement it is throwing error as not enough values. The situation is explained clearly below.The insert stmt is implemented in second schema whose table name is b.

SQL> create table a(ID_A NUMBER,Name varchar2(10),rollno NUMBER,address varchar2(10));
Table created.
SQL> insert into a(ID_A,Name,rollno,address)values(1,'ravi', 101,'hyd');

1 row created.

SQL> insert into a(ID_A,Name,rollno,address)values(2,'rav', 102,'delhi');

1 row created.
SQL> insert into a(ID_A,Name,rollno,address)values(3,'ra', 103,'chn');

1 row created.

SQL> create table b(ID NUMBER,Name varchar2(10),rollno NUMBER,address varchar2(1
0),route varchar2(10),direction varchar2(10));

Table created.
SQL> ALTER TABLE B ADD
2 CONSTRAINT B_PK1
3 PRIMARY KEY
4 (ID);

Table altered.
SQL> create sequence b_seg start with 1;

Sequence created.
SQL> insert into b select b_seg.nextval,lexcom.a.* from lexcom.a,dual;
insert into b select b_seg.nextval,lexcom.a.* from lexcom.a,dual
*
ERROR at line 1:
ORA-00947: not enough values

So,for table b in ID column sequence needed to be inserted and other columns need to be taken from table a.I can understand the error is because two tables are not having equal columns.So,the insert stmt is throwing error.

I can manually write by taking columns from a and b and write insert stmt as follows,but this is tedious process.

SQL> insert into b(ID,Name,rollno,address)select b_seg.nextval,lexcom.a.Name,lex
com.a.rollno,lexcom.a.address from lexcom.a,dual;

3 rows created.

But this is time taking and I had tables which has many columns to be inserted.So is there any other way to solve it and implement insert stmt.

View 6 Replies View Related

Forms :: Restrict Field After Insertion Before Commit?

Nov 9, 2010

Restrict field after insertion before commit. i mean when user inputs the data in one field and moves to next field.when ever he want to return back on the previous field it can't be edited.before commit;

View 2 Replies View Related

BLOB Based Schema - Performance Of Loading / Insertion

May 17, 2012

Would using a blob based schema load noticeably faster than a binary_double based schema?

CODEBlob Scenario:

Load 1 row: 5 columns (1 integer column, 4 blob columns) of size X
VERSUS

CODEDouble Scenario:

Load 10,000 rows: 5 columns (1 integer column, 4 binary_double columns) of size X

While the benefit of using the rows approach is obviously the capability to query the values, I'd like quick answer concerning the loading/insertion performance. Associative array binding is used for loading from a .NET client. Also, would the answer also hold true for 200 columns instead of just 5 columns.

View 1 Replies View Related

SQL & PL/SQL :: Insertion Of Text Content Length Contains More Than 4000 Into CLOB?

May 31, 2011

i want to insert the text lenght containing more than 4000 characters, that column datatype is in CLOB Even though in CLOB we can able to store upto 4GB. Its not allowing me to insert more than 4000 characters at a time , but we can able to insert by splitting the data by 4000 and can append remaining characters But i am receving the text contains more than 4000, that how can i split the data upto 4000

View 6 Replies View Related

Globalization :: Junk Character Insertion In Oracle Table

Feb 6, 2013

How to avoid Junk character insertion in oracle table. I have prepared scripts like this Say

customer - info

After insertion the data is inserted like below in production

Customer ¿ info

We are using command prompt for script execution in production environment. I am using PLSQL developer and SQL developer for development. i cannot see junk data in PLSQL developer and latest SQL developer , but its caught in old version of SQL developer. Also in Application also i can able to figure out junk data.

View 6 Replies View Related







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