Home » SQL & PL/SQL » SQL & PL/SQL » Stored proc using merge (Oracle
Stored proc using merge [message #419606] Mon, 24 August 2009 08:12 Go to next message
Messages: 39
Registered: August 2009
Location: INDIA
Dear Oracle geeks,
Can we do the follwing problem using Merge? If yes, how?
Check for P_ID exists . if it does not exist then it should be an insert then update.

table name: CUST01
	COLUMN   												Update rules
	P_ID                			NOT NULL  NUMBER(10,0)   	N	   
	C_ID                      		NOT NULL  NUMBER(3,0)     	N	   
	FILE_ID							NOT NULL  NUMBER(5,0)		V	
	BILL_NO                         NOT NULL  NUMBER(5,0)		V	   
	CUST_NO                     	NOT NULL  NUMBER(11,0)		V	      
	UPD_DT                      NOT NULL  DATE (8)				Y   
	UPD_USER                 	NOT NULL  VARCHAR2(8)			Y   
	UPD_G_ID                   	NOT NULL  VARCHAR2(8)			Y   
V- Verify if it is different from the existing one only then update. 
If null is passed, ignore the null if data exists already	 

Input parameters : All the columns in the table 
Re: Stored proc using merge [message #419615 is a reply to message #419606] Mon, 24 August 2009 08:24 Go to previous messageGo to next message
Messages: 402
Registered: July 2005
Senior Member

Are you looking for how to write Merge command?

Re: Stored proc using merge [message #419625 is a reply to message #419606] Mon, 24 August 2009 09:07 Go to previous message
Michel Cadot
Messages: 65084
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Previous Topic: Performabce tuning in SQL
Next Topic: ORA-01460: unimplemented or unreasonable conversion requested
Goto Forum:

Current Time: Fri Jul 21 21:59:00 CDT 2017

Total time taken to generate the page: 0.10114 seconds