Home » SQL & PL/SQL » SQL & PL/SQL » fetch insert and delete record + stored procedure (Oracle 10g)
fetch insert and delete record + stored procedure [message #349857] Tue, 23 September 2008 05:45 Go to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Hi,

I want to write a procedure which will

Fetch N number of record from TableA one by one,
Insert that record it into another TableB, and
Delete the same record from TableA after inserting into TableB.


How do i do it?
Do i need to use cursor?
Can i use cursor inside a procedure?

Thanks & Regards,
Jigar Naik.
Re: fetch insert and delete record + stored procedure [message #349858 is a reply to message #349857] Tue, 23 September 2008 05:54 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Simple Insert and Delete statements.

why you need to write a procedure for this??

Regards,
Rajat
Re: fetch insert and delete record + stored procedure [message #349859 is a reply to message #349857] Tue, 23 September 2008 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you use SQL for this instead of choosing the slowest way you can find?

Regards
Michel
Re: fetch insert and delete record + stored procedure [message #349861 is a reply to message #349859] Tue, 23 September 2008 06:03 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Well, actually there will 3 Table Instead of TableA,
from all the three table which are almost of same structure,i will be inserting data into TableB,

The tableB will be containing one additional column, (may be table name) though which i can identify the source of data.
Re: fetch insert and delete record + stored procedure [message #349863 is a reply to message #349861] Tue, 23 September 2008 06:10 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

Well, actually there will 3 Table Instead of TableA,
from all the three table which are almost of same structure,i will be inserting data into TableB,

The tableB will be containing one additional column, (may be table name) though which i can identify the source of data.


Post what you actually tried and what you are trying to achieve.

Regards,
Rajat
Re: fetch insert and delete record + stored procedure [message #349871 is a reply to message #349863] Tue, 23 September 2008 06:31 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

all right, i thought actual problem will be little complex so i tried to make it simple.

bellow is the three table from which i will be reading & deleting records one by one,

SQL> DESC OUTBOUND_SMS;	
Column Name	Type
OUT_MSG_ID                     	NUMBER(20)
MOBILE_NO                      	VARCHAR2(15)
CONTENT                        	VARCHAR2(160)
MESSAGE_TYPE                   	VARCHAR2(30)
RECEIVER_NAME                  	VARCHAR2(1000)
RECEIVER_CATEGORY              	VARCHAR2(30)
WORKGROUP                      	VARCHAR2(30)
MESSAGE_CODE                   	NUMBER(10)
DNIS                           	NUMBER(15)
COMPLAINT_ID                   	VARCHAR2(30)
U_ID                           	VARCHAR2(20)
ENTRYTIME                      	TIMESTAMP(6)
	
	
SQL> DESC REPORTINCOMINGSMS;	
Column Name	Type
PKEY                   	NUMBER(21)
SMSID                  	VARCHAR2(20)
SENDERNUMBER           	VARCHAR2(20)
MESSAGE                	VARCHAR2(400)
DNIS                   	VARCHAR2(20)
RECEIVETIME            	TIMESTAMP(6)
STATUS                 	VARCHAR2(20)
	
	
SQL> DESC REPORTSMSRESPONSE;	
Column Name	Type
PKEY                   	NUMBER(10)
SMSID                  	VARCHAR2(20)
RESPONSEMESSAGE        	VARCHAR2(400)
CUSTOMERNO             	VARCHAR2(20)
SENTTIME               	TIMESTAMP(6)
RESPONSETYPE           	VARCHAR2(20)



and bellow is the table in which i want to insert records.

Column Name	Type
PKEY	NUMBER(20)
ID	NUMBER(21)
MOBILE_NO	NUMBER(20)
TEXT_MESSAGE	VARCHAR2(400)
DNIS	VARCHAR2(20)
RECEIVER_NAME	VARCHAR2(1000)
SMS_ID	VARCHAR2(20)
RECEIVER_CATEGORY	VARCHAR2(30)
WORKGROUP	VARCHAR2(30)
MESSAGE_CODE	NUMBER(10)
COMPLAINT_ID	VARCHAR2(30)
U_ID	VARCHAR2(20)
STATUS	VARCHAR2(20)
RESPONSE_TYPE	VARCHAR2(20)
ENTRY_TIME	TIMESTAMP(6)
TABLE_NAME	VARCHAR2(50)


[Updated on: Tue, 23 September 2008 07:02] by Moderator

Report message to a moderator

Re: fetch insert and delete record + stored procedure [message #349889 is a reply to message #349857] Tue, 23 September 2008 07:07 Go to previous messageGo to next message
chakradhar.adhikari
Messages: 5
Registered: September 2008
Junior Member
Below find one of the way to solve the issue.in This I considered t as main table and i insert into t2 from t as follows.Then delete the same from t

CREATE OR REPLACE PROCEDURE temp_pro
AS
BEGIN
FOR c IN (SELECT *
FROM t)
LOOP
INSERT INTO t2
(c1, c2, c3
)
VALUES (c.c1, c.c2, c.c3
);

DELETE FROM t
WHERE t.c1 = c.c1;
END LOOP;

COMMIT;
END;
Re: fetch insert and delete record + stored procedure [message #349895 is a reply to message #349889] Tue, 23 September 2008 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you still don't follow the OraFAQ Forum Guide.

Regards
Michel
Re: fetch insert and delete record + stored procedure [message #349897 is a reply to message #349895] Tue, 23 September 2008 07:33 Go to previous message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Thanks a lot all...

okey, will follow forum guide now onwards.
Previous Topic: problem with stored procedure and asp page.
Next Topic: Inserting default value during insert
Goto Forum:
  


Current Time: Sat Dec 03 22:09:09 CST 2016

Total time taken to generate the page: 0.32133 seconds