Home » SQL & PL/SQL » SQL & PL/SQL » generating table column values sequentially (oracle 10g)
generating table column values sequentially [message #398703] Fri, 17 April 2009 03:47 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,
i am doing like this
---------------------------USING SEQUENCE
CREATE SEQUENCE SEQ_MAIN INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 10000
/

CREATE TABLE EMRSEARCH (P_SEARCH_ID     NUMBER(10,0),
                        P_FIRST_NAME    VARCHAR2(30),
			P_LAST_NAME     VARCHAR2(30),
			P_DOB           DATE)
/


INSERT INTO EMRSEARCH (P_SEARCH_ID, P_FIRST_NAME,P_LAST_NAME,P_DOB) VALUES (SEQ_MAIN.NEXTVAL,'ROHIT','SHARMA','23-JUN-1986')
/
INSERT INTO EMRSEARCH (P_SEARCH_ID, P_FIRST_NAME,P_LAST_NAME,P_DOB) VALUES (SEQ_MAIN.NEXTVAL,'YUSUF','PATHAN','12-AUG-1982')
/
INSERT INTO EMRSEARCH (P_SEARCH_ID, P_FIRST_NAME,P_LAST_NAME,P_DOB) VALUES (SEQ_MAIN.NEXTVAL,'ANDREW','SYMONDS','30-NOV-1971')
/
INSERT INTO EMRSEARCH (P_SEARCH_ID, P_FIRST_NAME,P_LAST_NAME,P_DOB) VALUES (SEQ_MAIN.NEXTVAL,'DWAYNE','SMITH','30-NOV-1982')
/
INSERT INTO EMRSEARCH (P_SEARCH_ID, P_FIRST_NAME,P_LAST_NAME,P_DOB) VALUES (SEQ_MAIN.NEXTVAL, 'VVS',  'LAXMAN','31-JAN-1969')
/
INSERT INTO EMRSEARCH (P_SEARCH_ID, P_FIRST_NAME,P_LAST_NAME,P_DOB) VALUES (SEQ_MAIN.NEXTVAL, 'AJANTHA','MENDIS,'13-FEB-1981')
/
and i have also tried out using an anonymous block too but i an not getting proper results with this.can you tell me where exactly i am doing wrong
DECLARE
P1_FIRST_NAME VARCHAR2(30);
P1_LAST_NAME  VARCHAR2(30);
P1_DOB        DATE;
A INT;
BEGIN
	  FOR i IN 1 .. 10 LOOP
	  A:=i+1;
INSERT INTO EMRSEARCH (P_SEARCH_ID, P_FIRST_NAME,P_LAST_NAME,P_DOB) VALUES (SEQ_MAIN.NEXTVAL,P1_FIRST_NAME,P1_LAST_NAME,P1_DOB);
	 END LOOP;
END;
/



Re: generating table column values sequentially [message #398706 is a reply to message #398703] Fri, 17 April 2009 03:52 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
i an not getting proper results with this.can you tell me where exactly i am doing wrong



What were you expecting? What you did not get?

Also it has been said so many times here that '30-NOV-1982',
'23-JUN-1986' etc are all strings you need to explicitly convert using to_date() function.
Re: generating table column values sequentially [message #398710 is a reply to message #398706] Fri, 17 April 2009 03:55 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
no the thing is i dont want to insert all the times p_seacrh_id
giving 1,2,3-----------so on like that.based on that condition iam
using a sequence and anonymous block.
Re: generating table column values sequentially [message #398715 is a reply to message #398710] Fri, 17 April 2009 04:03 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I still could not understand your issue.
Re: generating table column values sequentially [message #398723 is a reply to message #398715] Fri, 17 April 2009 04:32 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi this is the one really looking for


CREATE OR REPLACE TRIGGER TRI_EMRSEARCH BEFORE INSERT ON EMRSEARCH
REFERENCING NEW AS n
FOR EACH ROW
DECLARE
	NEWVAL number;
BEGIN
	select SEQ_MAIN.NEXTVAL into NEWVAL from dual;
	:n.P_SEARCH_ID := NEWVAL;
END;
/
 

Re: generating table column values sequentially [message #398762 is a reply to message #398723] Fri, 17 April 2009 08:18 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Is this another question? An Answer? More info? Can you write a grammatically correct post so we know what on earth you want?
Previous Topic: Date format
Next Topic: Select query have issue.?
Goto Forum:
  


Current Time: Sat Dec 10 11:01:23 CST 2016

Total time taken to generate the page: 0.17832 seconds