Home » SQL & PL/SQL » SQL & PL/SQL » procedure for insert and update
procedure for insert and update [message #263178] Wed, 29 August 2007 05:01 Go to next message
sankara.gattu
Messages: 10
Registered: August 2007
Location: bangalore
Junior Member

SQL> CREATE OR REPLACE PROCEDURE SpowPpidMapping
2 (v_PpId IN VARCHAR2,
3 v_MappingId IN VARCHAR2,
4 v_IsEpp IN CHAR
5 )
6 IS
7 v_Cnt number;
8 BEGIN
9 SELECT count(*)
10 INTO v_Cnt
11 FROM SPOW_RETAILER
12 WHERE PP_ID = v_Ppid
13 AND MAPPING_ID = v_MappingId;
14
15 IF v_Cnt = 0
16 THEN
17
18 INSERT INTO SPOW_RETAILER(PP_ID,MAPPING_ID,IS_EPP,HP_ACCOUNT_NR)
19 VALUES(v_Ppid,v_MappingId,v_IsEpp,v_MappingId);
20
21 DBMS_OUTPUT.PUT_LINE(' The account has been configured');
22 END IF;
23 COMMIT;
24 END SpowPpidMapping;
25 /

Procedure created.

first time:
---------------------------------
SQL> exec SpowPpidMapping('1-1TUHOF','testing','N');
The account has been configured

PL/SQL procedure successfully completed.
second time
--------------------------------
SQL> exec SpowPpidMapping('1-1TUHOF','testing','N');

PL/SQL procedure successfully completed.
third time
-------------------------------------
SQL> exec SpowPpidMapping('1-1TUHOF','TEST','N');
BEGIN SpowPpidMapping('1-1TUHOF','TEST','N'); END;

*
ERROR at line 1:
ORA-00001: unique constraint (BEP3.PK_SPOW_RETAILER_PP_ID) violated
ORA-06512: at "BEP3.SPOWPPIDMAPPING", line 18
ORA-06512: at line 1
Re: procedure for insert and update [message #263187 is a reply to message #263178] Wed, 29 August 2007 05:46 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Do you have a question?
Re: procedure for insert and update [message #263190 is a reply to message #263178] Wed, 29 August 2007 05:53 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

If you had question then I think

Quote:

unique constraint (BEP3.PK_SPOW_RETAILER_PP_ID) violated

has showed you what to do?
Re: procedure for insert and update [message #263204 is a reply to message #263178] Wed, 29 August 2007 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition:
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: procedure for insert and update [message #263244 is a reply to message #263178] Wed, 29 August 2007 08:43 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Assign v_Cnt:= 0
after begin and before query statement.
Re: procedure for insert and update [message #263254 is a reply to message #263244] Wed, 29 August 2007 09:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
why?
Re: procedure for insert and update [message #263630 is a reply to message #263178] Thu, 30 August 2007 09:41 Go to previous messageGo to next message
lenin_babu55
Messages: 12
Registered: August 2007
Junior Member
hi

CREATE OR REPLACE PROCEDURE SpowPpidMapping
(v_PpId IN VARCHAR2,
v_MappingId IN VARCHAR2,
v_IsEpp IN CHAR
)
IS
v_Cnt number;
BEGIN
SELECT count(*)
INTO v_Cnt
FROM SPOW_RETAILER2
WHERE PP_ID = v_Ppid
AND MAPPING_ID = v_MappingId;

IF v_Cnt = 0
THEN

INSERT INTO SPOW_RETAILER2(PP_ID,MAPPING_ID,IS_EPP,HP_ACCOUNT_NR)
VALUES(v_Ppid,v_MappingId,v_IsEpp,v_MappingId);

DBMS_OUTPUT.PUT_LINE(' The account has been configured');
END IF;
COMMIT;
END SpowPpidMapping;

execute SpowPpidMapping('1-1TUHOF','testing','N');



create table spow_retailer2(PP_ID varchar2(10),MAPPING_ID varchar2(10),IS_EPP varchar2(10),HP_ACCOUNT_NR varchar2(10)));

check it out once and check the constraints of the table and then give the values to the parameters
Re: procedure for insert and update [message #263644 is a reply to message #263630] Thu, 30 August 2007 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 29 August 2007 13:35
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel


Re: procedure for insert and update [message #263668 is a reply to message #263630] Thu, 30 August 2007 12:51 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
lenin_babu55 wrote on Thu, 30 August 2007 10:41

check it out once and check the constraints of the table and then give the values to the parameters


Huh? Are you answering the question or asking a question yourself? Are you the same person as the original poster?
Previous Topic: inserting data between records
Next Topic: Creating a tablespace
Goto Forum:
  


Current Time: Mon Dec 05 14:48:58 CST 2016

Total time taken to generate the page: 0.12663 seconds